如何用SQL查找重叠日期区间中的保险空档？

问题描述 #

现有一张包含ID、start_dt（保险生效日期）、end_dt（保险到期日期）的表，需找出同一ID下保险覆盖区间的空档（即没有任何保险生效的日期段），原始数据的日期区间可能存在重叠或包含关系。

示例1：无空档情况 #

输入数据：

ID start_dt  end_dt
1|2016-07-01|2020-05-01
1|2017-04-12|2020-04-12
1|2008-10-21|2017-10-18
1|2016-07-15|2016-11-21
1|2013-03-04|2013-06-08

结果：无空档，返回空集（对应需求的NULL）

示例2：有空档情况 #

输入数据：

ID start_dt  end_dt
1|2017-04-12|2020-04-12
1|2014-10-21|2016-11-21
1|2013-07-15|2015-05-21
1|2013-03-04|2013-06-08

预期结果：

2013-06-08|2013-07-15
2016-11-21|2017-04-12

解决方案：修正后的岛屿法实现 #

岛屿法的核心是先将重叠/连续的区间合并为完整的"保险覆盖岛屿"，再查找岛屿之间的间隙。以下是标准SQL实现（不同数据库可根据语法微调）：

WITH sorted_intervals AS (
    -- 按ID和生效日期排序，标记每个区间所属的岛屿
    SELECT 
        ID,
        start_dt,
        end_dt,
        -- 当前区间生效日晚于前面所有区间的最大到期日，则为新岛屿
        SUM(CASE WHEN start_dt > MAX(end_dt) OVER (PARTITION BY ID ORDER BY start_dt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) THEN 1 ELSE 0 END) OVER (PARTITION BY ID ORDER BY start_dt) AS island_id
    FROM 
        insurance_table
),
merged_islands AS (
    -- 合并同一岛屿的所有区间，得到完整覆盖段
    SELECT 
        ID,
        MIN(start_dt) AS island_start,
        MAX(end_dt) AS island_end
    FROM 
        sorted_intervals
    GROUP BY 
        ID, island_id
),
gap_detection AS (
    -- 获取前一个岛屿的到期日，计算空档起止
    SELECT 
        ID,
        LAG(island_end) OVER (PARTITION BY ID ORDER BY island_start) AS gap_start,
        island_start AS gap_end
    FROM 
        merged_islands
)
-- 筛选出有效空档
SELECT 
    gap_start,
    gap_end
FROM 
    gap_detection
WHERE 
    gap_start IS NOT NULL 
    AND gap_start < gap_end
ORDER BY 
    gap_start;

逻辑说明 #

1. sorted_intervals：对每个ID的区间按start_dt排序，通过窗口函数标记岛屿——如果当前区间的生效日晚于前面所有区间的最大到期日，就标记为新岛屿。
2. merged_islands：按ID和岛屿ID分组，合并每个岛屿的所有区间，得到每个完整覆盖段的起止日期。
3. gap_detection：用LAG()函数获取前一个岛屿的到期日，当前岛屿的生效日与前者的差值就是空档。
4. 最后筛选出真实存在的空档（前一个岛屿到期日早于当前岛屿生效日），即为结果。

若无空档，最终查询会返回空结果，对应示例1的需求。

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