Uber 面试：求每个城市每小时的供需缺口（订单 vs 司机数）。

考察要点

这道题面试官想看你是否懂：

1. 时间序列数据处理：如何从 DATETIME 或 TIMESTAMP 类型中提取和聚合时间窗口（例如，按小时）。
2. 多表关联与数据整合：如何将两个独立的数据源（订单代表需求，司机代表供给）通过 JOIN 准确地关联起来，并处理好没有匹配的场景。
3. 聚合函数的精确使用：特别是 COUNT(DISTINCT ...) 的应用，以及在 JOIN 之后如何正确处理 NULL 值（使用 COALESCE 或 IFNULL）。

核心概念

本题的核心是将两个不同实体（订单、司机）的行为数据，在“城市”和“小时”这两个维度上进行对齐和汇总。

1. 模拟数据表

为了方便说明，我们假设有以下两张表：

orders (订单表 - 代表需求)

driver_logs (司机在线日志 - 代表供给)

2. 核心函数与子句

• 时间窗口切分: 我们需要将 request_time 和 log_time 归一化到小时级别。最常用的方法是使用 DATE_FORMAT (MySQL) 或 DATE_TRUNC (PostgreSQL)。
  sql

  ⧉⛶

   1-- MySQL
   2DATE_FORMAT(request_time, '%Y-%m-%d %H:00:00')
   3-- PostgreSQL
   4DATE_TRUNC('hour', request_time)

• 供给的定义: “每小时的司机数”通常指在该小时内有过“在线”记录的独立司机数量。这要求我们使用 COUNT(DISTINCT driver_id)。
• JOIN 的选择:
  • INNER JOIN: 只会返回在某个小时内既有订单又有在线司机的城市。这会丢失数据。
  • LEFT JOIN / RIGHT JOIN: 会保留一侧的全部数据。例如，以需求为准，会丢失那些只有司机在线但没有订单的时间段。
  • FULL OUTER JOIN: 理论上最完美的选择。它能保留所有时间窗口，无论该窗口内是否有需求、是否有供给。MySQL 不直接支持 FULL OUTER JOIN，但可以通过 LEFT JOIN UNION RIGHT JOIN 模拟。

3. 期望输出

基于上述数据，我们期望得到如下结果：

多种解法

解法 1：使用 CTE 和 FULL OUTER JOIN（标准 SQL / PostgreSQL / Oracle）或模拟（MySQL）

这是最结构化、最易读、最健壮的解法。我们将需求和供给的计算分别放在两个 CTE 中，然后进行全连接。

• 版本: ✅ PostgreSQL / Oracle / SQL Server. 对于 MySQL，需要用 UNION 模拟 FULL OUTER JOIN。
• SQL (PostgreSQL):

 1WITH demand_per_hour AS (
 2    SELECT
 3        city_id,
 4        DATE_TRUNC('hour', request_time) AS hour_bucket,
 5        COUNT(order_id) AS demand_count
 6    FROM orders
 7    GROUP BY 1, 2
 8),
 9supply_per_hour AS (
10    SELECT
11        city_id,
12        DATE_TRUNC('hour', log_time) AS hour_bucket,
13        COUNT(DISTINCT driver_id) AS supply_count
14    FROM driver_logs
15    WHERE status = 'online'
16    GROUP BY 1, 2
17)
18SELECT
19    COALESCE(d.city_id, s.city_id) AS city_id,
20    COALESCE(d.hour_bucket, s.hour_bucket) AS hour_bucket,
21    COALESCE(d.demand_count, 0) AS demand_count,
22    COALESCE(s.supply_count, 0) AS supply_count,
23    (COALESCE(d.demand_count, 0) - COALESCE(s.supply_count, 0)) AS gap
24FROM demand_per_hour d
25FULL OUTER JOIN supply_per_hour s
26    ON d.city_id = s.city_id AND d.hour_bucket = s.hour_bucket;

• SQL (MySQL 8.0+，模拟 FULL OUTER JOIN):

 1WITH demand_per_hour AS (
 2    SELECT
 3        city_id,
 4        DATE_FORMAT(request_time, '%Y-%m-%d %H:00:00') AS hour_bucket,
 5        COUNT(order_id) AS demand_count
 6    FROM orders
 7    GROUP BY 1, 2
 8),
 9supply_per_hour AS (
10    SELECT
11        city_id,
12        DATE_FORMAT(log_time, '%Y-%m-%d %H:00:00') AS hour_bucket,
13        COUNT(DISTINCT driver_id) AS supply_count
14    FROM driver_logs
15    WHERE status = 'online'
16    GROUP BY 1, 2
17)
18SELECT
19    city_id,
20    hour_bucket,
21    MAX(demand_count) AS demand_count,
22    MAX(supply_count) AS supply_count,
23    (MAX(demand_count) - MAX(supply_count)) AS gap
24FROM (
25    SELECT
26        d.city_id,
27        d.hour_bucket,
28        d.demand_count,
29        IFNULL(s.supply_count, 0) AS supply_count
30    FROM demand_per_hour d
31    LEFT JOIN supply_per_hour s ON d.city_id = s.city_id AND d.hour_bucket = s.hour_bucket
32    UNION
33    SELECT
34        s.city_id,
35        s.hour_bucket,
36        IFNULL(d.demand_count, 0) AS demand_count,
37        s.supply_count
38    FROM demand_per_hour d
39    RIGHT JOIN supply_per_hour s ON d.city_id = s.city_id AND d.hour_bucket = s.hour_bucket
40) AS full_join_simulation
41GROUP BY 1, 2;

• 复杂度: 设订单表大小为 $N$，司机日志表大小为 $M$。聚合操作复杂度为 $O(N+M)$（使用 Hash Aggregate）。最终 Join 的是聚合后的小表，其大小远小于 $N$ 和 $M$，所以整体性能瓶颈在聚合阶段。
• 索引依赖: 在 orders(city_id, request_time) 和 driver_logs(city_id, log_time, status) 上建立复合索引，可以极大地加速 GROUP BY 操作。

解法 2：使用派生表（子查询）和 LEFT JOIN

这是一种更传统的写法，不使用 CTE。为了简化，这里使用 LEFT JOIN，但需要注意它会丢失“有供给无需求”的场景。

• 版本: ✅ 所有主流数据库版本。
• SQL (MySQL):

 1SELECT
 2    d.city_id,
 3    d.hour_bucket,
 4    d.demand_count,
 5    IFNULL(s.supply_count, 0) AS supply_count,
 6    (d.demand_count - IFNULL(s.supply_count, 0)) AS gap
 7FROM
 8    (
 9        SELECT
10            city_id,
11            DATE_FORMAT(request_time, '%Y-%m-%d %H:00:00') AS hour_bucket,
12            COUNT(order_id) AS demand_count
13        FROM orders
14        GROUP BY 1, 2
15    ) AS d
16LEFT JOIN
17    (
18        SELECT
19            city_id,
20            DATE_FORMAT(log_time, '%Y-%m-%d %H:00:00') AS hour_bucket,
21            COUNT(DISTINCT driver_id) AS supply_count
22        FROM driver_logs
23        WHERE status = 'online'
24        GROUP BY 1, 2
25    ) AS s
26ON d.city_id = s.city_id AND d.hour_bucket = s.hour_bucket;

• 说明: 此解法以“需求”为主表，只展示了有订单的时间段内的供需缺口。如果面试官要求看到所有有活动的（无论是供给还是需求）时间段，这个答案就是有缺陷的，需要像解法 1 一样补充 RIGHT JOIN 和 UNION。
• 复杂度与索引: 与解法 1 相同。

常见低分回答

1. 使用 INNER JOIN：INNER JOIN 会导致只计算那些既有订单又有在线司机的时间段，丢失了大量信息，是面试中的严重扣分项。
2. 供给计算错误：使用 COUNT(driver_id) 而不是 COUNT(DISTINCT driver_id)。如果一个司机在一小时内多次上报在线状态，会被重复计算，导致供给被高估。
3. 忘记处理 NULL：在 LEFT/RIGHT/FULL JOIN 后，不使用 COALESCE 或 IFNULL 处理 NULL 值。demand - NULL 的结果是 NULL，而不是 demand，导致 gap 计算错误。
4. 时间聚合维度错误：仅按 HOUR(request_time) 分组，这会把所有天数的同一小时数据混在一起（例如，所有日期的上午9点都会被分到一组），而题目要求的是每个具体的小时。
5. 在 WHERE 子句中使用函数：例如 WHERE DATE_FORMAT(request_time, '%Y-%m-%d') = '2023-10-27'。这会导致 request_time 列上的索引失效，演变成全表扫描。正确的写法是范围查询：WHERE request_time >= '2023-10-27 00:00:00' AND request_time < '2023-10-28 00:00:00'。

工程实践与优化

• 数据倾斜与预聚合：在 Uber 这种体量下，原始日志表可能有数万亿行。直接查询是不现实的。工程上会通过 Flink 或 Spark Streaming 等流处理引擎，实时地将数据预聚合成以 (city_id, hour_bucket) 为 key 的小时级宽表。分析师和算法工程师直接查询这张结果表，响应速度是毫秒级。
• 执行计划分析 (EXPLAIN)：
  • 检查 type 列：理想情况下，对聚合子查询的扫描应为 index 或 range，而不是 ALL（全表扫描）。
  • 检查 key 列：确保用上了我们设计的 (city_id, time_column) 复合索引。
  • 检查 Extra 列：避免出现 Using temporary; Using filesort，这表示数据库需要创建临时表并进行文件排序，是性能瓶颈。可以通过优化索引来消除。
• 读写分离：这种大规模分析查询（OLAP）必须在只读副本或专门的数据仓库（如 ClickHouse, BigQuery, Snowflake）上执行，绝对不能在处理线上交易（OLTP）的主库上运行，否则会造成锁表、CPU/IO 资源抢占，影响主业务。
• 时区问题：Uber 是全球业务，所有时间戳在数据库中应以 UTC 标准存储。在计算时，需要根据 city_id 关联城市信息表，获取该城市对应的时区（如 America/New_York），然后使用 CONVERT_TZ() (MySQL) 或 AT TIME ZONE (PostgreSQL) 将 UTC 时间转换为本地时间再进行聚合。这样才能保证“上午9点”是每个城市的本地时间上午9点。

常见追问与延伸题目

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

   • 回答要点 1 (瓶颈分析)：当前 SQL 的性能瓶颈在于对两张巨大事实表的 GROUP BY 全扫描。数据量增长 100 倍后，查询可能需要数小时甚至根本无法完成。
   • 回答要点 2 (架构级优化)：必须放弃直接查询原始表的方案。转向大数据架构：
     • 批处理 (Batch Processing)：每天或每小时使用 Spark/Hive 任务，从原始数据源（如 HDFS, S3）中计算出供需结果，写入一张新的汇总表（Summary Table）。后续查询直接查这张小得多的汇总表。
     • 流处理 (Stream Processing)：对于实时性要求高的场景（如实时监控大盘），使用 Flink/Spark Streaming 消费 Kafka 中的订单和司机日志流，在内存中进行实时聚合，并将结果秒级更新到类似 Druid/ClickHouse/Redis 的系统中。

2. 追问：你对“供给”的定义是“小时内有过在线记录”，如果业务方想换个定义，比如“小时内平均在线司机数”，你该怎么实现？

   • 回答要点 1 (重新定义问题)：这是一个更复杂的数据建模问题，而不纯是 SQL 问题。“平均在线司机数”需要我们知道每个司机在这一小时内“在线”的总时长。
   • 回答要点 2 (实现思路)：
     • 需要更详细的日志，比如司机状态从 online -> on_trip -> online -> offline 的每次变化都有时间戳记录。
     • 我们需要对每个司机的日志进行“会话窗口（Sessionization）”分析，计算出每个 online 状态的持续时长。
     • SQL 实现会非常复杂，可能需要用窗口函数 LAG() 或 LEAD() 来计算每次状态变更之间的时间差。
     • 最终，将每个司机在目标小时内的所有“在线”时长加总，再除以 3600 秒，得到该司机的“等效在线小时数”。最后将所有司机的这个数值相加，得到“平均在线司机数”。这通常在 ETL 过程中用更强大的语言（如 Python/Scala with Spark）实现，而不是纯 SQL。

3. 追问：如果发现这条 SQL 跑得很慢，索引也加了，你会从哪些方面去排查？

   • 回答要点 1 (EXPLAIN ANALYZE)：首先使用 EXPLAIN ANALYZE (而不是 EXPLAIN) 来获取真实的执行计划和运行时统计信息。关注 actual rows 和 estimated rows 的差异，如果差异巨大，说明 MySQL 的统计信息不准，可以运行 ANALYZE TABLE 来更新。
   • 回答要点 2 (系统资源)：排查数据库服务器的 CPU、内存、I/O 使用情况。是否是内存不足导致大量数据需要写入磁盘临时表（Created_tmp_disk_tables 状态变量）？是否是 I/O 带宽成为瓶颈？
   • 回答要点 3 (数据分布)：检查数据是否存在严重倾斜。例如，某个城市的订单量占了总量的 90%？这会导致 GROUP BY 时，单个 reducer 或线程负载极高。如果是这样，可能需要对热点数据进行特殊处理，比如拆分或在应用层进行部分聚合。
   • 回答要点 4 (锁等待)：检查 SHOW PROCESSLIST 或 information_schema.innodb_locks，看查询是否在等待其他事务释放的元数据锁（MDL）或行锁。虽然分析查询一般不加锁，但它可能被其他持有锁的 DDL 或 DML 阻塞。