SQL实现按月度统计活跃客户数的查询问题求助

核心逻辑 #

要实现按月份拆分统计，核心是将客户的活跃时间区间与每个统计月份做重叠判断，只要活跃区间与当月有交集，该客户就算入当月活跃客户。

实现步骤 #

1. 预处理状态表数据：将end_date为null的记录替换为当前日期（或你需要统计的最大结束日期），过滤掉非active状态的记录。
2. 生成统计周期内的所有月份维度序列，包含每个月的第一天和最后一天。
3. 将月份序列和预处理后的状态表做关联，匹配条件为：客户活跃起始日 < 当月最后一天 且 客户活跃结束日 > 当月第一天。
4. 按月份分组，对client_id去重计数得到当月活跃客户数。

代码示例（MySQL 8.0+ 语法，其他数据库仅需调整日期函数） #

-- 递归生成统计范围内的月份序列，这里示例统计2020-01到2023-12
WITH RECURSIVE months AS (
    SELECT '2020-01-01' AS month_start, LAST_DAY('2020-01-01') AS month_end
    UNION ALL
    SELECT DATE_ADD(month_start, INTERVAL 1 MONTH), LAST_DAY(DATE_ADD(month_start, INTERVAL 1 MONTH))
    FROM months
    WHERE month_start < '2023-12-01'
),
-- 清洗客户状态表
client_active_range AS (
    SELECT 
        client_id,
        start_date,
        COALESCE(end_date, CURRENT_DATE) AS end_date
    FROM 你的表名
    WHERE status = 'active'
)
-- 关联统计
SELECT 
    DATE_FORMAT(m.month_start, '%Y-%m') AS statistics_month,
    COUNT(DISTINCT c.client_id) AS active_client_count
FROM months m
LEFT JOIN client_active_range c 
    ON c.start_date < m.month_end 
    AND c.end_date > m.month_start
GROUP BY statistics_month
ORDER BY statistics_month;

适配说明 #

• PostgreSQL可直接用generate_series生成月份序列，无需递归CTE
• SQL Server使用EOMONTH函数替代LAST_DAY，递归语法微调即可
• 你提供的样例数据中client_id=3的inactive记录存在end_date早于start_date的笔误，生产环境不会出现该类异常数据，不影响本逻辑的正确性

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