次日留存率怎么算？7 日留存？30 日留存？

考察要点

这道题面试官想看你是否懂：

1. 时间序列数据处理：如何精确地从 DATETIME 或 TIMESTAMP 类型中提取日期，并进行日期间隔计算（如 DATE_ADD, DATEDIFF）。
2. 集合关联与比较：如何找出两个用户集合（某日活跃用户 vs N日后活跃用户）的交集，核心是 JOIN 或子查询的运用。
3. SQL 逻辑构建能力：能否将“留存率”这个业务指标，清晰地拆解为 SQL 查询的各个步骤（定义初始用户群、定义留存用户群、计算比例），并写出结构清晰、可维护的 SQL。

核心概念

留存率 (Retention Rate) 是衡量用户粘性的核心指标。其标准定义是：某一个时间窗口（如某一天）内首次或活跃的用户，在之后的特定时间周期（如第 2 天、第 7 天）内再次活跃的比例。

• 次日留存率：今天活跃的用户中，明天还活跃的用户占比。
• N 日留存率：第 0 天活跃的用户中，在第 N 天还活跃的用户占比。

计算公式：

数据模型示例 假设我们有一张用户登录日志表 user_login_log：

计算 2023-10-01 的次日留存率：

1. 第 0 天 (2023-10-01) 活跃用户：{101, 102, 103}，总共 3 人。
2. 第 1 天 (2023-10-02) 活跃用户：{101, 103}。
3. 留存用户 (交集)：{101, 103}，共 2 人。
4. 次日留存率：(2 / 3) * 100% ≈ 66.7%。

计算 2023-10-01 的 7 日留存率：

1. 第 0 天 (2023-10-01) 活跃用户：{101, 102, 103}，总共 3 人。
2. 第 7 天 (2023-10-08) 活跃用户：{101}。
3. 留存用户 (交集)：{101}，共 1 人。
4. 7 日留存率：(1 / 3) * 100% ≈ 33.3%。

多种解法

假设表结构如下：

 1CREATE TABLE user_login_log (
 2    user_id INT,
 3    login_ts DATETIME,
 4    INDEX idx_logints_userid (login_ts, user_id)
 5);

解法 1：使用 LEFT JOIN 和 GROUP BY (最通用，兼容性好)

这种方法思路清晰，将问题分解为“当日活跃用户”和“N日后活跃用户”两个集合，然后通过 LEFT JOIN 关联，最后聚合计算。

• 版本：✅ 所有 SQL 数据库版本通用。
• SQL (计算次日、7日、30日留存率)：

 1-- 使用 CTE (公共表表达式) 提高可读性
 2WITH daily_active_users AS (
 3    -- 步骤1: 计算每日的独立活跃用户
 4    SELECT DISTINCT
 5        DATE(login_ts) AS login_date,
 6        user_id
 7    FROM
 8        user_login_log
 9)
10-- 步骤2: 计算每日的总活跃用户数和各类留存用户数
11SELECT
12    t1.login_date AS cohort_date,
13    COUNT(t1.user_id) AS total_users,
14    -- 计算次日留存
15    COUNT(t2.user_id) AS retained_1d_users,
16    -- 计算7日留存
17    COUNT(t7.user_id) AS retained_7d_users,
18    -- 计算30日留存
19    COUNT(t30.user_id) AS retained_30d_users,
20    -- 计算留存率
21    COUNT(t2.user_id) * 1.0 / COUNT(t1.user_id) AS retention_rate_1d,
22    COUNT(t7.user_id) * 1.0 / COUNT(t1.user_id) AS retention_rate_7d,
23    COUNT(t30.user_id) * 1.0 / COUNT(t1.user_id) AS retention_rate_30d
24FROM
25    daily_active_users AS t1
26LEFT JOIN
27    daily_active_users AS t2 ON t1.user_id = t2.user_id AND t2.login_date = DATE_ADD(t1.login_date, INTERVAL 1 DAY)
28LEFT JOIN
29    daily_active_users AS t7 ON t1.user_id = t7.user_id AND t7.login_date = DATE_ADD(t1.login_date, INTERVAL 7 DAY)
30LEFT JOIN
31    daily_active_users AS t30 ON t1.user_id = t30.user_id AND t30.login_date = DATE_ADD(t1.login_date, INTERVAL 30 DAY)
32GROUP BY
33    t1.login_date
34ORDER BY
35    t1.login_date;

• 时间/空间复杂度：
  • CTE daily_active_users 对 user_login_log 进行去重，若使用 Hash Aggregate，时间复杂度为 $O(N)$，空间复杂度为 $O(M)$，其中 N 是日志总行数，M 是去重后的 (login_date, user_id) 总数。
  • 后续的 JOIN 和 GROUP BY 操作在 CTE 的结果上进行，其复杂度也与 M 相关，通常为 $O(M)$。
• 索引依赖：idx_logints_userid (login_ts, user_id) 索引可以加速 CTE 的 DISTINCT 操作。

解法 2：使用窗口函数 LEAD() (更现代，代码更简洁)

此方法通过 LEAD() 函数，在一次扫描中就能拿到每个用户“下一次”登录的日期，从而直接计算留存。

• 版本：✅ MySQL 8.0+ / PostgreSQL / Oracle / SQL Server。❌ MySQL 5.7 不支持。
• SQL (仅以次日留存为例)：

 1-- 步骤1: 使用窗口函数计算每个用户每次登录的下一次登录日期
 2WITH user_next_login AS (
 3    SELECT
 4        user_id,
 5        login_date,
 6        -- 获取该用户按时间排序的下一次登录日期
 7        LEAD(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date) AS next_login_date
 8    FROM (
 9        -- 先对用户和日期去重
10        SELECT DISTINCT
11            user_id,
12            DATE(login_ts) AS login_date
13        FROM
14            user_login_log
15    ) AS daily_logins
16)
17-- 步骤2: 根据日期差异判断是否为次日留存，并聚合计算
18SELECT
19    login_date AS cohort_date,
20    COUNT(user_id) AS total_users,
21    -- 如果当前登录日期和下次登录日期相差1天，则计为次日留存
22    COUNT(CASE WHEN DATEDIFF(next_login_date, login_date) = 1 THEN user_id END) AS retained_1d_users,
23    COUNT(CASE WHEN DATEDIFF(next_login_date, login_date) = 1 THEN user_id END) * 1.0 / COUNT(user_id) AS retention_rate_1d
24FROM
25    user_next_login
26GROUP BY
27    login_date
28ORDER BY
29    login_date;

注意：此方法计算的是“连续登录”的次日留存，如果要计算严格的“N日留存”，JOIN 法更直观。例如，一个用户1号登录，3号登录，LEAD() 法不会将1号的登录计入任何留存，而 JOIN 法会正确地报告1号的次日留存为0。因此，JOIN 法是计算标准留存率的更优选择。

常见低分回答

1. 在 WHERE 子句中对列使用函数：WHERE DATE(login_ts) = '2023-10-01'。这会导致 login_ts 列上的索引失效，数据库需要对每一行都计算 DATE() 函数，从而进行全表扫描。正确的做法是使用范围查询：WHERE login_ts >= '2023-10-01 00:00:00' AND login_ts < '2023-10-02 00:00:00'。
2. 忽略用户去重：直接使用 COUNT(user_id) 而不是 COUNT(DISTINCT user_id)。一个用户一天内可能登录多次，计算活跃用户数时必须去重，否则会导致分母（总用户数）偏大，留存率偏低。
3. 逻辑过于复杂，使用低效的相关子查询：在 SELECT 列表中为每个留存天数都使用一个相关子查询，性能极差。
   sql

   ⧉⛶

    1-- 反例: 极度低效
    2SELECT
    3    t1.login_date,
    4    COUNT(DISTINCT t1.user_id) AS total_users,
    5    (SELECT COUNT(DISTINCT t2.user_id)
    6     FROM user_login_log t2
    7     WHERE DATE(t2.login_ts) = DATE_ADD(t1.login_date, INTERVAL 1 DAY)
    8       AND t2.user_id IN (SELECT user_id FROM user_login_log t3 WHERE DATE(t3.login_ts) = t1.login_date)
    9    ) AS retained_1d_users
   10FROM ...

4. 计算口径错误：将留存用户数除以平台总用户数，而不是除以“当天”的活跃用户数。留存率的分母必须是特定初始队列的用户数。

工程实践与优化

• 预聚合：对于海量数据（如数十亿行日志），直接在原始日志表上运行上述查询会非常慢。最佳实践是进行预聚合。通过离线任务（如每日凌晨运行的 Spark/Flink/Hive SQL 任务）生成一个 daily_active_user 表。

  sql

  ⧉⛶

   1-- 每日物化的日活表
   2CREATE TABLE ads_daily_active_user (
   3    active_date DATE,
   4    user_id BIGINT,
   5    PRIMARY KEY (active_date, user_id)
   6);

  所有留存率计算都在这张大小适中的聚合表上进行，查询速度会提升几个数量级。

• 执行计划分析：使用 EXPLAIN 分析解法 1 的查询计划。

  • 关键点在于 daily_active_users 这个 CTE 的物化过程。MySQL 8.0+ 会智能处理。理想情况下，对 user_login_log 的访问应该是 type: range 或 index，利用 idx_logints_userid 索引。
  • 后续的 LEFT JOIN 操作，由于是在 CTE 的结果上进行，如果数据量不大，hash_join 是最高效的方式。Extra 字段应避免出现 Using filesort 或 Using temporary。

• 索引设计：

  • 原始日志表 user_login_log：INDEX(login_ts, user_id) 是黄金索引。login_ts 在前，可以快速筛选时间范围；user_id 在后，方便去重和关联。
  • 日活表 ads_daily_active_user：PRIMARY KEY(active_date, user_id) 非常适合留存计算，因为 JOIN 条件总是 active_date 和 user_id。

常见追问与延伸题目

1. 追问：如果数据量增长 100 倍，达到千亿级别，这条 SQL 性能怎样？怎么优化？

   • 回答要点 1 (瓶颈分析)：直接在源表上查询将不可行。查询会扫描大量数据，产生巨大的 I/O 和 CPU 消耗，可能导致数据库实例 OOM 或长时间夯住，影响线上业务。
   • 回答要点 2 (架构级优化)：必须采用数据仓库分层和预计算的思路。
     • 数据分层：将原始日志（ODS 层）通过 ETL 任务（如 Spark/Flink）清洗、聚合。
     • 创建中间表 (DWS/DWM)：生成一张 ads_daily_active_user (日活用户明细表)，只包含 (active_date, user_id)。这张表的数据量会远小于原始日志表。
     • 在聚合表上计算：我们最终的留存率 SQL 将直接查询这张日活表，查询性能将非常高，通常在秒级或分钟级完成，与原始数据量无关。
     • 结果物化：甚至可以将最终的留存率结果（每天一行，包含各种留存率指标）也物化到一张结果表（ADS 层）中，供 BI 工具或前端直接查询，实现报表秒开。

2. 追问：如果要计算“新用户”次日留存率，SQL 该怎么写？

   • 回答要点：这需要引入一张用户表 users，其中包含用户的注册日期。
     sql

     ⧉⛶

      1CREATE TABLE users (
      2    user_id INT PRIMARY KEY,
      3    register_date DATE
      4);

     • 思路：首先，筛选出在某天注册的用户（即“新用户”），然后看这些用户在第二天是否登录。
     • SQL 示例：
       sql

       ⧉⛶

        1WITH new_users_cohort AS (
        2    -- 步骤1: 找出每天的新增用户
        3    SELECT register_date, user_id FROM users
        4),
        5daily_logins AS (
        6    -- 步骤2: 获取每日登录的用户
        7    SELECT DISTINCT DATE(login_ts) AS login_date, user_id FROM user_login_log
        8)
        9-- 步骤3: 关联新用户群和他们的次日登录行为
       10SELECT
       11    c.register_date,
       12    COUNT(c.user_id) AS new_users_count,
       13    COUNT(l.user_id) AS retained_new_users_1d,
       14    COUNT(l.user_id) * 1.0 / COUNT(c.user_id) AS new_user_retention_rate_1d
       15FROM
       16    new_users_cohort AS c
       17LEFT JOIN
       18    daily_logins AS l ON c.user_id = l.user_id AND l.login_date = DATE_ADD(c.register_date, INTERVAL 1 DAY)
       19GROUP BY
       20    c.register_date
       21ORDER BY
       22    c.register_date;

3. 追问：除了 N 日留存，还有一种叫“滚动留存”，你知道是什么吗？SQL 如何实现？

   • 回答要点：
     • 定义区别：
       • N 日留存 (N-day Retention)：用户在第 0 天活跃，并且必须在第 N 天也活跃。这衡量的是用户周期性的回访习惯。
       • 滚动留存 (Rolling/Unbounded Retention)：用户在第 0 天活跃，并且在第 N 天及之后的任意一天有活跃即可。这衡量的是用户是否“最终没有流失”。
     • SQL 实现：实现滚动留存，只需修改 LEFT JOIN 的关联条件。以 7 日滚动留存为例，条件从 t2.login_date = DATE_ADD(t1.login_date, INTERVAL 7 DAY) 变为 t2.login_date >= DATE_ADD(t1.login_date, INTERVAL 7 DAY)。但这样会导致一个初始用户被重复计算多次，所以需要再次去重。
     • 正确 SQL (7日滚动留存)：
       sql

       ⧉⛶

        1WITH daily_active_users AS (
        2    SELECT DISTINCT DATE(login_ts) AS login_date, user_id FROM user_login_log
        3)
        4SELECT
        5    t1.login_date,
        6    COUNT(DISTINCT t1.user_id) AS total_users,
        7    COUNT(DISTINCT t2.user_id) AS rolling_7d_retained_users,
        8    COUNT(DISTINCT t2.user_id) * 1.0 / COUNT(DISTINCT t1.user_id) AS rolling_retention_rate_7d
        9FROM
       10    daily_active_users AS t1
       11LEFT JOIN
       12    daily_active_users AS t2 ON t1.user_id = t2.user_id AND t2.login_date >= DATE_ADD(t1.login_date, INTERVAL 7 DAY)
       13GROUP BY
       14    t1.login_date
       15ORDER BY
       16    t1.login_date;