美团面试：求外卖单平均配送时长和分位数（P50 / P90 / P99）。

考察要点

这道题面试官想看你是否掌握 SQL 中高级分析能力，特别是窗口函数的应用。核心考察点包括：

1. 聚合函数与窗口函数的区别：是否理解 AVG() 作为普通聚合与窗口函数的不同用法。
2. 分位数的多种计算方法：是否能熟练使用 PERCENT_RANK / NTILE 等窗口函数，或者了解 PERCENTILE_CONT 等标准函数，来解决没有直接 PERCENTILE 函数的场景（如 MySQL）。
3. SQL 性能与大数据思维：是否能意识到全表排序的性能瓶颈，并提出在真实海量数据场景下的工程解决方案（如离线计算、近似算法）。

核心概念

本题的核心是计算平均值和分位数。假设我们有如下的订单表 orders：

DDL 示例:

 1CREATE TABLE orders (
 2    order_id BIGINT PRIMARY KEY,
 3    city_id INT,
 4    create_time DATETIME,
 5    delivered_time DATETIME
 6);

数据示例:

首先，我们需要计算每单的配送时长，通常用分钟或秒为单位： TIMESTAMPDIFF(SECOND, create_time, delivered_time)

1. 平均值 (Average)

使用 AVG() 聚合函数即可，非常直接。 AVG(TIMESTAMPDIFF(SECOND, create_time, delivered_time))

2. 分位数 (Percentile / Quantile)

分位数是统计学概念，用于描述一个值在数据分布中的相对位置。

• P50 (中位数): 数据排序后，位于 50% 位置的值。一半的数据比它小，一半的数据比它大。
• P90: 数据排序后，位于 90% 位置的值。90% 的数据都小于或等于该值。
• P99: 99% 的数据都小于或等于该值。

在 SQL 中，实现分位数有多种方式：

多种解法

假设配送时长（秒）已经计算并存入一个名为 delivery_duration 的 CTE 中。

 1WITH delivery_duration AS (
 2    SELECT
 3        TIMESTAMPDIFF(SECOND, create_time, delivered_time) AS duration
 4    FROM orders
 5    WHERE delivered_time IS NOT NULL AND create_time IS NOT NULL
 6)

解法 1：使用 PERCENT_RANK 窗口函数（推荐，精准）

这种方法利用 PERCENT_RANK 计算每个配送时长的精确百分位排名，然后通过条件聚合得到 P50, P90, P99 的值。

• 版本：✅ MySQL 8.0+ / PostgreSQL / Oracle / SQL Server
• 思路：先计算每单的 duration 和其对应的 PERCENT_RANK，然后在外层查询中，找到排名刚好跨过 0.50, 0.90, 0.99 阈值的那个 duration。MAX(CASE WHEN pr <= 0.5 THEN duration END) 巧妙地获取了排名在 50% 及以下的所有值中的最大值，即 P50。

 1WITH delivery_duration AS (
 2    SELECT
 3        TIMESTAMPDIFF(SECOND, create_time, delivered_time) AS duration
 4    FROM orders
 5    WHERE delivered_time IS NOT NULL AND create_time IS NOT NULL
 6),
 7ranked_duration AS (
 8    SELECT
 9        duration,
10        PERCENT_RANK() OVER (ORDER BY duration ASC) AS pr
11    FROM delivery_duration
12)
13SELECT
14    AVG(duration) AS avg_duration,
15    MAX(CASE WHEN pr <= 0.50 THEN duration END) AS p50_duration,
16    MAX(CASE WHEN pr <= 0.90 THEN duration END) AS p90_duration,
17    MAX(CASE WHEN pr <= 0.99 THEN duration END) AS p99_duration
18FROM ranked_duration;

• 时间/空间复杂度：主要开销在于窗口函数的排序，时间复杂度为 $O(N \log N)$，空间复杂度为 $O(N)$，其中 N 是订单数。
• 索引依赖：ORDER BY duration 是对计算列进行排序，无法直接利用 create_time 或 delivered_time 上的索引。查询需要全表扫描，性能瓶颈在于 filesort。

解法 2：使用 NTILE 窗口函数（近似，简单）

NTILE(100) 将数据分为 100 个桶，每个桶代表 1% 的数据。我们可以取第 50、90、99 个桶中的最大值作为对应分位数的近似值。

• 版本：✅ MySQL 8.0+ / PostgreSQL / Oracle / SQL Server
• 思路：用 NTILE(100) 对配送时长进行分桶，然后对每个桶聚合，找到 P50/P90/P99 对应的桶的最大值。

 1WITH delivery_duration AS (
 2    SELECT
 3        TIMESTAMPDIFF(SECOND, create_time, delivered_time) AS duration
 4    FROM orders
 5    WHERE delivered_time IS NOT NULL AND create_time IS NOT NULL
 6),
 7tiled_duration AS (
 8    SELECT
 9        duration,
10        NTILE(100) OVER (ORDER BY duration ASC) AS percentile_bucket
11    FROM delivery_duration
12)
13SELECT
14    (SELECT AVG(duration) FROM delivery_duration) AS avg_duration,
15    MAX(CASE WHEN percentile_bucket = 50 THEN duration END) AS p50_approx,
16    MAX(CASE WHEN percentile_bucket = 90 THEN duration END) AS p90_approx,
17    MAX(CASE WHEN percentile_bucket = 99 THEN duration END) AS p99_approx
18FROM tiled_duration;

• 时间/空间复杂度：同解法 1，为 $O(N \log N)$ 的排序开销。
• 索引依赖：同解法 1，无法利用索引优化排序。
• 何时使用：当业务对分位数的精度要求不高，或者作为一种更易理解和实现的备选方案时。NTILE 的计算结果是近似值，因为分桶可能不均匀（当总行数不能被 100 整除时）。

解法 3：使用用户变量（兼容老版本）

在 MySQL 5.7 等不支持窗口函数的版本中，可以通过用户变量模拟行号来实现。

• 版本：✅ MySQL 5.7；⚠️ MySQL 8.0+ 不推荐，因为优化器可能不保证其执行顺序。
• 思路：先获取总行数，计算出 P50/P90/P99 对应的行号。然后对数据排序，用变量 @rn 模拟行号，在行号匹配时取出对应的 duration。

 1-- 需要分步执行或在客户端中处理
 2SET @p50_rank := CEIL((SELECT COUNT(*) FROM orders WHERE delivered_time IS NOT NULL) * 0.50);
 3SET @p90_rank := CEIL((SELECT COUNT(*) FROM orders WHERE delivered_time IS NOT NULL) * 0.90);
 4SET @p99_rank := CEIL((SELECT COUNT(*) FROM orders WHERE delivered_time IS NOT NULL) * 0.99);
 5SET @rn := 0;
 6
 7SELECT
 8    AVG(duration) AS avg_duration,
 9    MAX(CASE WHEN rn = @p50_rank THEN duration END) AS p50_duration,
10    MAX(CASE WHEN rn = @p90_rank THEN duration END) AS p90_duration,
11    MAX(CASE WHEN rn = @p99_rank THEN duration END) AS p99_duration
12FROM (
13    SELECT
14        @rn := @rn + 1 AS rn,
15        duration
16    FROM (
17        SELECT TIMESTAMPDIFF(SECOND, create_time, delivered_time) AS duration
18        FROM orders
19        WHERE delivered_time IS NOT NULL
20        ORDER BY duration ASC
21    ) AS sorted_times
22) AS ranked_times;

• 时间/空间复杂度：依然是 $O(N \log N)$，瓶颈在内部的 ORDER BY。
• 索引依赖：同上。

常见低分回答

1. 忘记过滤无效数据：不使用 WHERE delivered_time IS NOT NULL，导致 TIMESTAMPDIFF 结果为 NULL，影响 AVG 计算，并且 NULL 在排序中的位置（默认最小）会干扰分位数计算。
2. 误用 GROUP BY：GROUP BY 用于将多行聚合为一行，而分位数计算需要基于全体数据的排序，两者目的不同。试图用 GROUP BY 计算分位数是概念性错误。
3. 低效的 LIMIT OFFSET：SELECT duration FROM ... ORDER BY duration LIMIT 1 OFFSET (total_count * 0.9)。这种写法需要先计算 total_count，然后数据库仍然需要排序全部 N 行数据，再扫描到 OFFSET 位置，效率极低，且需要多次查询。
4. 对 AVG 和 P50 的区别理解不清：当被问及为何要计算分位数时，无法清晰说明平均值易受极端异常值影响，而中位数（P50）更能反映数据的集中趋势。
5. 不了解 MySQL 8.0 窗口函数：在面试中仍然只提供 MySQL 5.7 的变量解法，会显得知识体系陈旧，没有跟进版本新特性。

工程实践与优化

• 大数据量下的性能灾难：在生产环境中，orders 表可能有数十亿甚至上百亿行。直接在主库上执行上述任何一种 SQL 都会导致 CPU 和 IO 飙升，产生慢查询，甚至拖垮整个数据库。严禁在 OLTP 数据库上直接进行此类全表分析查询。

• 正确的工程实践：

  1. T+1 离线计算：将数据同步到数据仓库（如 Hive, Spark, ClickHouse, BigQuery）中。在数据仓库中，利用其强大的分布式计算能力执行分位数计算。这些系统原生支持窗口函数，并且为大规模数据处理做了优化。
  2. 近似计算：对于需要近实时展示的监控看板，精确计算分位数代价太高。应采用近似算法。
     • 工具：ClickHouse 的 quantileTDigest, Presto/Spark SQL 的 approx_percentile。
     • 原理：这些函数使用如 T-Digest、KLL 等流式算法，在内存中维护一个数据结构的摘要（sketch），只需一次遍历数据，空间占用小，速度极快，结果误差可控。
     • 示例 (ClickHouse)：SELECT avg(duration), quantileTDigest(0.5)(duration), quantileTDigest(0.9)(duration) FROM ...
  3. 预聚合/物化视图：如果业务场景固定（如按城市、按小时统计），可以定时任务将计算好的分位数结果存入一张结果表（Data Mart），前端查询直接读取结果，实现秒级响应。

• 执行计划分析：

  • 使用 EXPLAIN 查看上述 SQL，会发现关键步骤是 Using temporary; Using filesort。这表示 MySQL 无法利用索引进行排序，需要将数据（或部分列）加载到内存或临时磁盘文件中进行排序，这是主要性能瓶颈。
  • 使用 EXPLAIN ANALYZE (MySQL 8.0+) 可以看到真实的执行时间和行数，能更精确地定位问题。

常见追问与延伸题目

1. 追问：如果需要按城市计算每个城市的分位数，SQL 怎么改？性能会有什么变化？

   • 回答要点 1 (SQL修改)：非常简单，只需在窗口函数的 OVER() 子句中加入 PARTITION BY city_id 即可。
     sql

     ⧉⛶

      1-- 以解法1为例
      2PERCENT_RANK() OVER (PARTITION BY city_id ORDER BY duration ASC) AS pr

     同时，外层的 AVG 和 MAX 也需要按 city_id 分组：... GROUP BY city_id。
   • 回答要点 2 (性能变化)：性能可能变好也可能不变，取决于数据分布。
     • 有利情况：如果城市数量多，且每个城市的数据量相对不大，可以将大的排序任务分解为多个小的、可并行处理的、甚至可以全部在内存中完成的排序任务，总体性能会提升。
     • 不利情况：如果存在严重的数据倾斜（Data Skew），例如某个一线城市订单量占了总量的 90%，那么对这个城市的 PARTITION 排序和全局排序几乎没有区别，性能瓶颈依然存在。

2. 追问：为什么我们更关心 P99 配送时长，而不是平均时长？

   • 回答要点 1 (用户体验)：平均值容易被大量“正常”订单掩盖问题。少数极端差的体验（如1-2小时才送到）会被平均掉，但这些正是造成用户流失的关键。P90/P99 指标直接反映了最差情况下的用户体验（长尾延迟）。
   • 回答要点 2 (问题发现)：优化 P99 时长，意味着要去解决那些系统性的、导致极端延迟的问题，比如特定区域运力不足、恶劣天气影响、高峰期调度系统瓶颈等。这比单纯优化平均值更能驱动系统和运营的改进。P99 的波动也是服务稳定性的一个重要“哨兵”指标。

3. 追问：如果数据库换成 PostgreSQL，你的首选方案是什么？

   • 回答要点 1 (标准函数)：我会直接使用 PostgreSQL 内置的、符合 SQL 标准的分位数函数 PERCENTILE_CONT，因为它语法更简洁、意图更明确。
     sql

     ⧉⛶

      1-- PostgreSQL 写法
      2SELECT
      3    AVG(duration),
      4    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY duration) AS p50_duration,
      5    PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY duration) AS p90_duration,
      6    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY duration) AS p99_duration
      7FROM delivery_duration;

   • 回答要点 2 (概念对比)：我会解释 PERCENTILE_CONT（连续插值）和 PERCENTILE_DISC（离散取值）的区别。CONT 更通用，因为它会计算一个精确的数学分位点，即使这个值不在原始数据中。DISC 则总是返回一个真实发生过的配送时长。对于配送时长这类连续型数据，PERCENTILE_CONT 通常是更好的选择。这能体现出我对不同数据库方言和底层统计学概念的深入理解。