SQL实现按flag条件启停重置的跨行累计计数

需求说明 #

现有业务表包含id、date、flag三个字段，按id分组、date升序排序后，flag字段取值为0或1交替出现。需要新增计算字段count，严格遵循以下规则计数：

• 当行的flag值为0时，count取值为0
• 当行的flag值为1时，在连续为1的区间内从1开始逐行递增计数
• 遇到flag=0时计数立即重置为0，后续再次出现flag=1时重新从1开始累计，循环执行
  最终查询结果需完全匹配上述计数规则，覆盖所有测试样例。

实现思路 #

这类分组内连续同值段的计数问题，核心是先完成连续段的识别：

• 按id分区、date升序排序，累计计算flag值相对于上一行的切换次数，每一段连续相同的flag值会对应同一个累计值，作为连续段的唯一标识
• 基于连续段标识做二次计算：flag=0的行直接返回0；flag=1的连续段内，按日期升序生成从1开始的行号，即为要求的累计计数值

代码实现 #

以下代码兼容所有支持标准窗口函数的SQL引擎（MySQL 8.0+、PostgreSQL、Hive、Spark SQL等），假设源表名为t_flag：

WITH seg_mark AS (
    SELECT
        id,
        date,
        flag,
        -- 生成连续同flag段的唯一标识：flag发生切换时标识值+1
        SUM(
            CASE 
                WHEN flag = LAG(flag, 1, -1) OVER (PARTITION BY id ORDER BY date) THEN 0 
                ELSE 1 
            END
        ) OVER (PARTITION BY id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS seg_id
    FROM t_flag
)
SELECT
    id,
    date,
    flag,
    CASE
        WHEN flag = 0 THEN 0
        ELSE ROW_NUMBER() OVER (PARTITION BY id, seg_id ORDER BY date)
    END AS `count`
FROM seg_mark
ORDER BY id, date;

效果验证 #

以部分测试数据为例，查询输出结果如下，完全符合计数规则：

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