Oracle中高效标记被G订单完全覆盖的E订单方案咨询

问题描述 #

订单分为E、G两种类型，需标记所有满足以下条件的订单：同一part、supplier下，存在一个或多个G订单完全覆盖某E订单的时间范围。

数据量达百万级，不希望采用自连接方案。最初尝试用窗口函数，按part、supplier、start_date、end_date分区，通过判断max(order_type)='E'标记无对应G订单的E订单，但该方案因日期未对齐失效（例如2023年的E订单被两个G订单分段完全覆盖的场景）。若采用自连接，需在ON子句中使用类似“E订单start_date介于G订单start_date和end_date之间”的OR条件判断完全覆盖，但效率不足，现寻求高效解决方案。

示例数据 #

select  'part1' as part , 'supplierAAA' as supplier , 'order1' as order_name, 'E' as order_type , to_date('01-01-2023','dd-mm-yyyy') as start_date , to_date('31-12-2023','dd-mm-yyyy') as end_date from dual
union all 
 select  'part2' as part , 'supplierBBB' as supplier , 'order2' as order_name, 'E' as order_type , to_date('01-01-2022','dd-mm-yyyy') as start_date , to_date('31-12-2022','dd-mm-yyyy') as end_date from dual
union all 
 select  'part2' as part , 'supplierBBB' as supplier , 'order4' as order_name, 'G' as order_type , to_date('01-01-2023','dd-mm-yyyy') as start_date , to_date('31-12-2023','dd-mm-yyyy') as end_date from dual
union all 
 select  'part3' as part , 'supplierCCC' as supplier , 'order5' as order_name, 'E' as order_type , to_date('01-01-2022','dd-mm-yyyy') as start_date , to_date('30-06-2022','dd-mm-yyyy') as end_date from dual
union all 
 select  'part3' as part , 'supplierCCC' as supplier , 'order6' as order_name, 'E' as order_type , to_date('01-01-2023','dd-mm-yyyy') as start_date , to_date('31-12-2023','dd-mm-yyyy') as end_date from dual
union all 
 select  'part3' as part , 'supplierCCC' as supplier , 'order7' as order_name, 'G' as order_type , to_date('01-01-2022','dd-mm-yyyy') as start_date , to_date('30-06-2022','dd-mm-yyyy') as end_date from dual
union all 
 select  'part3' as part , 'supplierCCC' as supplier , 'order8' as order_name, 'G' as order_type , to_date('01-01-2023','dd-mm-yyyy') as start_date , to_date('15-12-2023','dd-mm-yyyy') as end_date from dual
union all 
 select  'part3' as part , 'supplierCCC' as supplier , 'order9' as order_name, 'G' as order_type , to_date('16-12-2023','dd-mm-yyyy') as start_date , to_date('31-12-2024','dd-mm-yyyy') as end_date from dual

高效解决方案 #

通过合并G订单的连续/重叠时间区间，再匹配E订单的方式实现，全程用窗口函数避免自连接，适合百万级数据量：

实现SQL #

WITH g_merged AS (
    SELECT 
        part,
        supplier,
        MIN(start_date) AS g_start,
        MAX(end_date) AS g_end
    FROM (
        SELECT 
            part,
            supplier,
            start_date,
            end_date,
            -- 标记连续/重叠区间的分组ID
            SUM(CASE WHEN start_date > LAG(end_date) OVER (PARTITION BY part, supplier ORDER BY start_date) THEN 1 ELSE 0 END) OVER (PARTITION BY part, supplier ORDER BY start_date) AS grp_id
        FROM your_table
        WHERE order_type = 'G'
    ) t
    GROUP BY part, supplier, grp_id
),
e_covered AS (
    SELECT 
        o.*,
        CASE 
            WHEN EXISTS (
                SELECT 1 
                FROM g_merged gm 
                WHERE gm.part = o.part 
                  AND gm.supplier = o.supplier 
                  AND gm.g_start <= o.start_date 
                  AND gm.g_end >= o.end_date
            ) THEN 'Y' ELSE 'N' END AS is_covered_by_g
    FROM your_table o
    WHERE order_type = 'E'
)
SELECT 
    o.part,
    o.supplier,
    o.order_name,
    o.order_type,
    o.start_date,
    o.end_date,
    ec.is_covered_by_g
FROM your_table o
LEFT JOIN e_covered ec 
    ON o.part = ec.part 
    AND o.supplier = ec.supplier 
    AND o.order_name = ec.order_name;

方案说明 #

1. 合并G订单区间：用LAG()窗口函数比较当前G订单的start_date和上一个G订单的end_date，若当前start_date晚于上一个end_date，则视为新的区间分组；最后按分组ID合并，得到每个part+supplier下的完整G覆盖区间。
2. 匹配E订单：判断E订单的时间范围是否被合并后的G区间完全包含，避免了大量自连接操作，效率显著提升。
3. 性能优化：在part、supplier字段上建立复合索引，可进一步加快区间合并和匹配的速度。

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