如何用高效Oracle SQL（含MATCH_RECOGNIZE）筛选异常订单

需求说明 #

现有按零件号（partno）分组的订单数据，每条订单包含字段：orderno（订单号）、start_date（起始日期）、end_date（结束日期）、rate（费率，取值0-100）。

有效状态定义：零件号下的所有订单，在任意时间段内的重叠订单总费率必须为80或100（单订单费率为80/100，或多重叠订单费率之和为80/100）。

筛选目标：找出所有存在至少一个时间段总费率非80/100的零件号下的全部订单，示例逻辑如下：

• part1：两订单同期运行总费率100 → 无需展示
• part2：存在单订单运行时段费率为50 → 需展示两订单
• part3：存在单日仅订单5运行（费率20） → 需展示两订单
• part4：所有时段总费率均为80/100 → 无需展示
• part5：1-3月总费率130 → 需展示两订单
• part6：全年总费率稳定100 → 无需展示
• part7：3月后仅订单15运行（费率30） → 需展示两订单
• part8：两订单无重叠且费率均为100 → 无需展示
• part9：11-12月总费率172 → 需展示该时段所有订单

要求避免按天计算总费率（长时段/永久订单会导致数据量爆炸、效率低下），寻求高效Oracle SQL方案。

方案解答：可用MATCH_RECOGNIZE，两种高效实现方式 #

方式一：基于日期事件点的窗口函数方案 #

核心思路是通过订单的起始/结束日期生成事件点，计算每个时段的总费率，再筛选异常零件号：

WITH order_events AS (
    -- 生成订单的费率增减事件：起始日加费率，结束日+1减费率
    SELECT 
        partno,
        orderno,
        start_date AS event_date,
        rate AS rate_change
    FROM orders
    UNION ALL
    SELECT 
        partno,
        orderno,
        end_date + 1 AS event_date,
        -rate AS rate_change
    FROM orders
),
period_rates AS (
    -- 计算每个时段的起始、结束日期及总费率
    SELECT 
        partno,
        event_date AS period_start,
        LEAD(event_date) OVER (PARTITION BY partno ORDER BY event_date) AS period_end,
        SUM(rate_change) OVER (PARTITION BY partno ORDER BY event_date) AS total_rate
    FROM order_events
),
invalid_parts AS (
    -- 筛选存在异常费率时段的零件号
    SELECT DISTINCT partno
    FROM period_rates
    WHERE period_end IS NOT NULL
      AND total_rate NOT IN (80, 100)
)
-- 关联原订单表，得到所有需要展示的订单
SELECT o.*
FROM orders o
JOIN invalid_parts ip ON o.partno = ip.partno;

方式二：基于MATCH_RECOGNIZE的模式匹配方案 #

利用Oracle的MATCH_RECOGNIZE语法直接识别存在异常费率时段的零件号，代码可读性更强：

SELECT DISTINCT o.*
FROM orders o
JOIN (
    SELECT partno
    FROM (
        -- 先计算每个事件点的累计总费率
        SELECT 
            partno,
            event_date,
            SUM(rate_change) OVER (PARTITION BY partno ORDER BY event_date) AS total_rate
        FROM (
            SELECT 
                partno,
                start_date AS event_date,
                rate AS rate_change
            FROM orders
            UNION ALL
            SELECT 
                partno,
                end_date + 1 AS event_date,
                -rate AS rate_change
            FROM orders
        ) events
    ) rate_changes
    -- 模式匹配：识别存在至少一个异常费率时段的零件号
    MATCH_RECOGNIZE (
        PARTITION BY partno
        ORDER BY event_date
        PATTERN (invalid+)
        DEFINE 
            invalid AS total_rate NOT IN (80, 100)
    )
) invalid_parts ON o.partno = invalid_parts.partno;

方案优势 #

两种方案都无需按天展开数据，仅通过订单的起始/结束日期点生成时段，数据量极小，效率极高，完全适配长时段订单甚至永久订单（可将永久订单的end_date设为DATE '9999-12-31'）。

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