上周运营负责人又来找我，张口就问：“最近新用户留存是不是掉了？你帮我看下按渠道拆的留存，到底哪个渠道质量不行。”这种需求我接过无数回，看起来一句话能讲清楚，可真要落到 SQL 里，坑多得能绊倒人。今天我就把“用 SQL 分析不同用户群组留存率”这件事完整拆一遍，从口径定义、表结构设计，到 SQL 写法、常见坑点，一次性聊透。适合刚接触数据分析的运营、后台开发，以及需要自己跑数的产品同学。
1. 先别急着写 SQL：把留存口径定下来
1.1 一次看似简单的需求，至少要想清五个问题
很多人拿到“算留存”的需求，第一反应就是打开编辑器写 SQL。但只要你多问业务方一句“你说的留存是第几天的留存”，对方可能会愣一下。留存率不是只有一个定义，它是“某个群组用户在指定时间间隔后，还继续使用产品的比例”。这里的“某个群组”“指定时间间隔”“继续使用”每个词都可以有完全不同的解释。
我在实际工作中，接需求后一定先和业务对齐几个问题：第一，用户是按什么口径算的？是注册用户、激活用户，还是只算当天下载安装的人？第二，用户身份用账号还是设备？第三，“留存”的时间口径是次日留存、7日内留存，还是30日留存？第四，什么叫“回来过”？打开 App 算，还是必须有某个业务行为才算？第五，是否要按渠道、版本、地区等维度切分？
这些问题不定清楚，SQL 跑出来的数字再漂亮也没用。比如有的团队把“启动事件”当活跃，有的团队把“完成下单”当活跃，两者的留存率能差出一大截，拿去给老板汇报的时候特别容易出问题。
1.2 几个经典留存口径，一定要先分清
行业里最常见的留存口径有这么几类：
| 口径名称 | 定义 | 典型用法 |
|---|---|---|
| D0 留存 | 注册/新增当天的活跃占比 | 通常就是新增转化率，不是严格意义的留存 |
| D1 留存 | 注册后第 1 个自然日回访的占比 | 最常用，衡量产品早期体验 |
| D3/D7/D30 留存 | 注册后第 N 个自然日回访的占比 | 衡量中期粘性和长期价值 |
| N 日内留存 | 注册后 N 天内至少回访过一次的占比 | 比单日留存更稳定，比如 7 日内留存 |
| 自然周/月留存 | 按周/月为单位的回访留存 | 适合低频工具类产品 |
这里要特别注意一个误区：D1 不是“注册满 24 小时”的意思，而是“注册后的第 1 个自然日”。比如用户 6 月 1 日 23:50 注册，D1 就是 6 月 2 日 00:00 到 23:59 之间有没有回访，而不是 6 月 2 日 23:50 之后才叫 D1。很多新手在这里算错，导致数据差一天。
1.3 为什么要按“群组”拆开看，而不是只看一个平均数
只算整体留存率是件很危险的事。举一个我真实遇到的例子：某个月整体 D30 留存看起来没有波动，但仔细拆开一看，付费买量渠道的用户留存跌了 40%，只是自然流量渠道的占比提高了，把平均数拉住了。如果你只看整体曲线，可能等到下个月投放预算烧完了才发现问题。
这就是按群组分析的意义。所谓群组，可以简单理解为“同一批有共同特征的用户”。最经典的群组是注册日期群组，也就是 Cohort 分析；常见的还有渠道群组、版本群组、地区群组，甚至按用户行为特征分出的群组。不同群组的留存差异，往往是产品和运营决策最重要的依据之一。
2. SQL 算留存的核心思路：主表、回访探针、日期差
2.1 主表选不对，结果一定错
讲完口径，开始落到 SQL。计算留存的核心逻辑其实不复杂：拿到一个群组的用户集合，然后去看这些人未来某天是否回访。这个逻辑听起来简单，但很多第一次写的人会把主表选错。
我见过最典型的错误写法是：先筛选当天的活跃用户，再去关联未来某天的活跃用户，最后算比例。表面上看也没毛病，实际上漏掉了最关键的一步——当天新增但第二天再也没回来的人，压根不在活跃表里。用活跃表当主表，等于提前把留存为 0 的用户全扔了，算出来的留存率虚高得离谱。
正确做法是把“用户表”作为主表，也就是说，哪怕这个用户之后 30 天一次都没活跃过，也必须保留在主表里，然后用行为表做 LEFT JOIN 去“探访”他有没有回来。LEFT JOIN 的精髓就在这里：左边主表的一行，右边匹配不上也会保留，NULL 正好用来表示“没回访”。
2.2 “第N日回访”在 SQL 里怎么表达
确认主表之后，下一步就是“探针”怎么写。这里我通常用日期差函数给每一行打一个“相对注册日”的标签，然后再做条件聚合。
想象一下你有一张用户表，里面有 user_id 和 register_date；还有一张活跃表，里面有 user_id 和 active_date。要判断某个用户注册后第 N 天有没有回访，只需要算一下 active_date 和 register_date 之间差了多少天，刚好等于 N，就说明他那天回来过。
SQL 里大体可以这么写：
SELECT u.user_id, u.register_date, DATEDIFF(a.active_date, u.register_date) AS day_diff FROM user_info u LEFT JOIN user_active_daily a ON u.user_id = a.user_id然后对 day_diff 做条件判断，就能得到每个用户在第 1 天、第 3 天、第 7 天是否回访。这里有一个很容易忽略的细节：关联条件里一定要把“注册当天”排除掉，因为注册当天的事件是新增行为，不是回访。所以通常要求a.active_date > u.register_date，而不是>=。
2.3 三种常见实现写法，按场景选
我在不同团队见过三种完全不同的留存 SQL 写法，各有各的适用场景，这里放在一起对比一下。
| 写法 | 核心思路 | 优点 | 缺点 | 适用场景 |
|---|---|---|---|---|
| 多次 LEFT JOIN | 给每个留存日单独关联一次活跃表 | 逻辑非常直白，易读 | SQL 很长，多次大表关联性能差 | 数据量小，临时验证 |
| 单次 JOIN + 条件聚合 | 一次关联所有活跃日期，用 DATEDIFF 打标签再条件聚合 | 代码简洁，一次扫表 | JOIN 后行数会被放大，需要控制范围 | 中等数据量，最常用 |
| 日期序列展开 | 把每个用户注册后 0-30 天展开成多行再关联 | 灵活，能算任意日留存 | 产生大量中间行，性能最差 | 特殊分析场景，不太推荐 |
我平时用到最多的还是第二种。单次 JOIN 加条件聚合，逻辑清楚，性能也还能接受，尤其是配合后面要讲的时间窗裁剪，生产环境基本够用。
2.4 日期函数和去重：细节决定成败
日期差函数在不同数据库里差异很大，这是最坑的地方之一。MySQL 里DATEDIFF(date1, date2)返回 date1 减 date2 的天数；SQL Server 里则是DATEDIFF(day, date1, date2)，日期参数顺序完全相反；PostgreSQL 更直接，日期相减返回整数天数；Hive 里的datediff(end_date, start_date)又和 MySQL 一样。如果你的代码要在多个平台跑，把这些差异写好注释，能省下后面无数排查时间的痛苦。
另外，很多活跃表并不是唯一记录，用户一天内可能被记录多次活跃事件。如果直接拿这种表去关联，用户和活跃日期会膨胀出很多行。所以在关联之前，最好先对活跃表做一次去重，只保留(user_id, active_date)唯一组合。写留存 SQL 时，COUNT(DISTINCT user_id)几乎是必须的，千万别用COUNT(*)去代替，否则一个用户多端登录、多次活跃，直接能给你算出一个天文数字。
还有一个很容易被忽略的时区问题。如果注册时间戳是北京时间，活跃日期却是 UTC 日期，那两者直接比较可能差出 8 小时甚至一整天。我的习惯是先把所有时间统一成业务所在时区的日期，再入库计算，否则留存率在每天凌晨的几个小时里会出现莫名其妙的波动。
3. 用户群组怎么切：从属性分组到行为分组
3.1 最经典的注册日期群组（Cohort）
Cohort 分析应该是留存分析里最常用的维度。简单说，就是把同一天（或同一周、同一个月）注册的用户当成一个群组，然后看这个群组在之后各个时间点的留存变化。
注册日期群组的 SQL 实现特别简单，只要在最终查询的 GROUP BY 里加上 register_date 就行了。但这里有个隐含的好处：同一个注册日的用户，在产品上经历过完全一样的版本、活动和运营策略，相互之间是可比的。如果 6 月 8 日注册的群组 D7 留存明显比 6 月 1 日的群组低，这时候就要回头检查 6 月 1 日到 6 月 8 日之间有没有发版、有没有上线新活动、有没有投放渠道变动。
用注册日期群组还有一个好处，就是可以比较同一批用户在 D1、D3、D7、D30 的衰减轨迹。不同产品形态的衰减曲线差别很大，比如社交产品初期衰减快但留存曲线后期平缓，工具产品则可能一路阴跌。这些都是做产品决策时非常有价值的信息。
3.2 渠道、版本、地区等属性分组：GROUP BY 的坑
除了注册日期，最常被要求细分的维度就是渠道、App 版本、地区、用户来源等属性维度。这类维度通常都在用户表的字段里，直接 GROUP BY 就行。
但这里有一个很隐蔽的坑：如果某个注册渠道字段没有填充，值是 NULL，GROUP BY 会把所有 NULL 归成一组，显示成空值。这会让报表解读变得很怪。我的习惯是写 SQL 时就用COALESCE(register_channel, 'unknown')把 NULL 替换成显式的“未知”分组，这样后续给业务方展示时不会出现莫名其妙的空白行。
另外，属性分组的留存率在对比时要特别注意基期问题。比如渠道 A 6 月新增 100 人，渠道 B 6 月新增 10 万人，哪怕两个渠道 D7 留存率相同，渠道 B 的样本代表性远高于渠道 A。用留存率对比时，一定要同时把新增人数输出出来，方便业务方自己判断哪些对比是有意义的。
3.3 行为特征分组：窗口函数和条件聚合的配合
比属性分组更高级的，是按用户的行为特征来分群。比如“注册后 7 天内是否完成过首次购买”，或者“注册后 30 天内的活跃天数分档”，然后分别看这些人群的后续留存。行为特征分群的用处很大，因为它能帮助运营识别出哪些早期行为是“好用户”的信号。
SQL 里做行为分群，我的通用套路是先算出一个用户维度的行为标签，再把它关联回留存主表。比如想按“注册后 7 天内是否产生过购买”分群：
WITH user_behavior AS ( SELECT e.user_id, MAX(CASE WHEN e.event_type = 'purchase' AND DATEDIFF(e.event_date, u.register_date) <= 7 THEN 1 ELSE 0 END) AS purchased_in_7d FROM behavior_log e JOIN user_info u ON e.user_id = u.user_id GROUP BY e.user_id )这时候窗口函数也很好用。比如用ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY active_date)找出用户第一次回访的日期，或者用SUM()窗口函数算累计活跃天数。窗口函数的优势是不用反复自关联同一张表，代码可读性也更好。比如给每个用户算注册后 7 天内的活跃天数，再按活跃天数分档：
SELECT user_id, CASE WHEN active_days_7 <= 0 THEN '0_天' WHEN active_days_7 <= 3 THEN '1_3_天' WHEN active_days_7 <= 5 THEN '4_5_天' ELSE '6_7_天' END AS activity_level FROM ( SELECT u.user_id, COUNT(DISTINCT a.active_date) AS active_days_7 FROM user_info u LEFT JOIN user_active_daily a ON u.user_id = a.user_id AND a.active_date > u.register_date AND a.active_date <= DATE_ADD(u.register_date, INTERVAL 7 DAY) GROUP BY u.user_id ) t行为分群有一个需要提醒的点：这类群组是根据用户已经发生的行为划分出来的，天然存在“幸存者偏差”。比如“注册 7 天内购买过的用户留存更高”，这不一定是购买这件事带来了留存，很可能这批用户本来就是高意愿用户。做结论时别急着说因果，先说是相关关系。
3.4 多维度交叉分组：GROUP BY 的正确打开方式
有时候光看渠道不够，要看“每个渠道下不同版本的留存”。这种情况本质就是多个维度字段一起 GROUP BY。SQL 写法不复杂，就是在 GROUP BY 后面加上需要的字段。但实际上跑数的时候会发现，维度加得越多，每个分组的人数就越少，数据波动越大，最终结果的可靠性越低。
我在实际项目中倾向先跑出一个“主维度加一个辅助维度”的交叉表，而不是一次把所有维度全塞进去。比如先看渠道×注册日期，发现问题后再下钻到渠道×版本。如果团队成员有 SQL 基础，也可以试试GROUP BY GROUPING SETS，它能一次性算出多个维度组合的汇总，不过对数据引擎要求更高，普通业务库不一定支持。
4. 实操：一条 SQL 跑出群组留存矩阵
4.1 准备一套数据表，先定好口径
为了让例子更具体，我假设两张最基础的业务表。第一张是用户表user_info，包含 user_id、注册日期、注册渠道、App 版本、地区；第二张是活跃行为表user_active_daily，包含 user_id、活跃日期。活跃表的定义是：用户当天只要有过任意关键行为（启动、浏览内容、下单等），就写入一条记录。
口径上我们约定：新增用户以注册日期为准，活跃用户以 active_date 为准，D1 留存表示注册后第 1 个自然日有活跃行为的用户占新增用户的比例。下面这段 SQL 是“直白版”，适合小数据量、临时验证：
SELECT u.register_date AS cohort_date, u.register_channel, COUNT(DISTINCT u.user_id) AS new_users, COUNT(DISTINCT IF(DATEDIFF(a.active_date, u.register_date) = 1, u.user_id, NULL)) AS d1_retained, COUNT(DISTINCT IF(DATEDIFF(a.active_date, u.register_date) = 3, u.user_id, NULL)) AS d3_retained, COUNT(DISTINCT IF(DATEDIFF(a.active_date, u.register_date) = 7, u.user_id, NULL)) AS d7_retained, COUNT(DISTINCT IF(DATEDIFF(a.active_date, u.register_date) = 30, u.user_id, NULL)) AS d30_retained FROM user_info u LEFT JOIN user_active_daily a ON u.user_id = a.user_id AND a.active_date > u.register_date AND a.active_date <= DATE_ADD(u.register_date, INTERVAL 30 DAY) WHERE u.register_date BETWEEN '2024-06-01' AND '2024-06-30' GROUP BY u.register_date, u.register_channel ORDER BY u.register_date, u.register_channel这段 SQL 的逻辑就是一个 LEFT JOIN 加条件聚合。COUNT(DISTINCT IF(...))的意思是：如果活跃日期和注册日期相差正好 N 天，就计数一次，否则忽略。NULL 用户不会被计入，因为 IF 的条件不满足时返回 NULL，COUNT不会统计 NULL。
4.2 生产环境的优化版：先缩范围再关联
上面这段直白版 SQL，一旦数据量大就会出问题。活跃表可能有几十亿行，直接和用户表做 JOIN，扫描范围太宽，跑起来又慢又费资源。我生产环境里一般会把活跃表先圈定在“注册日期 + 留存观察窗口”范围内，并且先用 DISTINCT 去掉重复的活跃记录。
WITH cohort AS ( SELECT user_id, register_date, register_channel FROM user_info WHERE register_date BETWEEN '2024-06-01' AND '2024-06-30' ), active AS ( SELECT DISTINCT user_id, active_date FROM user_active_daily WHERE active_date BETWEEN '2024-06-01' AND '2024-07-30' ) SELECT c.register_date AS cohort_date, c.register_channel, COUNT(DISTINCT c.user_id) AS new_users, COUNT(DISTINCT IF(DATEDIFF(a.active_date, c.register_date) = 1, c.user_id, NULL)) AS d1_retained, COUNT(DISTINCT IF(DATEDIFF(a.active_date, c.register_date) = 3, c.user_id, NULL)) AS d3_retained, COUNT(DISTINCT IF(DATEDIFF(a.active_date, c.register_date) = 7, c.user_id, NULL)) AS d7_retained, COUNT(DISTINCT IF(DATEDIFF(a.active_date, c.register_date) = 30, c.user_id, NULL)) AS d30_retained FROM cohort c LEFT JOIN active a ON c.user_id = a.user_id AND a.active_date > c.register_date AND a.active_date <= DATE_ADD(c.register_date, INTERVAL 30 DAY) GROUP BY c.register_date, c.register_channel ORDER BY c.register_date, c.register_channel这种写法有两个核心优化。第一，CTE 先把数据范围卡死，活跃表不需要全表扫描；第二，DISTINCT去重保证了后面 JOIN 不会出现一行用户对应多行重复活跃记录的情况。如果你的活跃表本身就是按(user_id, active_date)去重的，这个 DISTINCT 也可以省掉，能省一些开销。
4.3 结果怎么解读，怎么和业务方讲
跑完 SQL 之后，你会得到一张类似这样结构的结果表：
| 注册日期 | 渠道 | 新增用户 | D1 留存 | D3 留存 | D7 留存 | D30 留存 |
|---|---|---|---|---|---|---|
| 2024-06-01 | 自然搜索 | 8234 | 36.2% | 22.1% | 15.8% | 8.3% |
| 2024-06-01 | 信息流广告 | 15230 | 28.7% | 16.4% | 10.2% | 5.1% |
| 2024-06-02 | 自然搜索 | 7911 | 35.8% | 21.6% | 14.9% | 7.9% |
这里要提醒一个汇报技巧：给业务方看的时候，别只放留存率百分比，一定要把“新增用户数”放旁边。新增用户基数小到一定程度后，留存率的置信度就很低了。比如某个渠道当天新增只有 50 个人，留存率从 10% 跳到 20% 很可能只是一个人回访带来的波动，不值得过度解读。
另外，不要把单日的数据拉出来单独判断，至少要拉一周的趋势，看连续几天的变化方向。如果某一天突然掉得很厉害，多半是数据缺失或者上线了什么特殊情况，需要先排查，而不是立刻下结论说“产品变差了”。
5. 我踩过的那些坑：去重、性能与异常排查
5.1 数据口径的三个高频坑
留存 SQL 写多了以后，我发现最容易翻车的地方往往不是 SQL 语法，而是数据口径。第一个坑是去重。活跃表里同一用户一天可能有很多条行为记录，如果不先按(user_id, active_date)去重，JOIN 之后行数膨胀，再用COUNT(DISTINCT user_id)还能兜住结果，但跑数会非常慢；如果图省事用COUNT(*)，数字直接翻几倍。
第二个坑是账号和设备的口径。很多产品的用户体系允许同一个人在不同设备上登录，也有同一台设备登录过多个账号的情况。如果你今天用 user_id 算，明天用 device_id 算，两个数根本对不上。我一般会在数仓建模阶段就明确：业务核心指标以 user_id 为准，设备维度只是辅助参考。
第三个坑是时间字段的类型。有的表里存的是 datetime，有的存的是 date，直接相减或者 DATEDIFF 的时候容易懵。我建议在写留存 SQL 之前，先SELECT出来看一眼字段样例，确认到底有没有带时分秒、带的是哪个时区。这个习惯能帮你省掉不少排查时间。
5.2 慢 SQL 的根源与优化思路
数据量一旦上亿，留存 SQL 慢的问题就躲不掉了。最常见的根源有三类：一是 JOIN 放大，用户表关联活跃表后，每个用户对应了未来 30 天的所有活跃记录，行数可能膨胀到原来的几十倍；二是COUNT(DISTINCT ...)本身在大数据量下就很吃性能；三是没有做分区裁剪，每次都全表扫描。
针对这些问题，我的优化思路是这样的。第一，能先缩范围就先缩范围，活跃表只保留注册时间之后 30 天内的数据，别把历史所有活跃数据都扫一遍。第二，如果每天都要跑留存报表，不要每次都从原始活跃表DISTINCT，而是先建一张按日汇总的中间表，把(user_id, active_date)去重后的结果落在数仓里，查询直接基于中间表跑。第三，如果数据量实在太大，可以用近似去重函数来评估留存，比如 Hive 里的approx_count_distinct，误差在可接受范围内，性能和资源消耗能大幅下降。
还有一个小经验：如果查询里同时要算多个留存指标，尽量一次 JOIN 完成，别在原始表上反复 JOIN 操作，不然资源消耗会成倍增长。
5.3 留存率突然掉下来怎么办：维度下钻法
留存率出现异常波动时，第一反应别是“产品出 bug 了”。我习惯用维度下钻法一步步缩小范围。第一步看整体趋势，确认是单日波动还是连续多天下跌；第二步拆渠道，看是不是某个渠道新增量突然放大或缩小导致整体结构变化；第三步拆版本，看是不是最近发版引入了体验问题；第四步拆新老用户、拆地区，交叉定位问题到底出在哪一批人身上。
举个例子，我之前遇到过一次留存率连续三天走低，大家都以为是新版本的问题。结果拆下来发现，是某个投放渠道在几天前突然放量，带来了一批活跃度非常低的新增用户，拉低了整体数值。如果只看全局留存曲线，可能会被误导到错误的方向上。这种时候，群组拆解的价值就完全体现出来了。
6. 最后分享我的一点小习惯
做留存分析这几年，我慢慢养成了一些固定习惯。比如每次写留存 SQL 之前，一定先把口径用一句话写在注释里；比如结果表里永远保留新增用户数，不只看百分比；再比如任何异常波动，先怀疑数据，再怀疑产品，最后才下业务结论。
还有一个很实用的习惯：把常用的留存查询固化成一个带参数的模板，以后不管是看新渠道效果还是评估新版本影响，只要替换时间和分组维度就能快速跑出来。用 SQL 做用户群组留存分析这件事，说难不难，说简单也不简单，只要把口径、表结构、SQL 套路这三件事想明白，基本就能应对工作中绝大部分留存分析需求了。
