SQL Server查询如何补全连续月份并为无数据月份显示0值

核心实现逻辑 #

先生成统计时间范围内的所有连续月份清单，再将原有统计结果与完整月份清单左关联，无匹配数据的月份活跃客户数默认置为0，全程保留原有活跃客户判定规则不做改动。

注：GENERATE_SERIES 仅支持SQL Server 2022及以上版本，低版本兼容方案会在下文给出。

完整修改后代码 #

WITH 
    -- 原有数据源CTE 完全保留原有取数逻辑
    cte_data AS (
        SELECT
            customer_id,
            event_time, -- 保留原始时间字段方便计算月份边界，避免字符串格式转换的匹配问题
            'eventtype1' AS source
        FROM Pay.Money
        UNION 
        SELECT
            customer_id,
            event_time,
            'eventtype2' AS source
        FROM Pay.Send
    ),
    -- 取统计区间的起止月份（自动取源数据中最早/最晚月份，可手动替换为固定日期）
    cte_date_bound AS (
        SELECT
            DATEFROMPARTS(YEAR(MIN(event_time)), MONTH(MIN(event_time)), 1) AS start_month,
            DATEFROMPARTS(YEAR(MAX(event_time)), MONTH(MAX(event_time)), 1) AS end_month
        FROM cte_data
    ),
    -- 生成区间内所有连续月份 格式和示例输出对齐为MM-yyyy
    cte_all_months AS (
        SELECT
            FORMAT(DATEADD(MONTH, s.value, b.start_month), 'MM-yyyy') AS _month
        FROM cte_date_bound b
        CROSS APPLY GENERATE_SERIES(0, DATEDIFF(MONTH, b.start_month, b.end_month)) s
    ),
    -- 原有逻辑计算有数据月份的活跃客户数
    cte_active_stat AS (
        SELECT
            _month,
            COUNT(customer_id) AS active_customer
        FROM(
            SELECT 
                customer_id,
                _month
            FROM(    
                SELECT
                    customer_id,
                    FORMAT(event_time,'MM-yyyy') as _month, -- 和完整月份表格式保持一致
                    count(customer_id) AS _no
                FROM cte_data
                GROUP BY
                    FORMAT(event_time,'MM-yyyy'),
                    customer_id
            )l
            WHERE _no>2
        )j
        GROUP BY _month
    )
-- 主查询 左关联补全缺失月份
SELECT
    m._month,
    ISNULL(s.active_customer, 0) AS active_customer
FROM cte_all_months m
LEFT JOIN cte_active_stat s ON m._month = s._month
ORDER BY m._month;

低版本SQL Server（无GENERATE_SERIES）兼容写法 #

把上面代码里的cte_all_months部分替换成下面的代码即可，用系统辅助表生成连续数字序列，效果完全一致：

cte_all_months AS (
    SELECT
        FORMAT(DATEADD(MONTH, v.number, b.start_month), 'MM-yyyy') AS _month
    FROM cte_date_bound b
    INNER JOIN master..spt_values v 
        ON v.type = 'P' 
        AND v.number BETWEEN 0 AND DATEDIFF(MONTH, b.start_month, b.end_month)
)

注意事项 #

• 月份格式必须完全统一：如果需要输出yyyy-MM格式，把所有FORMAT函数里的格式字符串统一改成'yyyy-MM'即可，避免关联时格式不匹配导致统计错误
• 若需要固定统计时间范围，直接修改cte_date_bound里的start_month和end_month为固定日期值即可，例如SELECT '2021-10-01' AS start_month, '2022-02-01' AS end_month
• 原有活跃客户判定规则（单客户单月事件记录数>2判定为活跃）完全保留，不会改动原有正确月份的统计结果

执行后即可得到期望的补全结果：

10-2021 5
11-2021 0
12-2021 9
01-2022 0
02-2022 2

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