Oracle按连续行分组统计需求：实现相邻相同值分组计数

源表 #

期望结果 #

实现思路 #

要实现连续相同值的分组统计，核心是生成连续分组标识——将同ID下连续出现的相同HBS1值归为同一组，而非合并所有相同值。以下是两种通用的SQL实现方法：

方法1：基于行号差值生成分组标识 #

利用窗口函数计算全局行号和组内行号，两者的差值可作为连续分组的唯一标识：

SELECT 
    ID,
    HBS1,
    COUNT(*) AS COUNT
FROM (
    SELECT 
        ID,
        HBS1,
        -- 同ID内的全局行号
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) - 
        -- 同ID同HBS1内的组内行号
        ROW_NUMBER() OVER (PARTITION BY ID, HBS1 ORDER BY (SELECT NULL)) AS group_id
    FROM your_table
) AS temp
GROUP BY ID, HBS1, group_id
-- 按原表顺序输出结果
ORDER BY MIN(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)));

注：ORDER BY (SELECT NULL)用于保留原表行顺序，若表中有明确的排序字段（如自增ID），可替换为该字段。

方法2：基于LAG函数对比生成分组标识 #

通过LAG()函数对比当前行与上一行的HBS1值，每当值发生变化时分组标识加1：

SELECT 
    ID,
    HBS1,
    COUNT(*) AS COUNT
FROM (
    SELECT 
        ID,
        HBS1,
        -- 当前行与上一行HBS1不同时，分组标识+1
        SUM(CASE WHEN HBS1 = LAG(HBS1) OVER (PARTITION BY ID ORDER BY (SELECT NULL)) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY ID ORDER BY (SELECT NULL)) AS group_id
    FROM your_table
) AS temp
GROUP BY ID, HBS1, group_id
ORDER BY MIN(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)));

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