首日/次日留存需先计算用户首次活跃日，再用日期差生成距首日天数，最后条件聚合；窗口函数须作用于明细数据，避免与GROUP BY混用；注意数据库NULL处理差异及用户生命周期阶段划分。
窗口函数怎么写才能算对首日/次日留存
窗口函数本身不直接计算留存，关键在配合日期差和用户分组逻辑。很多人误以为 LAG() 或 LEAD() 能直接得出留存率，其实它们只负责取相邻记录，真正决定“是否留存”的是「当天是否活跃」+「是否在前N天也活跃」的布尔判断。
实操建议：
- 先用
MIN(event_date)算出每个用户的首次活跃日（first_login），这是所有留存计算的锚点 - 再用
DATE_DIFF(event_date, first_login, DAY)（BigQuery）或DATEDIFF(event_date, first_login)（MySQL）生成“距首日天数”字段 - 最后用条件聚合：例如
COUNTIF(day_diff = 1) * 1.0 / COUNTIF(day_diff = 0)得到次日留存率 - 别在窗口函数里直接套
COUNT(*) OVER (PARTITION BY user_id ORDER BY event_date)——这算的是累计登录次数，不是留存
GROUP BY 和窗口函数混用时为什么总报错
典型错误是写成 SELECT user_id, COUNT(*) OVER (PARTITION BY user_id), COUNT(*) FROM t GROUP BY user_id，MySQL/PostgreSQL 会直接拒绝：非聚合字段未出现在 GROUP BY 中，而窗口函数又不算聚合表达式。
根本原因是 SQL 执行顺序：GROUP BY 在窗口函数计算之前就完成了行压缩。所以窗口函数必须作用于“未分组前的明细数据”。
正确做法：
- 把窗口计算放在子查询或 CTE 里，确保输入是原始行为日志（每行一个用户-日期事件）
- 外层再按日期、分群等维度
GROUP BY汇总留存指标 - 如果硬要在同一层写，得把所有非聚合字段都塞进
GROUP BY，但会破坏留存所需的用户粒度
不同数据库的日期函数和NULL处理差异有多大
DATE_DIFF、LAG 默认返回 NULL 是共性，但 NULL 参与比较的行为不一致：PostgreSQL 中 day_diff = 1 对 NULL 返回 NULL（即 false-like），而某些 Hive 版本会直接跳过该行，导致分母变小、留存率虚高。
实操建议：
- 显式过滤掉
first_login IS NOT NULL和event_date IS NOT NULL，避免参与计算的日期不可靠 - 用
COALESCE(DATE_DIFF(...), -999)把 NULL 转为可控值，再用CASE WHEN day_diff IN (0,1,7) THEN ...控制分组逻辑 - BigQuery 的
LAG支持第三个参数设默认值（如LAG(event_date, 1, '1970-01-01')），但 MySQL 8.0 不支持，默认就是 NULL，得靠外层IFNULL
留存分析中漏掉“用户生命周期阶段”会怎样
只按自然日算留存（比如所有用户统一从注册日开始算第1天），会掩盖新老用户行为差异。刚注册的用户次日打开率天然高，而沉默30天的老用户突然回访，不该和新人混在同一分母里。
更合理的建模方式是引入“活跃断层”判断：
- 用
LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date)算出上次活跃日 - 再用
DATE_DIFF(event_date, prev_event_date, DAY) > 7定义“流失”，把用户打上churned_before标签 - 后续留存分母只取
churned_before = FALSE的用户，分子取churned_before = TRUE AND event_date - prev_event_date 的回流行为 - 否则你看到的“7日留存25%”，可能是10%新用户 + 40%回流用户混合的结果，无法归因
这个分层逻辑一旦漏掉，后续所有AB测试、渠道归因都会偏移——不是SQL写错了，是模型假设没对齐业务现实。
