如何在PostgreSQL中按月统计有历史订单的复购客户数量？

需求说明 #

需按月统计复购客户数量，复购客户定义为：曾有过任何历史订单（首次下单之外的后续订单）的客户，即客户在某月份下单且该月份晚于其首次下单月份，则该客户计入当月复购统计。

解决方案SQL #

WITH customer_first_order AS (
    SELECT 
        customer_id,
        MIN(month_year) AS first_order_month
    FROM orders
    GROUP BY customer_id
)
SELECT 
    o.month_year,
    COUNT(DISTINCT o.customer_id) AS repeat_orders
FROM orders o
JOIN customer_first_order cfo 
    ON o.customer_id = cfo.customer_id
WHERE o.month_year > cfo.first_order_month
GROUP BY o.month_year
ORDER BY o.month_year;

代码解释 #

1. CTE customer_first_order：先计算每个客户的首次下单月份，通过对customer_id分组后取month_year的最小值实现。
2. 关联筛选：将原订单表与首次下单信息关联，筛选出订单月份晚于首次下单月份的记录——这些记录对应的客户就是当月的复购客户。
3. 分组统计：按月分组，统计去重后的客户数量，得到每个月的复购客户数，最后按月份排序输出。

结果验证 #

执行上述SQL后，将得到与期望一致的结果：

month_year | repeat_orders
-----------+---------------
2016-05    |             1
2016-07    |             1
2016-08    |             1
2016-09    |             1
2016-10    |             2
2016-11    |             2
2016-12    |             1
2017-01    |             1

原SQL问题分析 #

你之前的SQL使用HAVING COUNT(order_id) > 1，仅能统计当月有多个订单的客户，无法覆盖“首次下单后，当月仅下1单但属于复购”的场景（比如2016-09的客户24662），因此逻辑不符合需求。

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