如何在Oracle SQL中编写查询修复日期间隙与重叠问题

需求说明 #

需按Assignment_ID合并PROD_TABLE与STAGING_TABLE的日期区间数据，自动处理区间的重叠、连续或间隙情况，生成无重叠、合理保留间隙的完整日期区间集。

测试数据建表语句 #

CREATE TABLE PROD_TABLE(
    Assignment_ID   number, 
    Effective_START_Date date,  
    Effective_END_Date date
);
INSERT INTO prod_table VALUES (30001,   TO_DATE('1/1/2001', 'MM/DD/YYYY'),  TO_DATE('2/1/2020', 'MM/DD/YYYY'));
INSERT INTO prod_table VALUES (30001,   TO_DATE('2/2/2020', 'MM/DD/YYYY'),  TO_DATE('2/2/2021', 'MM/DD/YYYY'));
INSERT INTO prod_table VALUES (30001,   TO_DATE('2/3/2021', 'MM/DD/YYYY'),  TO_DATE('3/19/2021', 'MM/DD/YYYY'));
INSERT INTO prod_table VALUES (30001,   TO_DATE('3/20/2023', 'MM/DD/YYYY'), TO_DATE('12/31/4712', 'MM/DD/YYYY'));
INSERT INTO prod_table VALUES (44444,   TO_DATE('3/20/2023', 'MM/DD/YYYY'), TO_DATE('12/31/4712', 'MM/DD/YYYY'));

CREATE TABLE STAGING_TABLE(
    Assignment_ID    number, 
    Effective_START_Date date,  
    Effective_END_Date date
);
INSERT INTO STAGING_TABLE VALUES (30001,   TO_DATE('1/1/2001', 'MM/DD/YYYY'),  TO_DATE('2/1/2020', 'MM/DD/YYYY'));
INSERT INTO STAGING_TABLE VALUES (30001,   TO_DATE('2/2/2020', 'MM/DD/YYYY'),  TO_DATE('5/4/2020', 'MM/DD/YYYY'));
INSERT INTO STAGING_TABLE VALUES (30001,   TO_DATE('5/5/2020', 'MM/DD/YYYY'),  TO_DATE('2/2/2021', 'MM/DD/YYYY'));
INSERT INTO STAGING_TABLE VALUES (30001,   TO_DATE('2/3/2021', 'MM/DD/YYYY'),  TO_DATE('3/19/2021', 'MM/DD/YYYY'));
INSERT INTO STAGING_TABLE VALUES (30001,   TO_DATE('3/20/2023', 'MM/DD/YYYY'),     TO_DATE('12/31/4712', 'MM/DD/YYYY'));
INSERT INTO STAGING_TABLE VALUES (44444,   TO_DATE('3/20/2023', 'MM/DD/YYYY'),     TO_DATE('4/19/2023', 'MM/DD/YYYY'));
INSERT INTO STAGING_TABLE VALUES (44444,   TO_DATE('4/20/2023', 'MM/DD/YYYY'),     TO_DATE('12/31/4712', 'MM/DD/YYYY'));

解决方案SQL #

WITH combined_data AS (
    -- 合并两张表的所有日期区间数据
    SELECT Assignment_ID, Effective_START_Date, Effective_END_Date FROM PROD_TABLE
    UNION ALL
    SELECT Assignment_ID, Effective_START_Date, Effective_END_Date FROM STAGING_TABLE
),
ordered_data AS (
    -- 按Assignment_ID分组排序，标记连续/重叠的区间组
    SELECT 
        Assignment_ID,
        Effective_START_Date,
        Effective_END_Date,
        -- 当前区间起始 > 前一区间结束+1时，新建分组
        SUM(CASE 
            WHEN Effective_START_Date > LAG(Effective_END_Date) OVER (PARTITION BY Assignment_ID ORDER BY Effective_START_Date) + 1 
            THEN 1 ELSE 0 
        END) OVER (PARTITION BY Assignment_ID ORDER BY Effective_START_Date) AS group_id
    FROM combined_data
)
-- 按分组聚合生成合并后的完整区间
SELECT 
    Assignment_ID,
    MIN(Effective_START_Date) AS merged_start_date,
    MAX(Effective_END_Date) AS merged_end_date
FROM ordered_data
GROUP BY Assignment_ID, group_id
ORDER BY Assignment_ID, merged_start_date;

逻辑说明 #

1. combined_data：合并两张表的所有原始区间数据，保留重复、重叠的记录。
2. ordered_data：按Assignment_ID分组，对区间按起始日期排序；利用LAG函数对比当前与前一区间的日期，判断是否存在间隙，生成分组ID区分不连续的区间段。
3. 最终聚合：按Assignment_ID和分组ID进行聚合，取每个组的最小起始日期和最大结束日期，得到合并后无重叠、保留合理间隙的日期区间。

测试结果说明 #

• Assignment_ID=30001：合并后保留原有的连续区间，2021-03-19到2023-03-19的间隙会被保留（两张表均存在该间隙）。
• Assignment_ID=44444：两张表的连续区间会合并为一个完整的2023-03-20至9999-12-31的区间。

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