使用BigQuery UNNEST函数转换指定表结构的正确SQL代码是什么？

实现思路 #

源表中event_params为数组类型字段，每个元素包含key、string_value、int_value三个属性。我们需要：

• 用UNNEST展开event_params数组，将嵌套元素转为独立行
• 提取每个key对应的有效数值（同一key只会在string_value或int_value中存在一个值）
• 通过聚合或PIVOT将不同key值转为列，最终按user_id合并得到目标宽表

简洁版SQL代码 #

WITH unnested_data AS (
  SELECT
    user_id,
    event_params.key,
    -- 统一提取有效值，如需保留数值类型可单独处理
    COALESCE(event_params.string_value, CAST(event_params.int_value AS STRING)) AS param_value
  FROM
    `your_source_table`, -- 替换为你的源表名称
    UNNEST(event_params) AS event_params
)
SELECT
  user_id,
  MAX(IF(key = 'event_label', param_value, NULL)) AS event_label,
  MAX(IF(key = 'event_category', param_value, NULL)) AS event_category
FROM unnested_data
GROUP BY user_id;

使用PIVOT语法的实现 #

SELECT
  user_id,
  event_label,
  -- 如需保留数值类型，转换为INT64
  CAST(event_category AS INT64) AS event_category
FROM (
  SELECT
    user_id,
    event_params.key,
    COALESCE(event_params.string_value, CAST(event_params.int_value AS STRING)) AS param_value
  FROM
    `your_source_table`, -- 替换为你的源表名称
    UNNEST(event_params) AS event_params
)
PIVOT (
  MAX(param_value) FOR key IN ('event_label', 'event_category')
);

说明 #

• 请将代码中的your_source_table替换为实际源表名称
• 聚合函数使用MAX是为了确保每个user_id下同一key只保留唯一有效值
• 如果需要严格区分字段类型，可针对event_label和event_category分别提取对应类型的值

内容的提问来源于stack exchange，提问作者Smasell