留存率是某批用户（cohort）在后续第N天再次活跃人数与该批初始用户数之比，不能用GROUP BY直接算因会丢失用户跨日行为关联、导致分母错配；需用窗口函数标记首次登录日、计算距首日天数、再按cohort聚合。
什么是留存率，为什么不能用 GROUP BY 直接算？
留存率不是简单统计「第N天还登录的用户数 / 首次登录用户数」，关键在于：分母必须是某一批用户的**初始 cohort（同期群）**，分子是这批用户中在后续某天**再次活跃**的人数。用 GROUP BY 按日期聚合会丢失用户跨日行为关联，导致分母被重复计算或错配。
窗口函数的核心价值是：在不破坏原始行粒度的前提下，为每行打上 cohort 标签，并支持跨时间维度聚合。实际要做的三件事：标记首次登录日 → 定义 cohort → 对每个 cohort 内用户做跨日存在性判断。
用 MIN() 窗口函数标记每个用户的首次登录日
这是构建 cohort 的基础。不能依赖业务表里现成的「注册时间」，因为留存看的是「实际首次活跃行为」。假设原始表叫 user_event，字段含 user_id、event_date：
SELECT user_id, event_date, MIN(event_date) OVER (PARTITION BY user_id) AS first_login_date FROM user_event WHERE event_date >= '2024-01-01';
注意点：
-
MIN(event_date) OVER (PARTITION BY user_id)是关键，它把每个用户所有记录都补上其最早登录日 - 务必加
WHERE限定时间范围，否则历史脏数据会污染first_login_date - 如果表里有明确注册时间且和首次登录严格一致，可直接用，但多数场景下仍建议以行为日志为准
用 DATE_DIFF() 或日期运算计算「距首次登录的天数」
不同数据库语法略有差异，但目标一致：把每个事件行映射到「该用户属于哪个 cohort + 当前是第几天」：
BigQuery 示例：
SELECT
user_id,
first_login_date,
event_date,
DATE_DIFF(event_date, first_login_date, DAY) AS day_offset
FROM (
SELECT
user_id,
event_date,
MIN(event_date) OVER (PARTITION BY user_id) AS first_login_date
FROM user_event
WHERE event_date >= '2024-01-01'
);
PostgreSQL / MySQL 需换用 event_date - first_login_date 或 DATEDIFF() 函数。重点：
-
day_offset = 0表示首日，day_offset = 1是次日，以此类推 - 不要用
ROW_NUMBER()替代，它按顺序编号，不是真实日历差 - 若需周留存，用
WEEK或FLOOR(day_offset / 7.0)，但要注意跨年/跨月边界
用 COUNT(DISTINCT) + 条件聚合算各天留存率
最终聚合必须在 cohort 维度上做，且分子只计「在指定 day_offset 出现过的用户」：
SELECT first_login_date AS cohort_date, COUNT(DISTINCT user_id) AS cohort_size, COUNT(DISTINCT CASE WHEN day_offset = 0 THEN user_id END) AS d0, COUNT(DISTINCT CASE WHEN day_offset = 1 THEN user_id END) AS d1, COUNT(DISTINCT CASE WHEN day_offset = 7 THEN user_id END) AS d7, COUNT(DISTINCT CASE WHEN day_offset = 30 THEN user_id END) AS d30 FROM ( -- 上面带 day_offset 的子查询 ) t GROUP BY first_login_date ORDER BY cohort_date;
真正容易出错的地方：
- 分母必须是
COUNT(DISTINCT user_id)，不是总行数——同一用户某天多次登录只算 1 人 - 分子用
CASE WHEN ... THEN user_id END，再套COUNT(DISTINCT)，不能写成SUM(CASE WHEN ... THEN 1 ELSE 0 END)，后者会重复计数 - 如果某 cohort 的
d7为 0，说明该批用户无人在第 7 天活跃，这是真实数据，不是 SQL 写错了
复杂点不在语法，而在定义本身：你得确认「活跃」指什么——是登录？下单？还是某个特定事件？只要把 user_event 表替换成对应行为表，整套逻辑就复用。别漏掉对行为类型的过滤条件。
