分组留存率必须按分组独立计算首日,否则分母混入其他组用户导致失真;正确做法是用PARTITION BY channel, user_id算窗口函数得各组首登日,再打标聚合。

分组留存率不能直接在全局用户集上算完再 GROUP BY,必须让每个分组(如渠道、设备类型、地区)独立确定“首日”,否则分母会混入其他组用户,结果完全失真。
为什么 GROUP BY channel 后直接套用全局留存逻辑会出错
常见错误是先算出全量用户的 first_login,再按 channel 分组统计留存。问题在于:某用户在 iOS 渠道注册,但首次登录行为被记在 Android 渠道埋点里(比如跨端同步延迟),全局 MIN(login_time) 就会把它划进错误分组,导致 iOS 分母虚高、Android 分母漏计。
- 根本矛盾:留存的“首日”必须是该分组内用户在该分组下的首次行为,不是全量视角的首次
-
GROUP BY channel本身不改变原始行的first_login值,它只是聚合,不会重算每组的基准日 - MySQL/PostgreSQL 等主流引擎中,
MIN(login_time) OVER (PARTITION BY channel, user_id)才能真正绑定分组上下文
正确做法:用 PARTITION BY channel, user_id 算首日
窗口函数是目前最简洁、兼容性最好的方案,避免多层子查询和反复 JOIN。关键是要把分组维度塞进 PARTITION BY,确保每个用户在各自分组下独立打标。
- 先用
MIN(login_date) OVER (PARTITION BY channel, user_id)得到每个用户在该渠道的首次登录日 - 再用
login_date - first_date计算距首登天数(注意数据库日期减法语法差异) - 最后用
COUNT(DISTINCT CASE WHEN day_diff = 0 THEN user_id END)构造分母,CASE WHEN day_diff = 1 THEN user_id END构造分子 - 不要在最外层直接
GROUP BY channel—— 要先完成用户级打标,再聚合,否则窗口结果会被截断
CTE 分步写法更稳,适合兼容旧版 Hive 或字段逻辑复杂场景
如果目标环境不支持窗口函数,或需要显式控制中间态(比如过滤掉测试账号、清洗异常日期),用 CTE 分三步走更可控:
-
daily_active:先去重,提取user_id, channel, DATE(login_time) AS login_date -
first_by_group:对user_id, channel组合求MIN(login_date),强制分组隔离 -
retention_join:用LEFT JOIN关联次日行为,条件含d.channel = f.channel和d.login_date = DATE_ADD(f.first_date, INTERVAL 1 DAY) - 最后
GROUP BY channel汇总,分子分母都用COUNT(DISTINCT ...)防重复
容易被忽略的细节:NULL 值、时区、日期精度
真实生产环境中,80% 的分组留存偏差来自这三点,而不是逻辑本身:
-
login_date字段含 NULL?必须加WHERE login_date IS NOT NULL AND login_date >= '2023-01-01',否则MIN()可能返回无效值 - 日志时间是 UTC,但业务要求按本地时区(如 Asia/Shanghai)切分?得先用
CONVERT_TZ()或AT TIME ZONE转换,再取DATE() - 用
SUBSTR(event_time, 1, 10)截字符串当日期?一旦遇到'2026-11-03 '(带空格)或'2026/11/03'格式就崩,必须用DATE(event_time)或显式STR_TO_DATE()










