SQL按条件分组：起始为1的连续0分组及逆序分组优化需求

一、正向分组：data=1开头+连续data=0，遇1/2停止 #

解决方案（窗口函数实现） #

用窗口函数的累加特性替代coalesce子查询的复杂逻辑，精准识别分组边界：

WITH trigger_marks AS (
    SELECT 
        id,
        data,
        -- 标记分组起始/终止边界：data为1或2时触发分组切换
        CASE WHEN data IN (1,2) THEN 1 ELSE 0 END AS boundary_flag
    FROM your_table
)
SELECT 
    id,
    data,
    -- 累加边界标记生成组ID，每个连续段对应唯一group_id
    SUM(boundary_flag) OVER (ORDER BY id) AS group_id
FROM trigger_marks
-- 可选：仅保留以data=1开头的有效分组
WHERE group_id IN (
    SELECT DISTINCT group_id 
    FROM trigger_marks 
    WHERE data = 1
);

逻辑说明 #

• 用boundary_flag标记所有需要切换分组的行（data=1或2）；
• SUM(boundary_flag) OVER (ORDER BY id)会在每次遇到边界行时递增，因此1后续的连续0会被归入同一个组，直到下一个边界行出现；
• 若之前coalesce子查询结果不符合预期，通常是未处理连续边界行的累加逻辑，此方案通过窗口函数的顺序累加直接解决问题。

二、逆序分组：从下往上以data=2开头，遇1/2纳入并关闭分组 #

优化方案（无JOIN纯窗口函数实现） #

通过逆序排序+窗口函数累加替代原LEFT JOIN写法，简化查询逻辑：

WITH reversed_data AS (
    SELECT 
        id,
        data,
        -- 生成逆序行号，用于后续恢复原顺序
        ROW_NUMBER() OVER (ORDER BY id DESC) AS reverse_row,
        -- 标记逆序下的分组边界：data为1或2时触发分组切换
        CASE WHEN data IN (1,2) THEN 1 ELSE 0 END AS boundary_flag
    FROM your_table
),
grouped_reversed AS (
    SELECT 
        id,
        data,
        reverse_row,
        -- 逆序累加边界标记生成组ID
        SUM(boundary_flag) OVER (ORDER BY reverse_row) AS group_id
    FROM reversed_data
)
SELECT 
    id,
    data,
    group_id
FROM grouped_reversed
ORDER BY id;

逻辑说明 #

1. 先按原排序字段（如id）逆序，生成reverse_row记录逆序位置；
2. 在逆序数据中用SUM(boundary_flag)累加边界标记，实现从下往上的分组：遇到data=2时开启新组，后续行（原表中位于2上方的行）直到遇到1或2时被归入同一组并关闭；
3. 最后按原id顺序恢复数据，全程无需JOIN操作，性能更优。

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