如何用SQL拆分重叠日期的租车合同记录（优先短合同）

我有一张存储租车合同的SQL表，每条记录包含contract_id、car_id、start_date和end_date。部分同car_id的合同存在日期范围重叠，需要对数据进行清洗。清洗规则为拆分重叠记录，优先保留短合同，将长合同拆分为不重叠的分段。

输入数据示例 #

期望输出数据示例 #

我曾尝试用lag、lead和row_number函数实现，但效果不佳，现寻求可行的解决方案或建议。可使用以下插入语句测试：

测试插入语句 #

INSERT INTO Contracts (contract_id, car_id, start_date, end_date)
VALUES
    ('aaa', 123, '2024-03-01', '2024-06-30'),
    ('bbb', 123, '2024-02-15', '2024-03-15'),
    ('ccc', 123, '2024-06-15', '2024-07-15'),
    ('ddd', 123, '2024-04-01', '2024-04-05'),
    ('eee', 456, '2024-12-01', '2024-12-01');

可行解决方案SQL代码 #

WITH ContractDurations AS (
    -- 计算合同时长，标记优先级：短合同优先级更高
    SELECT 
        contract_id,
        car_id,
        start_date,
        end_date,
        DATEDIFF(day, start_date, end_date) + 1 AS duration,
        ROW_NUMBER() OVER (PARTITION BY car_id ORDER BY DATEDIFF(day, start_date, end_date) + 1) AS priority
    FROM Contracts
),
DatePoints AS (
    -- 收集所有关键日期节点：合同开始日、合同结束日+1（用于拆分区间）
    SELECT car_id, start_date AS point_date FROM ContractDurations
    UNION
    SELECT car_id, DATEADD(day, 1, end_date) AS point_date FROM ContractDurations
),
SortedDatePoints AS (
    -- 按车辆分组排序日期节点，生成连续的日期区间
    SELECT 
        car_id,
        point_date AS interval_start,
        LEAD(point_date) OVER (PARTITION BY car_id ORDER BY point_date) AS interval_end
    FROM DatePoints
    WHERE point_date IS NOT NULL
),
ValidIntervals AS (
    -- 转换为有效的闭区间（结束日期调整为前一天）
    SELECT 
        car_id,
        interval_start,
        DATEADD(day, -1, interval_end) AS interval_end
    FROM SortedDatePoints
    WHERE interval_end IS NOT NULL AND interval_start < interval_end
),
ContractIntervalMatches AS (
    -- 匹配区间与覆盖它的所有合同，选出每个区间优先级最高的合同
    SELECT 
        vi.car_id,
        vi.interval_start,
        vi.interval_end,
        cd.contract_id,
        cd.priority,
        ROW_NUMBER() OVER (PARTITION BY vi.car_id, vi.interval_start ORDER BY cd.priority) AS rn
    FROM ValidIntervals vi
    JOIN ContractDurations cd 
        ON vi.car_id = cd.car_id
        AND cd.start_date <= vi.interval_end
        AND cd.end_date >= vi.interval_start
)
-- 输出最终清洗结果：每个区间只保留优先级最高的合同
SELECT 
    contract_id,
    car_id,
    interval_start AS start_date,
    interval_end AS end_date
FROM ContractIntervalMatches
WHERE rn = 1
ORDER BY car_id, start_date;

方案说明 #

1. ContractDurations：计算每个合同的时长，用ROW_NUMBER给短合同标记更高优先级（数字越小优先级越高）。
2. DatePoints：收集所有合同的开始日期和结束日期+1，这些节点是拆分重叠区间的关键边界。
3. SortedDatePoints：对每个车辆的日期节点排序，用LEAD生成连续的日期区间。
4. ValidIntervals：把区间转换为有效的闭区间格式，确保日期范围正确。
5. ContractIntervalMatches：将每个区间与所有覆盖它的合同匹配，再选出每个区间内优先级最高的合同。
6. 最后筛选出每个区间的最优合同，按车辆和日期排序，得到符合要求的清洗后数据。

内容的提问来源于stack exchange，提问作者Francesco Pegoraro