正确计算留存率需先用min(login_date) over(partition by user_id)求首登日，再用日期差（非row_number）算天数，最后条件聚合分子分母；须过滤null、统一date类型、避免join补零。
直接用 LAG() 或 ROW_NUMBER() 算留存率会得到错误结果——它们处理的是行序或相邻行为，不是“距首次登录的自然天数”。PostgreSQL 14 中正确做法是：先用窗口函数算出每个用户的 first_login，再用日期差构造留存标签，最后靠条件聚合算比率。
用 MIN() OVER (PARTITION BY user_id) 定义首登日
这是整个留存计算的前提。不能依赖子查询或 GROUP BY + JOIN，那样性能差、可读性低，且在大表上容易 OOM。
-
MIN(login_date) OVER (PARTITION BY user_id)一行搞定首登日，无需 CTE 或子查询 - 必须加
WHERE login_date IS NOT NULL AND login_date >= '2025-01-01'过滤脏数据，否则MIN()可能返回远古日期或 NULL - PostgreSQL 14 对
MIN() OVER的 NULL 处理稳定，但若字段类型是timestamptz，建议先::date转为日期粒度，避免时区干扰分组
用 DATEDIFF 或减法算 day_diff，别用 ROW_NUMBER()
次日留存不是“第二次登录”，而是“登录后第二天还来”。ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) 返回的是登录序号，和自然天数无关；错用会导致偏差超 30%。
- PostgreSQL 用
login_date - first_login得到整数天差（注意：两个DATE相减结果是integer） - 如果字段是
timestamp，先转日期：(login_date::date - first_login::date) - 别用
EXTRACT(DAY FROM ...)或AGE()，它们返回 interval，参与CASE WHEN易出隐式转换错误
用 COUNT(DISTINCT CASE WHEN ...) 做条件聚合，不是窗口函数直接算比率
窗口函数不负责最终的除法。留存率是集合比值，必须用分母（首日用户数）和分子（第 N 日回访用户数）分别统计，再相除。
- 分母固定为
COUNT(DISTINCT CASE WHEN day_diff = 0 THEN user_id END)，即首日活跃用户 - 分子写成
COUNT(DISTINCT CASE WHEN day_diff = 1 THEN user_id END)（次日）、= 7（7 日）等 - 不要在
OVER()里直接写COUNT(DISTINCT ...) / COUNT(DISTINCT ...)—— 窗口函数无法跨分区做这种分母归一化 - 若需环比，先保留原始分子分母字段，外层再用
LAG()拉取前一期值并手动相除
PostgreSQL 14 中避免 LEFT JOIN 构造全量用户 × 日期组合
传统做法用 LEFT JOIN 行列扩展来补“零留存”用户，但在千万级用户表上极易爆内存或超时。PostgreSQL 14 更推荐轻量解法：
- 用
GENERATE_SERIES()配合WITH RECURSIVE仅生成观察期内的日期序列，而非全量笛卡尔积 - 或直接接受“未回访用户不出现在日志中”的事实，用条件聚合天然覆盖：只要分母含首日用户、分子为对应天数的回访用户，比率就准确
- 真正难处理的是“首日有登录，次日没行为但第三日有”，这时
day_diff = 1就是 NULL，COUNT(DISTINCT CASE WHEN day_diff = 1 THEN user_id END)自动计为 0，无需额外补行
最容易被忽略的一点：所有日期运算必须统一用 DATE 类型对齐。混用 timestamp 和 date、或在 WHERE 中用 login_date >= '2026-09-28' 匹配 timestamptz 字段，会导致索引失效和结果漏数。
