留存率队列分析是按用户首次行为日期分组，追踪各队列随时间的留存衰减过程；直接group by event_date会打散用户首日标识，无法实现队列追踪，必须通过三层嵌套子查询先识别首日、再对齐行为、最后分组聚合。
什么是留存率队列分析，为什么不能直接用 GROUP BY
留存率队列分析本质是按「首次行为日期」分组，再看后续各天有多少人重复出现。它不是简单统计每天的活跃用户，而是追踪每个用户群（队列）随时间衰减的过程。直接用 GROUP BY event_date 会把用户打散到不同天，无法锁定“首日”——必须先识别每个用户的 first_login_date，再关联其后续行为，这正是嵌套子查询最自然的使用场景。
核心步骤：三层嵌套子查询拆解
典型实现需要三步：① 找出每个用户的首次行为日期；② 将用户所有行为与自己的首日对齐，算出“距首日天数”；③ 按首日和天数分组统计留存人数。三层嵌套对应三个逻辑层级，缺一不可。
- 外层：按
first_date和day_offset聚合，计算COUNT(DISTINCT user_id) - 中层：用
JOIN或LEFT JOIN把原始行为表和首日表关联，生成day_offset = DATEDIFF(event_date, first_date) - 内层：用
MIN(event_date)或窗口函数MIN(event_date) OVER (PARTITION BY user_id)算首日（注意：GROUP BY user_id+MIN()更兼容老版本 MySQL）
MySQL 5.7 兼容写法（避免窗口函数）
很多生产环境还在用 MySQL 5.7，不支持窗口函数，此时必须用相关子查询或自连接。下面这个写法在 5.7/8.0 都能跑通，且逻辑清晰：
SELECT first_date, DATEDIFF(t2.event_date, t2.first_date) AS day_offset, COUNT(DISTINCT t2.user_id) AS retained_users FROM ( SELECT user_id, MIN(event_date) AS first_date FROM user_events GROUP BY user_id ) t1 JOIN user_events t2 ON t1.user_id = t2.user_id WHERE t2.event_date >= t1.first_date GROUP BY first_date, day_offset ORDER BY first_date, day_offset;
⚠️ 容易踩的坑：JOIN 条件漏掉 t2.event_date >= t1.first_date，会导致出现负偏移；user_events 表若无索引，JOIN 会极慢——务必在 (user_id, event_date) 上建联合索引。
计算 7 日留存时的常见错误
想快速得到“每个队列的第 7 天留存数”，别在外部加 HAVING day_offset = 7——那会丢掉中间天数，破坏队列结构。正确做法是先产出完整队列矩阵，再用应用层或外层 PIVOT（或条件聚合）提取指定天数：
SELECT
first_date,
COUNT(DISTINCT CASE WHEN day_offset = 0 THEN user_id END) AS day0,
COUNT(DISTINCT CASE WHEN day_offset = 1 THEN user_id END) AS day1,
COUNT(DISTINCT CASE WHEN day_offset = 7 THEN user_id END) AS day7,
ROUND(
COUNT(DISTINCT CASE WHEN day_offset = 7 THEN user_id END) * 100.0 /
NULLIF(COUNT(DISTINCT CASE WHEN day_offset = 0 THEN user_id END), 0),
2
) AS retention_7d
FROM (...) AS cohort_matrix
GROUP BY first_date;
关键点在于：分母必须是当天队列的 day0 用户数，不是总用户数；NULLIF 防止除零；CASE WHEN 必须配合 COUNT(DISTINCT ...)，否则会重复计数。
队列分析真正的复杂点不在 SQL 写法，而在于如何定义“活跃行为”和“用户唯一标识”——比如用设备 ID 还是登录账号？是否去重跨端行为？这些业务规则一旦定错，嵌套再深也没用。
