SQL 合併重疊/相鄰時間區間：自關聯 + 分組聚合經典解法

SQL 合併時間區間問題：將重疊/相鄰區間合併為不相交區間集

給定區間表 Tsheets(id, start_date, end_date)，輸入含重疊/相鄰區間，輸出合併後的不相交區間集。經典解法：**自關聯找「區間起點」與「區間終點」配對，再分組取最小終點。**

輸入數據示例

id  start_date   end_date
1   1997-01-01   1997-01-03
2   1997-01-02   1997-01-04
3   1997-01-04   1997-01-05   -- 與 2 相鄰 (end=start)
4   1997-01-06   1997-01-09
5   1997-01-09   1997-01-09   -- 單點區間
6   1997-01-09   1997-01-09
7   1997-01-12   1997-01-15
8   1997-01-13   1997-01-14   -- 包含於 7
9   1997-01-14   1997-01-14
10  1997-01-17   1997-01-17

期望輸出

start_date   end_date
1997-01-01   1997-01-05
1997-01-06   1997-01-09
1997-01-12   1997-01-15
1997-01-17   1997-01-17

標準 SQL 解法 (三表自關聯)

SELECT X.start_date, MIN(X.end_date) AS end_date
FROM (
    SELECT T1.start_date, T2.end_date
    FROM Tsheets AS T1, Tsheets AS T2, Tsheets AS T3
    WHERE T1.end_date <= T2.end_date
    GROUP BY T1.start_date, T2.end_date
    HAVING MAX(CASE
        WHEN (T1.start_date > T3.start_date AND T1.start_date <= T3.end_date)
          OR (T2.end_date >= T3.start_date AND T2.end_date < T3.end_date)
        THEN 1 ELSE 0 END) = 0
) AS X
GROUP BY X.start_date;

邏輯解讀

1. T1 提供候選「起點」，T2 提供候選「終點」，要求 T1.end <= T2.end。
2. T3 為「幹擾區間」：若存在任一 T3 使得候選起點落在 T3 內部，或候選終點落在 T3 內部，則該配對無效 (被覆蓋/延伸)。
3. HAVING MAX(...) = 0 保留**無幹擾**的起終點配對 → 即合併後的邊界點。
4. 外層 GROUP BY start_date 取 MIN(end_date) 消除同一起點對應多終點的冗餘。

現代數據庫更優寫法 (窗口函數 / 遞歸 CTE)

MySQL 8.0+, PostgreSQL, SQL Server 支持：

WITH sorted AS (
    SELECT start_date, end_date,
           LAG(end_date) OVER (ORDER BY start_date) AS prev_end
    FROM Tsheets
), grouped AS (
    SELECT start_date, end_date,
           SUM(CASE WHEN start_date > prev_end THEN 1 ELSE 0 END) OVER (ORDER BY start_date) AS grp
    FROM sorted
)
SELECT MIN(start_date) AS start_date, MAX(end_date) AS end_date
FROM grouped
GROUP BY grp
ORDER BY start_date;

邏輯：按起點排序 → 標記「新組」起點 (當前起點 > 前一終點) → 累加分組號 → 分組聚合取最小起點/最大終點。性能 O(N log N) 遠優於自關聯 O(N³)。