如何按月份统计各状态客户数（含状态间隔期及注册客户筛选）

一、全量客户每月各状态统计（覆盖状态变更间隔月份） #

原SQL仅统计有状态变更的月份，要补全两次变更之间的空白月份，核心思路是先生成连续的月份序列，再匹配每个客户在对应月份的有效状态：

实现步骤 #

• 生成覆盖统计时间范围的所有月份列表
• 为每个客户计算状态生效的时间区间（当前状态的起始日期到下一次变更的前一个月，无后续变更则到统计截止月）
• 将月份序列与客户状态区间关联，得到每个月每个客户的有效状态
• 按月份和状态分组计数

示例SQL（SQL Server环境） #

-- 1. 生成连续月份序列，这里默认统计从最早状态变更月到当前月的范围
WITH Months AS (
    SELECT DATEFROMPARTS(YEAR(MIN(Date)), MONTH(MIN(Date)), 1) AS MonthStart
    FROM Table1
    UNION ALL
    SELECT DATEADD(MONTH, 1, MonthStart)
    FROM Months
    WHERE MonthStart < DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
),
-- 2. 提取每个客户的状态生效区间
CustomerStatusRanges AS (
    SELECT 
        ID,
        Status,
        Date AS StatusStartDate,
        -- 下一次状态变更日期，无后续变更则用当前月最后一天
        ISNULL(LEAD(Date) OVER (PARTITION BY ID ORDER BY Date), DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)))) AS StatusEndDate
    FROM Table1
)
-- 3. 关联月份与状态区间，统计每月各状态客户数
SELECT 
    FORMAT(m.MonthStart, 'yyyy-MM') AS Month_Year,
    csr.Status,
    COUNT(DISTINCT csr.ID) AS Count_Status
FROM Months m
JOIN CustomerStatusRanges csr 
    ON m.MonthStart BETWEEN DATEFROMPARTS(YEAR(csr.StatusStartDate), MONTH(csr.StatusStartDate), 1) 
        AND DATEFROMPARTS(YEAR(csr.StatusEndDate), MONTH(csr.StatusEndDate), 1)
GROUP BY FORMAT(m.MonthStart, 'yyyy-MM'), csr.Status
ORDER BY Month_Year, Status;

二、仅统计当月注册客户的每月状态统计 #

需关联注册表（假设表名为CustomerRegistration，含ID、RegistrationDate字段），先筛选出当月注册的客户，再按上述逻辑统计他们的状态：

示例SQL（SQL Server环境） #

-- 1. 生成连续月份序列，范围从最早注册月到当前月
WITH Months AS (
    SELECT DATEFROMPARTS(YEAR(MIN(RegistrationDate)), MONTH(MIN(RegistrationDate)), 1) AS MonthStart
    FROM CustomerRegistration
    UNION ALL
    SELECT DATEADD(MONTH, 1, MonthStart)
    FROM Months
    WHERE MonthStart < DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
),
-- 2. 提取各月注册的客户列表
NewCustomersByMonth AS (
    SELECT 
        ID,
        FORMAT(RegistrationDate, 'yyyy-MM') AS RegistrationMonth
    FROM CustomerRegistration
),
-- 3. 获取当月注册客户的状态生效区间
CustomerStatusRanges AS (
    SELECT 
        cs.ID,
        cs.Status,
        cs.Date AS StatusStartDate,
        ISNULL(LEAD(cs.Date) OVER (PARTITION BY cs.ID ORDER BY cs.Date), DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)))) AS StatusEndDate,
        nc.RegistrationMonth
    FROM Table1 cs
    JOIN NewCustomersByMonth nc ON cs.ID = nc.ID
)
-- 4. 关联月份，统计当月注册客户在各月的状态数
SELECT 
    m.Month_Year,
    csr.Status,
    COUNT(DISTINCT csr.ID) AS Count_Status
FROM (
    SELECT FORMAT(MonthStart, 'yyyy-MM') AS Month_Year, MonthStart
    FROM Months
) m
JOIN CustomerStatusRanges csr 
    ON m.MonthStart BETWEEN DATEFROMPARTS(YEAR(csr.StatusStartDate), MONTH(csr.StatusStartDate), 1) 
        AND DATEFROMPARTS(YEAR(csr.StatusEndDate), MONTH(csr.StatusEndDate), 1)
    AND csr.RegistrationMonth = m.Month_Year
GROUP BY m.Month_Year, csr.Status
ORDER BY m.Month_Year, Status;

注意事项 #

• 若使用MySQL、PostgreSQL等其他数据库，需调整日期函数（比如MySQL用DATE_FORMAT、LAST_DAY，PostgreSQL用TO_CHAR、DATE_TRUNC）
• 使用COUNT(DISTINCT ID)避免同一客户在单月内被重复计数
• 可根据实际需求修改Months CTE中的时间范围起始/结束条件

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