正确计算留存率应先汇总分子分母再相除，而非直接avg(留存率)；分母需用LEFT JOIN保证完整cohort；DATE(event_time)必须显式转换以避免时分秒导致漏数据。
别直接 avg(留存率) —— 权重失真会骗人
很多人写完每日留存率后，顺手加个 AVG(retention_rate) 算“平均留存”，结果偏差高达十几个百分点。这不是 SQL 写错了，是统计逻辑错了。
- 问题根源：每个日期的分母（首日用户数）差异巨大，但
AVG()把它们全当 1 来算权重 - 举个例子：
2025-12-01有 1000 新用户，次日留存 60%；2025-12-02只有 10 个新用户，次日留存 90%。直接AVG(60, 90) = 75%，但真实加权均值是(600 + 9) / (1000 + 10) ≈ 60.3% - 正确做法：先汇总所有分母（总首日用户）、所有分子（总次日回访用户），再做一次除法 —— 不要用
AVG()套比率字段
LEFT JOIN 还是 INNER JOIN？漏掉“没回来的人”就毁了分母
计算留存率时，分母必须是完整首日用户集合；分子是其中在第 N 日有行为的子集。用错连接方式，等于主动丢掉分母的一部分。
-
INNER JOIN：只保留“当天来了 + 次日也来了”的用户 → 分母变小，结果虚高 -
LEFT JOIN+COUNT(DISTINCT c.user_id)：确保分母始终是原始 cohort，哪怕没人回访，分母也不缩水 - 常见错误写法：
SELECT COUNT(DISTINCT e.user_id)/COUNT(DISTINCT c.user_id)却用了INNER JOIN→ 表面看语法通，实际逻辑崩了
DATE(event_time) 别漏 cast —— 时分秒会让同一天变“不同天”
很多日志表的 event_time 是 DATETIME 或 TIMESTAMP 类型，直接 WHERE event_time = '2025-12-01' 几乎查不到数据，因为默认匹配到 00:00:00。
- MySQL/PostgreSQL：必须用
DATE(event_time) = '2025-12-01'或event_time >= '2025-12-01' AND event_time - ClickHouse：推荐用
toDate(event_time)，比toStartOfDay()更稳 - 隐患：如果没统一转日期，某天的“首日用户”可能被切碎到多个日期桶里，导致 cohort 被重复或遗漏
“第 0 天”还是“第 1 天”？业务定义不统一，跨团队报表对不上
同一个“次日留存”，A 同事算的是 day1 / day0，B 同事算的是 day2 / day1，俩数字都对，但放一起就是灾难。
- 必须在 SQL 注释或文档里明确定义：
-- 本口径：起始日为 day0，次日留存 = day1 回访用户数 / day0 首次登录用户数 - 尤其注意跨系统对接：BI 工具预设的留存模板、埋点 SDK 自带的统计口径、自研 SQL 脚本，三者要对齐
- 最稳妥做法：在 CTE 里显式标注日期偏移，比如
cohort_date AS '2025-12-01', retention_day AS 1, target_date AS '2025-12-02'
真正卡住人的从来不是 JOIN 或 DATEDIFF 怎么写，而是“我们到底在算什么”。留存量子一动，整个运营归因、AB 实验结论、预算分配逻辑都会跟着偏——所以每次上线新留存脚本前，先拿 3 个已知样本手工验算一遍分母、分子、日期对齐是否一致。
