sql多日留存核心是锁定新用户基准池后用count(distinct)+case when偏移判断，分母必须为同日新用户数；clickhouse宜用retention函数或uniqcombined替代join；漏斗分母始终为首日新用户数，时间窗口需严格对齐。
用 COUNT(DISTINCT) + CASE WHEN 算多日留存
直接在 GROUP BY 日期后，对每个用户首次登录日（first_date）做偏移判断，是 SQL 实现留存漏斗最通用、兼容性最强的方式。关键不是“漏斗有多层”，而是“每层是否来自同一新用户池”。
常见错误是把不同日期的活跃用户混在一起算，比如用 COUNT(DISTINCT user_id) 统计某天所有登录用户，再除以当天注册用户——这算的是“活跃渗透率”，不是留存率。
- 必须先用子查询或 CTE 提取每个用户的
first_date，锁定新用户基准池 - 再用
LEFT JOIN或JOIN关联后续行为表，确保只统计该池中人在第1/3/7天是否出现 - 用
CASE WHEN date_diff(login_date, first_date) = 1 THEN user_id END包裹后再COUNT(DISTINCT ...)，避免 NULL 干扰计数 - 别忘了把分母也限定为同一天的
COUNT(DISTINCT user_id)，否则分母会随 JOIN 膨胀
GROUP BY first_date 后不能直接套用 LEAD() 做滚动留存
LEAD() 是窗口函数，适合分析单个用户的连续行为路径（比如“注册后第2天是否登录”），但它无法天然支持“以天为粒度聚合留存率”。如果强行在 GROUP BY first_date 后用 LEAD()，会出现窗口定义错位：每个分组里只剩一个用户 ID，LEAD() 失去意义。
真正需要滚动留存（如“注册后第1/2/3天都活跃的用户占比”）时，得换思路：
- 用
MIN(login_date)定义首次活跃日 - 用
MAX(login_date)和MIN(login_date)差值判断连续活跃天数 - 或者用
arrayJoin(range(1, 8))（ClickHouse）或递归 CTE（PostgreSQL）生成偏移序列再 JOIN
ClickHouse 里别硬写多表 JOIN 做留存漏斗
在 ClickHouse 中，对十几亿行行为日志做 JOIN + GROUP BY + COUNT(DISTINCT)，很容易 OOM 或超时。这不是语法问题，是引擎设计使然——它不擅长高基数去重关联。
更可行的路径是预计算或换函数：
- 优先用
retention(v1, v2, v3)：传入布尔表达式，例如retention(toDate(first_date) = '2025-01-01', toDate(login_date) = '2025-01-02', toDate(login_date) = '2025-01-04')，返回数组 [1,1,0] 表示该用户在第1/3天有行为 - 用
uniqCombined(user_id)替代COUNT(DISTINCT user_id)，内存更友好 - 如果要复用结果，提前按
first_date和login_date - first_date分区建物化视图
漏斗各环节的分母必须一致且不可替换
很多人误以为“浏览 → 收藏 → 加购 → 下单”这个漏斗，每一层的分母都可以是上一层的分子。但在留存+漏斗混合场景下（比如“新用户7日内完成加购的占比”），分母永远是第一天的新用户数，不是第7天还活跃的人数。
容易被忽略的一点是：时间窗口和行为定义必须对齐。例如“7日留存”指注册后第7天当天登录，不是7天内任意一天登录；而“7日内加购”才是只要在7天区间内发生过即可。这两个指标底层逻辑不同，不能共用同一段 CASE WHEN 判断。
