PostgreSQL中按日计算累计唯一客户数的正确SQL写法求助

PostgreSQL确实不支持在窗口函数中使用COUNT(DISTINCT)，你可以通过先统计每个客户的首次下单日期，再累计每日新客户数的方式来实现需求。

基础版（仅显示有新客户的日期） #

WITH first_orders AS (
    -- 找出每个客户的首次下单日期
    SELECT 
        customer_id,
        MIN(created_at::date) AS first_order_date
    FROM orders
    GROUP BY customer_id
)
SELECT 
    date,
    -- 按日期累计新客户数，得到累计唯一客户数
    SUM(daily_new_users) OVER (ORDER BY date) AS cumulative_users
FROM (
    -- 统计每日新增的唯一客户数
    SELECT 
        first_order_date AS date,
        COUNT(customer_id) AS daily_new_users
    FROM first_orders
    GROUP BY first_order_date
) AS daily_users
ORDER BY date;

完整版（显示所有连续日期，无新客户时累计数保持不变） #

如果需要包含订单时间范围内的所有日期（哪怕某天没有新客户），可以用generate_series生成连续日期序列：

WITH date_range AS (
    -- 生成订单最早到最晚日期的连续日期序列
    SELECT generate_series(
        (SELECT MIN(created_at::date) FROM orders),
        (SELECT MAX(created_at::date) FROM orders),
        '1 day'::interval
    )::date AS date
),
first_orders AS (
    -- 找出每个客户的首次下单日期
    SELECT 
        customer_id,
        MIN(created_at::date) AS first_order_date
    FROM orders
    GROUP BY customer_id
),
daily_new_users AS (
    -- 统计每日新增客户数（无新增时为0）
    SELECT 
        dr.date,
        COUNT(fo.customer_id) AS new_users
    FROM date_range dr
    LEFT JOIN first_orders fo ON dr.date = fo.first_order_date
    GROUP BY dr.date
)
SELECT 
    date,
    -- 累计每日新增客户数
    SUM(new_users) OVER (ORDER BY date) AS cumulative_users
FROM daily_new_users
ORDER BY date;

逻辑说明 #

1. 先通过MIN(created_at::date)获取每个客户的首次下单日期，确保每个客户只被统计一次；
2. 统计每日新增的唯一客户数；
3. 用窗口函数SUM() OVER (ORDER BY date)对每日新增数进行累计，最终得到截止到当天的累计唯一客户数。

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