如何在同一分区内按条件重置Running Sum（累计求和）

需求回顾 #

需要在按user_id分区、date排序的数据集上，为布尔列status生成累计求和列：

• 当status为0时，累计值保持0
• 每当status在0与1之间切换时，累计值重置为1（连续1时正常累加）

示例数据：

原代码问题分析 #

你当前的代码逻辑是标记状态切换的行后累加标记值，这会导致连续1的行因为没有状态切换，累加值始终停留在1，无法实现连续累加的需求。

正确解法 #

核心思路是先为每个连续相同status的序列生成分组ID，再在分组内对status做累计求和，同时处理status=0的情况：

SELECT
    user_id,
    date,
    status,
    CASE
        WHEN status = 0 THEN 0
        ELSE SUM(status) OVER (PARTITION BY user_id, group_id ORDER BY date)
    END AS desired_output
FROM (
    SELECT
        *,
        -- 生成连续状态的分组ID：首次行或状态切换时加1，否则继承分组ID
        SUM(CASE WHEN prev_status IS NULL OR prev_status != status THEN 1 ELSE 0 END) 
            OVER (PARTITION BY user_id ORDER BY date) AS group_id
    FROM (
        SELECT
            *,
            LAG(status) OVER (PARTITION BY user_id ORDER BY date) AS prev_status
        FROM table1
    ) sub1
) sub2
ORDER BY user_id, date;

步骤解释 #

1. 获取前一行状态：用LAG(status)得到每个用户前一行的status，标记为prev_status。
2. 生成连续状态分组：通过累加状态变化的标记（首次行或状态切换时为1），得到每个连续状态序列的group_id，同一连续状态的行拥有相同的group_id。
3. 分组内累计求和：在user_id和group_id的分区内对status求和，同时判断status=0时直接返回0，完全匹配需求。

测试验证 #

将上述代码应用到示例数据中，会得到与desired output完全一致的结果。

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