group by 本身不能单独算出正确留存率，它只是分组工具；真正决定结果对不对的，是分母是否严格限定为“某日首次行为用户”，以及回访逻辑是否用 left join + 显式日期偏移来保障零留存不丢。
直接说结论：GROUP BY 本身不能单独算出正确留存率，它只是分组工具；真正决定结果对不对的，是分母是否严格限定为“某日首次行为用户”，以及回访逻辑是否用 LEFT JOIN + 显式日期偏移来保障零留存不丢。
GROUP BY login_date 错在哪？
这是最常见也最致命的误用。写 SELECT login_date, COUNT(DISTINCT user_id) FROM user_logins GROUP BY login_date 看起来在“按天聚合”，但实际统计的是每日活跃用户（DAU），不是新增用户——分母完全错了。
留存率的分母必须是“当天首次登录的用户”，不是“当天任意一次登录的用户”。否则 6 月 1 日有 100 人登录、其中 30 人是老用户，你却把 100 当分母，结果必然虚高。
- 错误根源：没剥离每个用户的
first_login，就直接对原始日志GROUP BY - 后果：所有“多日重复登录”的用户，每天都会被重复计入分母，导致留存率系统性低估
- 验证方法：对比
COUNT(DISTINCT user_id)和COUNT(*)，若远小于后者，说明存在大量重复登录，不能直接用原始日志分组
必须先算 first_login，再 GROUP BY first_login
正确路径的第一步，是给每个用户打上“首日标签”。这一步必须独立完成，不能和后续 JOIN 混在一起。
推荐用 CTE 或子查询明确分离：
WITH first_login AS ( SELECT user_id, MIN(login_date) AS first_date FROM user_logins WHERE login_date IS NOT NULL GROUP BY user_id )
注意点：
-
WHERE login_date IS NOT NULL必须加，否则MIN()可能返回NULL，污染整个分母 - 如果要按渠道/设备等维度分组计算留存，
GROUP BY必须包含这些字段，例如GROUP BY channel, user_id，否则首日归属错乱 - 别用窗口函数
MIN() OVER (PARTITION BY user_id)直接套在大表上——数据量大时性能差，且易因未去重导致 first_date 偏移
LEFT JOIN 回访记录时，日期条件必须写在 ON 里
这是另一个高频翻车点。很多人写成：
FROM first_login f LEFT JOIN user_logins u ON f.user_id = u.user_id WHERE u.login_date = DATE_ADD(f.first_date, INTERVAL 1 DAY)
这实际变成了 INNER JOIN：WHERE 过滤会把没次日登录的用户整行剔除，分母变小，留存率虚高。
正确写法是把日期判断放进 ON：
FROM first_login f LEFT JOIN user_logins u ON f.user_id = u.user_id AND u.login_date = DATE_ADD(f.first_date, INTERVAL 1 DAY)
这样即使用户没次日登录，u.user_id 也为 NULL，你能用 CASE WHEN u.user_id IS NOT NULL THEN 1 ELSE 0 准确标记留存与否。
- 多日留存（如 7 日、30 日）统一用这个模式，只改
INTERVAL值，别为每种天数写一个 JOIN - Hive/MaxCompute 用户注意：
DATE_ADD(f.first_date, 1, 'dd')才是等效写法，INTERVAL语法不通用 - 如果业务要求按 cohort（获客日期）看趋势，
GROUP BY f.first_date后，横轴就是 cohort，纵轴才是各周期留存值
条件聚合比多次 COUNT(DISTINCT) 更稳
想一次性出次日、7 日、30 日留存？别写三个 LEFT JOIN——容易漏条件、别名冲突、JOIN 顺序错乱。
用单次 JOIN + 多个 CASE WHEN 更可靠：
COUNT(DISTINCT CASE WHEN u1.user_id IS NOT NULL THEN f.user_id END) AS day1_retained, COUNT(DISTINCT CASE WHEN u7.user_id IS NOT NULL THEN f.user_id END) AS day7_retained, COUNT(DISTINCT CASE WHEN u30.user_id IS NOT NULL THEN f.user_id END) AS day30_retained
但要注意：
- 大数据量下
COUNT(DISTINCT)开销大，若只需比例，可改用SUM(CASE WHEN ... THEN 1 ELSE 0 END)配合整型除法 - 分母必须是
COUNT(DISTINCT f.user_id)，不能用COUNT(*)，否则含重复用户 - 所有
uX表都需独立 LEFT JOIN，且ON中的日期偏移必须精确对应，比如u7的login_date = DATE_ADD(f.first_date, INTERVAL 7 DAY)
真正难的不是写 SQL，而是想清楚“谁该进分母”“谁该进分子”“谁必须保留为空”。GROUP BY 只是执行者，逻辑错，它再快也没用。
