Oracle基于另一表时间区间拆分压缩表记录的实现方案

核心解决方案 #

直接通过区间重叠判断与日期函数计算，避免按日拆分的性能问题，SQL语句如下：

SELECT
    t1.rec#,
    t1.col1,
    GREATEST(t1.startdate, t2.startdate) AS startdate,
    LEAST(t1.enddate, t2.enddate) AS enddate
FROM
    table1 t1
JOIN
    table2 t2 ON t1.col1 = t2.col1
        -- 判断两个区间存在重叠的核心条件
        AND t1.startdate <= t2.enddate
        AND t2.startdate <= t1.enddate
ORDER BY
    t1.rec#, startdate;

逻辑说明 #

1. 关联与过滤：通过col1关联两张表，并使用区间重叠条件t1.startdate <= t2.enddate AND t2.startdate <= t1.enddate，直接过滤掉完全无重叠的记录（如table1的Rec2会被自动排除）。
2. 计算交集区间：
   • 用GREATEST()取两个区间的较晚起始日期，作为有效区间的开始
   • 用LEAST()取两个区间的较早结束日期，作为有效区间的结束，自动兼容31-Dec-9999这类最大日期场景
3. 自动拆分长区间：当table1的单个区间与table2的多个区间重叠时，会自动生成多条拆分后的有效记录（如table1的Rec1会与table2的Rec1、Rec2分别生成两条结果）

修正后的建表语句（包含Rec#字段） #

原DDL未包含示例中的Rec#字段，补充后如下：

Create table table1 as
select 1 rec#, 'A' col1,to_date('15-09-2024','DD-MM-YYYY') startdate, to_date('31-10-2024','DD-MM-YYYY') enddate from dual
union
select 2 rec#, 'A',to_date('01-01-2025','DD-MM-YYYY') startdate, to_date('20-02-2025','DD-MM-YYYY') enddate from dual
union
select 3 rec#, 'A',to_date('08-03-2025','DD-MM-YYYY') startdate, to_date('31-12-9999','DD-MM-YYYY') enddate from dual;

Create table table2 as
select 1 rec#, 'A' col1,to_date('20-09-2024','DD-MM-YYYY') startdate, to_date('15-10-2024','DD-MM-YYYY') enddate from dual
union
select 2 rec#, 'A',to_date('20-10-2024','DD-MM-YYYY') startdate, to_date('10-11-2024','DD-MM-YYYY') enddate from dual
union
select 3 rec#, 'A',to_date('15-03-2025','DD-MM-YYYY') startdate, to_date('01-06-2025','DD-MM-YYYY') enddate from dual;

执行结果 #

运行核心SQL后，将得到与预期完全一致的输出：

Rec#   Col1    startdate           enddate
-------------------------------------------
1       A      20-Sep-24       15-Oct-24
1       A      20-Oct-24       31-Oct-24 
3       A      15-Mar-25       1-Jun-25

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