正确计算留存率应先汇总分子分母再相除，而非直接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 个已知样本手工验算一遍分母、分子、日期对齐是否一致。