Google BigQuery窗口函数排序引发内存错误的问题咨询

问题场景 #

基础表（超10亿行）包含userID、balance_increment（每日余额增量）、date（日期）字段，执行以下SQL计算累计余额及下一次增量日期时触发内存错误：

select 
    userID
    ,   date
    ,   sum(balance_increment) over (partition by userID order by date) as balance
    ,   lead(date, 1, current_date()) over (partition by userID order by date) as next_date
from my_base_table

错误信息：

BadRequest: 400 Resources exceeded during query execution: The query could not be executed in the allotted memory. Peak usage: 135% of limit.
Top memory consumer(s):
sort operations used for analytic OVER() clauses: 98%
other/unattributed: 2%

补充信息：

• 单个userID最多出现不足4000次，userID总数超3100万
• 统计userID出现次数的SQL可正常运行：

SELECT
  userID
  , count(*) as userID_count
FROM my_base_table
GROUP BY userID
ORDER BY userID_count DESC

疑问解答 #

1. 内存错误是否确实由order by date导致？ #

是的。错误日志明确指出98%的内存消耗来自分析函数OVER()子句中的排序操作，两个窗口函数均指定了order by date，排序是内存占用的直接诱因。

2. 已使用partition by且单分区数据不足4000条，为何排序仍引发内存问题？ #

虽然单分区数据量不大，但userID总数超过3100万，BigQuery执行模型中单个worker可能需要同时处理大量分区的排序任务。每个分区的排序都需要将对应数据加载到内存，当worker分配到的分区数量过多时，累加的内存占用会超过限制。此外，数据分布不均匀时，部分worker可能被分配到更多或更大的分区，进一步加剧内存溢出风险。

3. 为何统计userID次数的查询需排序3100万行却能正常执行？ #

两者的排序量级和场景完全不同：

• 统计查询的排序是在GROUP BY之后，仅对3100万条聚合结果做全局排序，数据量远小于窗口函数的处理规模。
• 窗口函数的排序是对每个userID分区内部的行排序，总排序操作覆盖全表10亿行，每个分区的内存开销累加后，远高于前者的单轮全局排序。

4. 如何解决该内存错误问题？ #

可尝试以下几种方案：

• 优化表结构：将表按userID聚类（CLUSTER BY userID），让同一userID的行物理存储在一起，减少排序时的数据 shuffle，降低内存开销。
• 拆分窗口函数：将两个窗口函数拆分为两个执行步骤，先计算累计余额，再计算next_date，避免同时执行两个排序操作带来的内存叠加。
• 调整资源配置：查询时设置更高优先级，或申请更多slot资源（按需付费/预留slot场景），让BigQuery分配更多内存用于排序。
• 预处理替代实时计算：如果业务允许，定期离线计算用户余额并存储到专用表，避免直接在10亿行大表上执行窗口函数。

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