必须用 LEFT JOIN + 显式日期约束，否则分母失真、分子漂移；分母是“首次行为发生在当天的用户子集”，非当日活跃用户。
必须用 LEFT JOIN + 显式日期约束，否则分母失真、分子漂移；分母不是“当天活跃用户”，而是“首次行为发生在当天的用户子集”。
LEFT JOIN 的 ON 条件漏掉日期会怎样
常见错误是写成：ON t1.user_id = t2.user_id，却没加 t2.login_date = DATE_ADD(t1.first_login, INTERVAL 1 DAY)。这会导致右表匹配所有后续登录记录，而非仅目标日——比如次日留存，本该只连 2026-08-27 的登录，结果把 2026-08-28、29 的全连进来。分子变成累计活跃数，算出来次日留存率可能高达 200%，远超物理上限。
正确写法必须同时约束两个字段：ON t1.user_id = t2.user_id AND t2.login_date = DATE_ADD(t1.first_login, INTERVAL 1 DAY)
- MySQL/StarRocks 用
DATE_ADD(first_login, INTERVAL N DAY) - Hive/SparkSQL 用
DATE_ADD(first_login, N)（注意参数顺序相反） - ClickHouse 用
plusDays(first_login, N)或toDate(addDays(toDate(first_login), N)) - 若原始字段是字符串（如
'20260826'），先转日期：STR_TO_DATE(dt, '%Y%m%d')或TO_DATE(dt, 'yyyymmdd')
分母为什么不能直接 GROUP BY login_date
写 SELECT login_date, COUNT(DISTINCT user_id) FROM login_log GROUP BY login_date 得到的是日活（DAU），不是新增用户数。留存率的分母必须是“首次登录发生在当天”的用户集合。
推荐两种安全做法：
- 用窗口函数打标：
MIN(login_date) OVER (PARTITION BY user_id)，外层WHERE first_login = '2026-08-26' - 用子查询先取每人最小日期：
SELECT user_id, MIN(login_date) AS first_login FROM login_log GROUP BY user_id，再与原表关联 - 务必加
WHERE login_date IS NOT NULL，避免MIN()返回 NULL 污染整个分组 - 若无显式注册时间，需确认数据已剔除测试账号、爬虫 IP 等干扰项
COUNT(DISTINCT) 必须按 (user_id, date) 维度去重
一个用户一天登录 5 次，只算 1 个活跃用户；但如果他在基准日登录、次日又登录，这算 1 次留存行为，不是 5 次。所以分子分母都要基于 (user_id, date) 去重，而非单 user_id。
错误写法：COUNT(DISTINCT t1.user_id) → 同一用户多日行为被压缩为 1
正确写法：COUNT(DISTINCT t1.user_id, t1.login_date) → 每个 (用户+日期) 独立计 1
- 若原始字段是
event_time（含时分秒），先用DATE(event_time)提取日期再参与去重 - 时间粒度不统一（比如左表用 DATE，右表用 DATETIME）会导致 JOIN 失败，务必提前对齐
- 业务口径若以“交易”或“浏览”为活跃标准，替换
login_date为对应事件日期字段即可
多日留存别硬写多个 LEFT JOIN
想同时看次日、7 日、30 日留存，不用写三个 LEFT JOIN。更简洁的做法是单次 JOIN + CASE WHEN 条件聚合：
COUNT(DISTINCT CASE WHEN t2.login_date = DATE_ADD(t1.first_login, INTERVAL 1 DAY) THEN t2.user_id END) AS retained_d1
- 避免多次 JOIN 导致笛卡尔爆炸，尤其在用户量大时性能陡降
- 所有目标日期的判断逻辑都放在 SELECT 中，可读性高、易扩展
- 若引擎不支持
CASE WHEN内嵌COUNT(DISTINCT)（如旧版 Hive），改用子查询先标记再聚合 - 零留存用户（首日注册后第二天完全没登录）天然会被
LEFT JOIN保留（t2.user_id为 NULL），无需额外补空行
真正难的不是写 SQL，而是确认“首次行为”是否真实反映业务新增——比如注册时间被延迟上报、跨时区未转换、设备 ID 被复用，这些都会让 first_login 偏移，最终导致整张留存报表系统性偏差。
