BigQuery SQL:将列值转换为列名

Question

我想将所有键转换为列标题，将所有值转换为下面的一行中的相应平均值，按日期分组。

这是我的查询：

select 
substr(CAST((DATE) AS STRING),0,8) as daydate,
split(x,':')[safe_offset(0)] as key, 
cast(split(x,':')[safe_offset(1)] as float64) as value
from `gdelt-bq.gdeltv2.gkg_partitioned`, 
unnest(split(GCAM, ',')) as x
where _PARTITIONTIME BETWEEN TIMESTAMP('2019-02-02') AND TIMESTAMP('2019-02-03')

有没有办法在bigquery标准sql中做到这一点？

非常感谢

Answer 1

这取决于您有多少个不同的键？如果你谈论的是几千个(甚至几十个或数百个不同的键(，那么你的数据设计不是很理想。但是，如果它是确定的且可管理的，则可以创建一堆IF条件来将键转换为列。

假设您只有 3 个不同的键，那么您可以执行以下操作：

select 
daydate,
key,
sum(if(key = 'x', value, 0)) as val_x,
sum(if(key = 'y', value, 0)) as val_y,
sum(if(key = 'z', value, 0)) as val_z
from (
select 
substr(CAST((DATE) AS STRING),0,8) as daydate,
split(x,':')[safe_offset(0)] as key, 
avg(cast(split(x,':')[safe_offset(1)] as float64)) as value
from `gdelt-bq.gdeltv2.gkg_partitioned`, unnest(split(GCAM, ',')) as x
where _PARTITIONTIME BETWEEN TIMESTAMP('2019-02-02') AND TIMESTAMP('2019-02-03')
group by 1,2
)
group by 1,2

更新：您可以采用更强大，实用和更好的解决方案：

select 
substr(CAST((DATE) AS STRING),0,8) as daydate,
split(x,':')[safe_offset(0)] as key, 
avg(cast(split(x,':')[safe_offset(1)] as float64)) as value
from `gdelt-bq.gdeltv2.gkg_partitioned`, unnest(split(GCAM, ',')) as x
where _PARTITIONTIME BETWEEN TIMESTAMP('2019-02-02') AND TIMESTAMP('2019-02-03')
group by 1,2