小红书：找出过去 N 天内活跃且发布内容的用户的内容互动数据。

考察要点

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

1. 复杂业务需求拆解能力：如何将“过去N天内活跃且发布内容的用户”这个复合业务口径，精准地翻译成多表关联和过滤的 SQL 逻辑。
2. 高级 SQL 特性运用：是否熟练掌握 CTE (Common Table Expressions) 或子查询来构建清晰、分步的查询逻辑，处理中间结果集。
3. 性能优化意识：是否能预判查询的性能瓶颈，并主动提出索引设计、查询改写等优化策略，尤其是在处理时间范围筛选和大数据量 JOIN 时。

核心概念

要解决此问题，需要精准理解以下几个核心数据库概念：

1. Common Table Expression (CTE) CTE 使用 WITH 关键字，允许你为复杂的子查询定义一个临时的、命名的结果集。它极大地增强了 SQL 的可读性和模块化。

   • 优点：

     • 可读性：将复杂查询分解为逻辑上独立的步骤，代码更易理解和维护。
     • 可重用性：在单个查询中，一个 CTE 可以被多次引用。
     • 递归查询：支持实现递归，用于处理层级或图结构数据。

   • 语法示例 (MySQL 8.0+, PostgreSQL, SQL Server 等现代数据库支持):

     sql

     ⧉⛶

      1WITH CteName AS (
      2    SELECT column1, column2 FROM some_table WHERE condition
      3)
      4SELECT * FROM CteName WHERE column1 = 'some_value';

2. 子查询 (Subquery) 子查询是嵌套在另一个 SQL 查询中的查询。在不支持 CTE 的老版本 MySQL (如 5.7) 中，它是实现复杂逻辑的主要方式。

   • 分类：

     • IN / NOT IN 子查询：WHERE user_id IN (SELECT user_id FROM ...)
     • EXISTS / NOT EXISTS 子查询：WHERE EXISTS (SELECT 1 FROM ...)
     • FROM 子查询：SELECT ... FROM (SELECT ... ) AS derived_table

   • 性能对比：

     • IN：当子查询结果集较小时效率不错。对于大结果集，性能可能下降。
     • EXISTS：通常比 IN 更高效，因为它执行“半连接 (semi-join)”逻辑，一旦找到匹配项就停止搜索子查询的剩余部分。
     • FROM 子查询：优化器通常能很好地处理，但多层嵌套会使逻辑变得混乱。

3. 时间窗口筛选与索引 对 "过去 N 天" 的筛选是本题的关键性能点。

   • 高效写法：直接使用数据库原生日期函数与列进行比较。
     sql

     ⧉⛶

      1-- ✅ 高效，可利用索引
      2WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)

   • 低效写法：在列上使用函数。
     sql

     ⧉⛶

      1-- ❌ 低效，导致索引失效
      2WHERE DATE_FORMAT(create_time, '%Y-%m-%d') >= '2023-10-20'

   这背后的原理是，对列使用函数后，数据库无法直接使用 B+ 树索引进行范围扫描，被迫对每一行计算函数值再比较，退化为全表扫描。

多种解法

假设我们有以下三张表：

• user_activity_log (user_id, activity_time): 用户活跃日志
• notes (note_id, user_id, content, create_time): 用户发布的内容笔记
• interactions (interaction_id, note_id, user_id, interaction_type, create_time): 内容的互动数据（点赞、评论、收藏等）

目标：找出过去 7 天内（N=7）活跃过 且 发布过内容的用户，他们所发布的所有内容（不限于过去7天）获得的全部互动数据。

解法 1：使用 CTE (Common Table Expression)

这是最推荐的解法，逻辑清晰，可读性强。

• 版本：✅ MySQL 8.0+ / PostgreSQL / SQL Server

• 思路：

  1. 创建一个 CTE active_and_posting_users，用于筛选出过去 7 天内同时满足“活跃”和“发布内容”两个条件的 user_id 集合。
  2. 将 notes 表与此 CTE 进行 JOIN，找出这些目标用户发布的所有 note_id。
  3. 最后将 interactions 表与上一步的结果 JOIN，得到最终的互动明细数据。

• SQL 实现：

  sql

  ⧉⛶

   1WITH active_and_posting_users AS (
   2    -- 步骤1: 找出在过去7天内既活跃又发布过内容的用户ID
   3    SELECT DISTINCT t1.user_id
   4    FROM user_activity_log AS t1
   5    INNER JOIN notes AS t2 ON t1.user_id = t2.user_id
   6    WHERE
   7        t1.activity_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
   8        AND t2.create_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
   9)
  10-- 步骤2 & 3: 基于上述用户ID，查询他们所有笔记的互动数据
  11SELECT
  12    i.interaction_id,
  13    i.note_id,
  14    i.user_id AS interaction_user_id, -- 互动用户
  15    i.interaction_type,
  16    i.create_time AS interaction_time,
  17    n.user_id AS author_user_id -- 内容作者
  18FROM interactions AS i
  19INNER JOIN notes AS n ON i.note_id = n.note_id
  20WHERE n.user_id IN (SELECT user_id FROM active_and_posting_users);

• 复杂度：主要取决于 JOIN 操作。如果索引良好，时间复杂度约等于 O(\text{filtered_logs} + \text{filtered_notes} + \text{interactions_on_target_notes})。

• 索引依赖：

  • user_activity_log(activity_time, user_id): activity_time 在前用于范围查询。
  • notes(create_time, user_id): create_time 在前用于范围查询。
  • notes(user_id, note_id): 用于从 user_id 快速找到 note_id。
  • interactions(note_id): 用于从 note_id 快速找到互动记录。

解法 2：使用子查询

此方法兼容老版本 MySQL，但可读性稍差。

• 版本：✅ MySQL 5.7+

• 思路：与 CTE 思路一致，但将 CTE 部分改写为 FROM 子查询或 IN 子查询。

• SQL 实现：

  sql

  ⧉⛶

   1SELECT
   2    i.interaction_id,
   3    i.note_id,
   4    i.user_id AS interaction_user_id,
   5    i.interaction_type,
   6    i.create_time AS interaction_time,
   7    n.user_id AS author_user_id
   8FROM interactions AS i
   9INNER JOIN notes AS n ON i.note_id = n.note_id
  10WHERE n.user_id IN (
  11    -- 子查询: 找出在过去7天内既活跃又发布过内容的用户ID
  12    SELECT DISTINCT t1.user_id
  13    FROM user_activity_log AS t1
  14    WHERE
  15        t1.activity_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
  16        AND EXISTS ( -- 使用EXISTS通常比IN更高效
  17            SELECT 1
  18            FROM notes AS t2
  19            WHERE t2.user_id = t1.user_id
  20              AND t2.create_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
  21        )
  22);

• 复杂度与索引依赖：与 CTE 解法基本相同。MySQL 优化器在很多情况下能将 IN 子查询优化为 JOIN 或 EXISTS，但 EXISTS 的写法通常能给出更稳定的高性能执行计划。

常见低分回答

1. 逻辑错误，筛选条件放错位置：在最后才对 interactions 表的 create_time 进行过滤，这与题意“找出特定用户的...互动数据”不符。

   sql

   ⧉⛶

    1-- 错误示例：过滤了互动时间，而不是用户筛选时间
    2SELECT ...
    3FROM interactions i JOIN ...
    4WHERE i.create_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY); -- 业务逻辑错误

2. 在 WHERE 子句的列上使用函数：这是导致索引失效最常见的原因，会导致全表扫描。

   sql

   ⧉⛶

    1-- 错误示例：函数包裹列，索引失效
    2WHERE YEAR(t1.activity_time) = YEAR(CURDATE()) AND MONTH(t1.activity_time) = MONTH(CURDATE()) ...
    3-- 正确方式
    4WHERE t1.activity_time >= '2023-10-01' AND t1.activity_time < '2023-11-01'

3. 使用效率低下的 OR：在没有复合索引的情况下，使用 OR 连接两个不同列的条件，可能导致全表扫描。本题用 INNER JOIN 或 EXISTS 表达 AND 逻辑是正确的，但如果误解为 OR 且写法不当，性能会很差。

   sql

   ⧉⛶

    1-- 错误示例：假设要找活跃或发帖的用户，这样写性能差
    2SELECT user_id FROM user_activity_log ual, notes n
    3WHERE ual.user_id = n.user_id AND (ual.activity_time > ... OR n.create_time > ...);
    4-- 更优的方式是使用 UNION
    5SELECT user_id FROM user_activity_log WHERE ...
    6UNION
    7SELECT user_id FROM notes WHERE ...

4. 不必要的 SELECT * 和 GROUP BY：在子查询中返回了不需要的列，或进行了不必要的聚合，增加了 I/O 和计算开销。

   sql

   ⧉⛶

    1-- 错误示例：子查询里 SELECT *
    2WHERE n.user_id IN (SELECT * FROM active_and_posting_users);

工程实践与优化

• 执行计划分析：拿到一条慢 SQL，第一步就是 EXPLAIN。

  • type：检查 type 列，理想情况是 const, eq_ref, ref, range。出现 index 或 ALL 就意味着扫描了整个索引或全表，需要立即优化。
  • key：确认是否用上了正确的索引。NULL 表示没有使用索引。
  • rows：估算的扫描行数。这个数字越小越好。
  • Extra：关注 Using where（正常），Using index（覆盖索引，最佳），Using temporary; Using filesort（性能杀手，需要优化）。

• 索引设计：

  • 为 user_activity_log 表创建 (activity_time, user_id) 的复合索引，可以高效完成时间范围筛选。如果查询频繁，甚至可以考虑 (user_id, activity_time)，这取决于查询模式。
  • 为 notes 表创建 (create_time, user_id) 和 (user_id) 索引。
  • 为 interactions 表创建 (note_id) 索引。

• 数据量级问题：

  • 小到中量级 (百万-千万)：上述 SQL 解法配合良好索引，足以应对。
  • 超大规模 (亿-十亿)：
    1. 临时表：可以将第一步筛选出的 user_id 存入一个临时表，并为该临时表创建主键索引，后续 JOIN 操作会更稳定高效。
    2. 离线计算：对于这种分析类查询，在生产环境中通常不会直接查主库。数据会同步到数据仓库（如 Hive, ClickHouse）或由大数据平台（如 Spark, Flink）进行批处理/流处理。
    3. 用户标签/画像系统：更进一步，会有一个系统每天预计算用户的状态标签，如 is_active_last_7d, posted_in_last_7d。查询时直接 SELECT ... FROM user_profile WHERE is_active_last_7d = true AND posted_in_last_7d = true，将复杂的计算逻辑前置，实现查询的毫秒级响应。

常见追问与延伸题目

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

   • 回答要点 1 (瓶颈分析)：性能会急剧下降。JOIN 和 IN 子查询的中间结果集（满足条件的 user_id 列表）可能会变得非常大，导致内存压力和磁盘 I/O 飙升。数据库优化器可能选择错误的 JOIN 순서，导致巨大的中间表生成。
   • 回答要点 2 (优化方案)：
     • 应用层拆分：将查询拆分为两步。第一步，用一个单独的查询获取 user_id 列表，并将其缓存或存入临时表。第二步，分批（比如一次 1000 个 user_id）用 IN 子句去查询他们的笔记和互动数据，在应用层合并结果。
     • 架构级优化：引入数据仓库和预计算。搭建用户标签体系，每天 T+1 计算好每个用户的活跃和发布状态。查询时直接从用户标签表找到目标用户，再关联事实表，这从根本上解决了实时复杂计算的压力。
     • 数据库选型：考虑使用列式存储数据库（如 ClickHouse）进行此类分析查询，其针对大数据量聚合和扫描的性能远超行式数据库（如 MySQL）。

2. 追问：如果我只需要统计每个目标用户获得的互动总数，而不是互动明细，SQL 该怎么改写？

   • 回答要点：这是一个聚合查询。我们不再需要返回 interactions 表的所有行，而是在最后一步使用 GROUP BY 和 COUNT。这样可以大大减少网络传输的数据量和最终结果集的大小。
   • SQL 示例：
     sql

     ⧉⛶

      1-- 沿用解法1的CTE
      2WITH active_and_posting_users AS (...)
      3SELECT
      4    n.user_id AS author_user_id,
      5    COUNT(i.interaction_id) AS total_interactions
      6FROM notes AS n
      7INNER JOIN interactions AS i ON n.note_id = i.note_id
      8WHERE n.user_id IN (SELECT user_id FROM active_and_posting_users)
      9GROUP BY n.user_id;

3. 追问：如果“活跃”的定义改为“过去7天内连续3天登录”，SQL 该如何实现？

   • 回答要点：这是一个典型的“连续问题”，需要使用窗口函数 LAG() 或 LEAD() 来解决，这在 MySQL 8.0+ 中受支持。
   • 思路：
     1. 对每个用户的登录记录 user_activity_log，按 activity_time 排序。
     2. 使用 LAG(activity_time, 2) OVER (PARTITION BY user_id ORDER BY activity_time) 获取该用户两天前的登录时间。
     3. 计算当前登录时间与两天前登录时间的日期差 DATEDIFF()。
     4. 如果日期差为 2，则说明构成了连续 3 天的登录。
   • SQL 示例：
     sql

     ⧉⛶

      1WITH consecutive_logins AS (
      2    SELECT
      3        user_id,
      4        activity_date,
      5        LAG(activity_date, 2) OVER (PARTITION BY user_id ORDER BY activity_date) AS prev_2_day_login
      6    FROM (
      7        -- 先对每天的活动去重，确保一天只算一次
      8        SELECT DISTINCT user_id, DATE(activity_time) AS activity_date
      9        FROM user_activity_log
     10        WHERE activity_time >= DATE_SUB(CURDATE(), INTERVAL 9 DAY) -- 窗口稍大以捕捉边界
     11    ) daily_logins
     12)
     13SELECT DISTINCT user_id
     14FROM consecutive_logins
     15WHERE DATEDIFF(activity_date, prev_2_day_login) = 2;

     然后将这个查询作为子查询，替换掉原问题中对“活跃”的定义部分。