BigQuery中基于另一列最大值取对应列值并聚合的实现方法

BigQuery 原生支持通过数组聚合函数直接实现需求，不需要写多层子查询+自关联的冗余逻辑，单条分组查询即可完成计算。

核心实现逻辑 #

按account字段分组后，分别完成两个聚合计算：

• 对gross_revenue字段直接用SUM函数求和
• 对account_type字段，按original_valid_to倒序排序后取分组内第一条值，即可得到original_valid_to最大值对应的账户类型

参考SQL代码 #

SELECT
  account,
  ARRAY_AGG(account_type ORDER BY original_valid_to DESC LIMIT 1)[OFFSET(0)] AS account_type,
  SUM(gross_revenue) AS gross_revenue
FROM `你的项目.你的数据集.你的业务表`
GROUP BY account

注意事项 #

• 如果同一个账户下存在多条记录的original_valid_to值并列最大，可以在ORDER BY后补充二级排序规则（比如记录入库时间倒序、主键倒序），保证返回结果稳定
• 该写法扩展成本极低，如果后续需要取最大original_valid_to对应的其他字段值，只需要新增对应字段的ARRAY_AGG聚合逻辑即可，不需要调整查询整体结构
• 相比「子查询先算每个账户的max(original_valid_to)再回表关联原表取属性」的传统写法，该方案只需要一次聚合计算，查询性能更优

内容的提问来源于stack exchange，提问作者T.B.