留存率队列分析是将用户按首次活跃日期分组并追踪其后续N日活跃情况的分析方法；仅用GROUP BY按日期统计日活会丢失“新用户来源”维度，无法区分真实留存与老用户混入，必须结合窗口函数（如MIN() OVER）计算首活日并标记cohort，再通过DATEDIFF得出相对天数以实现精准回溯。
什么是留存率队列分析，为什么不能只用 GROUP BY
留存率队列分析的核心是：把用户按首次活跃日期（cohort）分组，再看他们在后续第 N 天是否还活跃。如果只用 GROUP BY 按日期统计日活，就丢失了“谁是新用户”“谁是从哪天来的”这个关键维度——你无法区分一个 7 天后还在用的用户，到底是第 1 天的新用户，还是第 6 天才注册的老油条。
窗口函数能解决这个问题，因为它允许你在保留原始行粒度（比如每条用户-日期行为记录）的前提下，跨行计算相对时间差、标记 cohort、聚合回溯周期。关键不是“用没用窗口函数”，而是怎么用对。
用 DATEDIFF + MIN() OVER 实现 cohort 标记
第一步必须准确打上 cohort 标签，即每个用户第一次出现的日期。错误做法是直接用注册表——很多业务里用户没注册就产生行为（如浏览商品），所以得从行为日志里算首活日。
- 先用
MIN(event_date) OVER (PARTITION BY user_id)算出每个user_id的首活日（cohort_date） - 再用
DATEDIFF(event_date, cohort_date)得到该行为距首活的天数（days_since_cohort） - 注意：MySQL 8.0+ 和 PostgreSQL 用
DATEDIFF或event_date - cohort_date；BigQuery 用DATE_DIFF(event_date, cohort_date, DAY) - 别在 WHERE 中提前过滤
days_since_cohort ——这会丢掉 cohort 后第 8 天才回访的用户，导致分母不准
用 COUNT(DISTINCT) + CASE WHEN + 窗口聚合算各期留存
有了 cohort_date 和 days_since_cohort，就可以按 cohort 分组、按天数切片统计。但不能直接 GROUP BY cohort_date, days_since_cohort 就完事——那样算的是“当天活跃用户数”，不是“从 cohort 里留存下来的人数”。必须确保分母是每个 cohort 的首日去重用户数，分子是该 cohort 在第 N 天仍有行为的去重用户数。
- 分母：用
COUNT(DISTINCT user_id) OVER (PARTITION BY cohort_date)把首日用户数广播到 cohort 内每一行 - 分子：对每个
cohort_date，分别判断days_since_cohort = 0、=1、=7等，用COUNT(DISTINCT CASE WHEN days_since_cohort = 1 THEN user_id END) OVER (PARTITION BY cohort_date) - 最终留存率 = 分子 / 分母，结果需转为
DECIMAL(5,4)或乘 100.0 避免整数除零截断 - BigQuery 中
COUNT(DISTINCT ...)在窗口中不支持，得改用APPROX_COUNT_DISTINCT或先聚合再 JOIN
导出队列表格时小心 NULL 和稀疏数据
直接查出来的结果是长表（每行一个 user_id + event_date + days_since_cohort），要变成宽表形式（cohort_date 为行，day 0/1/7 为列），得用条件聚合或 pivot。但更常见的坑是：某 cohort 第 3 天没人活跃，对应列就是 NULL，而前端或 Excel 容易把 NULL 当 0 处理，导致留存率被算成 0% 而不是“无数据”。
- 用
COALESCE(COUNT(...), 0)显式补 0，但注意：补 0 和真实 0 留存含义不同，建议保留 NULL 并在下游注明“无行为记录” - 避免用
PIVOT硬转——SQL 标准不统一，Spark SQL 不支持，Redshift 要写子查询，不如用多层CASE WHEN+MAX()模拟 - 如果 cohort 规模小（如单日 COUNT(DISTINCT) 误差可能明显，此时应检查是否真需要精确值，还是可用 HyperLogLog 近似
真正难的不是写出第一个留存率，而是让 cohort 划分逻辑和业务定义完全对齐——比如“首次下单才算首活”，那就要把行为表换成订单表，并确保订单时间不含测试单、退款单。这点没法靠窗口函数自动修正，得靠上游数据清洗兜底。
