Oracle 連續月份記錄查詢：以「連續 5 月打卡」為例的 SQL 實作

業務常見需求：找出**連續 N 個月**都有記錄的用戶，並給出起始/結束月份。本文以「連續 5 月」為例，展示利用 ADD_MONTHS、ROWNUM、分組聚合的經典解法，並提供通用化參數化模板與效能優化建議。

測試資料

CREATE TABLE checkin (name VARCHAR2(10), dt VARCHAR2(6));  -- dt 格式 YYYYMM
INSERT INTO checkin VALUES ('a', '200101');
INSERT INTO checkin VALUES ('a', '200102');
INSERT INTO checkin VALUES ('a', '200103');
INSERT INTO checkin VALUES ('a', '200104');
INSERT INTO checkin VALUES ('a', '200105');
INSERT INTO checkin VALUES ('a', '200401');
INSERT INTO checkin VALUES ('a', '200403');
INSERT INTO checkin VALUES ('a', '200404');
INSERT INTO checkin VALUES ('a', '200405');
INSERT INTO checkin VALUES ('c', '200901');
INSERT INTO checkin VALUES ('c', '200902');
INSERT INTO checkin VALUES ('b', '200301');
INSERT INTO checkin VALUES ('b', '200304');
INSERT INTO checkin VALUES ('b', '200801');
INSERT INTO checkin VALUES ('b', '200802');
INSERT INTO checkin VALUES ('b', '200803');
INSERT INTO checkin VALUES ('b', '200804');
INSERT INTO checkin VALUES ('b', '200805');
COMMIT;

預期結果：a: 200101-200105、b: 200801-200805 (c 僅 2 月、a 2004 年中斷、b 2003 年中斷)。

核心解法：偽欄位分組法

SELECT name,
       MIN(dt) AS start_ym,
       MAX(dt) AS end_ym
FROM (
  SELECT name,
         dt,
         -- 關鍵：將日期減去「行號」個月，連續月份會產生相同值
         ADD_MONTHS(TO_DATE(dt, 'YYYYMM'), -ROW_NUMBER() OVER (PARTITION BY name ORDER BY dt)) AS grp_key
  FROM   checkin
)
GROUP  BY name, grp_key
HAVING COUNT(*) >= 5  -- 連續 N 月門檻
ORDER  BY name, start_ym;

原理解析

1. ROW_NUMBER() OVER (PARTITION BY name ORDER BY dt) 為每位用戶的記錄按月份排序編號 (1,2,3...)。
2. ADD_MONTHS(date, -rn) 將第 n 筆記錄的日期向前推 n 個月。
   • 連續月份：200101-1=200012、200102-2=200012、200103-3=200012... **同一群組鍵值**
   • 中斷月份：間隔導致推算結果不同，自然分入不同群組
3. 按 name, grp_key 分組，COUNT(*) 即為連續月份數。
4. HAVING COUNT(*) >= 5 過濾達標群組，MIN/MAX 取邊界。

原文腳本 (相同邏輯、舊寫法)

SELECT 姓名, Start_HM, End_HM FROM (
  SELECT b.姓名, MIN(b.日期) Start_HM, MAX(b.日期) End_HM
  FROM (
    SELECT a.*, ADD_MONTHS(a.日期, ROWNUM * (-1)) cc
    FROM (
      SELECT * FROM t ORDER BY 姓名, 日期
    ) a
  ) b
  GROUP BY b.姓名, b.cc
)
WHERE MONTHS_BETWEEN(End_HM, Start_HM) >= 4;

注意： 原文使用 ROWNUM 需配合內層排序，現代寫法推薦 ROW_NUMBER() OVER 更清晰穩定。

通用化參數化模板

-- 參數：表名、用戶欄位、日期欄位(YYYYMM字串)、連續門檻N
WITH ranked AS (
  SELECT user_col,
         dt,
         ADD_MONTHS(TO_DATE(dt, 'YYYYMM'), -ROW_NUMBER() OVER (PARTITION BY user_col ORDER BY dt)) AS grp_key
  FROM   your_table
)
SELECT user_col,
       TO_CHAR(MIN(TO_DATE(dt, 'YYYYMM')), 'YYYYMM') AS start_ym,
       TO_CHAR(MAX(TO_DATE(dt, 'YYYYMM')), 'YYYYMM') AS end_ym,
       COUNT(*) AS consecutive_months
FROM   ranked
GROUP  BY user_col, grp_key
HAVING COUNT(*) >= :N
ORDER  BY user_col, start_ym;

效能優化建議

• 索引：CREATE INDEX idx_checkin_user_dt ON checkin(name, dt); 支援分區排序與篩選。
• 分區表：大歷史表按 dt 範圍分區，查詢自動裁剪。
• 物化視圖：高頻報表預先計算 grp_key，查詢直接聚合。
• 避免隱性轉換：日期欄位建議用 DATE 或 NUMBER(YYYYMMDD) 型別，而非字串。

擴充：連續天數/週數

-- 連續 N 天：用 date - ROW_NUMBER() 直接相減 (數值相減即天數)
date_col - ROW_NUMBER() OVER (PARTITION BY user_col ORDER BY date_col) AS grp_key

-- 連續 N 週：TRUNC(date_col, 'IW') - 7 * ROW_NUMBER() ...

掌握「序號位移分組」核心模式，即可靈活解決連續天/週/月/季等各類時間序列連續性分析需求，是 SQL 進階分析的必備技法。