更改 BigQuery 中的表结构

蒂莫·里特维尔德

假设有以下查询（在本文中称为“基本查询”）：

#standardSQL
SELECT '1' AS session_id, 'product_view' AS event_name, STRUCT(['country','environment'] AS parameter_name, ['NL','production'] AS parameter_value) AS event_details
UNION ALL
SELECT '1' AS session_id, 'lister_view' AS event_name, STRUCT(['country','environment','page_type'] AS parameter_name, ['DE','staging','lister'] AS parameter_value) AS event_details
UNION ALL
SELECT '2' AS session_id, 'product_view' AS event_name, STRUCT(['country','environment'] AS parameter_name, ['DE','production'] AS parameter_value) AS event_details
UNION ALL
SELECT '2' AS session_id, 'checkout_view' AS event_name, STRUCT(['country','environment','page_type'] AS parameter_name, ['GB','production','checkout'] AS parameter_value) AS event_details
UNION ALL
SELECT '2' AS session_id, 'checkout_view' AS event_name, STRUCT(['country','environment'] AS parameter_name, ['NL','staging'] AS parameter_value) AS event_details

现在，我想稍微更改此表的结构，以便 event_name 列成为每个 session_id 的嵌套字段。我为此做了以下查询：

#standardSQL
SELECT session_id, ANY_VALUE(event_name_nested) AS event_name_nested FROM (
SELECT session_id, ARRAY_AGG(event_name) OVER (PARTITION BY session_id) AS event_name_nested FROM (    
[base query, please see above]    
))
GROUP BY session_id,FORMAT('%T', event_name_nested)

但是，在这个重新定义的结构中，我还想为每个事件添加名为“event_details”的 STRUCT（有关详细信息，请参阅上面的基本查询）。有谁知道如何做到这一点？

米哈伊尔·贝里安特

在下面使用（对原始查询进行最少的更改）

#standardSQL
SELECT session_id, 
  ANY_VALUE(events_nested) AS events_nested 
FROM (
  SELECT session_id, ARRAY_AGG(struct(event_name, event_details)) OVER (PARTITION BY session_id) AS events_nested
  FROM base_query
)
GROUP BY session_id

输出是