小红书：求每条笔记的留存（首次曝光 → 7 日内再次浏览）。

考察要点

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

1. 复杂业务场景的 SQL 建模能力：如何将“首次曝光”、“7日内再次浏览”、“留存”这些业务概念，精确地转化为 SQL 查询逻辑。
2. 窗口函数的熟练运用：特别是 MIN() OVER (...), ROW_NUMBER() OVER (...) 或 LEAD() OVER (...) 在处理时间序列数据、进行分组内计算（如标记首次行为）时的核心作用。
3. SQL 性能优化意识：是否能写出在海量数据（如小红书的曝光日志）下依然高效运行的 SQL，并能清晰地解释其索引设计和执行计划。

核心概念

本题的核心是识别每个用户对每条笔记的“首次访问”和“后续访问”，并计算时间差。

1. 用户-笔记访问序列：对于任意一个用户（user_id）和一条笔记（note_id），其访问行为是一个按时间（event_date）排序的序列。

   user_id	note_id	event_date
   101	2001	2023-01-01
   101	2001	2023-01-05
   101	2001	2023-01-10
   102	2001	2023-02-03

2. 窗口函数 (Window Function)：与 GROUP BY 将多行聚合为单行不同，窗口函数可以为每一行计算一个“窗口”内的值，并返回给该行，行数不减少。这是解决本题最优雅、最高效的工具。

   • MIN(event_date) OVER (PARTITION BY user_id, note_id): 计算每个 (user_id, note_id) 分组内的最小日期（即首次访问日期），并将这个值赋予该分组内的所有行。
   • LEAD(event_date, 1) OVER (PARTITION BY user_id, note_id ORDER BY event_date): 在每个 (user_id, note_id) 分组内，按时间排序，获取当前行的“下一行”的 event_date。这是计算访问间隔的利器。
   • ROW_NUMBER() OVER (PARTITION BY user_id, note_id ORDER BY event_date): 在每个 (user_id, note_id) 分组内，按时间排序，为每一行打上序号。序号为 1 的行即为首次访问。

3. 通用表表达式 (CTE - Common Table Expression)：使用 WITH ... AS (...) 语法，可以将复杂的查询拆解为多个逻辑步骤，使 SQL 结构清晰，可读性、可维护性大大增强。

4. 条件聚合 (Conditional Aggregation)：在 GROUP BY 聚合时，使用 CASE WHEN 语句来定义满足特定条件的计数或求和。这是在一次扫描中计算多个指标（如总用户数、留存用户数）的标准模式。

   sql

   ⧉⛶

    1COUNT(DISTINCT CASE WHEN condition THEN user_id ELSE NULL END)

多种解法

假设我们有一张曝光日志表 note_exposure_log：

 1CREATE TABLE note_exposure_log (
 2    user_id BIGINT,
 3    note_id BIGINT,
 4    event_date DATE
 5);
 6-- 为了性能，理想的索引是
 7CREATE INDEX idx_note_user_date ON note_exposure_log (note_id, user_id, event_date);

解法 1：使用窗口函数 LEAD 和 ROW_NUMBER (推荐，最高效)

这种解法思路清晰，性能优秀，是现代 SQL 的首选方案。

• 版本：✅ MySQL 8.0+ / PostgreSQL / SQL Server / Oracle；❌ MySQL 5.7 不支持窗口函数。

• 思路：

  1. 使用 CTE 和 ROW_NUMBER 标记出每个用户对每条笔记的首次访问记录。
  2. 同时使用 LEAD 函数，在首次访问的记录行上，直接获取到下一次访问的日期。
  3. 最后根据首次访问和下次访问的日期差，进行条件聚合，计算留存用户数。

• SQL 代码：

  sql

  ⧉⛶

   1WITH ranked_visits AS (
   2    SELECT
   3        user_id,
   4        note_id,
   5        event_date,
   6        -- 标记是否为首次访问
   7        ROW_NUMBER() OVER(PARTITION BY user_id, note_id ORDER BY event_date) AS visit_rank,
   8        -- 获取该用户对该笔记的下一次访问日期
   9        LEAD(event_date, 1) OVER(PARTITION BY user_id, note_id ORDER BY event_date) AS next_visit_date
  10    FROM
  11        note_exposure_log
  12)
  13SELECT
  14    note_id,
  15    -- 计算总的首次访问用户数
  16    COUNT(DISTINCT user_id) AS total_first_day_users,
  17    -- 计算7日内留存的用户数
  18    COUNT(DISTINCT
  19        CASE
  20            WHEN next_visit_date IS NOT NULL AND DATEDIFF(next_visit_date, event_date) <= 7
  21            THEN user_id
  22        END
  23    ) AS retained_7d_users,
  24    -- 计算7日留存率
  25    COUNT(DISTINCT
  26        CASE
  27            WHEN next_visit_date IS NOT NULL AND DATEDIFF(next_visit_date, event_date) <= 7
  28            THEN user_id
  29        END
  30    ) / COUNT(DISTINCT user_id) AS retention_rate_7d
  31FROM
  32    ranked_visits
  33WHERE
  34    visit_rank = 1 -- 只关心首次访问的记录
  35GROUP BY
  36    note_id;

• 复杂度：时间复杂度主要由排序决定，为 $O(N \log N)$，其中 N 是表中的总行数。空间复杂度为 $O(N)$，用于存储窗口函数计算的中间结果。

• 索引依赖：idx_note_user_date (note_id, user_id, event_date) 索引是此查询的绝配。数据库可以利用该索引的有序性，高效地执行 PARTITION BY 和 ORDER BY 操作，避免额外的 filesort。

解法 2：使用窗口函数 MIN (思路更直接)

这种解法不依赖 LEAD，而是将首次访问日期广播到所有相关行，再进行判断。

• 版本：✅ MySQL 8.0+ / PostgreSQL 等。

• 思路：

  1. 使用 CTE 和 MIN() OVER (...) 为每条访问记录都附加上对应的“首次访问日期”。
  2. 在最终聚合阶段，筛选出那些“访问日期”大于“首次访问日期”且小于等于7天的记录，对这些记录背后的用户进行去重计数。

• SQL 代码：

  sql

  ⧉⛶

   1WITH visits_with_first_date AS (
   2    SELECT
   3        user_id,
   4        note_id,
   5        event_date,
   6        MIN(event_date) OVER (PARTITION BY user_id, note_id) AS first_visit_date
   7    FROM
   8        note_exposure_log
   9)
  10SELECT
  11    note_id,
  12    -- 计算总的首次访问用户数
  13    COUNT(DISTINCT user_id) AS total_first_day_users,
  14    -- 计算7日内留存的用户数
  15    COUNT(DISTINCT
  16        CASE
  17            WHEN event_date > first_visit_date AND DATEDIFF(event_date, first_visit_date) <= 7
  18            THEN user_id
  19        END
  20    ) AS retained_7d_users
  21FROM
  22    visits_with_first_date
  23GROUP BY
  24    note_id;

• 复杂度：与解法 1 类似，时间复杂度 $O(N \log N)$，空间复杂度 $O(N)$。

• 索引依赖：同样，idx_note_user_date 索引能极大地提升性能。

解法 3：使用自连接 Self-Join (兼容老版本)

在没有窗口函数的旧版 MySQL (如 5.7) 中，自连接是实现此类需求的常用方法，但通常性能较差。

• 版本：✅ 所有 SQL 数据库版本。

• 思路：

  1. 创建一个临时表或 CTE，first_visits，通过 GROUP BY 找到每个用户对每条笔记的首次访问日期。
  2. 将原始日志表 log 与 first_visits 表进行连接，找出所有后续访问记录。
  3. 在 JOIN 的结果上进行筛选和聚合。

• SQL 代码：

  sql

  ⧉⛶

   1WITH first_visits AS (
   2    SELECT
   3        user_id,
   4        note_id,
   5        MIN(event_date) AS first_date
   6    FROM
   7        note_exposure_log
   8    GROUP BY
   9        user_id, note_id
  10)
  11SELECT
  12    log.note_id,
  13    COUNT(DISTINCT log.user_id) AS total_first_day_users,
  14    COUNT(DISTINCT
  15        CASE
  16            -- 这里的 log.event_date 是后续访问日期
  17            WHEN log.event_date > fv.first_date AND DATEDIFF(log.event_date, fv.first_date) <= 7
  18            THEN log.user_id
  19        END
  20    ) AS retained_7d_users
  21FROM
  22    note_exposure_log AS log
  23JOIN
  24    first_visits AS fv ON log.user_id = fv.user_id AND log.note_id = fv.note_id
  25GROUP BY
  26    log.note_id;

• 复杂度：理论上，first_visits 的 GROUP BY 是 $O(N)$。后续的 JOIN 操作，即使有索引，其代价也通常高于窗口函数。在数据量巨大时，性能会显著低于前两种解法。

• 索引依赖：idx_note_user_date 索引对 first_visits 的计算和后续的 JOIN 都至关重要。

常见低分回答

1. 错误定义“首次”：SELECT note_id, MIN(event_date) FROM log GROUP BY note_id。这种写法完全忽略了用户维度，找到了每条笔记在全平台上的最早曝光时间，而不是针对每个用户的首次。
2. 重复计算留存用户：如果一个用户在7天内多次回访（如第2天、第5天），使用 COUNT(CASE WHEN ...) 而不是 COUNT(DISTINCT CASE WHEN ...) 会导致这个用户被计为多次留存，结果偏高。
3. 使用低效的相关子查询：在主查询的每一行都执行一个子查询来寻找后续访问，这是 $O(N^2)$ 级别的性能灾难，在面试中是绝对的禁忌。
   sql

   ⧉⛶

    1-- 极度不推荐的写法
    2SELECT ...
    3FROM note_exposure_log t1
    4WHERE EXISTS (
    5    SELECT 1 FROM note_exposure_log t2
    6    WHERE t2.user_id = t1.user_id
    7      AND t2.note_id = t1.note_id
    8      AND t2.event_date > t1.event_date
    9      AND DATEDIFF(t2.event_date, t1.event_date) <= 7
   10);

4. 日期/边界条件处理错误：DATEDIFF 的参数顺序、开闭区间（<= 7 vs < 7）理解不清，或者错误地使用了 event_date - first_visit_date <= 7 这种不标准的日期计算方式。
5. 无法处理无后续访问的情况：LEAD 函数对于分组内的最后一条记录会返回 NULL，逻辑中必须正确处理 NULL 值，否则 DATEDIFF(NULL, ...) 的结果也是 NULL，可能导致逻辑错误。

工程实践与优化

• 数据量级与架构选择：小红书的曝光日志是百亿、千亿级别。这种规模的计算不会在生产 OLTP 数据库（如 MySQL）上直接运行。它会在数据仓库（如 Hive, ClickHouse, MaxCompute）或数据湖（如 Spark SQL, Flink SQL）中作为离线任务执行。面试时主动提及这一点，体现你的系统视野。
• 索引设计的艺术：在 MySQL/PostgreSQL 这类行存数据库中，INDEX (note_id, user_id, event_date) 是最优索引。note_id 在前，方便按笔记聚合；user_id 其次，用于 PARTITION BY；event_date 最后，用于 ORDER BY。这个索引可以完美地成为一个覆盖索引，窗口函数计算时无需回表，性能极高。
• 执行计划分析 (EXPLAIN)：对于解法 1，理想的 EXPLAIN 结果应该显示：
  • key: idx_note_user_date，表示用上了正确的索引。
  • Extra: 不应出现 Using filesort，因为索引已经提供了排序。如果出现 Using index，则表示实现了覆盖索引，是最佳情况。
• 数据倾斜：如果某条笔记是超级爆款，其数据量远超其他笔记，可能会导致处理该 note_id 的任务成为瓶颈。实践中可能需要对超大 note_id 进行特殊处理，例如拆分 user_id 范围来并行计算。
• 任务调度与物化：这种留存指标通常是 T+1 计算的。每天凌晨，ETL 任务会处理前一天的增量日志，更新留存结果。结果可以物化到一张结果表中，供业务方快速查询，而不是每次都实时计算。

常见追问与延伸题目

1. 追问：如果需要同时计算次日、3日、7日、30日留存，如何优化？

   • 回答要点：可以在一次查询中通过多个 CASE WHEN 完成，而不是跑多次查询。这利用了条件聚合的强大能力，只需一次数据扫描和排序，性能远高于多次独立计算。
     sql

     ⧉⛶

      1-- 在解法1或2的最终SELECT中扩展
      2SELECT
      3    note_id,
      4    COUNT(DISTINCT user_id) AS total_first_day_users,
      5    COUNT(DISTINCT CASE WHEN DATEDIFF(next_visit_date, event_date) = 1 THEN user_id END) AS retained_1d_users,
      6    COUNT(DISTINCT CASE WHEN DATEDIFF(next_visit_date, event_date) <= 3 THEN user_id END) AS retained_3d_users,
      7    COUNT(DISTINCT CASE WHEN DATEDIFF(next_visit_date, event_date) <= 7 THEN user_id END) AS retained_7d_users,
      8    COUNT(DISTINCT CASE WHEN DATEDIFF(next_visit_date, event_date) <= 30 THEN user_id END) AS retained_30d_users
      9FROM ...
     10WHERE visit_rank = 1
     11GROUP BY note_id;

     注意：这里的 next_visit_date 仅代表首次访问后的第一次回访，如果要计算 N 日内任意回访，则需要用解法2的思路。

2. 追问：如果数据量增长 100 倍，达到万亿级别，你的 SQL 还能跑吗？怎么办？

   • 回答要点：
     • SQL 本身：SQL 逻辑（窗口函数）依然是最高效的，但执行它的引擎需要改变。
     • 执行引擎：必须使用分布式计算框架，如 Spark、Flink 或 MaxCompute。这些框架能将数据分片（Partition），在数百台机器上并行执行 PARTITION BY 和 GROUP BY 操作，通过 MapReduce/Shuffle 机制汇总结果。
     • 数据存储：数据会存储在分布式文件系统（如 HDFS, S3）或专门的数仓表中，并按日期进行分区（Partitioning）。查询时可以只加载特定时间范围的分区，极大减少 I/O。
     • 优化策略：在 Spark 中，会进一步考虑 spark.sql.shuffle.partitions 的设置，处理数据倾斜（Salting），使用 Broadcast Join 等高级优化。

3. 追问：如果业务定义改变：留存指“首次曝光后的7个自然日内，有任意一天再次曝光即可”，SQL 该如何修改？

   • 回答要点：这个定义比原题更宽泛。解法 1 的 LEAD 只找到了紧邻的下一次访问，不适用于此场景。解法 2 的思路则完全适用。
   • 解法：应使用解法 2（MIN() OVER()）的思路。visits_with_first_date CTE 已经为每一条访问记录都关联了首次访问日期。最终的聚合逻辑 COUNT(DISTINCT CASE WHEN event_date > first_visit_date AND DATEDIFF(event_date, first_visit_date) <= 7 THEN user_id END) 正是计算“首次访问后7天内任意一次访问”的用户数。这体现了不同解法在应对需求变化时的灵活性差异。