快手：分析直播观看时长，求每个用户最长一次直播观看时长以及对应主播。

考察要点

这道题是典型的 "Top N per Group" 问题，面试官想考察你是否掌握了解决此类问题的核心武器——窗口函数。同时，也希望看到你是否了解其他传统解法（如关联子查询、自连接），并能清晰地分析和对比不同方案在性能、可读性和场景普适性上的优劣。这背后反映了你对 SQL 语义的理解深度和性能优化意识。

核心概念

解决本题的核心是为每个用户的观看记录进行分组排序。涉及的关键概念如下：

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

   • 语法结构: FUNCTION_NAME() OVER (PARTITION BY ... ORDER BY ...)
   • PARTITION BY user_id: 定义窗口的范围。这里表示按 user_id 将数据分成不同的“桶”，函数在每个桶内独立计算。
   • ORDER BY watch_duration_minutes DESC: 定义桶内数据的排序规则。这里按观看时长降序排列，最长的排在最前面。
   • 核心排序函数:
     • ROW_NUMBER(): 在每个分区内，从 1 开始为每一行分配一个唯一的连续排名。即使值相同，排名也不同。
     • RANK(): 值相同时排名相同，但后续排名会跳跃。例如：1, 2, 2, 4。
     • DENSE_RANK(): 值相同时排名相同，但后续排名是连续的。例如：1, 2, 2, 3。 对于本题“求最长一次”，使用 ROW_NUMBER() 是最精准且无歧义的选择，因为它能确保即使有时长并列的情况，也只选出其中一条记录。

2. 公共表表达式 (CTE - Common Table Expression): 使用 WITH 关键字定义的临时命名结果集，可以提高 SQL 的可读性和模块化。CTE 在单个查询中可以被引用一次或多次。它是使用窗口函数的最佳拍档。

   sql

   ⧉⛶

    1WITH RankedWatches AS (
    2    SELECT
    3        user_id,
    4        anchor_id,
    5        watch_duration_minutes,
    6        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY watch_duration_minutes DESC) as rn
    7    FROM watch_log
    8)
    9SELECT user_id, anchor_id, watch_duration_minutes
   10FROM RankedWatches
   11WHERE rn = 1;

3. 关联子查询 (Correlated Subquery): 一种子查询，其执行依赖于外部查询提供的值。它会为外部查询的每一行都执行一次。

   sql

   ⧉⛶

    1-- 对于外部查询的每一行 w1，内部查询都会找到该用户(w1.user_id)的最大时长
    2SELECT user_id, anchor_id, watch_duration_minutes
    3FROM watch_log w1
    4WHERE watch_duration_minutes = (
    5    SELECT MAX(watch_duration_minutes)
    6    FROM watch_log w2
    7    WHERE w2.user_id = w1.user_id
    8);

   在没有合适索引的情况下，性能通常较差，接近 $O(N^2)$。

多种解法

假设我们有如下的直播观看日志表 watch_log：

期望输出：

解法 1：窗口函数 ROW_NUMBER()（最优解）

这是最现代、最高效、最推荐的解法。思路是先对每个用户的观看记录按时长降序排名，然后筛选出排名第一的记录。

• 适用版本: ✅ MySQL 8.0+ / PostgreSQL / Oracle / SQL Server
• SQL 代码:
  sql

  ⧉⛶

   1WITH RankedWatches AS (
   2  SELECT
   3    user_id,
   4    anchor_id,
   5    watch_duration_minutes,
   6    -- 对每个用户(PARTITION BY user_id)的观看记录按时长倒序(ORDER BY)编号
   7    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY watch_duration_minutes DESC) AS rn
   8  FROM
   9    watch_log
  10)
  11SELECT
  12  user_id,
  13  anchor_id,
  14  watch_duration_minutes
  15FROM
  16  RankedWatches
  17WHERE
  18  rn = 1;

• 复杂度: 时间复杂度主要取决于排序操作，为 $O(N \log N)$，其中 N 是总记录数。空间复杂度为 $O(N)$，需要存储中间结果。
• 索引依赖: 在 (user_id, watch_duration_minutes DESC) 上建立复合索引可以极大地加速 PARTITION BY 和 ORDER BY 的过程，数据库可能能利用索引顺序避免显式排序。

解法 2：JOIN 聚合子查询

这是一种更传统的解法，兼容性好，但逻辑稍显迂回。思路是先用 GROUP BY 找出每个用户的最大观看时长，然后将原表与这个聚合结果进行 JOIN，找出匹配的记录。

• 适用版本: ✅ 所有主流数据库版本
• SQL 代码:
  sql

  ⧉⛶

   1SELECT
   2    w1.user_id,
   3    -- 如果有多个主播达到最大时长，GROUP BY会只返回一个，但具体是哪个不确定
   4    -- 为了结果确定性，可以加一个聚合函数，比如 MIN()
   5    MIN(w1.anchor_id) AS anchor_id, 
   6    w1.watch_duration_minutes
   7FROM
   8    watch_log AS w1
   9INNER JOIN (
  10    -- 步骤1: 找出每个用户的最大观看时长
  11    SELECT
  12        user_id,
  13        MAX(watch_duration_minutes) AS max_duration
  14    FROM
  15        watch_log
  16    GROUP BY
  17        user_id
  18) AS w2
  19ON
  20    w1.user_id = w2.user_id AND w1.watch_duration_minutes = w2.max_duration
  21GROUP BY 
  22    w1.user_id, w1.watch_duration_minutes;

  注意：最后的 GROUP BY 和 MIN(anchor_id) 是为了处理时长并列的情况，确保每个用户只返回一行。
• 复杂度: 涉及一次 GROUP BY（通常是 $O(N)$ 的 Hash Aggregate 或 $O(N \log N)$ 的 Sort Aggregate）和一次 JOIN。整体性能通常也很好，接近 $O(N \log N)$。
• 索引依赖: 在 (user_id, watch_duration_minutes) 上建立复合索引，对子查询的 GROUP BY 和外部 JOIN 的条件都有加速效果。

解法 3：关联子查询

这种方法直观地表达了“查找时长等于该用户最大时长的记录”，但在老版本的 MySQL 或无索引的情况下性能堪忧。

• 适用版本: ✅ 所有主流数据库版本
• SQL 代码:
  sql

  ⧉⛶

   1SELECT
   2    w1.user_id,
   3    w1.anchor_id,
   4    w1.watch_duration_minutes
   5FROM
   6    watch_log AS w1
   7WHERE
   8    w1.watch_duration_minutes = (
   9        SELECT MAX(watch_duration_minutes)
  10        FROM watch_log AS w2
  11        WHERE w2.user_id = w1.user_id
  12    );
  13-- 注意：此方法在时长并列时会返回多行，需要额外处理
  14-- 例如用 DISTINCT ON (PostgreSQL) 或再包一层 GROUP BY

• 复杂度: 如果没有索引，对于 watch_log 中的每一行，子查询都要执行一次，复杂度接近 $O(N^2)$。
• 索引依赖: 必须在 (user_id, watch_duration_minutes) 上建立复合索引。有了这个索引，子查询的 MAX() 操作可以非常快地完成（索引扫描），性能会大幅提升，接近 $O(N \log N)$。

常见低分回答

1. 错误的 GROUP BY 用法：这是最常见的错误，尤其对于刚从旧版 MySQL（ONLY_FULL_GROUP_BY 关闭）过来的开发者。
   sql

   ⧉⛶

    1-- 错误！anchor_id 是一个非聚合列，它的值是随机的，不一定对应最大时长
    2SELECT
    3    user_id,
    4    anchor_id,
    5    MAX(watch_duration_minutes)
    6FROM
    7    watch_log
    8GROUP BY
    9    user_id;

2. 忽略时长并列问题：解法 2 和 3 如果不加额外处理（如 GROUP BY 或 LIMIT 1），在最大时长有多条记录时会返回多行，这可能不符合“求最长一次”的题意。面试官会认为你考虑不周。
3. 滥用 HAVING：试图用 HAVING 子句来代替 WHERE 进行行级过滤。
   sql

   ⧉⛶

    1-- 逻辑错误，HAVING 用于对 GROUP BY 的聚合结果进行过滤
    2SELECT user_id, MAX(watch_duration_minutes)
    3FROM watch_log
    4GROUP BY user_id
    5HAVING watch_duration_minutes = MAX(watch_duration_minutes); -- watch_duration_minutes 在此作用域不可用

4. 对 RANK() 和 ROW_NUMBER() 不加区分：如果使用 RANK() 或 DENSE_RANK() 并在外层 WHERE rank = 1，在时长并列时同样会返回多行。虽然有时业务需求如此，但若题目要求唯一行，则 ROW_NUMBER() 是更准确的选择。

工程实践与优化

• 索引是性能的关键：对于千万级以上的表，必须在 (user_id, watch_duration_minutes DESC) 上建立复合索引。这个索引对于三种解法都有巨大提升：
  • 窗口函数：可以直接利用索引的有序性，极大减少排序开销。
  • JOIN 子查询：GROUP BY 和 JOIN 条件都能高效利用该索引。
  • 关联子查询：子查询的 MAX() 操作可以变成一个快速的索引定位操作。
• 执行计划分析：使用 EXPLAIN / EXPLAIN ANALYZE 查看执行计划。
  • 窗口函数解法：关注是否出现 Window: row_number，以及之前的 Sort 操作成本是否高。如果索引生效，可能看不到 filesort。
  • JOIN 解法：关注子查询是否走了 Index Only Scan 或 Loose Index Scan，以及 JOIN 的类型是否为高效的 Hash Join 或 Index Nested Loop Join。
  • 关联子查询解法：检查 Extra 字段，避免出现 Dependent subquery 导致的高成本重复执行。
• 处理数据倾斜：如果某个 user_id（如平台测试账号）有海量的观看记录，会导致 PARTITION BY user_id 时单个分区过大，拖慢整个查询。在离线数仓场景中，可以对这类 key 进行特殊处理（如单独计算或加随机盐打散）。
• ** tie-breaking（结果确定性）**：在生产环境中，结果必须是确定的。如果时长并列，业务上需要明确一个第二排序规则，比如“选择最近观看的”或“选择主播 ID 最小的”。这可以在 ORDER BY 子句中体现。
  sql

  ⧉⛶

   1-- 增加 session_time 作为第二排序键，保证结果唯一
   2ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY watch_duration_minutes DESC, session_time DESC) AS rn

常见追问与延伸题目

1. 追问：如果题目改成“求每个用户观看时长 Top 3 的主播和时长”，你的 SQL 怎么修改？

   • 回答要点：这正是窗口函数的优势所在。只需将解法 1 中的 WHERE rn = 1 修改为 WHERE rn <= 3 即可。而对于 JOIN 或关联子查询的解法，实现起来会变得异常复杂和低效，这凸显了窗口函数在解决 Top-N 问题上的优越性和扩展性。

2. 追问：如果这张 watch_log 表有上百亿行，在 MySQL 上跑这个实时查询太慢了，有什么优化思路？

   • 回答要点：这考察的是数据架构能力。
     • 离线批处理：将数据同步到大数据平台（如 Hive, Spark, MaxCompute）。每天或每小时运行一次批处理任务，将结果（每个用户的最长观看记录）计算好，存入一个轻量的结果表（如 MySQL 或 Redis）中，供线上业务查询。这是典型的数据仓库/数据湖架构。
     • 实时计算：如果要求结果的实时性非常高，可以使用流处理引擎（如 Flink, Spark Streaming）。实时消费观看日志流，在内存或状态后端中为每个用户维护一个“当前最大观看时长和对应主播”的状态。当有新的观看记录时，更新这个状态。
     • 预聚合/物化视图：在数据库层面，可以创建一个聚合表 user_max_watch_stats，通过定时任务或触发器（不推荐高频写入场景）来更新。查询时直接查这张结果表，避免对原始大表进行复杂计算。

3. 追问：刚才你提到了 ROW_NUMBER、RANK 和 DENSE_RANK，请具体说明它们在本题场景下的差异。

   • 回答要点：
     • ROW_NUMBER(): 无论 watch_duration_minutes 是否相同，都会给出连续的排名（1, 2, 3, 4...）。使用 WHERE rn = 1 能确保每个用户严格只返回一行，即使存在时长并列的情况（会根据物理存储或额外排序键随机选择一个）。这符合题目“最长一次”的字面要求。
     • RANK(): 时长相同时排名相同，但会跳过后续排名。例如，两个60分钟的记录都会被标记为 rank=1，下一个时长（如45分钟）的记录会被标记为 rank=3。使用 WHERE rnk = 1 会将所有最大时长的记录都返回。
     • DENSE_RANK(): 与 RANK() 类似，时长相同时排名相同，但不会跳过后续排名。两个60分钟的记录是 rank=1，下一个45分钟的记录是 rank=2。使用 WHERE drnk = 1 同样会返回所有最大时长的记录。
     • 总结：如果产品需求是“找出最长的那一次观看”，用 ROW_NUMBER。如果需求是“找出所有时长并列为最长的观看记录”，则用 RANK 或 DENSE_RANK。面试时清晰地指出这一点，会体现你的严谨性。