上周运营负责人又来找我张口就问“最近新用户留存是不是掉了你帮我看下按渠道拆的留存到底哪个渠道质量不行。”这种需求我接过无数回看起来一句话能讲清楚可真要落到 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 最经典的注册日期群组CohortCohort 分析应该是留存分析里最常用的维度。简单说就是把同一天或同一周、同一个月注册的用户当成一个群组然后看这个群组在之后各个时间点的留存变化。注册日期群组的 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 就行。但这里有一个很隐蔽的坑如果某个注册渠道字段没有填充值是 NULLGROUP 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 的条件不满足时返回 NULLCOUNT不会统计 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自然搜索823436.2%22.1%15.8%8.3%2024-06-01信息流广告1523028.7%16.4%10.2%5.1%2024-06-02自然搜索791135.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 套路这三件事想明白基本就能应对工作中绝大部分留存分析需求了。