如何基于INUMBR和ITRLOC两列筛选连续3个月的记录？

数据库中有表[DWSTAGE].INVAUD，因数据量过大，创建临时表##INV_UD_TRANSACTION_71仅筛选交易类型为'71'的记录。目标是基于INUMBR、ITRLOC两列，筛选出存在连续3个月记录的所有相关行。

现有脚本 #

临时表创建脚本 #

SELECT INUMBR,ITRLOC,ITRDAT
INTO #INV_UD_TRANSACTION_71
FROM [DWSTAGE].INVAUD
WHERE ITRTYP = '71'

连续3个月查询脚本 #

SELECT DISTINCT * FROM
(SELECT  E1.INUMBR
        ,E1.ITRLOC
        ,E1.ITRDAT
FROM #INV_UD_TRANSACTION_71  E1
JOIN #INV_UD_TRANSACTION_71  E2
ON E2.INUMBR = E1.INUMBR
AND MONTH(E2.ITRDAT)  = MONTH(E1.ITRDAT) + 1
JOIN #INV_UD_TRANSACTION_71  E3
ON E3.INUMBR = E2.INUMBR
AND MONTH(E3.ITRDAT) = MONTH(E2.ITRDAT)  + 1

UNION ALL

SELECT  E2.INUMBR
        ,E2.ITRLOC
        ,E2.ITRDAT
FROM #INV_UD_TRANSACTION_71  E1
JOIN #INV_UD_TRANSACTION_71  E2
ON E2.INUMBR = E1.INUMBR
AND MONTH(E2.ITRDAT)  = MONTH(E1.ITRDAT) + 1
JOIN #INV_UD_TRANSACTION_71  E3
ON E3.INUMBR = E2.INUMBR
AND MONTH(E3.ITRDAT) = MONTH(E2.ITRDAT)  + 1

UNION ALL

SELECT   E3.INUMBR
        ,E3.ITRLOC
        ,E3.ITRDAT
FROM #INV_UD_TRANSACTION_71  E1
JOIN #INV_UD_TRANSACTION_71  E2
ON E2.INUMBR = E1.INUMBR
AND MONTH(E2.ITRDAT)  = MONTH(E1.ITRDAT) + 1
JOIN #INV_UD_TRANSACTION_71  E3
ON E3.INUMBR = E2.INUMBR
AND MONTH(E3.ITRDAT) = MONTH(E2.ITRDAT)  + 1
) A
ORDER BY INUMBR,ITRLOC

现有查询结果 #

INUMBR  ITRLOC  ITRDAT
40  13001   210823
40  14002   211115
40  15008   210419
40  15010   210416
40  15012   211115
43  11004   210129
43  12004   210909
43  12004   181018
43  12004   210129
43  12004   210701
43  12004   220404
43  13003   220117
43  13003   210329
43  14001   210301
43  14006   220214
43  14006   210617
43  14006   201009
43  14006   210909
43  14006   220110
43  14006   220505
......................

预期结果示例 #

INUMBR  ITRLOC  ITRDAT
92    12002     210105
92    12002     210210
92    12002     210311
92    12003     210405
107   12009     190104
107   12009     190210
107   12009     190329
1187  13001     220506
1187  13001     220611
1187  13001     220713
1187  13001     220817
1187  13001     220920

现有脚本的问题 #

1. 未关联ITRLOC字段：JOIN条件仅匹配INUMBR，但目标是基于INUMBR+ITRLOC组合筛选，导致不同ITRLOC的记录被错误关联。
2. 未考虑年份跨月：仅用MONTH()判断月份+1，忽略了年份（比如12月的下一个月是次年1月，此时MONTH()的计算会失效）。
3. 逻辑冗余且效率低：三次UNION ALL重复执行相同的JOIN逻辑，不仅冗余，还会产生大量重复数据，最终靠DISTINCT去重，影响性能。

正确实现方法 #

使用窗口函数分组处理，先将日期转换为标准年月格式，再识别连续3个月的记录组合，最后关联回原表获取所有符合条件的行。

完整脚本 #

WITH TRANSACTION_MONTH AS (
  SELECT 
    INUMBR,
    ITRLOC,
    ITRDAT,
    -- 将6位数字日期转换为年月起始日期（处理YYMMDD格式，区分19xx/20xx）
    DATEFROMPARTS(
      CASE WHEN LEFT(ITRDAT,2) >= 50 THEN 1900 + CAST(LEFT(ITRDAT,2) AS INT) ELSE 2000 + CAST(LEFT(ITRDAT,2) AS INT) END,
      CAST(SUBSTRING(ITRDAT,3,2) AS INT),
      1
    ) AS YEAR_MONTH
  FROM #INV_UD_TRANSACTION_71
),
GROUPED_MONTHS AS (
  SELECT 
    INUMBR,
    ITRLOC,
    YEAR_MONTH,
    -- 通过计算月份差与行号的差值，将连续年月归为同一组
    DATEDIFF(MONTH, MIN(YEAR_MONTH) OVER (PARTITION BY INUMBR, ITRLOC), YEAR_MONTH) - 
    ROW_NUMBER() OVER (PARTITION BY INUMBR, ITRLOC ORDER BY YEAR_MONTH) AS GROUP_ID
  FROM TRANSACTION_MONTH
),
CONTINUOUS_GROUPS AS (
  SELECT 
    INUMBR,
    ITRLOC,
    GROUP_ID
  FROM GROUPED_MONTHS
  GROUP BY INUMBR, ITRLOC, GROUP_ID
  -- 筛选出包含至少3个连续月份的组
  HAVING COUNT(DISTINCT YEAR_MONTH) >= 3
)
-- 关联回原表，获取所有属于连续组的记录
SELECT 
  TM.INUMBR,
  TM.ITRLOC,
  TM.ITRDAT
FROM TRANSACTION_MONTH TM
JOIN GROUPED_MONTHS GM
  ON TM.INUMBR = GM.INUMBR
  AND TM.ITRLOC = GM.ITRLOC
  AND TM.YEAR_MONTH = GM.YEAR_MONTH
JOIN CONTINUOUS_GROUPS CG
  ON GM.INUMBR = CG.INUMBR
  AND GM.ITRLOC = CG.ITRLOC
  AND GM.GROUP_ID = CG.GROUP_ID
ORDER BY TM.INUMBR, TM.ITRLOC, TM.YEAR_MONTH;

逻辑说明 #

1. TRANSACTION_MONTH：将原始的6位日期转换为标准的年月起始日期，统一格式以便后续计算。
2. GROUPED_MONTHS：按INUMBR+ITRLOC分组，通过计算月份差与行号的差值，把连续的年月标记为同一分组ID。
3. CONTINUOUS_GROUPS：筛选出分组内月份数≥3的组合，这些就是存在连续3个月记录的目标组。
4. 最后关联回原表，提取所有属于这些目标组的记录，得到符合预期的结果。

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