基于日期字段关联条件的月度用户数统计SQL查询问题

嘿，这个需求我之前做订阅系统的时候刚好碰到过！你猜的没错，必须要有一个日期月份序列表才能生成连续的月份统计结果——不然直接按用户的订阅/退订日期分组，会漏掉那些没有新增或退订操作但仍有活跃用户的月份（比如你示例里的2017-02-01）。

下面我给你拆解具体的实现步骤，分数据库类型给你写示例：

第一步：生成连续的月份序列 #

我们需要先得到所有要统计的月份（从最早的订阅日期到最晚的退订日期，没有退订的就到当前月份）。

如果你用PostgreSQL：

用generate_series可以快速生成序列：

WITH month_series AS (
    SELECT 
        generate_series(
            -- 从最早的订阅月份开始
            (SELECT date_trunc('month', MIN(subscribed)) FROM your_table),
            -- 到最晚的退订月份，没有退订就用当前月份
            (SELECT COALESCE(date_trunc('month', MAX(unsubscribed)), CURRENT_DATE) FROM your_table),
            INTERVAL '1 month'
        ) AS stats_month
)

如果你用MySQL：

MySQL没有generate_series，用递归CTE来生成：

WITH RECURSIVE month_series AS (
    -- 起始月份：最早的订阅月份第一天
    SELECT (SELECT DATE_FORMAT(MIN(subscribed), '%Y-%m-01') FROM your_table) AS stats_month
    UNION ALL
    -- 递归生成下一个月份
    SELECT DATE_ADD(stats_month, INTERVAL 1 MONTH)
    FROM month_series
    -- 终止条件：到最晚的退订月份或当前月份
    WHERE stats_month < (SELECT COALESCE(DATE_FORMAT(MAX(unsubscribed), '%Y-%m-01'), CURDATE()) FROM your_table)
)

第二步：关联用户表统计活跃用户数 #

核心是判断用户在统计月份内是否处于订阅状态：

• 订阅日期不晚于统计月份的最后一天（用户在该月份或之前订阅）
• 退订日期不早于统计月份的第一天（用户在该月份内还没退订），或者从未退订（unsubscribed为null）

把这个逻辑写成JOIN条件，再统计去重后的用户数：

PostgreSQL完整SQL：

WITH month_series AS (
    SELECT 
        generate_series(
            (SELECT date_trunc('month', MIN(subscribed)) FROM your_table),
            (SELECT COALESCE(date_trunc('month', MAX(unsubscribed)), CURRENT_DATE) FROM your_table),
            INTERVAL '1 month'
        ) AS stats_month
)
SELECT
    TO_CHAR(ms.stats_month, 'YYYY-MM-DD') AS date,
    COUNT(DISTINCT t.user) AS user_count
FROM month_series ms
LEFT JOIN your_table t
    ON t.subscribed <= ms.stats_month + INTERVAL '1 month - 1 day'
    AND (t.unsubscribed IS NULL OR t.unsubscribed >= ms.stats_month)
GROUP BY ms.stats_month
ORDER BY ms.stats_month;

MySQL完整SQL：

WITH RECURSIVE month_series AS (
    SELECT (SELECT DATE_FORMAT(MIN(subscribed), '%Y-%m-01') FROM your_table) AS stats_month
    UNION ALL
    SELECT DATE_ADD(stats_month, INTERVAL 1 MONTH)
    FROM month_series
    WHERE stats_month < (SELECT COALESCE(DATE_FORMAT(MAX(unsubscribed), '%Y-%m-01'), CURDATE()) FROM your_table)
)
SELECT
    DATE_FORMAT(ms.stats_month, '%Y-%m-%d') AS date,
    COUNT(DISTINCT t.user) AS user_count
FROM month_series ms
LEFT JOIN your_table t
    ON t.subscribed <= LAST_DAY(ms.stats_month)
    AND (t.unsubscribed IS NULL OR t.unsubscribed >= ms.stats_month)
GROUP BY ms.stats_month
ORDER BY ms.stats_month;

为什么需要月份序列表？ #

举个例子：你示例里的2017-02-01，没有用户在这个月订阅或退订，但有2个用户处于活跃状态。如果没有序列表，直接GROUP BY订阅/退订日期的话，根本不会出现这个月份的记录，也就满足不了你的需求。

如果你的统计范围是固定的（比如只统计2017年），也可以手动写死月份列表，不用动态生成，但动态生成更灵活。

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