SQL处理合同周期：合并无间隙周期，保留有间隙周期

场景说明 #

现有存储合同周期的表contract_periods，包含字段：id、year、startdate、enddate、drop_out、covered_days。需处理两类场景：

• 同一id的多段合同周期无间隙（如id1的2015-03-01至2020-12-31与2021-01-01至2021-04-20）：合并为单个周期，并将该id所有year行的周期信息统一更新为合并后的值；
• 同一id的周期存在间隙（如id3、id5）：保留独立周期，同时将同一周期内不同year行的周期信息统一。

通用SQL解决方案 #

核心思路是利用窗口函数识别连续无间隙的周期组，再通过聚合得到合并后的区间，最后关联原表完成更新。以下是兼容主流SQL数据库（如MySQL 8+、PostgreSQL、SQL Server）的实现代码：

WITH period_groups AS (
    -- 第一步：为每个id的周期分组，标记连续无间隙的区间
    SELECT 
        id,
        startdate,
        enddate,
        -- 当当前周期的startdate是上一周期enddate的次日，说明连续，否则开启新组
        SUM(CASE 
            WHEN startdate = DATE_ADD(LAG(enddate) OVER (PARTITION BY id ORDER BY startdate), INTERVAL 1 DAY)
            THEN 0 
            ELSE 1 
        END) OVER (PARTITION BY id ORDER BY startdate) AS group_id
    FROM contract_periods
),
merged_periods AS (
    -- 第二步：按id和group_id聚合，得到合并后的区间
    SELECT 
        id,
        group_id,
        MIN(startdate) AS merged_start,
        MAX(enddate) AS merged_end,
        -- 计算合并后的covered_days：总天数=合并后结束日-开始日+1
        DATEDIFF(MAX(enddate), MIN(startdate)) + 1 AS merged_covered_days
        -- drop_out字段如果是同一周期内统一值，可直接取MAX/MIN，若需保留原逻辑可调整
    FROM period_groups
    GROUP BY id, group_id
)
-- 第三步：更新原表，将对应行的周期信息替换为合并后的值
UPDATE contract_periods cp
JOIN merged_periods mp 
    ON cp.id = mp.id 
    -- 关联条件：原周期属于该合并组（原startdate在合并区间内）
    AND cp.startdate >= mp.merged_start 
    AND cp.enddate <= mp.merged_end
SET 
    cp.startdate = mp.merged_start,
    cp.enddate = mp.merged_end,
    cp.covered_days = mp.merged_covered_days;

关键逻辑说明 #

1. 周期分组：通过LAG(enddate)获取当前周期的上一个周期结束日，判断当前周期是否与上一周期连续（startdate等于上一周期enddate的次日），用累计求和SUM() OVER()生成group_id，同一连续区间的group_id相同。
2. 区间合并：按id和group_id聚合，取该组内最小的startdate和最大的enddate作为合并后的区间，同时计算总覆盖天数。
3. 关联更新：将原表与合并结果关联，确保每个原周期行都对应到所属的合并组，替换为合并后的周期信息。

注意事项 #

• 若drop_out字段在同一连续周期内有不同值，需根据业务规则调整（如取最新值、标记为混合状态等）；
• 不同数据库的日期函数略有差异：
  • PostgreSQL中日期加减用LAG(enddate) OVER (...) + INTERVAL '1 day'，天数计算用(MAX(enddate) - MIN(startdate)) + 1；
  • SQL Server中日期加减用DATEADD(day, 1, LAG(enddate) OVER (...))，天数计算用DATEDIFF(day, MIN(startdate), MAX(enddate)) + 1。

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