如何实现状态变更时重置的Running Total（累计计数）？

问题 #

需要编写SQL查询，为用户表生成基于特定状态的累计计数（Running Total），当用户状态发生变更时，累计计数需重置。常规累计计数脚本无法实现用户回到目标状态时计数从1重新开始的需求。

当前用户表结构及数据 #

期望结果 #

尝试的无效代码 #

select *
    , sum(t0.working_flag)
    OVER (PARTITION BY (t0.status_desc)
         ORDER BY t0.date asc)
from (select *
    , case when status_desc = 'working' then '1'
    else 0
    end as working_flag
from table) t0
order by date desc

解决方案 #

核心思路是为每个用户的连续同状态区间生成唯一分组标识，再基于该分组计算累计值，状态变更时分组ID更新，实现计数重置。

完整SQL代码 #

WITH user_status_change AS (
    SELECT 
        `用户`,
        `状态`,
        `日期`,
        `状态描述`,
        -- 标记当前行与上一行状态是否发生变更
        CASE 
            WHEN LAG(`状态描述`) OVER (PARTITION BY `用户` ORDER BY `日期` ASC) != `状态描述` 
            THEN 1 
            ELSE 0 
        END AS status_change_flag
    FROM your_table_name  -- 替换为实际表名
),
status_groups AS (
    SELECT 
        *,
        -- 累计变更标记，生成连续状态的分组ID
        SUM(status_change_flag) OVER (PARTITION BY `用户` ORDER BY `日期` ASC) AS group_id
    FROM user_status_change
)
SELECT 
    `用户`,
    `状态`,
    `日期`,
    `状态描述`,
    -- 仅对在岗状态计算分组内累计，缺勤状态返回0
    CASE 
        WHEN `状态描述` = 1 
        THEN ROW_NUMBER() OVER (PARTITION BY `用户`, group_id ORDER BY `日期` ASC)
        ELSE 0 
    END AS 累计计数
FROM status_groups
ORDER BY `用户`, `日期` DESC;

代码说明 #

1. user_status_change CTE：通过LAG函数获取当前用户上一行的状态，对比后标记状态变更点。
2. status_groups CTE：对每个用户的变更点进行累加，同一个连续状态区间的行将获得相同的group_id。
3. 最终查询：针对每个用户的连续在岗分组，用ROW_NUMBER()生成从1开始的累计计数；缺勤状态直接返回0，最后按用户和日期倒序排列，匹配期望结果。

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