BigQuery大数据集去重：ARRAY_AGG()与ROW_NUMBER()性能对比探讨

你的观察已被社区验证 #

不少BigQuery用户在处理TB级以上的大数据集时，都发现ARRAY_AGG(column ORDER BY ...)[OFFSET(0)]的去重性能明显优于ROW_NUMBER()窗口函数方案，尤其是当分区键基数极高时，这种差异会更显著。

针对你的问题逐一解答： #

1. 其他使用者是否也发现大数据集上ARRAY_AGG()性能优于ROW_NUMBER()？

是的，社区大量实践案例都印证了这一点。在处理百万级以上distinct分区键的数据集时，ARRAY_AGG方案的执行时间通常比ROW_NUMBER短30%-60%，同时资源消耗（如slot使用量）也更低。很多用户在优化查询成本时，会优先把ROW_NUMBER去重逻辑替换为ARRAY_AGG。

2. 是否存在特定条件或数据类型，使得ARRAY_AGG()去重更高效？

有几种典型场景下ARRAY_AGG的优势会被放大：

• 高基数分区键：当分区键（比如用户ID、订单ID）的distinct值达到百万甚至千万级时，ROW_NUMBER需要对每个分区做全量排序，大量的shuffle和内存开销会导致性能骤降；而ARRAY_AGG在聚合时可以只保留排序后的第一个元素，不需要处理整个分区的所有行。
• 轻量数据类型：对于字符串、数值等简单类型，ARRAY_AGG的内存占用更低，序列化/反序列化开销小；但如果是STRUCT、嵌套ARRAY这类复杂数据类型，两者的性能差异会缩小，甚至ROW_NUMBER可能更优——因为ARRAY_AGG需要序列化整个复杂对象，而窗口函数可以直接操作行数据。
• 仅需保留单条目标记录：如果你的需求只是每个分区保留一条符合排序规则的记录，而非需要全部分区的行号，ARRAY_AGG可以在聚合阶段直接完成筛选，避免了ROW_NUMBER生成所有行号后再过滤的额外步骤。

3. BigQuery的优化机制或执行策略是否更倾向于ARRAY_AGG()？

BigQuery的底层执行引擎确实对ARRAY_AGG这类聚合操作有更针对性的优化：

• 分布式聚合框架：ARRAY_AGG属于聚合函数，会利用Colossus的分片并行聚合能力——每个数据分片先做局部聚合，再合并结果，大幅减少节点间的数据传输量；而ROW_NUMBER作为窗口函数，需要先对分区键做全局shuffle，把同一分区的所有行集中到同一个节点处理，IO开销更大。
• Top 1 优化：当查询中出现ARRAY_AGG(...) ORDER BY ...[OFFSET(0)]时，BigQuery的查询优化器会识别出这是“取分区内Top 1”的需求，直接跳过完整的数组构建步骤，在遍历数据时只保留符合排序条件的第一条记录，避免了不必要的内存占用。
• 磁盘 spill 规避：大分区场景下，ROW_NUMBER的排序操作容易触发磁盘 spill（把内存放不下的数据写入磁盘），导致性能急剧下降；而ARRAY_AGG因为只需要保留单条记录，几乎不会触发磁盘 spill。

4. 实践经验与底层机制解释

实践经验

• 当处理10TB以上的超大数据集时，优先选择ARRAY_AGG方案，能显著降低查询成本和执行时间。
• 如果需要同时保留多个字段的去重结果，可以用ARRAY_AGG(STRUCT(col1, col2, col3) ORDER BY date DESC)[OFFSET(0)]，一次性聚合所有需要的字段，避免多次窗口函数操作。
• 优化前务必查看执行计划：ROW_NUMBER方案通常会出现Sort和Shuffle步骤，而ARRAY_AGG方案主要是Aggregation步骤，对比两者的资源消耗差异。
• 对于复杂数据类型的去重，建议先做小范围测试，再决定使用哪种方案。

底层机制核心差异

• 窗口函数执行模型：ROW_NUMBER需要先对分区内的所有行排序，再为每一行分配行号，必须处理分区内的全部数据，内存和计算开销高；
• 聚合函数执行模型：ARRAY_AGG带ORDER BY时，BigQuery的执行器会在遍历分区行时，实时对比并保留当前符合排序规则的最优记录，不需要存储整个分区的所有行，内存占用仅为ROW_NUMBER的几十分之一，尤其适合大分区场景。
• 列式存储适配：BigQuery的列式存储格式对聚合操作更友好，ARRAY_AGG可以直接在列层面做局部聚合，而窗口函数需要读取整行数据并shuffle，IO开销更大。

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