PieCloudDB活跃用户查询问题求助：现有SQL仅返回部分结果

原SQL的问题分析 #

你当前的查询语句存在以下关键问题，导致无法正确筛选出用户2：

• 排除同商品二次购买：u1.item != u2.item条件会过滤掉用户两次购买同一商品的情况，如果用户2属于这种场景，就会被遗漏。
• 未限定首次购买记录：没有指定u1是用户的首次购买记录，导致匹配逻辑混乱，可能出现非首次购买之间的无效匹配，而漏掉首次与二次购买的有效组合。
• 逻辑冗余：LEFT JOIN后添加WHERE u2.date is not null等价于INNER JOIN，但核心问题还是前面的匹配条件错误。

正确的查询语句 #

以下两种方法都可以准确筛选出符合要求的活跃用户：

方法一：基于首次购买日期筛选 #

先获取每个用户的首次购买日期，再检查是否存在二次购买记录在首次购买后的7天内（含第7天）：

SELECT DISTINCT p.user_id
FROM purchase p
JOIN (
    -- 先筛选出至少有两次购买的用户，并获取他们的首次购买日期
    SELECT user_id, MIN(date) AS first_purchase_date
    FROM purchase
    GROUP BY user_id
    HAVING COUNT(*) >= 2
) fp ON p.user_id = fp.user_id
-- 筛选出在首次购买之后、7天内的购买记录
WHERE p.date > fp.first_purchase_date 
  AND p.date <= fp.first_purchase_date + INTERVAL '7 days';

如果允许用户在首次购买当天完成二次购买（比如同一天多次下单），只需将p.date >改为p.date >=即可。

方法二：使用窗口函数标记购买顺序 #

通过窗口函数给每个用户的购买记录按日期排序，直接检查第二次购买是否在首次购买的7天内：

WITH ranked_purchases AS (
    SELECT 
        user_id,
        date,
        -- 按用户分组，按日期排序标记购买顺序
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) AS purchase_rank
    FROM purchase
)
SELECT DISTINCT user_id
FROM ranked_purchases rp1
JOIN ranked_purchases rp2 
    ON rp1.user_id = rp2.user_id 
    AND rp1.purchase_rank = 1  -- 关联首次购买记录
    AND rp2.purchase_rank = 2  -- 关联第二次购买记录
WHERE rp2.date <= rp1.date + INTERVAL '7 days';

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