巧用 PARTITION + RANK 解決連續相同資料列合併問題

需求：將連續且相同的狀態變更記錄合併，不連續或不同狀態保留。關鍵在於區分「連續」與「非連續」的相同群組。

測試資料

DECLARE @TestData TABLE (ID INT, Col1 VARCHAR(20), Col2 VARCHAR(20));
INSERT INTO @TestData VALUES
(1,'New','Approved'),
(2,'Approved','Commited'),
(3,'Commited','In Progress'),
(4,'New','Approved'),
(5,'New','Approved'),
(6,'New','Approved'),
(7,'Approved','Removed');

ID 1,4,5,6 皆為 New→Approved，但 1 與 4-6 不連續，需分兩組；4-6 連續合併為一組。

核心技巧：RANK + PARTITION 產生組內排名，配合 ID 差值分組

WITH Ranked AS (
    SELECT 
        ID, Col1, Col2,
        RANK() OVER (PARTITION BY Col1+Col2 ORDER BY ID) AS Rnk
    FROM @TestData
),
Grouped AS (
    SELECT 
        ID, Col1, Col2,
        ID - Rnk AS GrpKey  -- 連續相同記錄的 ID 與 Rnk 同步遞增，差值固定
    FROM Ranked
)
SELECT 
    MIN(ID) AS StartID, Col1, Col2, COUNT(*) AS MergeCount
FROM Grouped
GROUP BY Col1, Col2, GrpKey
ORDER BY StartID;

原理解析

1. PARTITION BY Col1+Col2 將相同狀態變更分組（1,4,5,6 同組）。
2. RANK() ORDER BY ID 產生組內排名 R：1→1, 4→2, 5→3, 6→4。
3. 連續記錄（4,5,6）的 ID 與 R 同步 +1，ID - R 恆定（4-2=2, 5-3=2, 6-4=2）。
4. 非連續記錄（1）的 ID - R = 0，與連續組差值不同，自然分組。
5. 最終以 Col1, Col2, ID-R 分組，取 MIN(ID) 即得合併後起始 ID。

結果

ID 不連續怎麼辦？

將 RANK() 改為 ROW_NUMBER()，邏輯不變，ID - RN 仍能識別連續段。

延伸應用

• 連續登入天數統計
• 感測器連續異常區間合併
• 股票連續漲/跌天數分組

此技巧屬於「間隙與島嶼」經典模式，掌握 ROW_NUMBER() 差值分組思維，可解決多種連續性分析問題。