留存率是某天新增用户在之后第N天仍活跃的比例，如次日留存=（D+1日仍登录的D日新用户数）/（D日新用户总数）；不能用GROUP BY直接算，因其无法关联同一用户跨日行为，易混淆新老用户，正确做法是先用MIN() OVER标记首日，再基于首日做DATEDIFF和条件聚合计算。
什么是留存率，为什么不能用 GROUP BY 直接算
留存率本质是「某天新增的用户，在之后第 N 天仍有活跃行为」的比例。比如次日留存 =（D+1 日仍登录的 D 日新用户数）/（D 日新用户总数）。问题在于：单靠 GROUP BY 无法把「一个用户在不同日期的行为」关联起来——你没法在聚合后还判断“这个用户昨天是不是新用户”。
常见错误是写成：SELECT DATE(event_time) AS dt, COUNT(DISTINCT user_id) / LAG(COUNT(DISTINCT user_id)) OVER (ORDER BY DATE(event_time)) FROM log GROUP BY dt。这算的是「每日活跃用户占前一天活跃用户的比」，不是留存，因为分母混入了老用户，分子也没限定“必须是昨日新增”。
用 ROW_NUMBER() + MIN() 定位每个用户首次行为日期
核心是先标记谁是“新用户”。不能依赖业务系统打标（常有延迟或漏标），应从原始行为日志中推导：
- 对每个
user_id，用ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time)找出第一条记录 - 或更稳妥：用
MIN(event_time) OVER (PARTITION BY user_id)直接算出该用户首次出现时间
然后给每条记录打上 first_event_date 标签。后续所有留存计算都基于这个字段做日期差。
SELECT user_id, DATE(event_time) AS dt, DATE(MIN(event_time) OVER (PARTITION BY user_id)) AS first_event_date FROM event_log WHERE event_type = 'login'
注意：如果用户注册和首次登录不在同一天，且你要算「注册用户留存」，那 first_event_date 必须来自注册表，不能用登录日志反推。
用 DATEDIFF() 和条件聚合算具体 N 日留存
有了 first_event_date，就可以对每个新用户群体（按首日分组）统计其后续活跃情况：
- 先过滤出所有「首日为 D 的用户」，再看他们在 D+N 日是否出现过行为
- 用
CASE WHEN DATEDIFF(dt, first_event_date) = 1 THEN 1 END标记次日活跃 - 最后用
COUNT(DISTINCT CASE WHEN ... THEN user_id END) / COUNT(DISTINCT user_id)算比率
SELECT
first_event_date AS cohort_date,
COUNT(DISTINCT user_id) AS new_users,
COUNT(DISTINCT CASE WHEN DATEDIFF(dt, first_event_date) = 1 THEN user_id END) * 1.0
/ COUNT(DISTINCT user_id) AS retention_d1,
COUNT(DISTINCT CASE WHEN DATEDIFF(dt, first_event_date) = 7 THEN user_id END) * 1.0
/ COUNT(DISTINCT user_id) AS retention_d7
FROM (
SELECT
user_id,
DATE(event_time) AS dt,
DATE(MIN(event_time) OVER (PARTITION BY user_id)) AS first_event_date
FROM event_log
WHERE event_type = 'login'
) t
GROUP BY first_event_date
关键点：
-
DATEDIFF在不同数据库语义不同（MySQL 是 end - start，BigQuery 是 start - end），务必查文档 - 分子分母都用
COUNT(DISTINCT user_id)，避免同一用户当天多次行为被重复计数 - 如果数据量大，
PARTITION BY user_id的窗口函数可能成为性能瓶颈，建议先用物化视图或预计算first_event_date到用户维表
为什么不能直接用 LAG/LEAD 算留存
有人尝试用 LAG(user_id, 1) OVER (PARTITION BY user_id ORDER BY dt) 判断“昨天有没有记录”，这是错的。原因有三：
-
LAG是行级偏移，但用户每天可能有多条记录，无法保证“前一行就是昨天” - 如果用户某天没行为，
LAG会跳到更早的日期，导致 D+1 变成 D+3 - 没法区分“老用户回流”和“新用户次日留存”，因为没锚定首日
真正需要的是以「首日」为基准的绝对偏移，不是相对行偏移。所以 MIN() OVER + DATEDIFF() 是不可替代的组合。
实际跑的时候，最容易被忽略的是时区和日期截断方式——DATE(event_time) 如果没统一转成业务时区，会导致跨日计算偏差；而用 event_time::DATE 在某些引擎里会隐式触发时区转换，最好显式用 CONVERT_TIMEZONE 或 AT TIME ZONE 处理。
