SQL连续月份计算异常排查与修正及12连续月ID筛选需求

我写的SQL代码未得到预期输出，为便于排查添加了行号及WHAT I WANT列。第4行的Consecutive字段计算异常：实际显示为1，但应该是2（因其上一行是前一个月）。这个问题并非特定ID独有，本次指定ID="7"仅为便于理解代码逻辑。我的最终目标是筛选出拥有至少12个连续月份记录的ID。

当前SQL代码 #

SELECT year, ID, YearMo
    ,ROW_NUMBER() OVER (PARTITION BY ID, gap ORDER BY yearmo) AS Consecutive
FROM (
SELECT year, ID, YearMo
        ,CASE WHEN yearmo - LAG(yearmo, 1, yearmo - 1) OVER (PARTITION BY ID ORDER BY yearmo) IN (1, 89) 
            THEN 0 ELSE 1 
        END 
+ 
SUM(CASE WHEN yearmo - LAG(yearmo, 1, yearmo - 1) OVER (PARTITION BY ID ORDER BY yearmo) IN (1, 89) 
         THEN 0 ELSE 1 
    END) OVER (PARTITION BY ID ORDER BY yearmo) as gap
    FROM T1
) subquery
where ID = "7"
ORDER BY 
    ID, 
    yearmo

执行结果对比 #

计算gap字段时，你把当前行的CASE判断结果和累计SUM值直接相加，导致连续月份的分组标识被错误递增。比如第4行（201906）和上一行（201905）是连续的，本应属于同一个分组，但相加操作让gap值改变，使得ROW_NUMBER()重新从1开始计数。

正确的做法是仅对“是否中断连续”的标记做累计求和，以此生成连续分组的唯一标识：

修正后的SQL代码（用于验证连续计数） #

SELECT 
    year, 
    ID, 
    YearMo,
    ROW_NUMBER() OVER (PARTITION BY ID, gap ORDER BY yearmo) AS Consecutive
FROM (
    SELECT 
        year, 
        ID, 
        YearMo,
        -- 仅对中断标记做累计求和，生成连续分组标识
        SUM(CASE 
                WHEN yearmo - LAG(yearmo, 1, yearmo - 1) OVER (PARTITION BY ID ORDER BY yearmo) IN (1, 89) 
                THEN 0 
                ELSE 1 
            END) OVER (PARTITION BY ID ORDER BY yearmo) AS gap
    FROM T1
) subquery
WHERE ID = "7"
ORDER BY ID, yearmo;

最终筛选目标实现（找出连续月份≥12的ID） #

如果要直接筛选符合条件的ID，可通过CTE聚合后判断：

WITH consecutive_stats AS (
    SELECT 
        ID,
        -- 每个连续分组的最大连续月份数
        MAX(ROW_NUMBER() OVER (PARTITION BY ID, gap ORDER BY yearmo)) AS max_consecutive
    FROM (
        SELECT 
            ID, 
            YearMo,
            SUM(CASE 
                    WHEN yearmo - LAG(yearmo, 1, yearmo - 1) OVER (PARTITION BY ID ORDER BY yearmo) IN (1, 89) 
                    THEN 0 
                    ELSE 1 
                END) OVER (PARTITION BY ID ORDER BY yearmo) AS gap
        FROM T1
    ) subquery
    GROUP BY ID, gap
)
SELECT ID
FROM consecutive_stats
WHERE max_consecutive >= 12;

逻辑说明 #

1. 内层查询的CASE语句：判断当前月份与上一记录月份是否连续（保留你原有的1和89的判断逻辑，分别对应同月内连续和跨年连续），连续则返回0，中断则返回1。
2. 对CASE结果做累计求和：每遇到一次中断，gap值加1，同一个连续区间的所有行将拥有相同的gap值。
3. 外层通过ROW_NUMBER()按ID和gap分区，即可正确计算连续月份的计数；后续聚合时，只需找出每个ID的最大连续月份数，即可筛选出符合要求的ID。

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