如何编写SQL获取本年每月新增订阅用户数（含订阅数为0的月份）

核心逻辑是先构造当前自然年的完整月份序列，再和订阅表的统计结果做左连接，空缺月份的统计值补0即可，以下是不同数据库的实现示例：

PostgreSQL 实现 #

WITH all_months AS (
    -- 生成当前自然年所有月份的首日序列
    SELECT generate_series(
        DATE_TRUNC('year', CURRENT_DATE), 
        DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '11 months', 
        INTERVAL '1 month'
    ) AS month_start
),
subscriber_monthly_count AS (
    -- 原统计逻辑，限定仅统计当前自然年数据
    SELECT
        DATE_TRUNC('month', create_timestamp) AS month_start,
        COUNT(subscriber_id) AS count
    FROM Subscriber
    WHERE create_timestamp >= DATE_TRUNC('year', CURRENT_DATE)
      AND create_timestamp < DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 year'
    GROUP BY DATE_TRUNC('month', create_timestamp)
)
SELECT
    TO_CHAR(am.month_start, 'YYYY-MM-DD') AS date,
    COALESCE(smc.count, 0) AS count
FROM all_months am
LEFT JOIN subscriber_monthly_count smc 
    ON am.month_start = smc.month_start
ORDER BY am.month_start ASC;

MySQL 8.0+ 实现 #

WITH RECURSIVE all_months AS (
    -- 递归生成当前自然年所有月份的首日序列
    SELECT DATE_FORMAT(CURRENT_DATE, '%Y-01-01') AS month_start
    UNION ALL
    SELECT DATE_ADD(month_start, INTERVAL 1 MONTH)
    FROM all_months
    WHERE month_start < DATE_FORMAT(CURRENT_DATE, '%Y-12-01')
),
subscriber_monthly_count AS (
    -- 原统计逻辑，限定仅统计当前自然年数据
    SELECT
        DATE_FORMAT(create_timestamp, '%Y-%m-01') AS month_start,
        COUNT(subscriber_id) AS count
    FROM Subscriber
    WHERE create_timestamp >= DATE_FORMAT(CURRENT_DATE, '%Y-01-01')
      AND create_timestamp < DATE_ADD(DATE_FORMAT(CURRENT_DATE, '%Y-01-01'), INTERVAL 1 YEAR)
    GROUP BY DATE_FORMAT(create_timestamp, '%Y-%m-01')
)
SELECT
    am.month_start AS date,
    COALESCE(smc.count, 0) AS count
FROM all_months am
LEFT JOIN subscriber_monthly_count smc 
    ON am.month_start = smc.month_start
ORDER BY am.month_start ASC;

核心说明 #

• 使用CTE生成完整月份序列是补全无数据月份的通用方案，不受订阅表是否有对应月份数据的影响
• COALESCE函数会将左连接产生的空统计值替换为0，符合要求的输出规则
• 统计订阅数据时提前过滤当前自然年的数据，可以避免全表扫描，提升查询效率

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