留存用户数是指某月注册用户在之后第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 <p>注意：<code>cohort_month</code> 和 <code>active_month</code> 都是 <code>'YYYYMM'</code> 格式整数，可直接比较大小。最后一行 <code>DATE_FORMAT(DATE_SUB(...))</code> 控制观察截止到上个月，避免当月数据不完整。</p><div class="aritcle_card flexRow artxards">
<div class="artcardd flexRow">
<a class="aritcle_card_img" rel="nofollow" href="/ai/3732" title="Gank Interview"><img
src="https://img.php.cn/upload/ai_manual/001/246/273/178599568532374.png" alt="Gank Interview" onerror="this.onerror='';this.src='/static/lhimages/moren/morentu.png'" ></a>
<div class="aritcle_card_info flexColumn">
<a rel="nofollow" href="/ai/3732" title="Gank Interview" class="overflowclass">Gank Interview</a>
<p class="overflowclass">Gank Interview是专为笔试和面试设计的AI面试助手。</p>
</div>
<a rel="nofollow" href="/ai/3732" title="Gank Interview" class="aritcle_card_btn flexRow flexcenter"><b></b><span>下载</span>
</a>
</div>
</div><h3>兼容 MySQL 5.7 的写法：用子查询替代 CTE</h3><p>MySQL 5.7 不支持 CTE，需把首活时间用关联子查询或临时表实现。性能会略差，但逻辑一致：</p><pre class="brush:php;toolbar:false;">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') <p>这里容易漏掉的坑：<code>WHERE</code> 条件必须同时限制 <code>e.event_time</code> 范围（保证只查近12个月活跃）和 <code>cohort_month</code> 起点（保证 cohort 本身在可回溯范围内）。否则可能拉出 2020 年注册、2024 年活跃的老用户，污染近一年统计。</p><h3>结果怎么变成“每月留存率表格”？加 pivot 是最后一步</h3><p>上面 SQL 输出的是长格式（cohort_month, active_month, count），要变成横向的留存矩阵（每行一个 cohort，列是 M0/M1/M2…），得靠应用层 pivot 或数据库侧条件聚合。MySQL 原生不支持 full pivot，但可用 <code>SUM(IF())</code> 模拟：</p><pre class="brush:php;toolbar:false;">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 写得再漂亮，结果也是错的。
