如何基于Green向Yellow/Red的切换生成特殊分组累计值？

需求说明 #

要基于value字段的特定切换规则生成分组标记：

• 初始状态标记为0
• 当前一行是Green，当前行是Yellow或Red时，触发分组值递增，后续所有行沿用新的分组值
• 每次触发上述切换，分组值加1

实现思路 #

1. 标记触发节点：用lag()函数获取前一行的value，判断是否满足前一行=Green 且 当前行属于Yellow/Red，满足则标记为1，否则为0。
2. 累计生成分组：对触发节点的标记值做顺序累计求和，得到最终的output字段。

示例SQL代码（以PostgreSQL为例） #

SELECT
    value,
    SUM(trigger_flag) OVER (ORDER BY sort_id) AS output
FROM (
    SELECT
        value,
        -- 生成排序ID保证行顺序（如果表有天然排序字段，比如自增ID、时间戳，直接用即可）
        ROW_NUMBER() OVER () AS sort_id,
        -- 判断是否触发分组递增
        CASE
            WHEN LAG(value) OVER (ORDER BY sort_id) = 'Green' 
                 AND value IN ('Yellow', 'Red')
            THEN 1
            ELSE 0
        END AS trigger_flag
    FROM your_table_name
) t;

结果验证 #

代入测试数据执行后，会得到与示例完全一致的输出：

注意事项 #

• 必须保证行的顺序正确，如果表中有天然排序字段（如创建时间、自增主键），一定要替换掉ROW_NUMBER() OVER ()，否则分组逻辑会混乱。
• 不同SQL方言的语法略有差异，比如MySQL 8.0+、Oracle都支持窗口函数，只需根据方言调整细节即可。

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