Oracle中多行数据不同Rule ID的日期范围交集求解

针对你的需求，不需要生成每一天的日期（这会因9999-12-31这类超大日期导致性能问题），我们可以直接基于区间重叠逻辑实现高效计算，步骤如下：

核心思路 #

1. 先合并同一Item_no+item_type+rule_id下的重叠/连续区间，避免同一规则内的多区间干扰。
2. 计算不同rule_id之间的区间交集。
3. 合并重叠的交集区间，得到最终的共同日期范围。

实现SQL #

WITH rule_intervals AS (
    SELECT 
        Item_no,
        item_type,
        rule_id,
        active_from,
        active_to,
        SUM(CASE WHEN prev_end >= active_from THEN 0 ELSE 1 END) OVER (PARTITION BY Item_no, item_type, rule_id ORDER BY active_from) AS grp
    FROM (
        SELECT 
            Item_no,
            item_type,
            rule_id,
            active_from,
            active_to,
            LAG(active_to) OVER (PARTITION BY Item_no, item_type, rule_id ORDER BY active_from) AS prev_end
        FROM your_table_name -- 替换为你的实际表名
    ) t
),
merged_rule_intervals AS (
    SELECT 
        Item_no,
        item_type,
        rule_id,
        MIN(active_from) AS merged_from,
        MAX(active_to) AS merged_to
    FROM rule_intervals
    GROUP BY Item_no, item_type, rule_id, grp
),
cross_rule_overlaps AS (
    SELECT 
        a.Item_no,
        a.item_type,
        GREATEST(a.merged_from, b.merged_from) AS overlap_from,
        LEAST(a.merged_to, b.merged_to) AS overlap_to
    FROM merged_rule_intervals a
    JOIN merged_rule_intervals b 
        ON a.Item_no = b.Item_no
        AND a.item_type = b.item_type
        AND a.rule_id < b.rule_id -- 避免重复配对
        AND a.merged_from <= b.merged_to
        AND b.merged_from <= a.merged_to -- 仅保留有重叠的区间对
),
final_overlaps AS (
    SELECT 
        Item_no,
        item_type,
        MIN(overlap_from) AS active_from,
        MAX(overlap_to) AS active_to
    FROM (
        SELECT 
            Item_no,
            item_type,
            overlap_from,
            overlap_to,
            SUM(CASE WHEN prev_overlap_to >= overlap_from THEN 0 ELSE 1 END) OVER (PARTITION BY Item_no, item_type ORDER BY overlap_from) AS grp
        FROM (
            SELECT 
                Item_no,
                item_type,
                overlap_from,
                overlap_to,
                LAG(overlap_to) OVER (PARTITION BY Item_no, item_type ORDER BY overlap_from) AS prev_overlap_to
            FROM cross_rule_overlaps
        ) t
    ) t
    GROUP BY Item_no, item_type, grp
)
SELECT * FROM final_overlaps;

逻辑说明 #

1. rule_intervals：通过LAG函数获取前一个区间的结束日期，标记出同一规则下的连续/重叠区间组。
2. merged_rule_intervals：按组合并同一规则下的区间，得到每个规则的有效合并区间。
3. cross_rule_overlaps：将不同规则的区间配对，用GREATEST和LEAST计算重叠部分，仅保留有实际重叠的配对。
4. final_overlaps：合并多个交集区间（如果存在），得到不重叠的最终共同日期范围。

针对你的示例数据的效果 #

• 合并后rule1的SAR区间为2020-01-01至2023-01-01，rule2的SAR区间合并为2020-05-01至2021-06-01。
• 两者的交集为2020-05-01至2021-06-01，与你期望的输出完全一致。

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