出租车订单分析：求每个司机平均每单的接驾距离与等待时长。

考察要点

这道题面试官想看你是否具备将业务需求转化为 SQL 查询的能力，核心是考察对聚合函数（AVG）和分组查询（GROUP BY）的熟练运用。同时，通过对数据有效性的处理（如只统计已完成订单），也能展现候选人分析问题的严谨性和业务理解能力。

核心概念

要解决此问题，首先需要理解以下几个核心 SQL 概念：

1. 数据表结构（Schema） 面试官通常会先让你假设一个表结构。一个合理的出租车订单表（orders）可能如下：

2. GROUP BY 子句 GROUP BY 用于将数据集按照一列或多列进行分组，所有具有相同值的行会被分到同一组。在本题中，我们需要按 driver_id 分组，为每个司机计算其名下所有订单的指标。

   mermaid

   ⧉⛶

    1graph TD
    2    subgraph 原始订单表 (orders)
    3        O1(o1, d1, 2.1km, 5min)
    4        O2(o2, d2, 1.5km, 2min)
    5        O3(o3, d1, 3.2km, 10min)
    6        O4(o4, d2, 0.8km, 0min)
    7        O5(o5, d1, 1.0km, 3min)
    8    end
    9
   10    subgraph GROUP BY driver_id
   11        G1(司机 d1 的分组)
   12        G2(司机 d2 的分组)
   13    end
   14
   15    subgraph 聚合计算
   16        R1(AVG for d1)
   17        R2(AVG for d2)
   18    end
   19
   20    O1 --> G1
   21    O3 --> G1
   22    O5 --> G1
   23    O2 --> G2
   24    O4 --> G2
   25
   26    G1 --> R1
   27    G2 --> R2

3. 聚合函数 AVG() AVG(expression) 函数计算一个组内所有非 NULL 值的平均值。其内部计算逻辑等价于 SUM(expression) / COUNT(expression)。注意，COUNT(expression) 只统计 expression 不为 NULL 的行数。

4. WHERE 子句 在进行聚合计算前，通常需要过滤掉无效数据。例如，取消的订单不应计入司机的平均接驾距离。WHERE 子句在 GROUP BY 之前执行，用于筛选出符合条件的行。

多种解法

假设我们有以下 orders 表：

预期输出：

解法 1：标准 GROUP BY + AVG（最高效、最通用）

这是最直接、最高效的解法，利用 GROUP BY 对司机进行分组，然后用 AVG 函数计算每个组的平均值。

• 适用版本：所有 SQL 数据库（MySQL, PostgreSQL, SQL Server, Oracle 等）。
• SQL 代码：
  sql

  ⧉⛶

   1SELECT
   2    driver_id,
   3    AVG(pickup_distance) AS avg_pickup_distance,
   4    AVG(wait_time_minutes) AS avg_wait_time
   5FROM
   6    orders
   7WHERE
   8    order_status = 'completed' -- 过滤有效订单
   9GROUP BY
  10    driver_id;

• 时间/空间复杂度：
  • 时间复杂度：$O(N)$。数据库通常使用哈希聚合（Hash Aggregation），只需扫描一次表。如果数据量巨大，哈希表无法完全放入内存，可能需要 $O(N \log N)$ 的外部排序聚合。
  • 空间复杂度：$O(D)$，其中 D 是司机的数量，用于存储哈希表。
• 索引依赖：
  • 为了加速 WHERE 和 GROUP BY，可以在 (order_status, driver_id) 上建立复合索引。
  • 如果能建立覆盖索引 (order_status, driver_id, pickup_distance, wait_time_minutes)，查询性能会达到最优，因为数据库引擎只需扫描索引，无需回表查询主键。

解法 2：使用 CTE（通用表表达式）提高可读性

对于更复杂的分析，使用 CTE 可以让逻辑更清晰，将数据筛选和聚合计算步骤分开。虽然对于本题来说有点小题大做，但在面试中展示这种代码风格是加分项。

• 适用版本：✅ MySQL 8.0+, PostgreSQL, SQL Server, Oracle；❌ MySQL 5.7 不支持。
• SQL 代码：
  sql

  ⧉⛶

   1WITH CompletedOrders AS (
   2    -- 步骤1: 筛选出所有已完成的订单
   3    SELECT
   4        driver_id,
   5        pickup_distance,
   6        wait_time_minutes
   7    FROM
   8        orders
   9    WHERE
  10        order_status = 'completed'
  11)
  12-- 步骤2: 对筛选后的数据进行聚合
  13SELECT
  14    driver_id,
  15    AVG(pickup_distance) AS avg_pickup_distance,
  16    AVG(wait_time_minutes) AS avg_wait_time
  17FROM
  18    CompletedOrders
  19GROUP BY
  20    driver_id;

• 时间/空间复杂度：与解法 1 完全相同。CTE 在大多数数据库中是逻辑上的语法糖，并不会物化中间结果（除非特殊指定），优化器会将其展开为与解法 1 类似的执行计划。
• 索引依赖：与解法 1 完全相同。

常见低分回答

1. 忘记 GROUP BY：直接使用 AVG() 会计算全表的平均值，而不是每个司机的。
   sql

   ⧉⛶

    1-- 错误: 返回一行，即所有已完成订单的全局平均值
    2SELECT AVG(pickup_distance), AVG(wait_time_minutes) FROM orders WHERE order_status = 'completed';

2. GROUP BY 的列不正确：按 order_id 分组是无意义的，因为每个 order_id 都是唯一的。
   sql

   ⧉⛶

    1-- 错误: 每个订单自成一组，AVG 计算结果就是其本身的值
    2SELECT driver_id, AVG(pickup_distance) FROM orders GROUP BY order_id;

3. 不处理无效数据：将已取消或异常的订单也计入统计，导致结果不准确，体现了业务理解的缺失。
   sql

   ⧉⛶

    1-- 错误: 将 'cancelled' 订单也纳入计算
    2SELECT driver_id, AVG(pickup_distance) FROM orders GROUP BY driver_id;

4. 对 NULL 值的处理不当：没有意识到 AVG 会自动忽略 NULL 值。如果业务要求将 NULL 视为 0，则需要显式处理。
   sql

   ⧉⛶

    1-- 错误(如果业务要求NULL算作0): AVG(pickup_distance) 会忽略 order_id=4 的记录
    2-- 正确(如果业务要求NULL算作0): AVG(COALESCE(pickup_distance, 0))

5. 在 SELECT 中包含非聚合列：在 SELECT 列表中选择了既没有在 GROUP BY 子句中，也没有在聚合函数中的列。这在标准 SQL 中是禁止的（MySQL 在 ONLY_FULL_GROUP_BY 模式关闭时允许，但返回的值不确定，是危险行为）。
   sql

   ⧉⛶

    1-- 错误: created_at 既不在 GROUP BY 中，也不是聚合函数
    2SELECT driver_id, AVG(pickup_distance), created_at FROM orders GROUP BY driver_id;

工程实践与优化

• 索引是性能的关键：对于千万级以上的 orders 表，必须有合适的索引。EXPLAIN 分析时，type 字段应避免 ALL（全表扫描）。理想的执行计划 Extra 字段会显示 Using index for group-by 或 Using index（覆盖索引）。
• 预聚合（Pre-aggregation）：当数据量达到亿级或更高时，即使有索引，实时聚合查询也可能对线上 OLTP 数据库造成压力。最佳实践是：
  • 创建一个司机指标日/小时快照表，如 driver_stats_daily。
  • 通过离线任务（如 Spark/Flink 或数据库定时任务）每天/每小时计算好每个司机的各项指标，并存入该快照表。
  • 前端查询直接访问这个小得多的快照表，响应速度极快，且不影响主业务库。
• 读写分离：对于高并发的读取需求，可以将此类分析查询路由到只读副本（Read Replica）上执行，避免对主库（Primary）的写入性能产生影响。
• 查看执行计划：使用 EXPLAIN 来分析查询性能瓶颈。
  sql

  ⧉⛶

   1EXPLAIN SELECT driver_id, AVG(pickup_distance) ... ;

  关注 type, key, rows, Extra 等字段。如果 Extra 中出现 Using temporary; Using filesort，说明 MySQL 无法利用索引完成 GROUP BY，而是创建了临时表并进行了文件排序，这是严重的性能瓶颈，需要优化索引或查询。

常见追问与延伸题目

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

   • 回答要点 1 (性能瓶颈)：直接查询将变得不可行。数据库需要扫描海量数据，GROUP BY 操作会消耗巨大内存和 CPU，甚至因为内存不足而使用磁盘临时文件进行排序（filesort），导致查询时间从秒级飙升到分钟级甚至小时级，并可能拖垮整个数据库。
   • 回答要点 2 (优化方案)：必须放弃实时计算，转向预计算/数据仓库方案。
     • 批处理预聚合：建立数据仓库（如 Hive, ClickHouse），通过每日或每小时的 ETL 任务，将 OLTP 数据库（如 MySQL）的增量数据同步过来，并计算好每个司机的聚合指标，存入结果表。业务查询直接访问这个结果表。
     • 流式计算：对于需要近实时指标的场景，可以引入流处理引擎（如 Flink, Spark Streaming）。订单数据通过消息队列（如 Kafka）实时进入流处理系统，在内存中进行窗口聚合，并将结果实时更新到 KV 存储（如 Redis）或分析型数据库中。

2. 追问：如何找出平均接驾距离最长的前 10 名司机？

   • 回答要点：这考察了对聚合结果的再处理能力。可以在原查询的基础上，使用 ORDER BY 和 LIMIT 子句。
   sql

   ⧉⛶

    1SELECT
    2    driver_id,
    3    AVG(pickup_distance) AS avg_pickup_distance
    4FROM
    5    orders
    6WHERE
    7    order_status = 'completed'
    8GROUP BY
    9    driver_id
   10ORDER BY
   11    avg_pickup_distance DESC -- 按平均接驾距离降序排序
   12LIMIT 10; -- 取前10条

3. 追问：如果 pickup_distance 列中存在 NULL，AVG 函数会如何处理？如果业务要求将 NULL 当作 0 来计算平均值，SQL 该怎么写？

   • 回答要点 1 (NULL 的处理)：SQL 的聚合函数（SUM, AVG, COUNT(column), MAX, MIN）在计算时会忽略 NULL 值。所以 AVG(pickup_distance) 的计算公式是 SUM(pickup_distance) / COUNT(pickup_distance)，其中 COUNT 只统计 pickup_distance 不为 NULL 的行数。
   • 回答要点 2 (将 NULL 视为 0)：如果业务需要将 NULL 视为 0，必须使用 COALESCE 或 IFNULL 函数进行显式转换。
   sql

   ⧉⛶

    1-- 使用 COALESCE (标准SQL，更通用)
    2SELECT
    3    driver_id,
    4    AVG(COALESCE(pickup_distance, 0)) AS avg_pickup_distance
    5FROM
    6    orders
    7WHERE
    8    order_status = 'completed'
    9GROUP BY
   10    driver_id;
   11
   12-- 在 MySQL 中也可以用 IFNULL
   13-- AVG(IFNULL(pickup_distance, 0))

   这个追问能很好地考察候选人对 NULL 这个 SQL 核心概念的理解深度。