Google BigQuery查询资源超限：如何拆分带分区的大SQL窗口框架？

核心逻辑 #

全局窗口（无PARTITION BY）会将全量数据加载到单个Slot，10亿行规模必然触发内存溢出。解决思路是先按时间维度预聚合压缩数据，再基于小数据集计算窗口函数，最后关联回原始数据，把大计算拆分成可并行的小任务，分散到多个Slot执行。

1. 预聚合时间维度统计值 #

先按time分组计算每个时间点的总value，将10亿行压缩到时间点量级（比如按天的话仅几千/几万行）：

WITH time_agg AS (
  SELECT
    time,
    SUM(value) AS daily_total
  FROM your_table -- 替换为实际大表名称
  GROUP BY time
),

2. 在预聚合数据上计算全局窗口函数 #

基于压缩后的time_agg计算各类滚动求和，此时数据量极小，内存压力可忽略：

window_calculations AS (
  SELECT
    time,
    -- 全局累计总和
    SUM(daily_total) OVER (ORDER BY time RANGE UNBOUNDED PRECEDING) AS total,
    -- 最近1天（含当前）的总和
    SUM(daily_total) OVER (ORDER BY time RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) AS total_last_day,
    -- 3天前到2天前的总和
    SUM(daily_total) OVER (ORDER BY time RANGE BETWEEN 3 PRECEDING AND 2 PRECEDING) AS total_prev_day
  FROM time_agg
)

3. 关联回原始数据 #

将计算好的窗口结果与原始表关联，得到每一行的最终输出：

SELECT
  t.id,
  t.value,
  t.type,
  t.time,
  wc.total,
  wc.total_last_day,
  wc.total_prev_day
FROM your_table t
JOIN window_calculations wc
ON t.time = wc.time
ORDER BY t.time;

验证用完整测试查询 #

替换为你提供的示例数据，可直接运行验证结果与原查询一致：

WITH data AS (
  SELECT * 
  FROM UNNEST([
    STRUCT
    ('A' as id,1 as value, 'out' as type, 1 as time), 
    ('A', -1, 'in', 2),
    ('B', 2, 'out', 2),
    ('C', 1, 'out', 3),
    ('B', -1, 'in', 4),
    ('A', 2, 'out', 4),
    ('C', 5, 'out', 5),
    ('B', 3, 'out', 6),
    ('A', 1, 'out', 6),
    ('A', -4, 'in', 6),
    ('C', -3, 'in', 7)
  ])
),
time_agg AS (
  SELECT
    time,
    SUM(value) AS daily_total
  FROM data
  GROUP BY time
),
window_calculations AS (
  SELECT
    time,
    SUM(daily_total) OVER (ORDER BY time RANGE UNBOUNDED PRECEDING) AS total,
    SUM(daily_total) OVER (ORDER BY time RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) AS total_last_day,
    SUM(daily_total) OVER (ORDER BY time RANGE BETWEEN 3 PRECEDING AND 2 PRECEDING) AS total_prev_day
  FROM time_agg
)
SELECT
  t.id,
  t.value,
  t.type,
  t.time,
  wc.total,
  wc.total_last_day,
  wc.total_prev_day
FROM data t
JOIN window_calculations wc
ON t.time = wc.time
ORDER BY t.time;

补充：含按id分区的窗口计算 #

如果需要同时计算每个id独立的滚动求和，可扩展为按id+time预聚合，再分区计算：

WITH data AS (
  -- 示例数据同前
),
-- 全局时间聚合
time_agg AS (
  SELECT
    time,
    SUM(value) AS daily_total
  FROM data
  GROUP BY time
),
window_calculations AS (
  SELECT
    time,
    SUM(daily_total) OVER (ORDER BY time RANGE UNBOUNDED PRECEDING) AS total,
    SUM(daily_total) OVER (ORDER BY time RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) AS total_last_day,
    SUM(daily_total) OVER (ORDER BY time RANGE BETWEEN 3 PRECEDING AND 2 PRECEDING) AS total_prev_day
  FROM time_agg
),
-- 按id+time聚合
id_time_agg AS (
  SELECT
    id,
    time,
    SUM(value) AS id_daily_total
  FROM data
  GROUP BY id, time
),
-- 按id分区的窗口计算
id_window_calculations AS (
  SELECT
    id,
    time,
    SUM(id_daily_total) OVER (PARTITION BY id ORDER BY time RANGE UNBOUNDED PRECEDING) AS id_total
  FROM id_time_agg
)
-- 合并全局与id分区的结果
SELECT
  t.id,
  t.value,
  t.type,
  t.time,
  wc.total,
  wc.total_last_day,
  wc.total_prev_day,
  iwc.id_total
FROM data t
JOIN window_calculations wc ON t.time = wc.time
JOIN id_window_calculations iwc ON t.id = iwc.id AND t.time = iwc.time
ORDER BY t.time;

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