BigQuery中基于自身值重置的累计求和实现问题

普通的SUM(value) OVER (PARTITION BY label ORDER BY dummy_sequence)只能实现同Label内的连续累计，无法满足「累计值达到阈值重置」的动态需求，这里用递归CTE来解决这个问题：

示例数据 #

先准备测试用的样本数据（模拟实际场景的序列、Label和数值）：

WITH sample_data AS (
  SELECT 'A' AS label, 3 AS value, 1 AS dummy_sequence UNION ALL
  SELECT 'A' AS label, 4 AS value, 2 AS dummy_sequence UNION ALL
  SELECT 'A' AS label, 5 AS value, 3 AS dummy_sequence UNION ALL
  SELECT 'B' AS label, 2 AS value, 4 AS dummy_sequence UNION ALL
  SELECT 'B' AS label, 6 AS value, 5 AS dummy_sequence UNION ALL
  SELECT 'B' AS label, 5 AS value, 6 AS dummy_sequence UNION ALL
  SELECT 'A' AS label, 3 AS value, 7 AS dummy_sequence
)

递归CTE实现逻辑 #

通过递归逐行判断重置条件，动态计算累计和：

, recursive_groups AS (
  -- 初始化：取全局第一条数据
  SELECT 
    label,
    value,
    dummy_sequence,
    value AS cumulative_sum,
    1 AS reset_group
  FROM sample_data
  WHERE dummy_sequence = (SELECT MIN(dummy_sequence) FROM sample_data)

  UNION ALL

  -- 递归处理后续每一行
  SELECT 
    sd.label,
    sd.value,
    sd.dummy_sequence,
    -- 双条件判断：Label变化 或 累计和+当前值≥阈值（10），则重置为当前值，否则累加
    CASE 
      WHEN sd.label != rg.label OR rg.cumulative_sum + sd.value >= 10 THEN sd.value
      ELSE rg.cumulative_sum + sd.value
    END AS cumulative_sum,
    -- 生成重置组编号，满足重置条件则组号+1，保持分组连续性
    CASE 
      WHEN sd.label != rg.label OR rg.cumulative_sum + sd.value >= 10 THEN rg.reset_group + 1
      ELSE rg.reset_group
    END AS reset_group
  FROM recursive_groups rg
  JOIN sample_data sd
    ON sd.dummy_sequence = rg.dummy_sequence + 1
)

-- 输出最终结果
SELECT label, value, dummy_sequence, cumulative_sum
FROM recursive_groups
ORDER BY dummy_sequence;

逻辑说明 #

1. 初始化阶段：取序列最开头的一行，初始化累计和为当前数值，重置组编号为1。
2. 递归阶段：逐行关联下一条数据，判断两个重置触发条件：
   • 当前行Label与上一行不同，直接重置累计和为当前值
   • 上一行累计和加上当前值达到/超过阈值（示例为10），重置累计和为当前值
3. 同时用reset_group字段标记每个独立的累计分组，后续如果需要基于分组做其他计算也可以直接使用这个字段。

这个方案能精准满足「Label变化重置」+「累计值达阈值重置」的双重需求，解决了普通窗口函数无法动态划分分区的问题。

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