1. 留存率这指标为什么拆开看才有意义做用户分析的同学一定听过这句话“留存是产品的生命线。”可真到自己动手算的时候很多人第一反应是拉一张活跃用户表数一数某天登录过的用户里一周后还有多少人回来一个百分比就出来了。这个数字没错但它只有一个。实际业务里运营会问“上周买过东西的用户这周还买吗”市场会问“抖音投流来的用户和自然注册的用户哪个留得更久”产品会问“用了新功能的人群留存是不是真的比没用的人高”。这些问题的共同点是只看总体留存不够必须按用户群组拆开看这就是标题里“不同用户群组留存率”的核心场景。我第一次做留存分析也是从一张宽表开始的表里有用户ID、注册时间、最后活跃时间。我天真地想直接算注册那天到现在的间隔再判断用户有没有在某个时间窗口内活跃。结果表里三十万用户最后算出来的留存率完全经不起推敲——因为“最后活跃时间”根本不能用来推断“第3天是否活跃”用户可能第10天回了第3天偏偏没来。后来我才意识到留存分析必须回到事件明细用SQL按天去匹配“每个用户是否在指定的一天出现过”而不是依赖任何冗余字段。这篇内容适合谁看大概是两类人一类是刚接触SQL数据分析、想系统搞懂留存计算逻辑的初学者另一类是已经从Excel迁到SQL、但一直觉得自己的留存SQL写得不够稳、想看看有没有更好写法的分析师。先说结论用SQL算留存最核心的不是会背几个窗口函数而是想清楚“留存分母是哪群用户”“留存窗口怎么定义”“活跃怎么去重”。这三件事想清楚了SQL本身只是翻译。接下来的内容围绕一个模拟场景展开——某App想分析“不同注册渠道的用户在注册后第1天、第3天、第7天、第30天的留存差异”——我会从数据准备一直写到性能优化全程给出可执行的SQL。2. 留存SQL的地基先搞清底表长什么样很多教程一上来就甩出几个JOIN但你照着抄完连数据都套不进去因为表结构不一样。留存分析最少需要两张表一张描述用户是谁一张描述用户做了什么这两张表在SQL里的角色完全不同。2.1 用户维表定义“群组”的出发点用户维表通常叫users或dim_user核心字段像这样user_id用户唯一标识字符串或数字register_time注册时间精确到秒channel注册渠道比如“抖音投流”“自然搜索”“老带新”“应用商店”device_type设备类型比如iOS/Android在这个场景里“不同用户群组”指的就是channel字段或者你也可以换成地域、活动批次、是否付费用户逻辑一样。用户维表是分母的来源——先圈定一群人再追踪他们后续的行为。提示如果你们公司没有一张现成的用户维表也别慌。可以按业务自己定义“成为用户”的事件比如完成注册、首次下单、首次充值。关键是这个时间点必须能从事件表里取出来并且全公司口径一致。口径不一致是所有留存SQL跑偏的头号原因。2.2 活跃事实表留存的判定依据第二张表是行为事件表通常叫event_log每行代表用户的一次行为。留存分析最少需要三个字段user_id用户ID和维表关联用event_name事件名比如“app_open”“login”“purchase”留存通常关心“登录/open”这类代表用户回来的事件event_time事件发生时间精确到秒一个用户一天可能打开App十几次在留存计算里通常只需要知道他“这天来没来”不需要知道他“来了几次”。所以第一步一般先把细粒度事件转换成“用户-活跃日期”的去重表这也是后续所有留存计算的公共底表。-- 活跃日表每个用户每天的活跃日期去重后输出 SELECT user_id, DATE(event_time) AS active_date FROM event_log WHERE event_name app_open GROUP BY user_id, DATE(event_time)这个GROUP BY就是最朴素的去重方式比COUNT(DISTINCT)再和原表JOIN要高效得多。做完这一步原表可能每天几千万行压缩成几百万行后续计算量小一个量级。2.3 时间口径注册日和自然日别混着用留存SQL里的时间维度有两条线一条是“注册日期”或“成为用户日期”叫群组日期一条是“活跃日期”用来判断用户在第N天是否回来。这两条线必须严格对齐同一个时区否则很容易出现“用户在注册后第2天活跃但按UTC时间算成了第1天”这种偏差。国内产品一般按北京时间自然日处理注册时间和活跃时间都取DATE()后用于JOIN。如果公司数据存在不同时区建议统一在SQL里显式转时区不要依赖数据库默认设置。从这两张表出发就能开始写第一版留存的SQL了。3. 第一版可用的留存SQL按注册日逐日计算我最早写的留存SQL长这样把用户维表按注册日聚合出分母再把活跃日表按日期聚合出活跃次数然后手动算每个窗口。这写法最大的毛病是重复劳动——每加一个留存窗口就要多写一段逻辑而且很容易算错“第3天”到底是哪天因为日期偏移写错一位是常有的事。后来我改成了一版用LEFT JOIN 条件聚合一口气算完所有窗口的写法。下面给的是一个简化示例场景是业务要求看每个注册日的“第1天、第3天、第7天、第30天留存”。-- 用户维表底数注册日 渠道 WITH user_base AS ( SELECT user_id, DATE(register_time) AS reg_date, channel FROM users WHERE DATE(register_time) BETWEEN 2025-01-01 AND 2025-01-31 ), -- 活跃日表提前去重避免产生重复计数 active AS ( SELECT user_id, DATE(event_time) AS active_date FROM event_log WHERE event_name app_open AND DATE(event_time) BETWEEN 2025-01-01 AND 2025-03-02 GROUP BY user_id, DATE(event_time) ) SELECT u.reg_date, COUNT(DISTINCT u.user_id) AS reg_cnt, COUNT(DISTINCT IF(a.active_date DATE_ADD(u.reg_date, INTERVAL 1 DAY), u.user_id, NULL)) / COUNT(DISTINCT u.user_id) AS day1_rate, COUNT(DISTINCT IF(a.active_date DATE_ADD(u.reg_date, INTERVAL 3 DAY), u.user_id, NULL)) / COUNT(DISTINCT u.user_id) AS day3_rate, COUNT(DISTINCT IF(a.active_date DATE_ADD(u.reg_date, INTERVAL 7 DAY), u.user_id, NULL)) / COUNT(DISTINCT u.user_id) AS day7_rate, COUNT(DISTINCT IF(a.active_date DATE_ADD(u.reg_date, INTERVAL 30 DAY), u.user_id, NULL)) / COUNT(DISTINCT u.user_id) AS day30_rate FROM user_base u LEFT JOIN active a ON u.user_id a.user_id AND a.active_date BETWEEN DATE_ADD(u.reg_date, INTERVAL 1 DAY) AND DATE_ADD(u.reg_date, INTERVAL 30 DAY) GROUP BY u.reg_date ORDER BY u.reg_date这版SQL的要点在三个地方。第一LEFT JOIN的目的不是拿active表去“关联出更多的用户”而是给每个注册用户匹配上“他之后30天内的活跃日期”。所以JOIN条件里同时带上了user_id和日期窗口这样能大幅减少JOIN过程中的中间行数。如果你不在JOIN条件里限定日期范围SQL会先生成一张“每个用户×他全部活跃日期”的笛卡尔集数据量直接膨胀几十倍。第二IF条件聚合的方式把多个留存窗口写在了同一层查询里避免了拆成多个子查询再JOIN的麻烦。IF(a.active_date DATE_ADD(u.reg_date, INTERVAL 1 DAY), u.user_id, NULL)的意思是如果活跃日期正好等于注册日1天就返回这个用户ID否则返回NULL然后用COUNT(DISTINCT)只统计非NULL的用户数。COUNT(DISTINCT)很重要因为用户可能在同一天有多次活跃——虽然active表已经按天去重了但保险起见这一层还是要保留DISTINCT。第三日期偏移统一用DATE_ADD函数注册日和活跃日都先用DATE()格式化成日期而不是用时间戳直接相减。直接用时间戳相减再除以86400很容易踩到“时间戳是整数、除法得到小数日期边界算不清”的坑。用DATE_ADD最直观也不容易出错。注意如果你用的SQL方言是SQL ServerDATE_ADD要换成DATEADD(DAY, 1, reg_date)如果是Hive或SparkSQL写法是DATE_ADD(reg_date, 1)PostgreSQL是reg_date INTERVAL 1 day。语法各有差异但思路完全一样。但是这版SQL跑出来的结果只是一个“按注册日”维度的留存距离“不同用户群组”还差一步。想按渠道拆开看需要把channel字段加进GROUP BY。4. 把不同群组做进留存计算从宽行到长表的进阶写法上面那版SQL如果把GROUP BY从reg_date改成reg_date, channel就能直接得到“某注册日、某渠道”的留存率。这样改确实可行但有两个问题一是输出结果每一行是“一个群组一个注册日多个留存率”字段是横向的不同渠道之间的对比要靠Excel透视再做一次不够直观。二是如果群组维度不止一个比如既想看渠道又想看新老用户还想看设备类型GROUP BY的列就得越加越多SQL越来越臃肿。更工程化的做法是写一个“长表”输出——保留一个日期字段再把活跃日期和注册日期做差得到“第几天”然后一行一行地输出某用户属于哪个群组在第几天是否活跃。这样下游无论做报表还是做分析都不需要再改SQL。-- 先把维度信息并入用户底表形成一个带标签的用户集合 WITH tagged_users AS ( SELECT u.user_id, DATE(u.register_time) AS reg_date, u.channel, CASE WHEN DATE(u.register_time) 2025-01-15 THEN 上旬注册 ELSE 下旬注册 END AS reg_period FROM users u WHERE DATE(u.register_time) BETWEEN 2025-01-01 AND 2025-01-31 ), active AS ( SELECT user_id, DATE(event_time) AS active_date FROM event_log WHERE event_name app_open GROUP BY user_id, DATE(event_time) ), -- 核心把所有注册用户和活跃日期关联计算“间隔天数” retention_long AS ( SELECT t.user_id, t.channel, t.reg_period, t.reg_date, DATEDIFF(a.active_date, t.reg_date) AS diff_day, a.active_date FROM tagged_users t LEFT JOIN active a ON t.user_id a.user_id AND a.active_date t.reg_date AND a.active_date DATE_ADD(t.reg_date, INTERVAL 30 DAY) ) SELECT channel, reg_period, diff_day, COUNT(DISTINCT user_id) AS active_users FROM retention_long WHERE diff_day IN (1, 3, 7, 30) GROUP BY channel, reg_period, diff_day ORDER BY channel, reg_period, diff_day这段SQL的输出结果长这样channelreg_perioddiff_dayactive_users应用商店上旬注册13200应用商店上旬注册32150应用商店上旬注册71450抖音投流上旬注册15800抖音投流上旬注册33100抖音投流上旬注册71200这个长表的好处是每一行只描述“某个群组、某个间隔天数的独立活跃人数”想做留存率时只需要把这个表拿去和用户维表的分母表JOIN一次或者直接在BI工具里做计算。如果将来想新增一个维度比如按照“是否付费”分组只需要在tagged_users里多加一个字段后面所有SQL一行都不用改。DATEDIFF函数在MySQL里返回的是日期差的天数在SQL Server里同样有DATEDIFF(DAY, start_date, end_date)在Hive里也可以直接用。如果是在Hive/SparkSQL里处理超大规模数据要注意DATEDIFF在部分版本里要求参数是STRING类型建议先CAST一下。这版SQL比第一版更接近“保留分析”的本质先把每个用户抽象成“间隔天数→是否活跃”的二进制记录再按任何维度做聚合。这是一种从行模型思维转成“事件流匹配”思维的进阶写完之后你会发现在很多分析场景都能复用——不只是留存回访间隔、复购周期、活跃频次都可以用类似逻辑拆。提示长表适用于下游还需要做分组聚合、多维度分析或者可视化的情况。如果只是临时看一眼结果第一版的宽表写法更快不用过度设计。5. 我在真实留存数据上踩过的四个坑写留存SQL跑通不是终点跑对才是。下面几个坑我每个都亲自踩过有些甚至是在线上报表已经跑了一个月之后才发现的。列出来给大家避雷。5.1 计数膨胀JOIN之后出现了幽灵用户这是最常见的坑。假设用户A在注册后第3天活跃了两次如果直接用event_log和users表做JOIN不先去重那么用户A会在结果里出现两行。再用COUNT(DISTINCT user_id)还好但如果团队里有同事图省事写成COUNT(*)留存率直接翻倍看着数据“挺好”其实是错的。我的习惯是凡是涉及留存计算event_log必须先按“user_id 活跃日期”做一步GROUP BY去重。别把去重寄托在最终的COUNT(DISTINCT)上因为中间过程可能产生大量重复行导致SQL运行时间暴增。5.2 活跃窗口截断新用户数据还没跑完就下了结论看留存有个时间问题今天注册的用户没法算第30天留存因为第30天还没到。很多人图省事直接把所有注册用户放在一起算结果最新一批注册用户的7日、30日留存全是0整体留存率被严重拉低。更隐蔽的问题是不同注册日的用户可观测窗口不同——月初注册的能看到30天月末注册的只能看到几天。正确的做法是注册日期必须限制在“今天-最大留存窗口”之前。比如想算30日留存注册日期最多取今天往前推30天也就是WHERE DATE(register_time) DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)。这样每个注册日都有完整的时间窗来观察留存算出来的数才是可比的。5.3 时区和凌晨活跃把“第1天”算成了“第0天”很多App的活跃高峰在晚上10点到凌晨1点。如果注册时间是北京时间晚上11点活跃表中有一条记录是凌晨0点10分那这条记录在自然日口径里属于“注册日的第二天”但实际上离注册才过去一小时。如果你拿“自然日相减”算间隔会把这个用户判为“第1天活跃”。这其实没错但它衡量的是“日活跃”而不是“注册后满24h是否活跃”。在产品早期团队必须先定清楚我们要的留存是“自然日留存”用户在第N个自然日是否有行为还是“精确间隔留存”注册后N×24小时是否有行为。两种口径对应的SQL不一样自然日留存用DATE_ADD对日期做偏移精确间隔留存用时间戳比较。经验绝大多数运营场景用自然日留存就够了因为大家习惯按天看报表。但如果你在验证“某个推送之后几小时用户是否回来”就要考虑精确间隔口径。最怕的是报表一直用自然日口径某天忽然有人拿它当精确间隔用结论必然偏乐观。5.4 事件名过滤用“登录”还是用“任意行为”留存里最常见的定义分歧是“什么叫活跃”。有人用app_open有人用login有人用任意埋点事件。严格来说app_open是最宽泛的活跃定义只要App被打开就算login要求用户通过账号体系登录任意事件的定义则波动很大因为可能有大量自动触发的后台事件比如“页面曝光”这种用户根本没主动操作也会产生。我建议是主留存指标用“用户显式启动或登录事件”避免用后台自动上报事件。如果事件表里混入了自动化行为留存率会虚高而且越是低活跃用户虚高越明显——因为他们在后台被自动上报“活跃”了实际上人根本没打开App。6. 数据量上来了留存SQL别硬跑上面的SQL在百万级用户、千万级事件量下能跑得动但到了亿级事件量同样的写法能把你数据库拖到报警。留存SQL优化核心就几个方向。6.1 先压事件表体积再做长表关联不要直接把event_log全表JOIN users这个操作在任何数据库里都是噩梦。应该先按“user_id 活跃日期”GROUP BY把事件表压缩成活跃日表再用活跃日表和用户表JOIN。压完之后数据量往往是原来的几十分之一JOIN成本断崖式下降。如果是Hive或SparkSQL还可以把active表做成“分桶表”按user_id分桶这样JOIN的时候可以走桶内匹配而不是全表扫描。6.2 只计算你需要的窗口业务只需要31天留存就没必要让JOIN把你从注册日开始的180天活跃数据全关联进来。JOIN条件里加上日期窗口过滤就等于告诉数据库引擎“我只关心注册后30天内的活跃记录”扫描的数据范围瞬间缩小。有些数据库对这种“时间窗口内的JOIN”支持不好那也可以退一步先按条件过滤出活跃日表到指定日期范围再JOIN。6.3 结果物化留存报表不是一次性查询运营要看的留存数据一般不需要实时计算。比如“昨日注册用户截至目前第1天留存”这个结果在次日凌晨就算出来了不会变。所以最靠谱的做法是用定时任务每天凌晨跑一次留存SQL把结果写入一张留存报表表BI系统每天早上读这张表。这样查询再复杂也只影响凌晨那几分钟的调度不会影响主库性能。增量更新的逻辑一般是每天只处理“当天注册的用户”老用户不再重新计算因为他们在更早的天数里已经被记录过了。只有“第30天留存”这类长窗口指标才需要每天回头补算“30天前注册的用户是否在第30天活跃了”。6.4 不要滥用窗口函数有些教程会推荐用LEAD/LAG窗口函数找“每个用户相邻两次活跃之间的天数”然后统计这些天数分布。这种做法不是不能用但它解决的是“回访间隔分布”问题不是标准留存问题。标准留存是“给定起点集合在固定时间点看活跃比例”用窗口函数反而绕远了还容易在大表上产生高耗能的排序操作。建议先把GROUP BY去重、日期偏移JOIN、条件聚合这三板斧练熟再用窗口函数做锦上添花的事。SQL里最值钱的能力是“知道什么时候该用什么算子”而不是背函数列表。7. 实操建议从0搭一个留存分析小流程如果你现在手里正好有一份用户表和一份事件表想快速搭出一个留存分析流程我的建议是分三步走。第一步把用户维表和活跃日表抽出来各写一条SQL确认行数和预期一致。第二步按第4节的长表写法生成一个“用户-间隔天数-是否活跃”的中间结果表。第三步用一张汇总表把结果按“渠道、注册日、间隔天数”聚合起来然后交给BI或Excel做透视。如果是在MySQL环境里强烈建议把活跃日表先建成临时表或物化表加上索引(user_id, active_date)。这一步看似多花了存储和一次写入时间但后续所有留存查询都会快很多。一条SQL里连续跑多个CTE性能未必比得上一次“先落临时表再查询”的传统写法——CTE在部分MySQL旧版本里不做物化每次引用都可能重跑一遍。我最后还想分享一个小技巧写留存SQL时先在最后加一个LIMIT 20用肉眼检查原始明细数据对不对再取掉LIMIT跑全量。我就是靠着这个习惯才在各种“看似正确、实则口径错误”的边缘救回过报表。没有太多的玄学留存分析这件事70%的功夫花在口径定义和数据去重上剩下30%才是SQL技巧。如果你想认真做用户增长建议把这套SQL沉淀成一个模板后续换指标、换维度都能复用会比每次从头写快得多。
