如何在PostgreSQL中按客户生成月度累计订单数（含缺失月份）

没问题，我来帮你把这个查询调整为按每个客户单独计算月度累计订单数，同时补上2018年1-5月的缺失月份。核心思路是先构建「所有客户 + 目标月份」的完整组合，再关联实际订单数据，最后用窗口函数做客户维度的累计计算。

完整SQL查询 #

WITH 
-- 1. 生成2018年1月至5月的所有月份（每个月的第一天）
target_months AS (
    SELECT generate_series(
        '2018-01-01'::date, 
        '2018-05-01'::date, 
        interval '1 month'
    ) AS mon
),
-- 2. 获取所有唯一的客户ID
unique_customers AS (
    SELECT DISTINCT customer_id FROM orders
),
-- 3. 构建每个客户对应每个目标月份的完整组合（确保无缺失）
customer_month_combos AS (
    SELECT 
        uc.customer_id,
        tm.mon
    FROM unique_customers uc
    CROSS JOIN target_months tm
),
-- 4. 计算每个客户每个月的实际订单数
monthly_customer_orders AS (
    SELECT
        customer_id,
        date_trunc('month', created_at)::date AS mon,
        COUNT(id) AS mon_count
    FROM orders
    WHERE created_at BETWEEN '2018-01-01' AND '2018-05-31'
    GROUP BY customer_id, mon
)
-- 5. 关联数据并计算累计订单数
SELECT
    cmc.customer_id,
    TO_CHAR(cmc.mon, 'MM/DD/YYYY') AS month, -- 匹配示例的日期格式
    COALESCE(
        SUM(mco.mon_count) OVER (
            PARTITION BY cmc.customer_id 
            ORDER BY cmc.mon 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ),
        0
    ) AS count
FROM customer_month_combos cmc
LEFT JOIN monthly_customer_orders mco
    ON cmc.customer_id = mco.customer_id 
    AND cmc.mon = mco.mon
ORDER BY cmc.customer_id, cmc.mon;

关键调整说明 #

和你原来的全局累计查询相比，主要做了这几个核心修改：

• 添加客户维度的完整组合：通过CROSS JOIN把所有客户和目标月份配对，确保每个客户每个月都有记录，哪怕该月没有订单。
• 窗口函数分区：在SUM() OVER()中加入PARTITION BY customer_id，这样累计计算会按每个客户单独进行，而不是全局累计。
• 日期格式匹配：用TO_CHAR(cmc.mon, 'MM/DD/YYYY')把月份格式转换成你示例里的01/01/2018样式。
• 过滤时间范围：在计算月度订单数时加上WHERE created_at BETWEEN '2018-01-01' AND '2018-05-31'，只统计目标时间段内的订单。

测试示例数据的结果 #

用你提供的示例数据运行这个查询，会得到和你预期完全一致的结果：每个客户的每个月份都有记录，缺失月份的累计数会延续上月的数值，比如客户200的2月累计数还是2，5月累计数保持5。

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