BigQuery如何扁平化多个不同长度重复列，处理GA同步数据展开问题

直接提供可复用、易扩展的最简实现代码：

-- standardSQL 按索引对齐扁平化，无数据爆炸
SELECT
  -- 保留所有非重复基础字段
  event_date,
  event_timestamp,
  event_name,
  user_ltv,
  device,
  geo,
  app_info,
  traffic_source,
  event_dimensions,
  ecommerce,
  -- 按索引取重复列值，短数组自动补null
  event_params[SAFE_ORDINAL(idx)] AS event_params,
  user_properties[SAFE_ORDINAL(idx)] AS user_properties,
  items[SAFE_ORDINAL(idx)] AS items -- 按需新增其他重复列即可
FROM 
  `yepic-2021.analytics_264796885.events_intraday_*`,
  -- 生成覆盖所有重复列最大长度的序数数组
  UNNEST(GENERATE_ARRAY(1, GREATEST(
    ARRAY_LENGTH(event_params),
    ARRAY_LENGTH(user_properties),
    ARRAY_LENGTH(items) -- 新增重复列时同步在这里添加长度计算
  ))) AS idx
ORDER BY event_timestamp DESC

方案说明 #

• 无需额外CTE、UUID行标识字段，代码逻辑极简，执行性能更优
• 用SAFE_ORDINAL取值，长度不足的重复列会自动返回null，完全避免数组越界报错
• 不会产生多REPEATED字段直接UNNEST的笛卡尔积数据爆炸问题，每个索引仅生成1行数据
• 扩展性极强：新增其他重复列时仅需要修改两处：SELECT中新增对应列的取值逻辑、GREATEST函数中新增对应列的长度计算即可

如果需要进一步拆分嵌套RECORD的字段为独立列（比如提取event_params的键值对），可参考扩展写法：

-- 扩展：拆分event_params、user_properties为独立列
SELECT
  event_date,
  event_timestamp,
  event_name,
  -- 提取event_params键值
  event_params[SAFE_ORDINAL(idx)].key AS ep_key,
  event_params[SAFE_ORDINAL(idx)].value.string_value AS ep_string_val,
  event_params[SAFE_ORDINAL(idx)].value.int_value AS ep_int_val,
  -- 提取user_properties键值
  user_properties[SAFE_ORDINAL(idx)].key AS up_key,
  user_properties[SAFE_ORDINAL(idx)].value.string_value AS up_string_val
FROM 
  `yepic-2021.analytics_264796885.events_intraday_*`,
  UNNEST(GENERATE_ARRAY(1, GREATEST(ARRAY_LENGTH(event_params), ARRAY_LENGTH(user_properties)))) AS idx

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