如何高效筛选同一客户流量记录30天内产生的订单？

我有两张表traffic和orders，希望仅筛选出同一客户的流量记录（timestamp）生成后30天内产生的订单。由于两张表数据量极大，内连接后去重的方式不可行，我考虑过将两表合并后用窗口函数获取同一客户的最近流量记录，但想知道是否有更优方案。

表结构与数据 #

订单表（Orders Table） #

流量表（Traffic Table） #

期望结果 #

订单总价值=5，原因如下：

• 订单A在流量记录（2月28日和3月1日）生成后的30天内产生，符合筛选条件
• 订单B被排除，因为它不在任何流量记录生成后的30天内（距3月1日已超过30天，且早于5月4日的流量记录）

现有方案 #

方案1（不希望使用distinct） #

-- 注：order是SQL关键字，建议表名改为orders；原SQL中customer_id应为customer，与表字段一致
SELECT SUM(value) 
FROM (
    SELECT DISTINCT order_date, order_id, customer, value 
    FROM orders o
    INNER JOIN traffic t 
        ON o.customer = t.customer 
        AND o.order_date >= t.timestamp
        AND o.order_date <= DATE_ADD(t.timestamp, INTERVAL 30 DAY)
) AS filtered_orders;

问题：内连接会产生大量匹配行，即使去重也会因前期数据膨胀导致效率低下，不适用于大数据量场景。

方案2（是否过于复杂？） #

WITH temp AS (
    SELECT order_date AS report_date, order_id, customer, value, NULL AS traffic_date  
    FROM orders
    UNION ALL  -- 用UNION ALL替代UNION，避免不必要的去重开销
    SELECT timestamp AS report_date, NULL AS order_id, customer, NULL AS value, timestamp AS traffic_date 
    FROM traffic 
),
temp2 AS (
    SELECT *,
        LAST_VALUE(traffic_date IGNORE NULLS) OVER (
            PARTITION BY customer 
            ORDER BY report_date 
            ROWS UNBOUNDED PRECEDING
        ) AS last_traffic
    FROM temp
)
SELECT SUM(value) 
FROM temp2 
WHERE report_date >= last_traffic
  AND report_date <= DATE_ADD(last_traffic, INTERVAL 30 DAY);

问题：合并两表会增加数据处理量，虽然窗口函数逻辑可行，但在大数据量下可能存在性能瓶颈。

更优解决方案 #

方案3：利用EXISTS关联，避免数据膨胀 #

通过EXISTS子查询直接判断当前订单是否存在符合条件的流量记录，仅返回符合条件的订单，不会产生大量中间行，适合大数据量场景：

SELECT SUM(o.value)
FROM orders o
WHERE EXISTS (
    SELECT 1
    FROM traffic t
    WHERE t.customer = o.customer
      AND o.order_date >= t.timestamp
      AND o.order_date <= DATE_ADD(t.timestamp, INTERVAL 30 DAY)
);

方案4：窗口函数优化版（精准匹配最近流量记录） #

先为每个订单找到最近的、早于订单日期的流量记录，再判断是否在30天内，避免全量关联：

WITH order_with_last_traffic AS (
    SELECT 
        o.*,
        MAX(t.timestamp) OVER (
            PARTITION BY o.customer 
            ORDER BY o.order_date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS last_traffic_time
    FROM orders o
    LEFT JOIN traffic t 
        ON o.customer = t.customer 
        AND t.timestamp <= o.order_date
)
SELECT SUM(value)
FROM order_with_last_traffic
WHERE order_date <= DATE_ADD(last_traffic_time, INTERVAL 30 DAY);

优化点：

• 仅关联订单日期之前的流量记录，缩小匹配范围
• 窗口函数直接取最近的流量时间，避免重复匹配
• 若traffic(customer, timestamp)和orders(customer, order_date)有联合索引，性能会大幅提升

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