如何将BigQuery中Firebase导出数据扁平化 实现每个参数键对应单独列

方案1：手动指定列（性能最优、适合固定键场景） #

你已经用到的UNNEST可以配合子查询或者条件聚合直接实现键转列，不需要额外函数：

无聚合写法（保留原表行数） #

如果不需要合并行，直接用子查询从嵌套字段中取对应值即可：

SELECT
  -- 保留原有字段
  event_name,
  event_timestamp,
  user_pseudo_id,
  -- 逐个提取指定键作为单独列，根据值类型选择int_value/string_value/double_value
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'firebase_screen_id') AS firebase_screen_id,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'board') AS board
FROM `你的项目ID.你的数据集名.你的Firebase导出表名`

聚合写法（适合分组统计场景） #

如果需要分组汇总，配合MAX+IF实现：

SELECT
  event_name,
  user_pseudo_id,
  MAX(IF(param.key = 'firebase_screen_id', param.value.int_value, NULL)) AS firebase_screen_id,
  MAX(IF(param.key = 'board', param.value.string_value, NULL)) AS board
FROM `你的项目ID.你的数据集名.你的Firebase导出表名`,
UNNEST(event_params) AS param
GROUP BY event_name, user_pseudo_id -- GROUP BY项包含所有非聚合的原有字段

方案2：动态生成列（适合键多、键不固定场景） #

如果需要提取的键数量多，手动写效率低，可以用BigQuery的EXECUTE IMMEDIATE自动生成查询语句批量提取所有键：

EXECUTE IMMEDIATE format("""
SELECT
  event_name,
  event_timestamp,
  user_pseudo_id,
  %s
FROM `你的项目ID.你的数据集名.你的Firebase导出表名`
""",
(
  SELECT STRING_AGG(
    "(SELECT value." || value_type || " FROM UNNEST(event_params) WHERE key = '" || key || "') AS " || key
  )
  FROM (
    -- 先查询当前表中所有存在的参数键和对应的值类型
    SELECT DISTINCT
      param.key,
      CASE
        WHEN param.value.int_value IS NOT NULL THEN 'int_value'
        WHEN param.value.string_value IS NOT NULL THEN 'string_value'
        WHEN param.value.double_value IS NOT NULL THEN 'double_value'
      END AS value_type
    FROM `你的项目ID.你的数据集名.你的Firebase导出表名`,
    UNNEST(event_params) AS param
  )
))

通用说明 #

• 如果需要扁平化的是user_properties字段，把上述代码中的event_params替换为user_properties即可，逻辑完全一致
• 如果存在同一个键有多种值类型的情况，优先保留你需要的类型，或者增加类型判断逻辑处理冲突

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