Oracle SQL中填充日期间隙与修复重叠日期范围的查询方法

现有表结构与数据 #

PROD_TABLE和STAGING_TABLE的建表及插入数据SQL如下：

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

CREATE TABLE STAGING_TABLE(Assignment_ID    number, Effective_START_Date date,  Effective_END_Date date, DEPARTMENT VARCHAR2(100));
INSERT INTO STAGING_TABLE VALUES (30001,    TO_DATE('2/2/2020', 'MM/DD/YYYY'),  TO_DATE('5/4/2020', 'MM/DD/YYYY'), 'DEPT1');
INSERT INTO STAGING_TABLE VALUES (30001,    TO_DATE('5/5/2020', 'MM/DD/YYYY'),  TO_DATE('2/2/2021', 'MM/DD/YYYY'), 'DEPT1');

重叠情况与需求 #

STAGING_TABLE的两条记录与PROD_TABLE中的一条日期范围记录存在重叠：

• STAGING_TABLE记录：
  1. 开始日期：2/2/2020，结束日期：5/4/2020
  2. 开始日期：5/5/2020，结束日期：2/2/2021
• PROD_TABLE中被重叠的记录：
  1. 开始日期：2/2/2020，结束日期：2/2/2021

需求是将STAGING_TABLE的记录合并到PROD_TABLE中，替换掉重叠的原有记录，最终预期结果需保留：

1. STAGING_TABLE的两条目标记录
2. PROD_TABLE中未被重叠的其他记录（1/1/2020 - 2/1/2020、2/3/2021 - 3/19/2021、3/20/2023 - 12/31/4712）

现有实现语句 #

以下是当前编写的查询语句：

WITH query1 AS ( SELECT prod_table.Assignment_ID,
       GREATEST(staging_table.effective_start_date, prod_table.effective_start_date) AS effective_START_date,
       LEAST(staging_table.effective_end_date, prod_table.effective_end_date) AS effective_END_date,
       staging_table.DEPARTMENT   FROM PROD_TABLE prod_table,
       STAGING_TABLE staging_table  WHERE prod_table.Assignment_ID = staging_table.Assignment_ID    AND prod_table.effective_start_date <
staging_table.effective_end_date    AND prod_table.effective_end_date
> staging_table.effective_start_date )     SELECT * FROM query1 UNION SELECT prod_table.Assignment_ID,
       prod_table.effective_start_date,
       prod_table.effective_end_date,
       prod_table.DEPARTMENT   FROM PROD_TABLE prod_table  WHERE NOT EXISTS (SELECT 1 
                     FROM query1 qry
                    WHERE prod_table.Assignment_ID = qry.Assignment_ID
                      AND prod_table.effective_start_date BETWEEN qry.effective_start_date AND qry.effective_end_date) ORDER BY  1, 2;

更简洁的实现方式 #

提供两种更简洁高效的写法：

写法一：直接合并+排除重叠区间 #

SELECT Assignment_ID, Effective_START_Date, Effective_END_Date, DEPARTMENT
FROM STAGING_TABLE
UNION ALL
SELECT p.Assignment_ID, p.Effective_START_Date, p.Effective_END_Date, p.DEPARTMENT
FROM PROD_TABLE p
WHERE NOT EXISTS (
    SELECT 1
    FROM STAGING_TABLE s
    WHERE p.Assignment_ID = s.Assignment_ID
      AND p.Effective_START_Date < s.Effective_END_Date
      AND p.Effective_END_Date > s.Effective_START_Date
)
ORDER BY Assignment_ID, Effective_START_Date;

说明：直接加入STAGING_TABLE的所有记录，同时筛选出PROD_TABLE中完全不与STAGING_TABLE重叠的记录，合并后排序。无需提前计算重叠区间，逻辑直观且性能更优。

写法二：合并后减去被覆盖的区间 #

SELECT Assignment_ID, Effective_START_Date, Effective_END_Date, DEPARTMENT
FROM (
    SELECT * FROM STAGING_TABLE
    UNION ALL
    SELECT * FROM PROD_TABLE
) combined
MINUS
SELECT p.Assignment_ID, p.Effective_START_Date, p.Effective_END_Date, p.DEPARTMENT
FROM PROD_TABLE p
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
ORDER BY Assignment_ID, Effective_START_Date;

说明：先合并两张表的所有记录，再减去PROD_TABLE中与STAGING_TABLE重叠的记录，最终得到替换后的结果，代码更简洁。

两种写法均能输出符合预期的结果。

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