基于首次购买的用户后续购买模式SQL查询优化问题

我需要查询客户购买历史顺序，基于首次购买商品分析常见订购模式。已经用PARTITION BY确定购买顺序，通过INNER JOIN找出了常见的第二次购买品类，但查询第三次及以后购买记录时遇到瓶颈——多次INNER JOIN基于user_id关联未得到预期结果，第二个INNER JOIN仅筛选出第二次购买对应品类的用户，无法关联到同时满足首次购买指定品类的用户，求解决思路。

现有查询：第二次购买商品品类 #

SELECT 
COUNT (DISTINCT "user_id") AS CUSTOMERS 
,"product_cateogry"
FROM (
       SELECT 
       ROW_NUMBER () OVER (
             PARTITION BY GR."user_id"
             ORDER BY "event_date" ASC) AS PURCHASE_ORDER 
             ,GR."user_id"
             ,GR."product_category"
       FROM CUSTOMER_ORDERS AS GR
INNER JOIN (
       SELECT 
       "user_id"
       ,"product_category"
       FROM (
             SELECT 
             *,
             ROW_NUMBER () OVER (
             PARTITION BY "user_id"
             ORDER BY "event_date" ASC) AS PUR_O
             FROM CUSTOMER_ORDERS 
             QUALIFY PUR_O = 1) 
             WHERE "product_category" = 'purchase_1_cateogry' AND "event_date" > '2022-01-01' ) AS SR ON GR."user_id" = SR."user_id" 
WHERE GR."product_category" NOT IN ('purchase_1_category') AND "event_date" > '2022-01-01'
QUALIFY PURCHASE_ORDER = 1)
GROUP BY 
"product_cateogry"
ORDER BY 
CUSTOMERS DESC
;

尝试的第三次购买查询语句 #

SELECT 
COUNT (DISTINCT "user_id") AS CUSTOMERS 
,"product_category"
FROM (
      SELECT 
      ROW_NUMBER () OVER (
          PARTITION BY "user_id"
          ORDER BY "event_date" ASC) AS PURCHASE_ORDER 
,GR."user_id"
,GR."product_category" 
FROM CUSTOMER_ORDERS AS GR
INNER JOIN (
       SELECT 
       "user_id"
       ,"product_category"
       FROM ( 
             SELECT 
             *,
            ROW_NUMBER () OVER (
            PARTITION BY  "user_id"
            ORDER BY "event_date" ASC) AS PUR_O
            FROM CUSTOMER_ORDERS
            QUALIFY PUR_O = 1) 
       WHERE "product_category" = 'purchase_1_category' AND "event_date" > '2022-01-01' ) AS SR ON GR."user_id" = SR."user_id"
INNER JOIN (
      SELECT 
      "user_id"
      ,"product_category"
           FROM (
                 SELECT 
                 *,
                 ROW_NUMBER () OVER (
                 PARTITION BY "user_id"
                 ORDER BY "event_date" ASC) AS PUR_O
                 FROM CUSTOMER_ORDERS 
                 QUALIFY PUR_O = 2)
       WHERE "product_category" = 'purchase_2_category' AND "event_date" > '2022-01-01' ) AS BR ON GR."user_id" = BR."user_id" AND SR."user_id" = BR."user_id" 
WHERE GR."product_category" NOT IN ('purchase_1_category') AND GR."product_category" NOT IN ( 'purchase_2_category')
QUALIFY PURCHASE_ORDER = 1)
GROUP BY 
"product_category"
ORDER BY 
CUSTOMERS DESC
;

解决思路与优化方案 #

核心问题分析 #

你当前的多次INNER JOIN逻辑存在漏洞：第二次JOIN的子查询仅筛选了第二次购买指定品类的用户，但没有和首次购买的条件做关联验证，导致最终用户池仅满足第二次购买要求，丢失了「首次购买指定品类」的约束；同时多次JOIN会重复扫描表，不仅冗余还容易出错。

优化方案1：用PIVOT筛选符合前置条件的用户 #

先给每个用户的所有购买记录统一标记购买顺序，再通过PIVOT聚合首次、第二次购买的品类，筛选出同时符合两个条件的用户，最后提取他们的第三次及以后购买数据：

SELECT 
    COUNT(DISTINCT user_id) AS CUSTOMERS,
    product_category
FROM (
    -- 给所有用户的购买记录标记顺序
    SELECT 
        user_id,
        product_category,
        event_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_date ASC) AS PURCHASE_ORDER
    FROM CUSTOMER_ORDERS
    WHERE event_date > '2022-01-01'
) AS user_purchases
-- 关联筛选出首次、第二次购买符合要求的用户
INNER JOIN (
    SELECT user_id
    FROM (
        SELECT 
            user_id,
            product_category,
            ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_date ASC) AS PURCHASE_ORDER
        FROM CUSTOMER_ORDERS
        WHERE event_date > '2022-01-01'
    ) AS user_first_two
    PIVOT (
        MAX(product_category) FOR PURCHASE_ORDER IN (1 AS first_cate, 2 AS second_cate)
    ) AS pivot_purchases
    WHERE first_cate = 'purchase_1_category' 
      AND second_cate = 'purchase_2_category'
) AS qualified_users ON user_purchases.user_id = qualified_users.user_id
-- 筛选第三次及以后的购买，排除前两次品类
WHERE user_purchases.PURCHASE_ORDER >= 3
  AND user_purchases.product_category NOT IN ('purchase_1_category', 'purchase_2_category')
GROUP BY product_category
ORDER BY CUSTOMERS DESC;

优化方案2：用窗口函数直接标记前置购买品类 #

如果你的SQL引擎支持窗口函数的FIRST_VALUE和NTH_VALUE，可以一次扫描表就获取首次、第二次购买的品类，无需PIVOT，逻辑更简洁：

SELECT 
    COUNT(DISTINCT up.user_id) AS CUSTOMERS,
    up.product_category
FROM (
    SELECT 
        user_id,
        product_category,
        event_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_date ASC) AS PURCHASE_ORDER,
        -- 获取该用户首次购买的品类
        FIRST_VALUE(product_category) OVER (PARTITION BY user_id ORDER BY event_date ASC) AS first_purchase_cate,
        -- 获取该用户第二次购买的品类
        NTH_VALUE(product_category, 2) OVER (PARTITION BY user_id ORDER BY event_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_purchase_cate
    FROM CUSTOMER_ORDERS
    WHERE event_date > '2022-01-01'
) AS up
-- 筛选符合条件的用户及第三次以后的购买
WHERE up.first_purchase_cate = 'purchase_1_category'
  AND up.second_purchase_cate = 'purchase_2_category'
  AND up.PURCHASE_ORDER >= 3
  AND up.product_category NOT IN ('purchase_1_category', 'purchase_2_category')
GROUP BY up.product_category
ORDER BY CUSTOMERS DESC;

关键改进点 #

1. 避免冗余JOIN：一次性处理所有用户的购买顺序标记，减少表扫描次数，提升性能。
2. 强化条件关联：确保最终筛选的用户同时满足首次和第二次购买的指定品类，不会丢失前置约束。
3. 逻辑更清晰：将用户筛选和购买记录提取分离，便于后续扩展到第四次、第五次购买的分析。

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