在这种情况下，如何在不手动列出所有类别的情况下对表（BigQuery）进行透视？

Question

我有一个表，其架构大致如下：

user_id | segment_id | day

segment_id 的值范围不小：从 1 到 70。 对于天，它是 0-2。

理想情况下，我想将此表转换为

user_id | segment_1_day_1_count | segment_2_day_1_count | ... segment_70_day_1_count | ... | segment_70_day_3_count | ... segment_1_count | segment_2_count | segment_3_count | day_1_count | day_2_count | day_3_count | total_count

粗略地说，对于每个 user_id 我想获取所有细分组合的计数：

• 按分段
• 按天
• 按分段和按天
• 总计

这看起来非常像旋转，但是我不确定是否有一种方法可以基于多个列进行旋转。

我当前的尝试如下：

SELECT
  user_id,
  segment_id,
  day,
  COUNT(*) OVER (PARTITION BY user_id, segment_id),
  COUNT(*) OVER (PARTITION BY user_id, day),
  COUNT(*) OVER (PARTITION BY user_id, segment_id, day),
  COUNT(*) OVER (PARTITION BY user_id)
FROM some_table

这是我需要的数据，但不是所需的格式。

Answer 1

请考虑以下方法

execute immediate (select '''select * from your_table 
pivot (count(*) for 'segment_' || segment_id || '_day_' || day || '_count' in (''' || 
string_agg('"segment_' || segment_id || '_day_' || day || '_count"', ',' order by day, segment_id) || '))'
from (select distinct segment_id from your_table),
(select distinct day from your_table));              

如果应用于类似于您所描述的虚拟数据，

- 输出为

Answer 2

是否还可以分别添加段、天和总计的计数？

当然。只是[相对]简单地扩展我最初提出的解决方案，

execute immediate (select '''
  select * from (
    select * 
    from your_table 
    pivot (count(*) for 'segment_' || segment_id || '_day_' || day || '_count' in (''' || list1 || '''))
  ) 
  join (
    select * 
    from (select * except(day) from your_table) 
    pivot (count(*) for 'segment_' || segment_id || '_count' in (''' || list2 || '''))
  )
  using(user_id)
  join (
    select * 
    from (select * except(segment_id) from your_table) 
    pivot (count(*) for 'day_' || day || '_count' in (''' || list3 || '''))
  )
  using(user_id)
  join (
    select user_id, count(*) total 
    from your_table
    group by user_id
  )
  using(user_id)
'''
from (
  select string_agg('"segment_' || segment_id || '_day_' || day || '_count"', ',' order by day, segment_id) list1
  from (select distinct segment_id from your_table), (select distinct day from your_table)
),(
  select string_agg('"segment_' || segment_id || '_count"', ',' order by segment_id) list2
  from (select distinct segment_id from your_table)
),(
  select string_agg('"day_' || day || '_count"', ',' order by day) list3
  from (select distinct day from your_table)
) 
)   

如果应用于类似于您所描述的虚拟数据 - 输出为