在PostgreSQL中查询客户首次交易周与首次复购周（去重）

需求回顾 #

现有交易数据包含CustomerID（客户ID）、Trans_date（交易日期）、Week_ISO（交易周）字段，已通过视图the_view提取交易周。需完成：

• 为每条交易记录匹配对应客户的首次交易周（first）和首次复购周（second）
• 输出去重后的CustomerID、first、second，仅保留首次交易周为第35周的客户（排除如C004这类不符合的客户）

实现思路 #

1. 按客户分组，对其交易周按时间排序，标记首次、首次复购的周数
2. 通过条件聚合提取每个客户的first和second周
3. 过滤出first为35的客户，确保结果天然去重

完整SQL代码 #

WITH customer_week_ranked AS (
    SELECT
        CustomerID,
        Week_ISO,
        -- 按客户分组，交易周升序排序，标记交易周的先后顺序
        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Week_ISO) AS week_rank
    FROM the_view
),
customer_first_second_week AS (
    SELECT
        CustomerID,
        -- 取rank=1的周作为首次交易周
        MAX(CASE WHEN week_rank = 1 THEN Week_ISO END) AS first,
        -- 取rank=2的周作为首次复购周（无复购则返回NULL）
        MAX(CASE WHEN week_rank = 2 THEN Week_ISO END) AS second
    FROM customer_week_ranked
    GROUP BY CustomerID
)
-- 筛选首次交易周为35的客户，输出最终结果
SELECT
    CustomerID,
    first,
    second
FROM customer_first_second_week
WHERE first = 35
ORDER BY CustomerID;

代码说明 #

• customer_week_ranked CTE：用ROW_NUMBER()为每个客户的交易周按时间先后编号，首次交易周对应week_rank=1，首次复购周对应week_rank=2
• customer_first_second_week CTE：通过条件聚合提取每个客户的首次和首次复购周，无复购行为的客户second字段会返回NULL
• 最后一步过滤first=35的客户，因已按CustomerID分组，结果天然去重，无需额外处理

预期输出示例 #

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