滴滴 2020 年校招真题：求每位司机一周内每天接单量的中位数。

考察要点

这道题是一道典型的复杂分析类 SQL 问题，面试官想考察你是否具备将一个业务问题拆解为多个 SQL 计算步骤的能力。具体来说，考察点包括：

1. 逻辑拆解能力：能否将“求中位数”这个非直接聚合操作，分解为“先按天聚合求量”和“再对求出的量进行排序和定位”两个步骤。
2. 窗口函数：是否熟练掌握现代 SQL (MySQL 8.0+, PostgreSQL) 提供的窗口函数，特别是用于排序 (ROW_NUMBER, RANK) 和百分位计算 (PERCENTILE_CONT) 的函数，这是解决此类问题的最高效、最优雅的方案。
3. 兼容性与备选方案：在不持支窗口函数的旧版本数据库（如 MySQL 5.7）中，是否能利用用户变量或自连接等技巧，实现同样的功能。这能体现候选人的知识广度和解决问题的灵活性。

核心概念

要解决此问题，首先需要对“中位数”和实现它的 SQL 工具集有清晰的认识。

1. 中位数 (Median)

中位数是将一个数值集合划分为数量相等的上下两部分，居于中间位置的那个数值。

• 奇数个元素：中位数是排序后最中间的那个数。例如，对于集合 {2, 4, 1, 5, 3}，排序后为 {1, 2, **3**, 4, 5}，中位数为 3。
• 偶数个元素：中位数是排序后中间两个数的平均值。例如，对于集合 {2, 6, 1, 5, 3, 8}，排序后为 {1, 2, **3**, **5**, 6, 8}，中位数为 $(3+5)/2 = 4$。

本题中，“一周内”通常指 7 天，是奇数个，但严谨的解法应同时考虑数据缺失导致天数可能为偶数的情况。

2. 窗口函数 (Window Functions)

窗口函数对与当前行相关的行集（即“窗口”）进行计算，但不会像 GROUP BY 那样将多行合并为一行，而是为结果集中的每一行都返回一个计算值。

语法结构: FUNCTION_NAME() OVER (PARTITION BY ... ORDER BY ...)

• PARTITION BY driver_id: 定义窗口的范围。在这里，表示对每个司机的数据独立进行计算。
• ORDER BY daily_order_count: 定义窗口内行的顺序。这是计算中位数、排名等顺序相关指标的基础。

本题关键函数:

• PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col): 这是计算中位数的标准函数（MySQL 8.0+ 支持）。它计算一个连续分布下的指定百分位数（0.5 即中位数）。
• ROW_NUMBER(): 为窗口内的每一行分配一个从 1 开始的唯一排名。
• COUNT(*) OVER (PARTITION BY ...): 计算窗口内的总行数。

3. 用户自定义变量 (MySQL 5.7 及之前)

在 MySQL 8.0 之前，没有标准的窗口函数。可以通过用户变量来模拟行号的生成。

 1-- 模拟 ROW_NUMBER()
 2SELECT
 3    daily_order_count,
 4    @row_num := IF(@prev_driver_id = driver_id, @row_num + 1, 1) AS rn,
 5    @prev_driver_id := driver_id
 6FROM
 7    (SELECT driver_id, daily_order_count FROM ... ORDER BY driver_id, daily_order_count) AS sorted_counts,
 8    (SELECT @row_num := 0, @prev_driver_id := NULL) AS init

注意：用户变量的赋值顺序在查询中不被保证，依赖它是一种技巧，但在 MySQL 8.0+ 中已被官方不推荐，应优先使用窗口函数。

4. 数据示例

假设我们有如下 orders 表：

 1CREATE TABLE orders (
 2    order_id INT,
 3    driver_id INT,
 4    order_time DATETIME
 5);
 6-- 假设 driver_id=101 在 2023-01-01 接了 3 单，2023-01-02 接了 5 单 ... 2023-01-07 接了 8 单

第一步：计算每日单量

 1SELECT
 2    driver_id,
 3    DATE(order_time) AS order_date,
 4    COUNT(order_id) AS daily_order_count
 5FROM orders
 6WHERE order_time BETWEEN '2023-01-01' AND '2023-01-07 23:59:59'
 7GROUP BY driver_id, DATE(order_time);

中间结果 (以 driver_id=101 为例):

第二步：求中位数 对 daily_order_count 列表 {3, 5, 2, 7, 4, 6, 8} 求中位数。 排序后为 {2, 3, 4, **5**, 6, 7, 8}。 最终结果: driver_id 101 的中位数为 5。

多种解法

解法 1：使用 PERCENTILE_CONT 窗口函数（最优解）

这是最直接、最符合 SQL 语义的现代解法。

• 版本：✅ MySQL 8.0+ / PostgreSQL / Oracle；❌ MySQL 5.7
• SQL 代码：

 1WITH daily_counts AS (
 2    -- 步骤1: 计算每位司机每天的订单量
 3    SELECT
 4        driver_id,
 5        COUNT(order_id) AS daily_order_count
 6    FROM
 7        orders
 8    -- 假设我们关注特定的一周
 9    WHERE order_time >= '2020-01-01' AND order_time < '2020-01-08'
10    GROUP BY
11        driver_id,
12        DATE(order_time)
13)
14-- 步骤2: 使用 PERCENTILE_CONT 计算每个司机单量列表的中位数
15SELECT
16    driver_id,
17    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY daily_order_count) OVER (PARTITION BY driver_id) AS median_orders
18FROM
19    daily_counts
20GROUP BY -- 使用 GROUP BY 去除每个分区内的重复结果
21    driver_id;

注：PERCENTILE_CONT 是窗口函数，但其 WITHIN GROUP 语法通常与 GROUP BY 结合使用以对每个分组产生一个结果。

• 时间/空间复杂度：
  • daily_counts CTE: GROUP BY 过程，如果使用哈希聚合，复杂度为 $O(N)$，其中 N 是 orders 表中一周内的记录数。
  • PERCENTILE_CONT: 需要对每个分区（每个司机的数据）进行排序，设司机数量为 M，平均每个司机有 D 天数据（本题 D≈7），则此步复杂度为 $O(M \cdot D \log D)$。
  • 整体复杂度主要由第一步的 I/O 和聚合决定。
• 索引依赖：在 orders 表上建立 (driver_id, order_time) 的复合索引，可以极大地加速 GROUP BY 过程，可能触发 "Index Loose Scan"，性能极佳。

解法 2：使用 ROW_NUMBER 和 COUNT 窗口函数（通用解）

此方法不直接计算中位数，而是通过排序编号和总数来定位中位数所在行。

• 版本：✅ MySQL 8.0+ / PostgreSQL；❌ MySQL 5.7
• SQL 代码：

 1WITH daily_counts AS (
 2    -- 步骤1: 同解法1
 3    SELECT
 4        driver_id,
 5        COUNT(order_id) AS daily_order_count
 6    FROM orders
 7    WHERE order_time >= '2020-01-01' AND order_time < '2020-01-08'
 8    GROUP BY driver_id, DATE(order_time)
 9),
10ranked_counts AS (
11    -- 步骤2: 对每个司机的每日单量进行排序并计算总天数
12    SELECT
13        driver_id,
14        daily_order_count,
15        ROW_NUMBER() OVER (PARTITION BY driver_id ORDER BY daily_order_count) AS rn,
16        COUNT(*) OVER (PARTITION BY driver_id) AS total_days
17    FROM daily_counts
18)
19-- 步骤3: 找出中位数所在行并计算平均值（兼容奇偶）
20SELECT
21    driver_id,
22    AVG(daily_order_count) AS median_orders
23FROM
24    ranked_counts
25WHERE
26    -- 如果总天数是7, 中间行是第4行, (7+1)/2=4, (7+2)/2=4.5, rn=4
27    -- 如果总天数是6, 中间行是第3,4行, (6+1)/2=3.5, (6+2)/2=4, rn in (3,4)
28    rn BETWEEN FLOOR((total_days + 1) / 2.0) AND CEIL((total_days + 1) / 2.0)
29GROUP BY
30    driver_id;

• 时间/空间复杂度：与解法 1 类似。窗口函数的计算开销主要在排序上。
• 索引依赖：同解法 1，orders(driver_id, order_time) 索引至关重要。

解法 3：使用用户变量（兼容 MySQL 5.7）

这是一种模拟窗口函数的 "hack" 方法，在老版本中很常见。

• 版本：✅ MySQL 5.7；⚠️ 在 MySQL 8.0+ 中不推荐
• SQL 代码：

 1SELECT
 2    driver_id,
 3    AVG(daily_order_count) AS median_orders
 4FROM (
 5    SELECT
 6        s.driver_id,
 7        s.daily_order_count,
 8        -- 模拟 ROW_NUMBER()
 9        @row_num := IF(@prev_driver_id = s.driver_id, @row_num + 1, 1) AS rn,
10        @prev_driver_id := s.driver_id
11    FROM
12        ( -- 必须先对数据排序，确保变量计算的正确性
13            SELECT
14                driver_id,
15                COUNT(order_id) AS daily_order_count
16            FROM orders
17            WHERE order_time >= '2020-01-01' AND order_time < '2020-01-08'
18            GROUP BY driver_id, DATE(order_time)
19            ORDER BY driver_id, daily_order_count
20        ) AS s,
21        (SELECT @row_num := 0, @prev_driver_id := NULL) AS init
22) AS ranked
23JOIN (
24    -- 计算每个司机的总天数
25    SELECT
26        driver_id,
27        COUNT(*) AS total_days
28    FROM (
29        SELECT driver_id, DATE(order_time)
30        FROM orders
31        WHERE order_time >= '2020-01-01' AND order_time < '2020-01-08'
32        GROUP BY driver_id, DATE(order_time)
33    ) AS days
34    GROUP BY driver_id
35) AS totals ON ranked.driver_id = totals.driver_id
36WHERE
37    ranked.rn BETWEEN FLOOR((totals.total_days + 1) / 2.0) AND CEIL((totals.total_days + 1) / 2.0)
38GROUP BY
39    driver_id;

• 复杂度与索引：此方法更复杂，通常性能不如原生窗口函数。它需要多次扫描和排序，并且 JOIN 操作增加了额外的开销。索引依赖依然是 orders(driver_id, order_time)。

常见低分回答

1. 误将中位数当成平均数：直接使用 AVG(COUNT(...)) 是错误的。SELECT driver_id, AVG(daily_order_count) FROM ... 计算的是平均值，不是中位数。
2. 逻辑混乱，无法分步：试图在一个 SELECT 语句中用复杂的 HAVING 或 WHERE 子句一次性解决问题，通常会导致逻辑错误且代码难以理解。
3. 使用 GROUP_CONCAT：SELECT driver_id, GROUP_CONCAT(daily_order_count ORDER BY daily_order_count) FROM ...。这种方法将每日单量拼接成一个字符串（如 '2,3,4,5,6,7,8'），然后打算在应用层代码中解析字符串并找到中位数。这是非常糟糕的实践，违反了 "在数据库中完成计算" 的原则，且 GROUP_CONCAT 有长度限制 (group_concat_max_len)。
4. 窗口函数 PARTITION BY 遗漏：ROW_NUMBER() OVER (ORDER BY daily_order_count) 忘记写 PARTITION BY driver_id，会导致对所有数据进行全局排序，而不是按司机分组，结果完全错误。
5. 用户变量用法错误：在使用用户变量时，没有在子查询中预先 ORDER BY，导致变量计算结果不可预测。这是最常见的坑。

工程实践与优化

• 数据量级与性能瓶颈：滴滴的订单表是千亿甚至万亿级别的。直接在主订单表上运行此查询是绝对不可行的，会导致数据库实例 I/O 和 CPU 飙升，引发线上故障。
• 解决方案：预计算与物化视图：
  1. 创建日/周粒度的汇总表：在生产环境中，我们会创建一个 driver_daily_stats (司机每日统计) 表。
  sql

  ⧉⛶

   1CREATE TABLE driver_daily_stats (
   2    stat_date DATE,
   3    driver_id BIGINT,
   4    order_count INT,
   5    -- 其他指标...
   6    PRIMARY KEY (driver_id, stat_date)
   7);

  2. 离线计算：通过离线大数据平台（如 Spark, Flink, Hive）每天凌晨定时任务，计算前一天的 driver_daily_stats 并写入该表。
  3. 在线查询：面试中的 SQL 查询将直接作用于这个小得多的汇总表 driver_daily_stats，查询性能会从几小时降低到毫秒级。
• 执行计划分析 (EXPLAIN)：
  • 对于原始查询，关注第一步 GROUP BY。理想的 EXPLAIN 结果中 Extra 字段应显示 Using index for group-by (或称 Loose Index Scan)，表示利用索引直接按组读取，避免了全表扫描和文件排序。
  • 如果 Extra 显示 Using temporary; Using filesort，说明无法有效利用索引，性能会很差。需要检查索引设计和查询语句。
• 避免锁和资源消耗：这种分析型查询（AP）应在只读从库 (Read Replica) 上执行，严禁在处理在线交易（OLTP）的主库上运行，以防长时间查询锁住资源，影响核心业务。

常见追问与延伸题目

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

   • 回答要点 1 (性能瓶颈)：性能会急剧下降，甚至无法在规定时间内跑出结果。瓶颈在于对海量 orders 表的全范围扫描和聚合 (GROUP BY)，这会消耗巨大的 I/O 和 CPU 资源。
   • 回答要点 2 (终极方案)：必须采用预计算的方案。我会设计一个数据仓库（Data Warehouse）或数据集市（Data Mart）架构。通过 ETL/ELT 流程，将原始订单数据（OLTP）清洗、转换并聚合到 OLAP（在线分析处理）系统中。具体就是上面提到的，用 Spark/Flink 任务每天生成 driver_daily_stats 汇总表。所有分析类查询都基于此汇总表进行，实现与原始数据量的解耦。

2. 追问：除了中位数，如果要计算众数（Mode，出现次数最多的单量），SQL 怎么写？

   • 回答要点：众数需要两层聚合。第一层计算每日单量，第二层计算每个单量出现的次数，然后排序取第一。
   • SQL 示例 (使用窗口函数)：
     sql

     ⧉⛶

      1WITH daily_counts AS (
      2    -- ... 同上 ...
      3),
      4count_of_counts AS (
      5    SELECT
      6        driver_id,
      7        daily_order_count,
      8        COUNT(*) AS freq -- 计算每个单量值出现的次数
      9    FROM daily_counts
     10    GROUP BY driver_id, daily_order_count
     11),
     12ranked_freq AS (
     13    SELECT
     14        driver_id,
     15        daily_order_count,
     16        -- 按频率降序排名
     17        RANK() OVER (PARTITION BY driver_id ORDER BY freq DESC) as rnk
     18    FROM count_of_counts
     19)
     20SELECT driver_id, daily_order_count AS mode_orders
     21FROM ranked_freq
     22WHERE rnk = 1;

   • 关键点：要用 RANK() 或 DENSE_RANK() 而不是 ROW_NUMBER()，因为众数可能不唯一。

3. 追问：如果换成 PostgreSQL 或其他数据库，答案有什么不同？

   • 回答要点 1 (PostgreSQL)：PostgreSQL 对窗口函数的支持非常完善且性能优秀。解法 1 (PERCENTILE_CONT) 和解法 2 (ROW_NUMBER) 在 PostgreSQL 中都是标准和高效的写法，完全适用。
   • 回答要点 2 (ClickHouse/Doris/StarRocks 等 OLAP 数据库)：这些列式存储数据库为分析查询做了极致优化。它们通常内置了更丰富的聚合函数，比如 median() 或 quantile()。在 ClickHouse 中，可以直接写 median(daily_order_count)，语法更简洁，性能也远超 MySQL 这类行式数据库。这再次印证了为不同场景选择合适工具的重要性。
   • 回答要点 3 (通用性)：基于 CTE 和标准窗口函数（如 ROW_NUMBER）的解法 2 是最具通用性的，在大多数现代关系型数据库中都能运行。而 PERCENTILE_CONT 的语法细节或可用性可能略有差异。用户变量法则基本是 MySQL 特有的历史遗留方案。