Oracle SQL求助：合并Tab_A与Tab_B生成连续EFF/DISC日期的结果表

表结构与测试数据 #

以下是Tab_A和Tab_B的建表语句及测试数据：

create table tab_A  
(item       varchar2(20),
 dest       varchar2(20),
 eff        date,
 disc       date,
 qty        number);
 
create table tab_B
(item       varchar2(20),
 source     varchar2(20),
 dest       varchar2(20),
 eff        date,
 disc       date);
 
 insert into tab_a values('item1','dest1','01 July 2022','31 July 2022',20);
 insert into tab_a values('item1','dest1','31 July 2022','15 Aug 2022',30);
 insert into tab_a values('item1','dest1','15 Aug 2022','15 Sep 2022',40);
 insert into tab_a values('item1','dest1','15 Sep 2022','15 Jan 2023',20);
 
 insert into tab_b values('item1-PS1','source1','dest1','01 July 2022','5 August 2022');
 insert into tab_b values('item1-PS2','source2','dest1','5 Aug 2022','10 Oct 2022');
 insert into tab_b values('item1-PS3','source3','dest1','10 October 2022','1 Feb 2023');

关联规则与需求 #

• Tab_A与Tab_B通过**物品核心编码（Tab_B的item去掉-PS后缀后与Tab_A的item匹配）**和dest字段关联
• 合并后生成的新表需具备连续的EFF（生效日期）和DISC（失效日期）区间：
  1. 以Tab_A的日期区间为基础，保留对应qty值
  2. 若Tab_B的日期区间覆盖Tab_A的部分区间，对应行使用Tab_B的item
  3. 当Tab_A的日期区间超出Tab_B的DISC日期时，Tab_A的记录以该DISC日期结束，同时新增行采用Tab_B的新item及对应EFF日期，延续至原Tab_A的DISC日期或Tab_B的下一个DISC日期

解决方案SQL #

WITH tab_a_clean AS (
    -- 确保Tab_A的日期区间合法（示例数据已满足，此步骤兼容通用场景）
    SELECT item, dest, eff, disc, qty
    FROM tab_a
),
tab_b_clean AS (
    -- 提取Tab_B物品的核心编码，用于关联Tab_A
    SELECT item, source, dest, eff, disc,
           REGEXP_REPLACE(item, '-PS\d+$', '') AS core_item
    FROM tab_b
),
date_boundaries AS (
    -- 收集所有关键日期点，用于拆分区间
    SELECT eff AS dt FROM tab_a_clean
    UNION
    SELECT disc AS dt FROM tab_a_clean
    UNION
    SELECT eff AS dt FROM tab_b_clean
    UNION
    SELECT disc AS dt FROM tab_b_clean
),
date_ranges AS (
    -- 生成连续的日期区间
    SELECT dt AS eff_dt,
           LEAD(dt) OVER (ORDER BY dt) AS disc_dt
    FROM date_boundaries
    WHERE LEAD(dt) OVER (ORDER BY dt) IS NOT NULL
),
matched_ranges AS (
    -- 关联Tab_A、Tab_B和拆分后的日期区间，筛选有效重叠部分
    SELECT dr.eff_dt,
           dr.disc_dt,
           COALESCE(tb.item, ta.item) AS item,
           ta.dest,
           ta.qty,
           tb.source
    FROM date_ranges dr
    JOIN tab_a_clean ta 
        ON dr.eff_dt < ta.disc 
        AND dr.disc_dt > ta.eff
        AND dr.eff_dt < dr.disc_dt
    LEFT JOIN tab_b_clean tb 
        ON ta.item = tb.core_item
        AND ta.dest = tb.dest
        AND dr.eff_dt < tb.disc 
        AND dr.disc_dt > tb.eff
    WHERE dr.disc_dt <= ta.disc -- 仅保留在Tab_A原区间内的部分
    ORDER BY dr.eff_dt
)
SELECT item, dest, eff_dt AS eff, disc_dt AS disc, qty, source
FROM matched_ranges;

逻辑说明 #

1. 数据预处理：
   • tab_a_clean：确保Tab_A的区间合规，兼容更复杂的原始数据
   • tab_b_clean：提取Tab_B物品的核心编码，实现和Tab_A的关联匹配
2. 日期拆分：
   • date_boundaries：收集所有Tab_A和Tab_B的生效、失效日期，作为拆分区间的节点
   • date_ranges：利用LEAD()函数生成连续的日期区间
3. 关联匹配：
   • 将拆分后的日期区间与Tab_A、Tab_B关联，筛选出重叠的有效区间
   • 使用COALESCE()优先取Tab_B的item，无匹配时保留Tab_A的item
4. 结果输出：整理字段顺序，输出连续区间的合并结果

内容的提问来源于stack exchange，提问作者B-Rad