如何用SQL累计计算客户的非活跃月份

需要基于现有销售表和月度日期表，计算每个客户自首次下单日期起的各月份销售订单数，以及连续非活跃月份数——当月无订单时显示连续未下单的累计月份数，有订单时显示null。

现有销售表结构及数据 #

注：假设存在一张名为month_dates的表，包含字段Start_Date_of_Month，存储每年每月的月初日期（如01/06/2022、01/07/2022等）。

期望输出结果 #

关键规则 #

• 输出仅包含客户首次下单日期之后的月份（客户A从2022年6月开始，客户B从2022年7月开始）
• 当月有销售订单时，Inactive_Months显示null；无订单时显示连续未下单的累计月份数

实现SQL代码（适用于支持窗口函数的数据库：MySQL8+、PostgreSQL、SQL Server等） #

-- 1. 获取每个客户的首次下单月份
WITH customer_first_month AS (
    SELECT 
        Customer,
        DATE_TRUNC('month', TO_DATE(Date, 'DD/MM/YYYY')) AS first_order_month
    FROM sales_table
    GROUP BY Customer
),
-- 2. 生成每个客户需要统计的月份范围（从首次下单月到订单最大月份）
customer_month_range AS (
    SELECT 
        c.Customer,
        m.Start_Date_of_Month
    FROM customer_first_month c
    JOIN month_dates m 
        ON TO_DATE(m.Start_Date_of_Month, 'DD/MM/YYYY') >= c.first_order_month
        AND TO_DATE(m.Start_Date_of_Month, 'DD/MM/YYYY') <= (
            SELECT DATE_TRUNC('month', MAX(TO_DATE(Date, 'DD/MM/YYYY'))) FROM sales_table
        )
),
-- 3. 统计每个客户每月的订单数
customer_month_orders AS (
    SELECT 
        cmr.Customer,
        cmr.Start_Date_of_Month,
        COUNT(s.Sale_order_ID) AS Sales_order_count
    FROM customer_month_range cmr
    LEFT JOIN sales_table s 
        ON cmr.Customer = s.Customer
        AND DATE_TRUNC('month', TO_DATE(s.Date, 'DD/MM/YYYY')) = TO_DATE(cmr.Start_Date_of_Month, 'DD/MM/YYYY')
    GROUP BY cmr.Customer, cmr.Start_Date_of_Month
),
-- 4. 标记活跃分组（有订单的月份会重置分组）
inactive_calculation AS (
    SELECT 
        Customer,
        Start_Date_of_Month,
        Sales_order_count,
        SUM(CASE WHEN Sales_order_count > 0 THEN 1 ELSE 0 END) OVER (
            PARTITION BY Customer ORDER BY TO_DATE(Start_Date_of_Month, 'DD/MM/YYYY')
        ) AS active_group
    FROM customer_month_orders
)
-- 最终输出
SELECT 
    Customer,
    Start_Date_of_Month,
    Sales_order_count,
    CASE 
        WHEN Sales_order_count > 0 THEN NULL
        ELSE ROW_NUMBER() OVER (
            PARTITION BY Customer, active_group ORDER BY TO_DATE(Start_Date_of_Month, 'DD/MM/YYYY')
        )
    END AS Inactive_Months
FROM inactive_calculation
ORDER BY Customer, TO_DATE(Start_Date_of_Month, 'DD/MM/YYYY');

代码逻辑说明 #

1. customer_first_month：提取每个客户的首次下单月份，作为统计的起始边界。
2. customer_month_range：关联月度日期表，筛选出每个客户从首次下单月到所有订单最大月份之间的所有月份，确保统计范围准确。
3. customer_month_orders：左连接销售表，按客户+月份分组统计订单数量，无订单的月份会返回0。
4. inactive_calculation：用窗口函数生成活跃分组编号，每当遇到有订单的月份，分组编号递增，这样连续的非活跃月份会被归为同一个分组。
5. 最终查询中，对非活跃分组内的月份按日期排序生成连续计数，有订单的月份直接返回null。

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