如何在PostgreSQL中统计每月新客户与重复客户数量

解决方案 #

我们可以通过窗口函数先确定每个客户的首次下单月份，再分别统计每月的新客户和重复客户数量，同时确保所有有订单的月份都出现在结果中。

完整SQL代码

WITH customer_first_order AS (
    -- 计算每个客户的首次下单月份，去重避免重复计算
    SELECT DISTINCT
        customer_id,
        MIN(month_year) OVER (PARTITION BY customer_id) AS first_order_month
    FROM orders
),
monthly_stats AS (
    -- 按月份统计新客户与重复客户数
    SELECT
        o.month_year,
        COUNT(DISTINCT CASE WHEN o.month_year = cfo.first_order_month THEN o.customer_id END) AS new_orders,
        COUNT(DISTINCT CASE WHEN o.month_year != cfo.first_order_month THEN o.customer_id END) AS repeat_orders
    FROM orders o
    JOIN customer_first_order cfo ON o.customer_id = cfo.customer_id
    GROUP BY o.month_year
)
-- 按月份排序输出，用COALESCE确保无数据时显示0
SELECT
    month_year,
    COALESCE(repeat_orders, 0) AS repeat_orders,
    COALESCE(new_orders, 0) AS new_orders
FROM monthly_stats
ORDER BY month_year;

代码说明

1. customer_first_order CTE：通过MIN(month_year)窗口函数按客户分组，获取每个客户的首次下单月份，并用DISTINCT过滤同一客户的重复订单行。
2. monthly_stats CTE：关联订单表与首次下单月份表，通过CASE分支分别统计：
   • 新客户：当月等于首次下单月份的不同客户数量
   • 重复客户：当月不等于首次下单月份的不同客户数量
3. 外层查询按月份排序，用COALESCE保证无对应数据时显示0，完全匹配期望输出格式。

执行结果

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

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