Oracle SQL实现区间交集并完整保留Dataset1区间的方案求助

问题概述 #

需实现两个区间数据集（Dataset1、Dataset2）的交集计算，同时完整保留Dataset1的所有区间片段——包括完全不相交部分、与Dataset2部分相交的剩余片段、以及相交片段。Dataset1/2均包含区间起止（From/To）及对应Value字段。

纯SQL解决方案（推荐） #

该方案通过生成拆分点将Dataset1拆分为最小粒度的子区间，再关联Dataset2判断交集状态，无需存储过程即可完成需求。

假设表结构 #

-- Dataset1：需完整保留的基准区间表
CREATE TABLE Dataset1 (
    d1_id NUMBER PRIMARY KEY,
    d1_from NUMBER NOT NULL,
    d1_to NUMBER NOT NULL,
    d1_value VARCHAR2(100) NOT NULL,
    CONSTRAINT chk_d1_range CHECK (d1_from < d1_to)
);

-- Dataset2：用于交集匹配的对比区间表
CREATE TABLE Dataset2 (
    d2_id NUMBER PRIMARY KEY,
    d2_from NUMBER NOT NULL,
    d2_to NUMBER NOT NULL,
    d2_value VARCHAR2(100) NOT NULL,
    CONSTRAINT chk_d2_range CHECK (d2_from < d2_to)
);

核心SQL代码 #

WITH split_points AS (
    -- 收集所有用于拆分Dataset1的关键点：自身端点+与Dataset1有交集的Dataset2端点
    SELECT d1_from AS point FROM Dataset1
    UNION
    SELECT d1_to AS point FROM Dataset1
    UNION
    SELECT d2_from AS point FROM Dataset2
    WHERE EXISTS (
        SELECT 1 FROM Dataset1 d1
        WHERE d1.d1_from < d2.d2_to AND d1.d1_to > d2.d2_from
    )
    UNION
    SELECT d2_to AS point FROM Dataset2
    WHERE EXISTS (
        SELECT 1 FROM Dataset1 d1
        WHERE d1.d1_from < d2.d2_to AND d1.d1_to > d2.d2_from
    )
),
ordered_points AS (
    -- 对拆分点排序，生成连续的区间对
    SELECT 
        point AS start_point,
        LEAD(point) OVER (ORDER BY point) AS end_point
    FROM split_points
),
d1_split_segments AS (
    -- 筛选出属于Dataset1原始区间的子区间，确保只保留Dataset1的部分
    SELECT 
        d1.d1_id,
        d1.d1_value,
        GREATEST(op.start_point, d1.d1_from) AS segment_from,
        LEAST(op.end_point, d1.d1_to) AS segment_to
    FROM ordered_points op
    JOIN Dataset1 d1 
        ON op.start_point < d1.d1_to AND op.end_point > d1.d1_from
    WHERE op.end_point IS NOT NULL -- 排除无后续端点的孤立点
),
final_result AS (
    -- 关联Dataset2，标记交集状态并带出对应Value
    SELECT 
        ds.d1_id,
        ds.segment_from,
        ds.segment_to,
        ds.d1_value,
        d2.d2_value,
        CASE WHEN d2.d2_value IS NOT NULL THEN '相交' ELSE '不相交' END AS segment_status
    FROM d1_split_segments ds
    LEFT JOIN Dataset2 d2
        ON ds.segment_from < d2.d2_to AND ds.segment_to > d2.d2_from
)
-- 按Dataset1 ID和区间起始排序输出
SELECT * FROM final_result ORDER BY d1_id, segment_from;

代码逻辑说明 #

1. split_points：收集所有能拆分Dataset1的关键点，确保拆分后覆盖所有与Dataset2相交/不相交的场景。
2. ordered_points：将拆分点排序，生成连续的区间对，为后续拆分做准备。
3. d1_split_segments：筛选出落在Dataset1原始区间内的子区间，避免生成无关区间。
4. final_result：左关联Dataset2，判断每个子区间是否相交，同时保留Dataset1的所有片段。

PL/SQL解决方案（适合复杂业务扩展） #

如果需要更灵活的业务逻辑处理（如自定义合并规则），可使用存储过程实现：

存储过程代码 #

CREATE OR REPLACE PROCEDURE split_d1_with_intersect(
    p_result_table VARCHAR2 DEFAULT 'D1_INTERSECT_RESULT'
) IS
    CURSOR c_d1 IS SELECT d1_id, d1_from, d1_to, d1_value FROM Dataset1;
    v_d1 c_d1%ROWTYPE;
    TYPE point_list IS TABLE OF NUMBER;
    v_points point_list;
    v_start NUMBER;
    v_end NUMBER;
BEGIN
    -- 初始化结果表（若不存在则创建）
    EXECUTE IMMEDIATE 'CREATE TABLE IF NOT EXISTS ' || p_result_table || ' (
        d1_id NUMBER,
        segment_from NUMBER,
        segment_to NUMBER,
        d1_value VARCHAR2(100),
        d2_value VARCHAR2(100),
        segment_status VARCHAR2(20)
    )';
    
    -- 清空历史结果
    EXECUTE IMMEDIATE 'DELETE FROM ' || p_result_table;
    
    OPEN c_d1;
    LOOP
        FETCH c_d1 INTO v_d1;
        EXIT WHEN c_d1%NOTFOUND;
        
        -- 收集当前Dataset1区间的所有拆分点
        SELECT point
        BULK COLLECT INTO v_points
        FROM (
            SELECT v_d1.d1_from AS point FROM DUAL
            UNION
            SELECT v_d1.d1_to AS point FROM DUAL
            UNION
            SELECT d2_from AS point FROM Dataset2
            WHERE d2_from BETWEEN v_d1.d1_from AND v_d1.d1_to
               OR d2_to BETWEEN v_d1.d1_from AND v_d1.d1_to
            UNION
            SELECT d2_to AS point FROM Dataset2
            WHERE d2_from BETWEEN v_d1.d1_from AND v_d1.d1_to
               OR d2_to BETWEEN v_d1.d1_from AND v_d1.d1_to
        )
        ORDER BY point;
        
        -- 遍历拆分点生成子区间并插入结果
        FOR i IN 1..v_points.COUNT-1 LOOP
            v_start := v_points(i);
            v_end := v_points(i+1);
            
            -- 插入相交的子区间（关联Dataset2）
            EXECUTE IMMEDIATE 'INSERT INTO ' || p_result_table || '
                SELECT :d1_id, :start, :end, :d1_val, d2.d2_value, ''相交''
                FROM Dataset2 d2
                WHERE :start < d2.d2_to AND :end > d2.d2_from'
            USING v_d1.d1_id, v_start, v_end, v_d1.d1_value, v_start, v_end;
            
            -- 插入不相交的子区间（仅保留Dataset1信息）
            EXECUTE IMMEDIATE 'INSERT INTO ' || p_result_table || '
                SELECT :d1_id, :start, :end, :d1_val, NULL, ''不相交''
                FROM DUAL
                WHERE NOT EXISTS (
                    SELECT 1 FROM Dataset2 d2
                    WHERE :start < d2.d2_to AND :end > d2.d2_from)'
            USING v_d1.d1_id, v_start, v_end, v_d1.d1_value, v_start, v_end;
        END LOOP;
    END LOOP;
    CLOSE c_d1;
    COMMIT;
END;
/

使用方式 #

-- 执行存储过程，生成结果到默认表
EXEC split_d1_with_intersect;

-- 或指定结果表名
EXEC split_d1_with_intersect('MY_CUSTOM_RESULT');

注意事项 #

• 确保所有区间的From < To，可通过表约束强制实现。
• 若Dataset2多个区间与同一子区间相交，纯SQL方案会返回多条记录，可使用LISTAGG(d2_value, ',') WITHIN GROUP (ORDER BY d2_id)合并Value。
• 大数据量场景下，建议给d1_from、d1_to、d2_from、d2_to字段添加索引优化性能。

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