带有嵌套字段的 bigquery 透视 [英] bigquery pivoting with nested field

问题描述

我有一个带有架构的 bigquery 表:

I have a bigquery table with schema:

我想要这样的结果:

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 中将多行聚合为一行?

I have tried same technic shown here, How to aggregate multiple rows into one in BigQuery?

但得到一个错误 SELECT 列表表达式引用列 flow_timestamp 既未在 [1:8] 处分组也未聚合

推荐答案

以下为 BigQuery Standard SQL

Below is for BigQuery Standard 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

这篇关于带有嵌套字段的 bigquery 透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！