BigQuery重复字段聚合：如何按clientid分组统计嵌套数组metric.value汇总

你给出的结构里，m.metric属于数组类型字段，每个元素包含key和value属性，按clientid分组聚合value可以通过以下两种常用方式实现：

方式1：先拆分数组再分组聚合 #

如果需要对所有value做跨数组的全局聚合，可以先用UNNEST将数组拆分为单行数据，再做分组计算：

SELECT
  clientid,
  SUM(metric.value) AS total_value, -- 求和，可替换为AVG/MAX/COUNT等聚合函数
  AVG(metric.value) AS avg_value,
  MAX(metric.value) AS max_value
FROM
  `你的项目名.你的数据集名.你的表名`,
  UNNEST(m.metric) AS metric
GROUP BY
  clientid

方式2：子查询内处理数组（保留原行结构） #

如果不需要拆分原表行，可以直接在子查询中处理每行的数组，同一个clientid有多行记录时，先合并所有数组再聚合：

SELECT
  clientid,
  -- 先合并同clientid的所有metric数组，再计算value总和
  (SELECT SUM(value) FROM UNNEST(ARRAY_CONCAT_AGG(m.metric))) AS total_value
FROM
  `你的项目名.你的数据集名.你的表名`
GROUP BY
  clientid

注意事项 #

• 聚合函数默认忽略value为NULL的项，需要统计NULL项时可以用IFNULL(metric.value, 自定义默认值)做预处理
• 仅需要统计满足特定key的value时，可以在UNNEST后加WHERE条件过滤，比如WHERE metric.key = 10

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