求每个用户的首次播放视频与最后一次播放视频。

考察要点

这道题是典型 "分组 Top-N" 问题的变种，面试官想考察你是否能熟练运用 SQL 来解决分组内排序和聚合的问题。具体来说，主要考察以下两点：

1. 窗口函数的掌握程度：是否能熟练使用 ROW_NUMBER()、FIRST_VALUE() 等窗口函数，这是解决此类问题的现代、高效且标准的方法。
2. 复杂查询的构建能力：能否使用传统 GROUP BY 结合 JOIN 或子查询等方式解决问题，这体现了你对 SQL 执行逻辑和多种解法思路的理解深度。

核心概念

解决本题的核心是为每个用户的播放记录进行排序，并从中抽取出第一条和最后一条。

1. 模拟数据

假设我们有一张用户视频播放日志表 user_play_logs。

DDL 示例:

 1CREATE TABLE user_play_logs (
 2    log_id INT PRIMARY KEY AUTO_INCREMENT,
 3    user_id INT NOT NULL,
 4    video_id INT NOT NULL,
 5    play_ts DATETIME NOT NULL,
 6    INDEX idx_user_play (user_id, play_ts)
 7);

数据示例:

期望输出:

2. 核心 SQL 概念

• 窗口函数 (Window Functions): 与聚合函数（如 SUM, COUNT）将多行聚合成一行不同，窗口函数为结果集的每一行计算一个值。

  • ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY play_ts ASC): 对每个 user_id 内部的数据，按照 play_ts 升序进行排名，排名从 1 开始，连续不重复。这是定位 "第一条" 的利器。
  • FIRST_VALUE(video_id) OVER (PARTITION BY ...): 获取窗口内排序后的第一个 video_id。注意：需要正确设置窗口帧 (Window Frame)，否则 LAST_VALUE 会产生非预期的结果。

• 公共表表达式 (Common Table Expression - CTE): 使用 WITH 关键字定义的临时结果集，可以提高复杂 SQL 的可读性和模块化。例如 WITH ranked_plays AS (...) SELECT ... FROM ranked_plays。

• 条件聚合 (Conditional Aggregation): 在 GROUP BY 聚合时，使用 CASE WHEN 语句来根据条件选择性地聚合数据。

  sql

  ⧉⛶

   1-- 示例：将排名为1的video_id作为first_video_id
   2MAX(CASE WHEN rnk_asc = 1 THEN video_id ELSE NULL END) AS first_video_id

多种解法

解法 1：使用窗口函数 ROW_NUMBER()（推荐）

这是最高效、最灵活的解法，也是面试官最期待听到的答案。

• 思路：

  1. 使用 CTE，通过 ROW_NUMBER() 对每个用户的播放记录按时间正序和倒序分别进行排名。
  2. rnk_asc = 1 的记录即为首次播放，rnk_desc = 1 的记录即为最后一次播放。
  3. 从 CTE 中筛选出 rnk_asc = 1 或 rnk_desc = 1 的记录。
  4. 按 user_id 分组，使用条件聚合将首次和最后一次的 video_id 合并到同一行。

• 版本兼容性：✅ MySQL 8.0+ / PostgreSQL / Oracle / SQL Server；❌ MySQL 5.7 及以下版本不支持。

• SQL 实现：

  sql

  ⧉⛶

   1WITH ranked_plays AS (
   2    SELECT
   3        user_id,
   4        video_id,
   5        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY play_ts ASC) AS rnk_asc,
   6        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY play_ts DESC) AS rnk_desc
   7    FROM
   8        user_play_logs
   9)
  10SELECT
  11    user_id,
  12    MAX(CASE WHEN rnk_asc = 1 THEN video_id END) AS first_video_id,
  13    MAX(CASE WHEN rnk_desc = 1 THEN video_id END) AS last_video_id
  14FROM
  15    ranked_plays
  16WHERE
  17    rnk_asc = 1 OR rnk_desc = 1
  18GROUP BY
  19    user_id
  20ORDER BY
  21    user_id;

• 复杂度：

  • 时间复杂度：$O(N \log N)$，主要开销在于 PARTITION BY 内部的排序。如果数据已按 (user_id, play_ts) 索引，数据库可以利用索引顺序，避免全量排序，性能更优。
  • 空间复杂度：$O(N)$，需要存储窗口函数计算的中间结果。

• 索引依赖：复合索引 (user_id, play_ts) 对此查询有巨大性能提升。

解法 2：使用 GROUP BY 和 JOIN

这是最经典、兼容性最好的解法，能体现你扎实的 SQL 基础。

• 思路：

  1. 使用 GROUP BY 找到每个用户的 MIN(play_ts) 和 MAX(play_ts)。
  2. 将原始日志表与第 1 步的结果进行两次 LEFT JOIN：
     • 第一次 JOIN 基于 user_id 和 play_ts = min_ts，找到 first_video_id。
     • 第二次 JOIN 基于 user_id 和 play_ts = max_ts，找到 last_video_id。
  3. 使用 GROUP BY 聚合最终结果。这里需要注意处理时间戳重复（tie）的情况。

• 版本兼容性：✅ 所有主流数据库版本。

• SQL 实现：

  sql

  ⧉⛶

   1WITH user_first_last_ts AS (
   2    SELECT
   3        user_id,
   4        MIN(play_ts) AS min_ts,
   5        MAX(play_ts) AS max_ts
   6    FROM
   7        user_play_logs
   8    GROUP BY
   9        user_id
  10)
  11SELECT
  12    t.user_id,
  13    -- 使用 MIN(video_id) 作为 tie-breaker
  14    MIN(first_log.video_id) AS first_video_id,
  15    MIN(last_log.video_id) AS last_video_id
  16FROM
  17    user_first_last_ts t
  18JOIN
  19    user_play_logs first_log ON t.user_id = first_log.user_id AND t.min_ts = first_log.play_ts
  20JOIN
  21    user_play_logs last_log ON t.user_id = last_log.user_id AND t.max_ts = last_log.play_ts
  22GROUP BY
  23    t.user_id
  24ORDER BY
  25    t.user_id;

  注意：如果同一用户在最早/最晚时间点播放了多个视频，上述 JOIN 会产生笛卡尔积。GROUP BY 和 MIN() 用于去重和 tie-breaking，确保每个用户只返回一行。

• 复杂度：

  • 时间复杂度：取决于实现和索引。通常是 $O(U + 2 \times \text{JoinCost})$，其中 U 是用户数。有 (user_id, play_ts) 索引时，GROUP BY 和 JOIN 都能高效利用索引，性能很好。
  • 空间复杂度：$O(U)$，用于存储 GROUP BY 的中间结果。

• 索引依赖：复合索引 (user_id, play_ts) 极度重要，能让子查询和 JOIN 都走上索引扫描，避免全表扫描。

常见低分回答

1. 误用 GROUP BY：试图在一次 GROUP BY 中同时获取非聚合列，依赖 MySQL 的非标准行为。
   sql

   ⧉⛶

    1-- 错误示例: video_id 不是聚合列，结果是随机的、不可靠的
    2SELECT user_id, video_id, MIN(play_ts), MAX(play_ts)
    3FROM user_play_logs
    4GROUP BY user_id;

2. 误用 FIRST_VALUE/LAST_VALUE：不理解窗口帧（Window Frame）的默认行为。
   sql

   ⧉⛶

    1-- 错误示例: LAST_VALUE 默认帧是 `RANGE ... CURRENT ROW`，结果是当前行的 video_id
    2SELECT DISTINCT
    3    user_id,
    4    FIRST_VALUE(video_id) OVER (PARTITION BY user_id ORDER BY play_ts) AS first_video,
    5    LAST_VALUE(video_id) OVER (PARTITION BY user_id ORDER BY play_ts) AS last_video -- 这里的 last_video 是错的
    6FROM user_play_logs;

   正确用法需要指定 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。
3. 使用低效的关联子查询：对主表的每一行都执行一次子查询，数据量大时性能极差。
   sql

   ⧉⛶

    1-- 性能灾难示例
    2SELECT
    3    u.user_id,
    4    (SELECT video_id FROM user_play_logs WHERE user_id = u.user_id ORDER BY play_ts ASC LIMIT 1) AS first_video,
    5    (SELECT video_id FROM user_play_logs WHERE user_id = u.user_id ORDER BY play_ts DESC LIMIT 1) AS last_video
    6FROM (SELECT DISTINCT user_id FROM user_play_logs) u;

4. 忽略时间戳重复（Tie）问题：JOIN 解法不考虑同一用户在最早/最晚时间点播放多个视频的情况，可能导致输出多行或错误结果，没有进行去重或确定性选择（tie-breaking）。

工程实践与优化

• 索引是性能的关键：对于此问题，INDEX(user_id, play_ts) 是最重要的优化。它能让 PARTITION BY 和 GROUP BY 操作直接利用有序数据，避免昂贵的 filesort。如果能建成覆盖索引 INDEX(user_id, play_ts, video_id)，则连回表都省了，性能达到极致。
• 执行计划分析：
  • 解法 1 (窗口函数)：EXPLAIN 结果中应关注 Extra 列是否出现 Using window function。理想情况下不应有 Using filesort。
  • 解法 2 (JOIN)：EXPLAIN 结果中，子查询应显示 Using index for group-by，JOIN 操作的 type 应为 ref 或 eq_ref，key 应为 idx_user_play。避免 type 为 ALL（全表扫描）。
• 大数据量下的考量：在字节跳动这种数据量级（每日百亿、千亿日志），直接在原始 OLTP 或日志表上跑这种查询是不可接受的。
  • 预聚合：真实的工程实践中，会通过离线数仓（如 Hive, Spark）或实时计算（如 Flink）对数据进行预处理。例如，每天凌晨跑一个批处理任务，计算出每个用户当天的首次/末次播放记录，并存入一个结果表（ODS/DWS 层）。查询时直接查这个小得多的结果表。
  • 数据分区：日志表本身会按时间（如天）进行分区。查询时可以指定分区，大大减少扫描的数据量。

常见追问与延伸题目

1. 追问：如果数据量增长 100 倍，你的 SQL 性能会怎样？如何优化？

   • 回答要点 1 (性能瓶颈)：两种解法的性能都会显著下降。窗口函数的 $O(N \log N)$ 排序成本和 JOIN 的 I/O 成本会变得无法接受。数据库单点会成为瓶颈。
   • 回答要点 2 (架构级优化)：必须放弃在单机数据库上实时计算的思路，转向分布式计算和预聚合。
     • 离线计算：使用 Spark/MapReduce，将数据按 user_id 分发到不同节点，在各节点内部完成排序和聚合，最后汇总结果。这是一种水平扩展的思路。
     • 实时/准实时计算：引入 Flink 或 Spark Streaming，当播放事件进入消息队列（如 Kafka）时，流处理程序实时维护每个 user_id 的状态（当前已知的首次/末次播放信息）。查询时直接从状态存储（如 Redis, HBase）中获取结果，达到毫秒级响应。

2. 追问：如果要查询每个用户播放次数排名前 3 的视频呢？

   • 回答要点：这个问题是 "分组 Top-N" 的标准形式，更能体现窗口函数的优势。
     • 最佳方案：先用 COUNT(*) 聚合每个用户对每个视频的播放次数，然后用 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY play_count DESC) 对播放次数进行排名，最后筛选出排名 rnk <= 3 的记录。
     • 对比：用传统 JOIN 和子查询来实现这个需求会变得异常复杂和低效，这恰好反衬出窗口函数的简洁与强大。

3. 追问：如果换成 PostgreSQL 或 Oracle，你的答案有什么不同？

   • 回答要点：
     • SQL 语法：核心逻辑和 SQL 语法基本通用。解法 1（窗口函数）和解法 2（GROUP BY+JOIN）在 PostgreSQL 和 Oracle 中都完全支持，且是标准用法。
     • 函数差异：可能会有一些细微的函数名或语法差异，但 ROW_NUMBER、MIN、MAX 等都是 SQL 标准函数，通用性极好。Oracle 中还有 KEEP (DENSE_RANK FIRST/LAST ...) 这种更直接的语法来解决此类问题，但 ROW_NUMBER 更通用。
     • 性能与优化：底层的执行计划、优化器策略、索引实现会有所不同，但优化的总原则（如使用索引避免排序、减少 I/O）是相通的。例如，PostgreSQL 的 EXPLAIN ANALYZE 会提供更详尽的执行细节。但对于面试中的 SQL 写法层面，答案基本不变。