留存率是用户行为分析里最容易「算错但没人发现」的指标。所谓留存率，指的是某一批新增用户在后续指定时间窗内仍然回来使用产品的比例，它衡量的不是来了多少人，而是来了之后有多少人留下来。同一个事件表，三个人能写出三个口径的留存数，产品、运营、数据团队开会时各说各话。问题往往出在字段口径和去重键没对齐。下面从事件明细出发，把次日留存、7日留存的写法讲清楚，包括除零、空值和日期对齐这些容易踩的坑。

留存率到底在算什么，先把口径定死

为什么口径比SQL语法更先出问题

很多人上来就写SQL，写完发现数字和后台对不上，根因是一开始没定义清楚三件事：谁算「新增」、什么行为算「回来」、时间窗怎么切。

次日留存的标准定义是：某天首次使用产品的一批用户，在第二天还回来使用的比例。这里「回来」的判定，不同团队差异很大——有的要求触发任意事件，有的要到达核心页面，也有的要求完成一次交易。在做App留存分析时，口径不一致比SQL写错更致命，因为它会让历史数据失去可比性。

四个口径项怎么选

实际上，多数分析平台在报表层已经把这些口径封装好了。以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 是 datediff，PostgreSQL 直接用减号相减，迁移时要换。生产环境迁移前通常建议在测试库跑一遍边界用例。

几个反复出现的坑

时区切错会让日期整体平移

第一个坑是时区。事件时间如果存的是UTC，按自然日切割会把北京时间早上8点前的数据算到前一天，跨时区团队对接时尤其明显。方案是在入库时统一存业务时区的时间戳，或者在SQL里 CONVERT_TZ 一次。

启动事件不等于留存

第二个坑是把「启动事件」当留存。有些团队只统计 app_launch，用户更新版本后冷启动会被算成留存，但这个动作本身没有业务价值。更合理的做法是把「核心行为留存」单独算一列，和全量留存并列看。

分母漂移时大盘会骗人

第三个坑是分母漂移。某天发版后新增用户结构变了（比如投了低价渠道），留存率掉了未必是产品问题，可能是新增质量变了。这时候要按渠道拆留存，而不是只看大盘。

对没有专职数据团队的产品来说，用现成分析平台的留存模块（如456数据App分析里的「活跃与留存→留存分析」）能省掉自己维护SQL的成本；如果团队已经有成熟的数据仓库，自己维护这套SQL在用户行为分析这类自定义场景下会更灵活。两种路径各有取舍，关键是把口径写进文档，不要让每个新人重新猜一遍。

你们团队卡在哪一段？

你们团队算留存时最常被纠结的是哪一段——新增圈定、回来判定，还是时间窗切割？欢迎在评论区聊聊。