BigQuery中如何高效对repeated record嵌套字段计算聚合值

核心思路 #

不需要将REPEATED字段全局UNNEST后再分组聚合，直接在SELECT子句中使用标量子查询逐行处理当前行的嵌套数组，全程保留原表行数，不会产生海量中间行，算力成本远低于全局UNNEST方案。

实现代码 #

假设你的表路径为your_project.your_dataset.your_table，对应需求的查询语句如下：

SELECT
  outer_1,
  outer_2,
  -- 计算inner_1非空值的和
  (SELECT SUM(elem.inner_1) FROM UNNEST(inner) elem WHERE elem.inner_1 IS NOT NULL) AS inner_1_sum,
  -- 计算inner_2非空值的计数（COUNT会自动忽略NULL，不需要额外加过滤条件）
  (SELECT COUNT(elem.inner_2) FROM UNNEST(inner) elem) AS inner_2_non_null_count
FROM
  `your_project.your_dataset.your_table`

性能优势说明 #

• 原有全局UNNEST方案会将1行原数据拆分为inner数组长度对应的N行中间数据，100万行原数据如果平均每个inner包含10个元素，会生成1000万行中间数据，还要额外做GROUP BY操作，开销极高。
• 上述方案仅在每行内部UNNEST当前行的inner数组做聚合计算，全程保留原表100万行的规模，没有额外全局分组开销，性能提升非常显著。

其他常用扩展写法 #

你可以根据需求灵活修改子查询内的聚合逻辑，例如：

• 求inner_1的最大值：(SELECT MAX(elem.inner_1) FROM UNNEST(inner) elem)
• 求inner_2为true的行数：(SELECT COUNTIF(elem.inner_2 = TRUE) FROM UNNEST(inner) elem)
• 求inner_1的平均值：(SELECT AVG(elem.inner_1) FROM UNNEST(inner) elem WHERE elem.inner_1 IS NOT NULL)

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