PostgreSQL 9如何最优筛选购买前存在浏览记录的商品

核心匹配逻辑：在user_id + product_id的相同维度下，存在event_time早于purchase事件的view事件，以下是适配PG9版本的可落地实现，按推荐优先级排序：

• 方案1：EXISTS半连接查询（性能最优，首选）
  半连接的特性是只要匹配到1条符合条件的前置浏览记录就会停止匹配，不会产生重复数据，也不需要全表聚合，配合联合索引在千万级数据量下性能表现极好。

-- 如果需要去重得到符合要求的商品ID列表，可替换为 SELECT DISTINCT product_id
SELECT DISTINCT user_id, product_id
FROM events e_buy
WHERE event_type = 'purchase'
  AND EXISTS (
    SELECT 1
    FROM events e_view
    WHERE e_view.user_id = e_buy.user_id
      AND e_view.product_id = e_buy.product_id
      AND e_view.event_type = 'view'
      AND e_view.event_time < e_buy.event_time
  );

索引优化建议：给events表创建(user_id, product_id, event_type, event_time)的联合索引，查询可以全程走索引检索，不需要回表。

• 方案2：窗口函数实现（适合扩展多维度统计场景）
  PG9已支持窗口函数，如果后续需要同时统计前置浏览次数、浏览到购买的时间间隔等关联指标，可以用窗口函数提前标记每个事件之前的浏览行为，灵活性更高，性能略低于EXISTS写法，适合中等数据量或多指标统计场景。

WITH event_with_pre_flag AS (
  SELECT
    user_id,
    product_id,
    event_type,
    event_time,
    SUM(CASE WHEN event_type = 'view' THEN 1 ELSE 0 END) OVER (
      PARTITION BY user_id, product_id
      ORDER BY event_time
      -- 统计范围限定为当前事件之前的所有记录
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS pre_view_cnt
  FROM events
)
SELECT DISTINCT user_id, product_id
FROM event_with_pre_flag
WHERE event_type = 'purchase'
  AND pre_view_cnt > 0;

结果验证 #

针对提供的样例数据：

1. 商品643、730存在同用户下早于购买的浏览事件，会被正常返回
2. 商品728无前置浏览事件，会被过滤
   两个方案的返回结果均符合预期。

避坑说明 #

不推荐使用左连接匹配后判非空的写法，这种写法会让1条购买记录匹配到多条前置浏览记录，产生大量冗余数据，后续去重的开销远高于EXISTS半连接写法，数据量越大性能差距越明显。

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