Oracle SQL按规则筛选订单行：优化含/排除逻辑的性能

针对你的需求，我们可以通过关联子查询+CASE表达式实现逻辑，避免多CTE带来的重复扫描问题，同时保证逻辑准确性。核心是通过两次EXISTS检查分别处理逻辑第2、3条，配合合适的索引可大幅提升性能。

完整SQL代码 #

SELECT 
    o1.*,
    CASE
        -- 逻辑1：已完成订单直接标记为1
        WHEN o1.order_fulfilled = 1 THEN 1
        ELSE
            CASE
                -- 逻辑2：未完成订单，若存在任意已完成订单的±30天包含当前订单日期，标记为0
                WHEN EXISTS (
                    SELECT 1
                    FROM orders o2
                    WHERE o2.customer_id = 123123
                      AND o2.product_id = 4444
                      AND o2.order_fulfilled = 1
                      AND o1.order_date BETWEEN o2.order_date - INTERVAL '30' DAY 
                                            AND o2.order_date + INTERVAL '30' DAY
                ) THEN 0
                -- 逻辑3：未完成订单，若不在已完成订单的±30天内，但存在后续未完成订单（间隔≤30天），标记为0
                WHEN EXISTS (
                    SELECT 1
                    FROM orders o3
                    WHERE o3.customer_id = 123123
                      AND o3.product_id = 4444
                      AND o3.order_fulfilled = 0
                      AND o3.order_date > o1.order_date
                      AND o3.order_date - o1.order_date <= INTERVAL '30' DAY
                ) THEN 0
                -- 逻辑4：其余情况标记为1
                ELSE 1
            END
    END AS include_flag
FROM orders o1
WHERE o1.customer_id = 123123
  AND o1.product_id = 4444;

关键逻辑与性能说明 #

1. 逻辑2的准确实现：通过EXISTS子查询直接检查当前未完成订单的日期是否落在任意一个已完成订单的前后30天范围内，避免了原CTE可能错误使用整体日期区间的问题。
2. 逻辑3的准确实现：同样用EXISTS子查询，筛选出当前未完成订单之后30天内的其他未完成订单，确保仅当存在这类后续未完成订单时才标记为0。
3. 性能优化：
   • 仅扫描目标表两次（主查询+两次EXISTS子查询），远少于多CTE的重复扫描。
   • 建议创建复合索引，让Oracle快速定位目标客户、产品的已完成/未完成订单，避免全表扫描：

     CREATE INDEX idx_orders_cust_prod_fulfill_date ON orders (customer_id, product_id, order_fulfilled, order_date);
     

替代优化方案（窗口函数版） #

如果希望进一步减少表扫描次数，可使用窗口函数提前计算每个订单的相关标记，仅扫描表一次，适合数据量较大的场景（需Oracle 12c及以上版本支持RANGE INTERVAL语法）：

WITH order_stats AS (
    SELECT 
        o.*,
        -- 标记当前订单是否在任意已完成订单的±30天内
        MAX(CASE WHEN order_fulfilled = 1 THEN 1 ELSE 0 END) OVER (
            PARTITION BY customer_id, product_id
            ORDER BY order_date
            RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND INTERVAL '30' DAY FOLLOWING
        ) AS in_fulfilled_window,
        -- 标记当前订单之后30天内是否有未完成订单
        MAX(CASE WHEN order_fulfilled = 0 AND order_date > o.order_date THEN 1 ELSE 0 END) OVER (
            PARTITION BY customer_id, product_id
            ORDER BY order_date
            RANGE BETWEEN CURRENT ROW AND INTERVAL '30' DAY FOLLOWING
        ) AS has_future_unfulfilled
    FROM orders o
    WHERE o.customer_id = 123123
      AND o.product_id = 4444
)
SELECT 
    *,
    CASE
        WHEN order_fulfilled = 1 THEN 1
        WHEN in_fulfilled_window = 1 THEN 0
        WHEN has_future_unfulfilled = 1 THEN 0
        ELSE 1
    END AS include_flag
FROM order_stats;

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