我有一个带有架构的 bigquery 表:
我想要这样的结果:
flow_timestamp, channel_name, number_of_digits
2019-10-31 15:31:15, channel_name_1, 3,
2019-10-31 15:31:15, channel_name_2, 4,
:
:
我的查询:SELECT flow_timestamp, timeseries.channel_name, MAX(IF(channel_properties.key = 'number_of_digits', channel_properties.value, NULL)) AS number_of_digits FROM my_table , unnest(timeseries.channel_properties) as channel_properties
我尝试过这里显示的相同技术,如何在 BigQuery 中将多行聚合为一行? https://stackoverflow.com/questions/35789156/how-to-aggregate-multiple-rows-into-one-in-bigquery#
但出现错误SELECT list expression references column flow_timestamp which is neither grouped nor aggregated at [1:8]
以下是 BigQuery 标准 SQL
#standardSQL
SELECT
flow_timestamp,
timeseries.channel_name,
( SELECT MAX(IF(channel_properties.key = 'number_of_digits', channel_properties.value, NULL))
FROM UNNEST(timeseries.channel_properties) AS channel_properties
) AS number_of_digits
FROM my_table
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)