Oracle中合并重叠日期范围的SQL实现问题

需求说明 #

• 保留PROD_TABLE中与STAGING_TABLE无重叠的记录；
• 若PROD_TABLE的日期范围与STAGING_TABLE完全匹配，仅更新ACTION_REASON字段为STAGING_TABLE对应值；
• 移除PROD_TABLE中与STAGING_TABLE重叠的分段记录，替换为STAGING_TABLE的完整日期范围记录。

测试表结构与数据 #

CREATE TABLE PROD_TABLE(Assignment_ID   number, Effective_START_Date date,  Effective_END_Date date, ACTION_REASON VARCHAR2(100));

INSERT INTO PROD_TABLE VALUES (30001,   TO_DATE('1/1/2010', 'MM/DD/YYYY'),  TO_DATE('2/2/2015', 'MM/DD/YYYY'), 'Name Change');
INSERT INTO PROD_TABLE VALUES (30001,   TO_DATE('2/3/2015', 'MM/DD/YYYY'),  TO_DATE('5/5/2021', 'MM/DD/YYYY'), 'Name Change');
INSERT INTO PROD_TABLE VALUES (30001,   TO_DATE('5/6/2021', 'MM/DD/YYYY'),  TO_DATE('3/3/2023', 'MM/DD/YYYY'), 'Name Change');
INSERT INTO PROD_TABLE VALUES (30001,   TO_DATE('3/4/2023', 'MM/DD/YYYY'),  TO_DATE('12/31/4712', 'MM/DD/YYYY'), 'Name Change');

CREATE TABLE STAGING_TABLE(Assignment_ID    number, Effective_START_Date date,  Effective_END_Date date, ACTION_REASON VARCHAR2(100));

INSERT INTO STAGING_TABLE VALUES (30001,    TO_DATE('5/6/2021', 'MM/DD/YYYY'),  TO_DATE('12/31/4712', 'MM/DD/YYYY'), 'Manager Change');

现有SQL问题分析 #

你的现有SQL逻辑存在偏差：

1. query1仅提取了PROD与STAGING的重叠区间，未处理PROD中完全不重叠的记录；
2. query2错误地从STAGING_TABLE筛选非重叠记录，逻辑颠倒，应该从PROD_TABLE中排除被STAGING覆盖的区间；
3. 未实现"替换为STAGING完整区间"的核心需求，也未处理完全匹配时的ACTION_REASON更新。

解决方案SQL #

正确的实现逻辑是：先筛选PROD中未被STAGING覆盖的记录，再加入STAGING的完整记录，最终合并结果。

WITH non_overlapping_prod AS (
    -- 筛选PROD_TABLE中与STAGING_TABLE无任何重叠的记录
    SELECT p.Assignment_ID,
           p.Effective_START_Date,
           p.Effective_END_Date,
           p.ACTION_REASON
      FROM PROD_TABLE p
      LEFT JOIN STAGING_TABLE s
        ON p.Assignment_ID = s.Assignment_ID
       -- 判定日期范围重叠的标准条件
       AND p.Effective_START_Date < s.Effective_END_Date
       AND p.Effective_END_Date > s.Effective_START_Date
     WHERE s.Assignment_ID IS NULL
),
staging_full_records AS (
    -- 获取STAGING_TABLE的完整记录，用于替换重叠区间
    SELECT s.Assignment_ID,
           s.Effective_START_Date,
           s.Effective_END_Date,
           s.ACTION_REASON
      FROM STAGING_TABLE s
)
-- 合并无重叠PROD记录和STAGING完整记录，按ID和起始日期排序
SELECT * FROM non_overlapping_prod
UNION ALL
SELECT * FROM staging_full_records
ORDER BY Assignment_ID, Effective_START_Date;

验证结果 #

执行后将得到符合需求的输出：

该结果满足所有需求：

• 保留了PROD中未与STAGING重叠的两段记录；
• 替换了PROD中重叠的两个分段为STAGING的完整日期区间，并更新了ACTION_REASON；
• 若存在PROD与STAGING日期范围完全匹配的记录，该PROD记录会被排除在non_overlapping_prod之外，最终以STAGING的记录呈现，实现了仅更新ACTION_REASON的效果。

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