在BigQuery/SQL中实现带条件重置的递归累加计数器

核心思路 #

要实现当C列为TRUE时重置计数器为1，否则累加当前val与前一行output值的需求，关键是先按C列的重置点将数据分割为独立分组，再在每个分组内完成累加计算。窗口函数方案比递归CTE更高效，适合大数据量场景；递归CTE则更直观，适合理解逻辑。

方案1：窗口函数实现（推荐） #

通过生成分组ID标记每个重置区间，再在分组内计算累加值：

WITH grouped_data AS (
  SELECT
    *,
    -- 每次C为TRUE时生成新的分组ID
    SUM(CASE WHEN C THEN 1 ELSE 0 END) OVER (ORDER BY id) AS group_id
  FROM your_table -- 替换为你的表名
),
grouped_rows AS (
  SELECT
    *,
    -- 标记分组内的行号
    ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY id) AS row_in_group
  FROM grouped_data
)
SELECT
  id,
  val,
  C,
  -- 分组第一行（重置点）output为1，后续行累加val
  1 + SUM(CASE WHEN row_in_group > 1 THEN val ELSE 0 END) OVER (PARTITION BY group_id ORDER BY id) AS output
FROM grouped_rows
ORDER BY id;

代码说明：

1. grouped_data：用累计求和生成分组ID，每遇到C=TRUE就新增一个分组。
2. grouped_rows：标记每个分组内的行顺序，区分重置点（行号1）和后续行。
3. 最终计算：重置点直接返回1，后续行用SUM窗口函数累加分组内从第二行开始的val，再加上初始值1。

方案2：递归CTE实现 #

如果更偏好递归逻辑，可通过递归逐行计算，注意先给数据生成连续排序序号（避免非连续ID导致的错误）：

WITH numbered_data AS (
  SELECT
    *,
    -- 生成连续排序号（替代可能非连续的id）
    ROW_NUMBER() OVER (ORDER BY id) AS rn
  FROM your_table -- 替换为你的表名
),
RECURSIVE counter_cte AS (
  -- 锚点：取第一行，重置点返回1，否则返回val（若第一行不是重置点）
  SELECT
    rn,
    id,
    val,
    C,
    CASE WHEN C THEN 1 ELSE val END AS output
  FROM numbered_data
  WHERE rn = 1
  
  UNION ALL
  
  -- 递归：逐行计算，遇到重置点则重置为1，否则累加前一行output与当前val
  SELECT
    t.rn,
    t.id,
    t.val,
    t.C,
    CASE
      WHEN t.C THEN 1
      ELSE t.val + c.output
    END AS output
  FROM numbered_data t
  JOIN counter_cte c ON t.rn = c.rn + 1
)
SELECT id, val, C, output FROM counter_cte ORDER BY rn;

测试示例 #

假设测试数据：

执行上述方案后，预期输出：

常见问题排查 #

你之前尝试失败的原因通常是：

• 递归CTE未正确处理排序逻辑（比如用非连续ID关联），导致行顺序混乱。
• 窗口函数未按重置点分组，累加时未截断，导致跨分组持续累加。

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