如何添加含累积重置逻辑的FLAG列？累积值超10时标记并重置

这个需求的核心是维护一个可动态重置的累加状态，LAG函数仅能获取单行历史值，无法跟踪跨多行的累积状态，因此需要用递归CTE或分组窗口函数实现。

方法1：通用递归CTE方案（支持PostgreSQL、MySQL 8.0+、SQL Server等） #

通过递归逐行计算，实时维护当前累积值和FLAG状态：

WITH recursive cte AS (
    -- 锚点：处理第一行数据
    SELECT 
        row_num,
        number,
        number AS running_total,
        CASE WHEN number > 10 THEN 1 ELSE 0 END AS FLAG
    FROM data_table
    WHERE row_num = 1

    UNION ALL

    -- 递归：逐行处理后续数据
    SELECT 
        dt.row_num,
        dt.number,
        -- 若上一行FLAG为1，重置累积值为当前number；否则继续累加
        CASE 
            WHEN cte.FLAG = 1 THEN dt.number
            ELSE cte.running_total + dt.number
        END AS running_total,
        -- 判断当前累积值是否超过10，设置FLAG
        CASE 
            WHEN (cte.FLAG = 0 AND cte.running_total + dt.number > 10) 
                OR (cte.FLAG = 1 AND dt.number > 10) THEN 1
            ELSE 0
        END AS FLAG
    FROM data_table dt
    INNER JOIN cte ON dt.row_num = cte.row_num + 1
)
-- 输出最终结果，隐藏中间计算的累积值字段
SELECT row_num, number, FLAG 
FROM cte
ORDER BY row_num;

代码说明 #

• 锚点成员初始化第一行的累积值和FLAG状态；
• 递归成员根据上一行的FLAG决定是否重置累积值，再判断当前累积值是否超过阈值10，设置对应FLAG；
• 最终仅保留需求的核心列，排除中间计算的running_total。

方法2：PostgreSQL专属分组窗口函数方案 #

利用窗口函数的分组特性，通过累积计数生成重置分组，再在分组内计算累加值：

WITH grp_cte AS (
    -- 生成重置分组：每次FLAG=1时，分组ID递增
    SELECT 
        row_num,
        number,
        SUM(CASE WHEN prev_flag = 1 THEN 1 ELSE 0 END) OVER (ORDER BY row_num) AS reset_grp
    FROM (
        -- 计算每行的FLAG，并获取上一行的FLAG
        SELECT 
            row_num,
            number,
            LAG(FLAG) OVER (ORDER BY row_num) AS prev_flag,
            CASE 
                WHEN SUM(number) OVER (PARTITION BY reset_grp ORDER BY row_num) > 10 THEN 1
                ELSE 0
            END AS FLAG
        FROM (
            -- 初始化分组ID：从0开始，之前每出现一次FLAG=1就加1
            SELECT 
                row_num,
                number,
                SUM(CASE WHEN FLAG = 1 THEN 1 ELSE 0 END) OVER (
                    ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
                ) AS reset_grp
            FROM data_table
        ) t1
    ) t2
)
SELECT row_num, number, FLAG 
FROM grp_cte
ORDER BY row_num;

代码说明 #

• 通过三层CTE嵌套，先生成重置分组ID，再在分组内计算累加值，最后判断阈值设置FLAG；
• 纯窗口函数实现，无需递归，适合PostgreSQL环境。

为什么LAG函数无法解决？ #

LAG函数仅能获取上一行的单一值，无法跟踪跨多行的累积累加状态。本需求需要持续维护累加值直到超过阈值才重置，必须通过递归或分组窗口函数来维护状态。

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