计算留存率需先识别用户首次登录日，再以该日为分母；直接按登录日分组仅得dau，会导致结果失真；须用窗口函数求first_login，left join保留未留存用户，并注意日期类型转换与去重。
GROUP BY 本身不能直接算留存率，必须配合首次登录识别
直接 GROUP BY login_date 只能得到每日活跃用户数，不是新增用户数——这是最常踩的坑。留存率的分母是「当天首次登录的用户」，不是「当天登录过的所有用户」。所以第一步永远是：用窗口函数或子查询算出每个用户的 first_login，再筛选出 first_login = 某日 的记录作为当日新增样本。
常见错误写法：SELECT login_date, COUNT(DISTINCT user_id) FROM user_logins GROUP BY login_date——这统计的是 DAU，不是新客，后续所有留存比都会失真。
- 必须加
WHERE login_date IS NOT NULL，否则MIN(login_date)可能返回NULL，污染分母 - 若原始字段是
DATETIME类型，统一用DATE(login_time)转换，避免时区或精度干扰 - 在 MySQL 中，
DATE_FORMAT(login_time, '%Y-%m-%d')和DATE()行为一致，但前者更显式、可读性更强
LEFT JOIN 是保留零留存用户的关键操作
计算次日留存时，如果只 INNER JOIN 次日登录记录，那些没回来的用户就彻底从结果里消失了，导致分母变小、留存率虚高。正确做法是让「首登用户」作左表，右表是「所有登录记录」，用 LEFT JOIN + ON user_id AND date = DATE_ADD(first_login, INTERVAL 1 DAY)，再靠 t2.user_id IS NOT NULL 判断是否留存。
- 多日留存（如 3 日、7 日）只需复制 JOIN 结构，改
INTERVAL值即可，但注意别漏掉DISTINCT去重，否则同用户多次登录会重复计数 - 若数据库不支持
DATE_ADD（如旧版 Hive），可用TO_DATE(ADD_MONTHS(...))或date_add()替代，函数名必须匹配引擎 - JOIN 条件里一定要限制右表日期范围（如
t2.date ），否则大表扫描极慢
用 CASE WHEN 统一计算多日留存，避免重复扫描
比起为每种留存天数写一个 JOIN，更高效的做法是在一次关联后用 CASE WHEN DATEDIFF(t2.date, t1.first_login) = 1 THEN t2.user_id END 提取各日留存用户，再用 COUNT(DISTINCT ...) 分别聚合。这样只需扫描登录表一次，性能提升明显，尤其在亿级日志表上。
- 分母必须固定为
COUNT(DISTINCT t1.user_id)，不能用COUNT(DISTINCT t2.user_id)，否则零留存用户不参与分子但被计入分母，逻辑才自洽 -
DATEDIFF在 MySQL 中是DATEDIFF(end_date, start_date)，顺序反了结果为负，会导致CASE匹配失败 - 若要支持 cohort 分析（按首登周/月分组），把
first_login替换为DATE_FORMAT(first_login, '%Y-%m')即可，但需注意当月未满 30 天时，该 cohort 的后期留存值天然偏低，得标注“不完整周期”
真实业务中，日期口径错位比语法错误更致命
很多团队跑出的留存率忽高忽低，问题不在 SQL 写错，而在日期定义不一致：比如前端埋点用 UTC 时间，而数据库服务器用东八区，DATE(login_time) 就可能把凌晨 1 点的登录算成前一天。结果就是首登日和回访日错开一天，次日留存直接归零。
- 统一用
CONVERT_TZ(login_time, '+00:00', '+08:00')（MySQL）或AT TIME ZONE（PostgreSQL）对齐时区 - 若业务要求“滚动 24 小时留存”，就不能用
DATE()，而要用login_time >= first_login_time + INTERVAL '1 day'做条件过滤 - 上线前务必拿小样本人工核对：挑 5 个用户，查他们
first_login和后续登录时间差，确认DATEDIFF输出是否符合预期
