留存率SQL怎么写？从事件明细到7日留存

留存率是用户行为分析里最容易「算错但没人发现」的指标。所谓留存率指的是某一批新增用户在后续指定时间窗内仍然回来使用产品的比例它衡量的不是来了多少人而是来了之后有多少人留下来。同一个事件表三个人能写出三个口径的留存数产品、运营、数据团队开会时各说各话。问题往往出在字段口径和去重键没对齐。下面从事件明细出发把次日留存、7日留存的写法讲清楚包括除零、空值和日期对齐这些容易踩的坑。留存率到底在算什么先把口径定死为什么口径比SQL语法更先出问题很多人上来就写SQL写完发现数字和后台对不上根因是一开始没定义清楚三件事谁算「新增」、什么行为算「回来」、时间窗怎么切。次日留存的标准定义是某天首次使用产品的一批用户在第二天还回来使用的比例。这里「回来」的判定不同团队差异很大——有的要求触发任意事件有的要到达核心页面也有的要求完成一次交易。在做App留存分析时口径不一致比SQL写错更致命因为它会让历史数据失去可比性。四个口径项怎么选口径项常见写法A常见写法B建议新增用户设备首次出现日期注册账号日期未登录阶段用设备ID登录后合并账号ID回来判定任意事件上报核心事件如进入首页与业务对齐全量留存核心留存各算一份时间窗自然日0点切割满24小时为一天报表默认自然日保留一种写死去重键user_iddevice_id登录后用user_id未登录用device_id实际上多数分析平台在报表层已经把这些口径封装好了。以456数据为例App分析里有「活跃与留存→留存分析」基础版起多维分析的留存模型也支持按条件拆分。但当需要和业务自定义事件对齐、或者要把留存接到内部数仓做联合分析时自己写SQL仍是绕不开的一步。不同数据库对 DENSE_RANK、DATEDIFF 的具体支持差异建议以各自官方文档为准。事件表结构写SQL之前先看有什么字段最小字段集留存计算的最小事件表只需要三个字段用户标识、事件时间、事件名。下面这个表结构是演示用的骨架真实业务表里还会带渠道、版本、页面等维度。-- 示例事件表 event_log演示结构非真实数据 -------------------------------------------- | user_id | event_time | event_name | -------------------------------------------- | u001 | 2026-10-20 08:12:03 | page_view | | u002 | 2026-10-20 09:30:44 | app_launch | | u001 | 2026-10-21 19:02:11 | page_view | --------------------------------------------为什么用窗口函数而不是自连接关键动作是把「每个用户第一次出现的日期」找出来再判断这批人后续日期有没有回来。这里用窗口函数 dense_rank 比自连接更直观也不容易在多版本SQL引擎里出兼容问题。次日留存用窗口函数圈新增再判回访第一步圈出每个用户的新增日WITH first_visit AS ( SELECT user_id, MIN(DATE(event_time)) AS dt FROM event_log GROUP BY user_id ) SELECT * FROM first_visit;第二步连接原表判断次日是否活跃这一步得到的是每个用户的「新增日」。第二步把首次访问表和原事件表按用户ID连接判断回访日期是否等于新增日加一天WITH first_visit AS ( SELECT user_id, MIN(DATE(event_time)) AS first_dt FROM event_log GROUP BY user_id ), retained AS ( SELECT f.first_dt AS cohort_dt, COUNT(DISTINCT f.user_id) AS new_users, COUNT(DISTINCT CASE WHEN EXISTS ( SELECT 1 FROM event_log e WHERE e.user_id f.user_id AND DATE(e.event_time) DATE_ADD(f.first_dt, INTERVAL 1 DAY) ) THEN f.user_id END ) AS retained_next_day FROM first_visit f GROUP BY f.first_dt ) SELECT cohort_dt, new_users, retained_next_day, ROUND( retained_next_day * 100.0 / NULLIF(new_users, 0), 2 ) AS next_day_retention_pct FROM retained ORDER BY cohort_dt;两个不能省的细节这里有两个细节容易被忽略。一是 NULLIF(new_users,0)某天如果没有新增用户分母为零会直接报错用 NULLIF 把它转成 NULL结果显示空值而不是整句SQL崩掉。二是 COUNT(DISTINCT …) 不能省同一个用户第二天可能触发多个事件不去重会把回访人数算大。经验值非硬性标准次日留存因品类差异很大工具类App通常高于内容类电商类偏低讨论时先对齐品类再比数字。7日留存不要写七段自连接为什么多段自连接会失控很多人写7日留存的第一反应是次日连一次、3日连一次、7日连一次写七段。这种写法在数据量小的时候能跑但SQL会很长而且每加一个时间窗就要改一遍。更稳妥的做法是一次算出「每个新增用户在第几天回来了」再透视成宽表。一次算出 day_n再透视WITH first_visit AS ( SELECT user_id, MIN(DATE(event_time)) AS first_dt FROM event_log GROUP BY user_id ), days_active AS ( SELECT f.user_id, f.first_dt, DATEDIFF(DATE(e.event_time), f.first_dt) AS day_n FROM first_visit f JOIN event_log e ON e.user_id f.user_id WHERE DATEDIFF(DATE(e.event_time), f.first_dt) BETWEEN 0 AND 30 ) SELECT first_dt AS cohort_dt, COUNT(DISTINCT user_id) AS new_users, ROUND(COUNT(DISTINCT CASE WHEN day_n 1 THEN user_id END)*100.0 / NULLIF(COUNT(DISTINCT user_id),0), 2) AS d1, ROUND(COUNT(DISTINCT CASE WHEN day_n 3 THEN user_id END)*100.0 / NULLIF(COUNT(DISTINCT user_id),0), 2) AS d3, ROUND(COUNT(DISTINCT CASE WHEN day_n 7 THEN user_id END)*100.0 / NULLIF(COUNT(DISTINCT user_id),0), 2) AS d7 FROM days_active GROUP BY first_dt ORDER BY first_dt;这种写法的好处是 day_n 一次算全要加14日、30日留存只多一行 CASE不用再写新的连接。需要注意 DATEDIFF 在不同引擎里函数名不一样MySQL 是 DATEDIFF(end,start)Hive 是 datediffPostgreSQL 直接用减号相减迁移时要换。生产环境迁移前通常建议在测试库跑一遍边界用例。写法优点缺点适用场景多段自连接逻辑直观SQL长、加窗要改只算次日留存窗口函数一次算day_n易扩展、性能好需要理解CTE多时间窗留存平台现成看板零代码、自动去重自定义程度有限日常监控几个反复出现的坑时区切错会让日期整体平移第一个坑是时区。事件时间如果存的是UTC按自然日切割会把北京时间早上8点前的数据算到前一天跨时区团队对接时尤其明显。方案是在入库时统一存业务时区的时间戳或者在SQL里 CONVERT_TZ 一次。启动事件不等于留存第二个坑是把「启动事件」当留存。有些团队只统计 app_launch用户更新版本后冷启动会被算成留存但这个动作本身没有业务价值。更合理的做法是把「核心行为留存」单独算一列和全量留存并列看。分母漂移时大盘会骗人第三个坑是分母漂移。某天发版后新增用户结构变了比如投了低价渠道留存率掉了未必是产品问题可能是新增质量变了。这时候要按渠道拆留存而不是只看大盘。对没有专职数据团队的产品来说用现成分析平台的留存模块如456数据App分析里的「活跃与留存→留存分析」能省掉自己维护SQL的成本如果团队已经有成熟的数据仓库自己维护这套SQL在用户行为分析这类自定义场景下会更灵活。两种路径各有取舍关键是把口径写进文档不要让每个新人重新猜一遍。你们团队卡在哪一段你们团队算留存时最常被纠结的是哪一段——新增圈定、回来判定还是时间窗切割欢迎在评论区聊聊。