在 TikTok 里，求每位创作者最近一次直播观看人数同比上一次的增长率。

考察要点

这道题面试官想看你是否懂 窗口函数（Window Functions） 的使用，特别是 LAG() 函数。同时，考察你对 CTE (Common Table Expression) 的熟练程度，以及处理分组内排序和行间计算的逻辑能力。这本质上是一道考察 "Top N per group" 问题的变种，要求在组内进行时序对比。

核心概念

本题的核心是获取一个分组内（每个创作者）按时间排序的“上一条”记录的数据。

1. 窗口函数 (Window Function): 与 GROUP BY 这种将多行聚合成一行的函数不同，窗口函数为结果集的每一行计算一个值，这个值基于与当前行相关的“窗口”（一组行）来计算。

   • 语法: FUNCTION_NAME() OVER (PARTITION BY ... ORDER BY ...)
   • PARTITION BY creator_id: 定义窗口的边界。这里表示按创作者 ID 分区，计算将在每个创作者的直播记录内部独立进行。
   • ORDER BY start_time: 定义分区内行的顺序。这里按直播开始时间升序排列，以确定“上一次”和“这一次”的顺序。

2. LAG() 函数: LAG() 是一个窗口函数，用于访问当前行之前的某一行的数据。

   • 语法: LAG(expression, offset, default_value)
   • expression: 要获取其值的列，本题中是 viewers。
   • offset: 偏移量，即往前数几行。1 表示紧邻的上一行。
   • default_value: 如果不存在上一行（例如，这是该创作者的第一次直播），则返回此默认值。这对于避免 NULL 和后续计算错误非常重要。

3. CTE (Common Table Expression): CTE 使用 WITH 关键字定义，可以创建一个临时的、命名的结果集，后续的查询可以像引用普通表一样引用它。

   • 为什么需要 CTE: 窗口函数的计算结果不能直接在 WHERE 或 GROUP BY 子句中使用。标准做法是先在 CTE 中计算出包含窗口函数结果的中间表，然后在主查询中对这个中间表进行过滤和最终计算。

   数据示例: 假设我们有 live_streams 表：

 1**期望输出**:
 2只针对每个创作者的**最近一次**直播。

 1**计算逻辑**:
 2- 创作者 1：最近一次观看 9000，上一次 6000。增长率 = (9000 - 6000) / 6000 = 0.5。
 3- 创作者 2：最近一次观看 7000，上一次 8000。增长率 = (7000 - 8000) / 8000 = -0.125。

多种解法

解法 1：使用窗口函数 LAG() 和 ROW_NUMBER()（推荐，最优解）

这种解法思路清晰，性能优秀，是现代 SQL 的标准答案。

• 思路:

  1. 使用 CTE 创建一个中间表。
  2. 在 CTE 内部，使用 ROW_NUMBER() OVER (PARTITION BY creator_id ORDER BY start_time DESC) 为每个创作者的直播记录按时间倒序排名，rn=1 即为最近一次直播。
  3. 同时，使用 LAG(viewers, 1, 0) OVER (PARTITION BY creator_id ORDER BY start_time ASC) 获取按时间正序排列的上一场直播的观看人数。
  4. 在主查询中，从 CTE 筛选出 rn = 1 的记录（即每个创作者的最近一次直播）。
  5. 计算增长率，并使用 NULLIF 防止除零错误。

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

• SQL 实现:

 1WITH RankedStreams AS (
 2    SELECT
 3        creator_id,
 4        viewers,
 5        -- 获取上一次直播的观看人数，按时间升序排列
 6        LAG(viewers, 1, 0) OVER (PARTITION BY creator_id ORDER BY start_time) AS previous_viewers,
 7        -- 为每个创作者的直播按时间倒序排名，rn=1 为最近一次
 8        ROW_NUMBER() OVER (PARTITION BY creator_id ORDER BY start_time DESC) AS rn
 9    FROM
10        live_streams
11)
12SELECT
13    creator_id,
14    -- 计算增长率，使用 NULLIF 防止除以 0 的错误
15    (viewers - previous_viewers) / NULLIF(previous_viewers, 0) AS growth_rate
16FROM
17    RankedStreams
18WHERE
19    rn = 1 AND previous_viewers IS NOT NULL; -- 也可以是 previous_viewers > 0，确保有上一次直播记录

• 复杂度:

  • 时间复杂度: $O(N \log N)$，主要开销在于窗口函数的排序 (ORDER BY)。如果 (creator_id, start_time) 上有索引，数据库可以利用索引顺序扫描，避免全量排序，性能会大幅提升。
  • 空间复杂度: $O(N)$，需要存储中间结果集。

• 索引依赖: 在 (creator_id, start_time) 上建立复合索引是性能优化的关键。

解法 2：使用自连接（兼容老版本）

在不支持窗口函数的旧版 MySQL (如 5.7) 中，可以通过自连接模拟 LAG() 的行为。

• 思路:

  1. 首先，需要一个方法来唯一标识每个创作者的每一次直播的顺序。我们可以通过一个子查询或 CTE，使用 ROW_NUMBER()（MySQL 8.0+）或用户变量（MySQL 5.7）来生成排名。
  2. 将排名后的表进行自连接。t1 代表当前直播，t2 代表上一次直播。
  3. 连接条件为 t1.creator_id = t2.creator_id 并且 t1.rank = t2.rank - 1。
  4. 最后，筛选出每个创作者的最新直播记录进行计算。

• 版本: ✅ MySQL 5.7+ / PostgreSQL 等所有主流数据库。

• SQL 实现 (使用 CTE 和 ROW_NUMBER，更通用):

 1WITH NumberedStreams AS (
 2    SELECT
 3        creator_id,
 4        viewers,
 5        start_time,
 6        ROW_NUMBER() OVER (PARTITION BY creator_id ORDER BY start_time) AS rn
 7    FROM
 8        live_streams
 9),
10LatestStreams AS (
11    -- 找到每个创作者的最新一次直播排名
12    SELECT
13        creator_id,
14        MAX(rn) AS max_rn
15    FROM
16        NumberedStreams
17    GROUP BY
18        creator_id
19)
20SELECT
21    t1.creator_id,
22    (t1.viewers - t2.viewers) / NULLIF(t2.viewers, 0) AS growth_rate
23FROM
24    NumberedStreams t1
25JOIN
26    NumberedStreams t2 ON t1.creator_id = t2.creator_id AND t1.rn = t2.rn + 1
27JOIN
28    LatestStreams ls ON t1.creator_id = ls.creator_id AND t1.rn = ls.max_rn;

• 复杂度:

  • 时间复杂度: $O(N \log N)$。需要对表进行排序以生成行号，然后进行连接操作。通常性能劣于窗口函数。
  • 空间复杂度: $O(N)$，需要存储 CTE 的结果。

• 索引依赖: 同样， (creator_id, start_time) 上的索引至关重要。

常见低分回答

1. 在 WHERE 子句中直接使用窗口函数：这是语法错误。窗口函数在 SELECT 和 ORDER BY 子句中执行，逻辑上晚于 WHERE 子句。
   sql

   ⧉⛶

    1-- 错误示例
    2SELECT
    3    creator_id,
    4    (viewers - LAG(viewers, 1, 0) OVER(...)) / LAG(viewers, 1, 0) OVER(...)
    5FROM live_streams
    6WHERE ROW_NUMBER() OVER(PARTITION BY creator_id ORDER BY start_time DESC) = 1;

2. 忽略首次直播的边界情况：如果一个创作者只有一次直播，LAG() 会返回 NULL 或默认值。如果默认值为 0，计算增长率时会导致除零错误。高分回答会使用 NULLIF 或 CASE WHEN 处理。
   sql

   ⧉⛶

    1-- 低分示例：未处理除零
    2(viewers - previous_viewers) / previous_viewers

3. 使用相关子查询获取上一条记录：这种方法对于每一行都会执行一次子查询，性能极差，时间复杂度接近 $O(N^2)$，在大数据量下是灾难。
   sql

   ⧉⛶

    1-- 性能极差的示例
    2SELECT
    3    l1.creator_id,
    4    (l1.viewers - (SELECT l2.viewers FROM live_streams l2
    5                   WHERE l2.creator_id = l1.creator_id AND l2.start_time < l1.start_time
    6                   ORDER BY l2.start_time DESC LIMIT 1))
    7    / (SELECT l2.viewers FROM live_streams l2
    8       WHERE l2.creator_id = l1.creator_id AND l2.start_time < l1.start_time
    9       ORDER BY l2.start_time DESC LIMIT 1)
   10FROM live_streams l1
   11WHERE ... -- 后续逻辑复杂

4. 忘记 PARTITION BY：如果 OVER() 子句中只写了 ORDER BY 而没有 PARTITION BY，LAG() 会获取整个表按时间排序的上一条记录，导致创作者 A 的直播与创作者 B 的直播进行比较，逻辑完全错误。

工程实践与优化

• 索引是王道：对于解法 1 和 2，live_streams(creator_id, start_time) 复合索引是性能的决定性因素。该索引完美匹配了 PARTITION BY creator_id 和 ORDER BY start_time，使得数据库引擎可以按索引顺序读取数据，避免了昂贵的“排序”操作。
• 执行计划分析：
  • 使用 EXPLAIN 查看执行计划。对于解法 1，理想的 Extra 字段不应包含 Using temporary; Using filesort。如果看到这个，说明索引没有被有效利用。
  • 对于自连接解法，EXPLAIN 会显示多次对 live_streams 表的扫描和连接操作，通常成本更高。
• 数据倾斜问题：在 TikTok 这种场景下，头部创作者的直播次数远超普通创作者。这会导致 PARTITION BY creator_id 时，某个分区（partition）的数据量极大。窗口函数在处理这个巨大分区时，可能会消耗大量内存和 CPU，甚至导致 OOM。工程上，可以考虑：
  • 对超大 V 单独处理。
  • 使用 Spark/Flink 等大数据框架进行离线计算，它们对数据倾斜有更好的处理机制。
• 物化视图/中间表：如果这个指标需要频繁查询（例如，在创作者数据后台实时展示），每次都实时计算是不可接受的。正确的工程做法是：
  • 每天或每小时通过离线任务（如 Spark SQL）计算好所有创作者的最新增长率。
  • 将结果存储在一个新的结果表（物化视图）中，如 creator_live_growth_stats。
  • 前端查询直接从这个结果表中读取，实现毫秒级响应。

常见追问与延伸题目

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

   • 回答要点 1 (性能瓶颈)：即使有索引，在百亿级别的表上运行窗口函数仍然是巨大的挑战。单机 MySQL 根本无法完成。主要瓶颈在于：
     • IO 瓶颈：需要扫描的数据量巨大，即使是索引扫描，IO 开销也很大。
     • CPU/内存瓶颈：窗口函数的排序和计算，尤其是在存在数据倾斜时，会消耗大量计算资源。
   • 回答要点 2 (优化方案)：必须放弃实时 SQL 查询的思路，转向大数据批处理/流处理架构。
     • 批处理 (Batch Processing)：使用 Apache Spark 或 Flink，在数据仓库（如 Hive, Iceberg）上执行。这些框架为分布式计算设计，能水平扩展，通过 repartition 和 sortWithinPartitions 等操作高效处理大规模数据和数据倾斜。任务按天或小时调度，将结果写入结果表。
     • 流处理 (Stream Processing)：如果要求准实时（分钟级），可以使用 Flink 或 Spark Streaming。当一个新的直播结束事件进入消息队列（如 Kafka）时，流处理任务可以维护每个创作者的状态（如上一次直播的观看人数），在新数据到达时立即计算增长率并更新结果库。

2. 追问：如果发现这条 SQL 跑得很慢，EXPLAIN 显示 Using filesort，怎么定位和修复？

   • 回答要点 1 (定位原因)：Using filesort 意味着数据库无法利用索引来满足 ORDER BY 的要求，必须在内存或磁盘上进行额外的排序操作。原因通常是：
     • 没有合适的索引：没有在 (creator_id, start_time) 上创建复合索引。
     • 索引顺序错误：索引是 (start_time, creator_id)，而 PARTITION BY 是 creator_id，顺序不匹配。
     • ORDER BY 的列混合了不同方向：如 ORDER BY col1 ASC, col2 DESC，这在旧版 MySQL 中可能无法完全利用索引。
   • 回答要点 2 (修复方法)：
     • 创建或修改索引：确保存在 CREATE INDEX idx_creator_time ON live_streams (creator_id, start_time); 这样的索引。
     • 检查查询：确保 PARTITION BY 和 ORDER BY 的列与索引前缀顺序一致。
     • 分析表结构：检查列的数据类型是否一致，避免因隐式类型转换导致索引失效。

3. 追问：如果换成分布式数据库如 TiDB，这条 SQL 的执行和优化有什么不同？

   • 回答要点 1 (执行原理差异)：TiDB 是一个分布式 SQL 数据库。数据根据表的主键或指定的 SHARD_KEY 被打散存储在不同的物理节点上。当执行带有 PARTITION BY creator_id 的窗口函数时：
     • 数据汇集 (Shuffle)：TiDB 需要从各个存储节点（TiKV）拉取数据，并按照 creator_id 的值将数据重新分发（Shuffle/Exchange 操作），确保同一个 creator_id 的所有记录都在同一个计算节点（TiDB Server）上进行窗口函数计算。这个过程涉及网络传输，是主要的性能开销。
   • 回答要点 2 (优化策略)：
     • 利用 TiDB_SHARD 或聚簇索引：如果 creator_id 是表的主键或聚簇索引的一部分，TiDB 会尝试将同一个 creator_id 的数据物理上存储在一起，可以极大减少或避免数据汇集，性能会好很多。
     • MPP 架构：TiDB 引入了 MPP（大规模并行处理）框架 TiFlash。可以将窗口函数的计算下推到 TiFlash 执行。TiFlash 是列式存储，并且可以并行处理数据 shuffle 和计算，对于这类分析型查询性能远超行存的 TiKV。查询时可以使用 /*+ READ_FROM_STORAGE(TIFLASH[table_name]) */ hint 来指定。
     • 查看执行计划：TiDB 的 EXPLAIN 会明确显示 HashAgg, StreamAgg 以及 Exchange 算子，通过分析这些算子可以了解数据是否发生了跨节点移动，从而进行针对性优化。