留存用户数是指某月注册用户在之后第N个月仍有活跃行为的人数，需通过同期群分析实现，核心是分离用户首次注册时间与后续活跃时间，并用年月格式对齐避免跨年断层。
什么是“留存用户数”？先明确计算逻辑
留存用户数不是简单查注册人数，而是指：某月注册的用户，在之后第N个月仍有活跃行为（比如登录、下单）的人数。统计过去一年每月留存，本质是做“同期群分析（Cohort Analysis）”，需要两个关键时间维度：注册月（cohort）和观察月（retention month）。
常见错误是直接用 WHERE event_time >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) 拉全量数据再 group by month——这算出来的是当月活跃用户，不是留存。
- 必须分离出每个用户的首次注册时间（
first_login或register_time），这是 cohort 的基准 - 再关联其后续任意一次活跃时间（
active_time），判断是否满足“注册后第1/2/…/12个月仍有行为” - 月份对齐要用
YEAR(active_time)*100 + MONTH(active_time)或DATE_FORMAT(active_time, '%Y%m')，避免跨年时MONTH()单独用导致 12→1 的断层
MySQL 8.0+ 实现：用 CTE + 窗口函数拉出首活时间
假设用户表 user_events 记录所有行为，含 user_id、event_time 字段。先提取每人最早注册时间，再关联后续行为：
WITH first_cohort AS (
SELECT user_id,
DATE_FORMAT(MIN(event_time), '%Y%m') AS cohort_month
FROM user_events
GROUP BY user_id
),
monthly_active AS (
SELECT user_id,
DATE_FORMAT(event_time, '%Y%m') AS active_month
FROM user_events
WHERE event_time >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
)
SELECT
c.cohort_month,
a.active_month,
COUNT(DISTINCT c.user_id) AS retained_users
FROM first_cohort c
JOIN monthly_active a ON c.user_id = a.user_id
WHERE a.active_month >= c.cohort_month
AND a.active_month <= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y%m')
GROUP BY c.cohort_month, a.active_month
ORDER BY c.cohort_month, a.active_month;注意：cohort_month 和 active_month 都是 'YYYYMM' 格式整数，可直接比较大小。最后一行 DATE_FORMAT(DATE_SUB(...)) 控制观察截止到上个月，避免当月数据不完整。
兼容 MySQL 5.7 的写法：用子查询替代 CTE
MySQL 5.7 不支持 CTE，需把首活时间用关联子查询或临时表实现。性能会略差，但逻辑一致：
SELECT DATE_FORMAT(f.first_time, '%Y%m') AS cohort_month, DATE_FORMAT(e.event_time, '%Y%m') AS active_month, COUNT(DISTINCT f.user_id) AS retained_users FROM ( SELECT user_id, MIN(event_time) AS first_time FROM user_events GROUP BY user_id ) f JOIN user_events e ON f.user_id = e.user_id WHERE e.event_time >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) AND DATE_FORMAT(e.event_time, '%Y%m') >= DATE_FORMAT(f.first_time, '%Y%m') AND DATE_FORMAT(e.event_time, '%Y%m') <= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y%m') GROUP BY cohort_month, active_month ORDER BY cohort_month, active_month;
这里容易漏掉的坑：WHERE 条件必须同时限制 e.event_time 范围（保证只查近12个月活跃）和 cohort_month 起点（保证 cohort 本身在可回溯范围内）。否则可能拉出 2020 年注册、2024 年活跃的老用户，污染近一年统计。
结果怎么变成“每月留存率表格”？加 pivot 是最后一步
上面 SQL 输出的是长格式（cohort_month, active_month, count），要变成横向的留存矩阵（每行一个 cohort，列是 M0/M1/M2…），得靠应用层 pivot 或数据库侧条件聚合。MySQL 原生不支持 full pivot，但可用 SUM(IF()) 模拟：
SELECT cohort_month, COUNT(DISTINCT CASE WHEN active_month = cohort_month THEN user_id END) AS M0, COUNT(DISTINCT CASE WHEN active_month = DATE_FORMAT(STR_TO_DATE(CONCAT(cohort_month, '01'), '%Y%m%d') + INTERVAL 1 MONTH, '%Y%m') THEN user_id END) AS M1, COUNT(DISTINCT CASE WHEN active_month = DATE_FORMAT(STR_TO_DATE(CONCAT(cohort_month, '01'), '%Y%m%d') + INTERVAL 2 MONTH, '%Y%m') THEN user_id END) AS M2 -- …继续写到 M12 FROM (/* 上面的 JOIN 结果子查询 */ ) t GROUP BY cohort_month;
真正难的不是写 SQL，而是定义清楚“活跃”的业务口径（是登录？下单？页面浏览？）、确认数据延迟（T+1 还是 T+3？），以及处理用户跨设备重复 ID 的问题。这些没对齐，SQL 写得再漂亮，结果也是错的。
