如何基于TEST1和TEST2两张表拆分生成目标日期区间？

一、数据库表结构及初始化数据 #

CREATE TABLE "TEST1" 
(   
    "ID" NUMBER(9,0) NOT NULL ENABLE, 
    "DATE_FROM" DATE, 
    "DATE_TO" DATE
);

CREATE TABLE "TEST2" 
(   
    "ID" NUMBER, 
    "PERCENT" NUMBER(5,4), 
    "DATE_FROM" DATE
);
   
INSERT INTO test1 (ID, DATE_FROM, DATE_TO)
VALUES (546, to_date('01-12-2005', 'dd-mm-yyyy'), to_date('01-12-2006', 'dd-mm-yyyy'));

INSERT INTO test1 (ID, DATE_FROM, DATE_TO)
VALUES (546, to_date('01-12-2006', 'dd-mm-yyyy'), to_date('01-07-2011', 'dd-mm-yyyy'));

INSERT INTO test1 (ID, DATE_FROM, DATE_TO)
VALUES (546, to_date('01-07-2011', 'dd-mm-yyyy'), to_date('31-12-4712', 'dd-mm-yyyy'));

INSERT INTO test2 (ID, PERCENT, DATE_FROM)
VALUES (546, 0.0100, to_date('01-12-2005', 'dd-mm-yyyy'));

INSERT INTO test2 (ID, PERCENT, DATE_FROM)
VALUES (546, 0.0450, to_date('01-12-2009', 'dd-mm-yyyy'));

二、需求说明 #

需要将TEST2中的日期节点融入TEST1的日期区间，最终得到4条拆分后的记录。此前尝试用笛卡尔积得到6条冗余记录，使用WHERE条件过滤时又丢失了t2.date_From字段值。由于原SQL规模较大，要求避免多次调用这两张表。

三、现有可行实现方案 #

SELECT * 
FROM 
    (SELECT 
         t1.date_from,
         t1.date_to,
         t2.percent,
         t2.date_from AS date_from2,
         t2.max_date,
         NVL(GREATEST(t1.date_from, t2.date_From), t1.date_from) AS new_vf,
         NVL(GREATEST(NVL(LEAD(GREATEST(t1.date_from, t2.date_From)) OVER (ORDER BY t1.date_from ASC), t1.date_to), t2.date_From), t1.date_to) AS new_vt
     FROM   
         test1 t1
     LEFT JOIN   
         (SELECT  
              t3.*,
              MAX(date_from) OVER() AS max_Date
          FROM    
              test2 t3) t2 ON t1.id = t2.id
                           AND t2.max_date BETWEEN t1.date_From AND t1.date_to
    ) t
ORDER BY 
    t.date_From ASC, t.date_to ASC

四、更优SQL实现方案 #

以下方案通过合并所有关键日期节点，再利用窗口函数生成连续区间，仅需关联两张表各一次，逻辑更清晰且性能更优：

WITH all_dates AS (
    -- 收集TEST1的区间起止日期和TEST2的节点日期
    SELECT id, date_from AS event_date FROM test1
    UNION ALL
    SELECT id, date_to AS event_date FROM test1
    UNION ALL
    SELECT id, date_from AS event_date FROM test2
),
sorted_dates AS (
    -- 按ID和日期排序，生成每个日期对应的下一个日期（即区间结束）
    SELECT 
        id,
        event_date AS new_vf,
        LEAD(event_date) OVER (PARTITION BY id ORDER BY event_date) AS new_vt
    FROM all_dates
),
valid_intervals AS (
    -- 过滤掉无效区间，确保属于原TEST1的有效范围
    SELECT 
        id,
        new_vf,
        new_vt
    FROM sorted_dates
    WHERE new_vt IS NOT NULL
    AND EXISTS (
        SELECT 1 FROM test1 t1
        WHERE t1.id = sorted_dates.id
        AND sorted_dates.new_vf >= t1.date_from
        AND sorted_dates.new_vt <= t1.date_to
    )
)
-- 关联TEST2获取对应区间的PERCENT值（取区间起始日期前最新的PERCENT）
SELECT 
    vi.id,
    vi.new_vf,
    vi.new_vt,
    (SELECT MAX(t2.percent) KEEP (DENSE_RANK LAST ORDER BY t2.date_from)
     FROM test2 t2
     WHERE t2.id = vi.id AND t2.date_from <= vi.new_vf) AS percent
FROM valid_intervals vi
ORDER BY vi.new_vf;

方案说明： #

1. all_dates：合并TEST1的所有区间起止日期和TEST2的节点日期，确保所有需要拆分的节点都被覆盖；
2. sorted_dates：按ID分组、日期排序，用LEAD函数生成每个日期对应的下一个日期，形成拆分后的区间；
3. valid_intervals：过滤掉不在原TEST1区间内的无效拆分，确保结果符合原始数据范围；
4. 最后关联TEST2，通过KEEP (DENSE_RANK LAST ORDER BY t2.date_from)获取对应区间生效的最新PERCENT值，避免多次关联。

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