窗口函数不能直接计算留存率，必须先打标（如first_login）、再聚合、最后算比值；直接用row_number()或lag()会混淆登录序号与日期差，导致结果错误。
窗口函数本身不能直接算出留存率或漏斗转化率，它只是辅助工具；真正起作用的是“先打标、再聚合、最后比值”这一链路。硬套 LAG() 或 ROW_NUMBER() 得到的不是留存，而是登录序号或时间差——两者语义完全不同。
为什么直接用 ROW_NUMBER() 算留存会出错
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) 给的是用户第几次登录，不是“距首次登录第几天”。比如某用户在 2026-09-01、2026-09-03 登录两次，这个函数返回 1、2，但次日留存要求的是 2026-09-02 是否有行为——根本没这行数据，ROW_NUMBER() 也查不到。
- 错误写法：
ROW_NUMBER() = 2→ 找的是“第二次登录”，不是“登录后第二天还来” - 正确思路：必须先算出
first_login，再用日期运算得day_diff = DATEDIFF(login_date, first_login) - MySQL 用
DATEDIFF(login_date, first_login)，ClickHouse 用dateDiff('day', first_login, login_date) - 分母必须是
COUNT(DISTINCT CASE WHEN day_diff = 0 THEN user_id END)，不能用总用户数或日活
用 LAG() 和 LEAD() 判断“是否回来”，但仅限单日留存场景
这类函数适合判断相邻行为是否存在（如次日是否活跃），但前提是数据里已有对应日期的记录。如果用户第二天完全没行为，那这行就不存在，LAG() 也拉不出空值——结果直接漏掉“未留存”样本。
- 必须加
PARTITION BY user_id，否则跨用户取值，结果全乱 - 必须显式写
ORDER BY login_date，否则排序不可控，LAG()返回随机行 - 示例：统计“当天活跃且明天也活跃”的用户占比，可写
COUNT(DISTINCT CASE WHEN next_login = DATE_ADD(login_date, INTERVAL 1 DAY) THEN user_id END) - 但注意：这只能算“连续两天活跃率”，不等于标准次日留存（后者要求以首日为基准）
LEFT JOIN + 日期偏移才是计算多日留存的稳态方案
想同时看次日、7日、30日留存，别写三个 LEFT JOIN，容易别名冲突、日期写错、漏条件。用一次 JOIN 配合多个 CASE WHEN 更可靠。
-
ON条件必须同时约束user_id和目标日期，例如：ON n.user_id = d.user_id AND d.login_date = DATE_ADD(n.first_login, INTERVAL 1 DAY) - MySQL 用
DATE_ADD(first_login, INTERVAL N DAY)，Hive/SparkSQL 用DATE_ADD(first_login, N)（参数顺序相反） - 分母必须来自首次行为子集，不能直接
GROUP BY login_date—— 那是日活，不是新增 cohort - 构造分母推荐用：
MIN(login_date) OVER (PARTITION BY user_id)打标后过滤WHERE first_login = '2026-09-25'
窗口函数只在环比/同比时才真正必要
留存率是比率，直接对比率字段用 LAG(retention_rate) 做环比会因分母波动失真。正确做法是保留原始分子、分母，再在外层用窗口函数分别拉取前后周期的数值，最后做除法。
- 错误：
LAG(retention_rate) OVER (ORDER BY week_start) - 正确：
LAG(retained_users) OVER (...) AS prev_ret和LAG(cohort_size) OVER (...) AS prev_cohort，然后算(curr_ret * 1.0 / curr_cohort) - (prev_ret * 1.0 / prev_cohort) - 漏掉
curr_ret * 1.0强转会导致整数除法截断（尤其 MySQL 默认），结果恒为 0 - ClickHouse 用户注意：
retention()这类内置函数虽简洁，但仅适用于固定日期序列，无法灵活适配动态 cohort 或非连续日期
最常被忽略的一点：所有计算前必须清洗 login_date IS NOT NULL，否则 MIN() 或 DATEDIFF() 产出 NULL，污染整个分组；另外，首次行为若来自测试账号或爬虫 IP，不剔除会导致分母虚高，后续所有比率全偏移。
