如何用单条MySQL查询统计各月流失用户数（无需PHP循环）

问题背景 #

现有两张表：

• Users表：id, name, created_at
• Transaction表：id, user_id, created_at, amount

需要统计每个月的流失用户数：即该月之前连续3个月内无任何交易的用户数量（例如2022年4月统计2022年1-3月均无交易的用户），要求用单条MySQL查询实现，无需PHP循环。

原单月查询的局限性 #

你提供的单月查询存在两个问题：

1. INNER JOIN transactions会过滤掉从未产生过交易的用户（如果这类用户需要纳入统计范围的话）；
2. NOT IN子查询如果遇到user_id为NULL的情况，会导致整个查询结果异常，可靠性不足。

全月份统计的解决方案 #

以下提供两种适配不同MySQL版本的单查询方案：

方案1：MySQL 8.0+（支持递归CTE）

利用递归CTE生成所有需要统计的月份序列，再结合左关联判断用户是否符合流失条件：

WITH RECURSIVE months AS (
    -- 生成起始月份：取用户创建和交易记录中的最早月份
    SELECT DATE_FORMAT(MIN(created_at), '%Y-%m-01') AS stat_month
    FROM (
        SELECT created_at FROM users
        UNION ALL
        SELECT created_at FROM transactions
    ) all_dates
    UNION ALL
    -- 递归生成后续每个月份
    SELECT DATE_ADD(stat_month, INTERVAL 1 MONTH) AS stat_month
    FROM months
    -- 终止条件：到用户创建和交易记录中的最晚月份为止
    WHERE stat_month <= (
        SELECT DATE_FORMAT(MAX(created_at), '%Y-%m-01')
        FROM (
            SELECT created_at FROM users
            UNION ALL
            SELECT created_at FROM transactions
        ) all_dates
    )
)
SELECT 
    m.stat_month AS 统计月份,
    COUNT(DISTINCT u.id) AS 流失用户数
FROM months m
-- 将每个月份与所有用户进行关联
CROSS JOIN users u
-- 左关联用户在统计月份前3个月内的交易记录
LEFT JOIN transactions t 
    ON u.id = t.user_id
    AND t.created_at >= DATE_SUB(m.stat_month, INTERVAL 3 MONTH)
    AND t.created_at < m.stat_month
-- 筛选出前3个月无交易的用户
WHERE t.id IS NULL
-- 可选：如果只统计「曾经有过交易」的流失用户，取消下面的注释
-- AND EXISTS (SELECT 1 FROM transactions t2 WHERE t2.user_id = u.id)
GROUP BY m.stat_month
ORDER BY m.stat_month;

逻辑说明：

1. 递归CTEmonths自动生成覆盖所有业务时间范围的月份序列，无需手动指定；
2. CROSS JOIN users确保每个用户在每个统计月份都有一条记录；
3. LEFT JOIN transactions判断用户在目标时间段内是否有交易，无匹配则说明该用户属于流失用户；
4. 可选的EXISTS条件可以过滤掉从未产生过交易的用户，根据业务需求调整。

方案2：MySQL 5.x（不支持递归CTE）

通过数字辅助表生成月份序列，替代递归CTE的功能：

SELECT 
    m.stat_month AS 统计月份,
    COUNT(DISTINCT u.id) AS 流失用户数
FROM (
    -- 生成足够覆盖业务范围的月份序列（这里生成2010-01到2030-01的所有月份）
    SELECT DATE_ADD('2010-01-01', INTERVAL n MONTH) AS stat_month
    FROM (
        SELECT a.N + b.N * 10 + c.N * 100 AS n
        FROM (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
        CROSS JOIN (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
        CROSS JOIN (SELECT 0 AS N UNION ALL SELECT 1) c
    ) numbers
    -- 过滤出在业务时间范围内的月份
    WHERE DATE_ADD('2010-01-01', INTERVAL n MONTH) <= (
        SELECT DATE_FORMAT(MAX(created_at), '%Y-%m-01')
        FROM (
            SELECT created_at FROM users
            UNION ALL
            SELECT created_at FROM transactions
        ) all_dates
    )
    AND DATE_ADD('2010-01-01', INTERVAL n MONTH) >= (
        SELECT DATE_FORMAT(MIN(created_at), '%Y-%m-01')
        FROM (
            SELECT created_at FROM users
            UNION ALL
            SELECT created_at FROM transactions
        ) all_dates
    )
) m
CROSS JOIN users u
LEFT JOIN transactions t 
    ON u.id = t.user_id
    AND t.created_at >= DATE_SUB(m.stat_month, INTERVAL 3 MONTH)
    AND t.created_at < m.stat_month
WHERE t.id IS NULL
-- 可选：过滤从未交易的用户
-- AND EXISTS (SELECT 1 FROM transactions t2 WHERE t2.user_id = u.id)
GROUP BY m.stat_month
ORDER BY m.stat_month;

逻辑说明：
和方案1核心逻辑一致，只是用数字表生成月份序列，适配不支持递归CTE的旧版MySQL。

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