SQL如何实现分组统计间隔重置的连续月份计数？

DevOps 小满

2026-10-8

连续月份计数重置最优实现方案

该需求属于SQL经典的「岛屿（连续序列）识别」场景，仅用标准窗口函数即可实现，时间复杂度为O(n)，适配所有支持窗口函数的SQL引擎（MySQL 8.0+、PostgreSQL、SQL Server、Hive、Spark SQL等）。

核心逻辑

第一步：对每个Case Number下的记录按发生月份升序排序，生成唯一行号
第二步：将Occurrence Month转换为总月数（年份*12 + 月份），减去第一步得到的行号，得到分组标记。连续的月份计算出的分组标记完全一致，出现间隔时分隔标记会自动变化
第三步：对每个Case Number和分组标记构成的小分组，再次按发生月份升序生成行号，就是需要的连续月份计数

完整实现代码

WITH rn_calc AS (
    SELECT 
        CaseNumber,
        OccurrenceMonth,
        -- 按Case分组生成行号
        ROW_NUMBER() OVER(PARTITION BY CaseNumber ORDER BY OccurrenceMonth) AS rn
    FROM `table`
),
group_calc AS (
    SELECT 
        CaseNumber,
        OccurrenceMonth,
        -- 计算连续分组标记
        (LEFT(OccurrenceMonth,4)*12 + RIGHT(OccurrenceMonth,2)) - rn AS group_id
    FROM rn_calc
)
SELECT 
    CaseNumber,
    OccurrenceMonth,
    -- 分组内生成连续计数
    ROW_NUMBER() OVER(PARTITION BY CaseNumber, group_id ORDER BY OccurrenceMonth) AS ConsecutiveMonths
FROM group_calc
ORDER BY CaseNumber, OccurrenceMonth;