Oracle中基于重叠日期拆分合并跨表匹配数据的技术实现咨询

嘿，这个需求我之前做类似报表时碰到过，核心就是处理日期区间的重叠拆分+跨表匹配后的合并计算，在Oracle里可以用CTE（公共表表达式）分步来实现，我给你拆解思路和具体代码：

第一步：先合并Table1内部的重叠日期区间 #

Table1里本身存在日期重叠的行（比如第二行24/Jun/2024 - 11/Jul/2024和第三行10/Jul/2024 - 20/Jul/2024有重叠，第一行更是覆盖了后续所有时间段），所以先把同一Col1+Col2组内的重叠/连续日期段合并，同时累加Quantity：

WITH table1_merged AS (
    SELECT
        col1,
        col2,
        MIN(start_date) AS start_date,
        MAX(end_date) AS end_date,
        SUM(quantity) AS quantity
    FROM (
        SELECT
            col1,
            col2,
            start_date,
            end_date,
            quantity,
            -- 用窗口函数识别连续/重叠的区间组
            SUM(CASE WHEN start_date <= LAG(end_date) OVER (PARTITION BY col1, col2 ORDER BY start_date) THEN 0 ELSE 1 END) 
                OVER (PARTITION BY col1, col2 ORDER BY start_date) AS group_id
        FROM table1
        -- 如果日期是字符串类型，先转成DATE：TO_DATE(start_date, 'DD/Mon/YYYY')
    ) t
    GROUP BY col1, col2, group_id
),

第二步：收集所有需要拆分的关键日期点 #

把Table1合并后的区间、Table2的区间的起始/结束日期（结束日期+1是为了处理闭区间边界）全部收集起来，作为拆分的节点：

all_dates AS (
    SELECT col1, col2, start_date AS date_point FROM table1_merged
    UNION
    SELECT col1, col2, end_date + 1 AS date_point FROM table1_merged
    UNION
    SELECT col1, col2, start_date AS date_point FROM table2
    UNION
    SELECT col1, col2, end_date + 1 AS date_point FROM table2
),

第三步：生成最小粒度的拆分日期区间 #

基于上面的日期点，生成每个Col1+Col2组内的连续、不重叠的小日期段：

split_intervals AS (
    SELECT
        col1,
        col2,
        date_point AS start_date,
        LEAD(date_point) OVER (PARTITION BY col1, col2 ORDER BY date_point) - 1 AS end_date
    FROM all_dates
    WHERE LEAD(date_point) OVER (PARTITION BY col1, col2 ORDER BY date_point) IS NOT NULL
)

第四步：计算匹配区间的Quantity并过滤非重叠部分 #

最后关联Table1合并后的区间计算每个拆分段的总Quantity，同时通过和Table2的关联，只保留有重叠的区间：

SELECT
    si.col1,
    si.col2,
    si.start_date,
    si.end_date,
    SUM(tm.quantity) AS quantity,
    -- 可选：还原你输出里的Quantity构成说明
    LISTAGG(tm.quantity, '+') WITHIN GROUP (ORDER BY tm.start_date) || ' = ' || SUM(tm.quantity) AS quantity_detail
FROM split_intervals si
-- 关联Table1合并后的区间计算总Quantity
JOIN table1_merged tm
    ON tm.col1 = si.col1
    AND tm.col2 = si.col2
    AND tm.start_date <= si.end_date
    AND tm.end_date >= si.start_date
-- 关联Table2过滤出有重叠的区间
JOIN table2 t2
    ON t2.col1 = si.col1
    AND t2.col2 = si.col2
    AND t2.start_date <= si.end_date
    AND t2.end_date >= si.start_date
GROUP BY si.col1, si.col2, si.start_date, si.end_date
ORDER BY si.col1, si.col2, si.start_date;

代码说明 #

1. Table1合并：用LAG窗口函数识别连续/重叠的区间，分组后合并成不重叠的段，避免重复计算Quantity。
2. 关键日期收集：确保拆分后的每个区间都能精准对应到原始数据的重叠边界。
3. 拆分区间生成：用LEAD生成最小粒度的连续日期段，保证每个段都是独立的无重叠单元。
4. 跨表匹配过滤：通过和Table2的关联，自动丢弃没有重叠的区间，最后分组得到符合要求的结果。

如果你的日期字段是字符串类型，记得在所有用到日期的地方用TO_DATE(日期字段, 'DD/Mon/YYYY')转成Oracle的DATE类型，避免格式错误。

备注：内容来源于stack exchange，提问作者U12