拆分重叠日期区间：T-SQL员工合同类型优先级处理

我有两张T-SQL表：预阶段表和阶段表。预阶段表存着员工合同数据，里面有不少日期区间重叠的情况，示例数据如下：

id,date_from,date_to,contract_type
308,01.01.2023,28.09.2023,1
308,04.03.2023,15.07.2023,2
308,01.10.2023,31.07.2024,1
477,02.04.2023,30.08.2023,1
477,01.06.2023,31.12.2023,2

想要处理成以下格式：

id,date_from,date_to,contract_type
308,01.01.2023,03.03.2023,1
308,04.03.2023,15.07.2023,2
308,16.07.2023,28.09.2023,1
308,01.10.2023,31.07.2024,1
477,02.04.2023,31.05.2023,1
477,01.06.2023,31.12.2023,2

要求把所有重叠的日期区间拆成不重叠的，而且重叠的部分要选数值更大的contract_type。需要把这个逻辑做成一个存储过程，用来把数据加载到阶段表里，该怎么实现？是逐个日期计算再按id和contract_type分组，还是有更高效的条件检测拆分范围的方法？

更推荐用日期点拆分+区间聚合的方法，比逐个日期计算效率高得多，逻辑也更清晰。核心思路是：

• 提取所有关键日期点（所有合同的date_from和date_to+1），按员工ID分组排序；
• 用这些日期点生成连续的不重叠小区间；
• 给每个小区间匹配所有覆盖它的合同，选数值最大的contract_type；
• 最后可选合并连续且类型相同的区间。

下面是具体的存储过程代码：

CREATE PROCEDURE LoadStagingTable
AS
BEGIN
    SET NOCOUNT ON;

    -- 清空阶段表（若需保留历史数据可删除该行，改为增量加载逻辑）
    TRUNCATE TABLE StagingTable;

    WITH AllDates AS (
        -- 提取所有用于拆分区间的关键日期：合同开始日、合同结束日的次日
        SELECT id, date_from AS dt FROM PreStagingTable
        UNION
        SELECT id, DATEADD(DAY, 1, date_to) AS dt FROM PreStagingTable
    ),
    DateRanges AS (
        -- 用LEAD函数将相邻日期拼接成连续区间
        SELECT 
            id,
            dt AS date_from,
            LEAD(dt) OVER (PARTITION BY id ORDER BY dt) AS date_to,
            LEAD(dt) OVER (PARTITION BY id ORDER BY dt) IS NOT NULL AS is_valid
        FROM AllDates
    ),
    ValidRanges AS (
        -- 将左闭右开区间转为闭区间（结束日减1天），过滤无效记录
        SELECT 
            id,
            date_from,
            DATEADD(DAY, -1, date_to) AS date_to
        FROM DateRanges
        WHERE is_valid = 1
    ),
    RangeContractTypes AS (
        -- 为每个小区间匹配优先级最高的contract_type
        SELECT 
            vr.id,
            vr.date_from,
            vr.date_to,
            MAX(pst.contract_type) AS contract_type
        FROM ValidRanges vr
        JOIN PreStagingTable pst 
            ON vr.id = pst.id
            AND vr.date_from <= pst.date_to
            AND vr.date_to >= pst.date_from
        GROUP BY vr.id, vr.date_from, vr.date_to
    ),
    MergedRanges AS (
        -- 可选：合并连续且contract_type相同的区间，避免细碎区间
        SELECT 
            id,
            date_from,
            date_to,
            contract_type,
            -- 为连续同类型区间标记分组ID
            SUM(CASE WHEN prev_end = DATEADD(DAY, -1, date_from) AND prev_type = contract_type THEN 0 ELSE 1 END) 
                OVER (PARTITION BY id ORDER BY date_from) AS group_id
        FROM (
            SELECT 
                id,
                date_from,
                date_to,
                contract_type,
                LAG(date_to) OVER (PARTITION BY id, contract_type ORDER BY date_from) AS prev_end,
                LAG(contract_type) OVER (PARTITION BY id ORDER BY date_from) AS prev_type
            FROM RangeContractTypes
        ) t
    )
    -- 将最终结果插入阶段表
    INSERT INTO StagingTable (id, date_from, date_to, contract_type)
    SELECT 
        id,
        MIN(date_from) AS date_from,
        MAX(date_to) AS date_to,
        contract_type
    FROM MergedRanges
    GROUP BY id, contract_type, group_id
    ORDER BY id, date_from;
END
GO

逻辑拆解 #

1. AllDates：收集所有能分割区间的关键日期，用date_to+1是为了让拆分后的区间刚好衔接，无间隙也无重复。
2. DateRanges：用LEAD函数将每个日期点与下一个日期点配对，生成左闭右开的区间[dt, next_dt)。
3. ValidRanges：将左闭右开区间转为业务需要的闭区间[date_from, date_to]，同时过滤掉最后一条无结束日的无效记录。
4. RangeContractTypes：将每个小区间与原表合同关联，只要区间和合同有重叠就纳入计算，取最大的contract_type作为当前区间的类型。
5. MergedRanges：若不需要保留细碎区间，可通过该CTE将连续的同类型区间合并为一个大区间，让结果更整洁。

两种方法对比 #

• 逐个日期计算：需遍历每个员工的每一天，数据量大时性能极差，完全不推荐。
• 日期点拆分法：仅处理关键日期点，生成的区间数量远少于总天数，关联和聚合效率更高，适合处理大量重叠合同的场景。

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