留存率需用LAG()/LEAD()判断用户是否“回来”，结合MIN()窗口函数计算首次登录日并日期归位，再通过条件聚合统计各日留存，分母须限定首日落在观察期内的新用户。
用 LAG() 或 LEAD() 标记用户是否“回来”了
留存率本质是看“老用户在后续周期是否再次活跃”，不是算总人数。直接 COUNT(DISTINCT) 某天用户再除以首日用户，会漏掉时间顺序——比如用户第 1 天和第 3 天登录，但没在第 2 天出现，他不算“次日留存”，但会被错误计入“3 日内活跃”。LAG() 能按用户分组、按时间排序，把上一次登录日期拎出来，和当前行对比：
SELECT user_id, login_date,<br> LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_login<br>FROM user_logins;常见错误是忘了
PARTITION BY user_id，结果跨用户拉错日期；或者 ORDER BY 没写，窗口行为不可控。
用 DATE_SUB() / DATEADD() 对齐“第 N 日”的基准日
留存分析必须固定一个起始点（比如每个用户的首次登录日），再看之后第 1/7/30 天有没有行为。“日期归位”就是把每次登录映射到相对于首日的偏移量。MySQL 用 DATE_SUB(login_date, INTERVAL first_login DAY)，PostgreSQL/Redshift 用 login_date - first_login，SQL Server 用 DATEADD(day, -1 * DATEDIFF(day, 0, first_login), login_date)。关键陷阱：别用 DAYOFYEAR() 或 WEEK() 这类非线性函数，它们跨年或跨周会断层；也别用字符串截取（如 SUBSTR(login_date, 1, 7)），丢失日粒度精度。
用 MIN(login_date) OVER (PARTITION BY user_id) 算首次登录日
这是归位的前提。有人用子查询或 GROUP BY user_id 再 JOIN，代码长、性能差；其实窗口函数一行搞定：
SELECT user_id, login_date,<br> MIN(login_date) OVER (PARTITION BY user_id) AS first_login<br>FROM user_logins;注意：如果原始数据里有脏数据（如
login_date 为 NULL 或远古日期），MIN() 会把它当真实值。建议先过滤：WHERE login_date >= '2023-01-01' 或加 AND login_date IS NOT NULL。另外，不同数据库对 MIN() 窗口的 NULL 处理一致，但某些旧版 Hive 可能不支持，需确认版本。
组合起来算次日留存率：避免漏掉“零留存”用户
真正难的是：如果某用户首日登录后，第二天完全没行为，他在原始日志里压根不出现，直接 GROUP BY 就会丢掉这个“未留存”的样本。正确做法是生成用户 × 日期的完整组合（用 LEFT JOIN 或 CTE 构造日期序列），再判断。更轻量的解法是用条件聚合 + 存在性标记：
SELECT<br> COUNT(DISTINCT CASE WHEN days_since_first = 0 THEN user_id END) AS dau_base,<br> COUNT(DISTINCT CASE WHEN days_since_first = 1 THEN user_id END) AS dau_d1,<br> ROUND(100.0 * COUNT(DISTINCT CASE WHEN days_since_first = 1 THEN user_id END) / <br> NULLIF(COUNT(DISTINCT CASE WHEN days_since_first = 0 THEN user_id END), 0), 2) AS retention_d1<br>FROM (<br> SELECT user_id, login_date,<br> MIN(login_date) OVER (PARTITION BY user_id) AS first_login,<br> DATEDIFF(login_date, MIN(login_date) OVER (PARTITION BY user_id)) AS days_since_first<br> FROM user_logins<br> WHERE login_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)<br>) t;重点：分母必须严格限定在“首日落在观察期内的用户”，否则会混入历史用户，拉低留存率；
NULLIF 防止除零；DATEDIFF 在不同库语法略有差异（如 PostgreSQL 用 -），别硬套。
日期归位本身不难，难在每一步都要对齐业务定义：谁算“新用户”、哪天算“第 1 天”、缺失行为要不要显式建模。这些细节不锁死，跑出来的数字就只是自洽，不是真实。
