拼多多：分析双 11 用户。先 GMV 总和、再分位数、再各时段下单峰值。

考察要点

这道题是一道综合性的业务分析题，面试官想考察你将复杂的、分步骤的业务需求（总览、分布、峰值）转化为结构化 SQL 查询的能力。核心考察点在于：

1. 高级 SQL 语法：特别是窗口函数（NTILE, ROW_NUMBER）的熟练运用。
2. 查询构建能力：能否使用公用表表达式（CTE）来组织逻辑，使复杂查询清晰、可读、可维护。
3. SQL 优化意识：是否考虑到在大数据量（双 11 场景）下的性能问题，并能提出初步的优化思路。

核心概念

为了解决这个问题，需要先精准理解以下几个核心概念。假设我们有如下订单表 orders：

1. GMV (Gross Merchandise Volume)

   • 定义：成交总额，即特定时间段内所有订单金额的总和。
   • SQL 实现：SUM(order_amount)。

2. 分位数 (Quantile)

   • 定义：将一个有序数据集切分为等大小部分的分割点。例如，百分位数（Percentile）就是将数据分为 100 份。P90 是指数据集中 90% 的值都小于或等于该值。
   • SQL 实现：NTILE(n) OVER (ORDER BY ...) 窗口函数。它将有序行集分成 n 个大致相等的桶（bucket），并为每一行分配一个桶编号（从 1 到 n）。
   • 示例：要计算用户的 GMV 百分位，可以先按用户聚合 GMV，然后使用 NTILE(100) OVER (ORDER BY user_gmv)。

3. 窗口函数 (Window Function)

   • 定义：一种特殊的函数，它对与当前行相关的行集（称为“窗口”）进行计算。与聚合函数不同，它不会将多行压缩为一行，而是为结果集中的每一行返回一个值。
   • 语法：function_name() OVER (PARTITION BY ... ORDER BY ...)
     • PARTITION BY col: 定义窗口的范围，类似于 GROUP BY。
     • ORDER BY col: 定义窗口内行的顺序。
   • 常用函数：
     • 聚合类：SUM(), AVG(), COUNT()
     • 排序类：ROW_NUMBER(), RANK(), DENSE_RANK()
     • 分箱类：NTILE(n)

4. 公用表表达式 (CTE - Common Table Expression)

   • 定义：一个临时的、命名的结果集，可以在后续的 SELECT, INSERT, UPDATE, DELETE 语句中引用。
   • 语法：WITH cte_name AS (SELECT ...)
   • 优势：
     • 可读性：将复杂查询分解为逻辑上独立的步骤。
     • 可维护性：修改逻辑块比修改庞大的嵌套子查询更容易。
     • 递归：支持递归查询（本题不涉及）。

多种解法

假设双 11 当天为 2023-11-11。

解法 1：使用 CTE 和窗口函数（推荐，主流方案）

这种解法逻辑清晰，将三个分析任务通过 CTE 串联或并行处理，是现代 SQL 的标准写法。

• 版本：✅ MySQL 8.0+ / PostgreSQL / SQL Server / Oracle

• 思路：

  1. TotalGMV CTE: 计算当天总 GMV。
  2. UserGMV CTE: 计算每个用户的总 GMV。
  3. UserQuantiles CTE: 在 UserGMV 基础上，使用 NTILE(100) 计算用户 GMV 的百分位。
  4. HourlyOrders CTE: 按小时聚合，计算每小时的订单数。
  5. PeakHours CTE: 在 HourlyOrders 基础上，使用 ROW_NUMBER() 找出订单数最多的小时。
  6. 最后将这些结果汇总输出。

• SQL 代码：

 1-- 定义分析日期范围
 2-- 在真实场景中，这些可能是变量或参数
 3SET @start_date = '2023-11-11 00:00:00';
 4SET @end_date = '2023-11-12 00:00:00';
 5
 6-- 使用 CTE 进行分步分析
 7WITH
 8-- 1. 计算总 GMV
 9TotalGMV AS (
10    SELECT SUM(order_amount) AS total_gmv
11    FROM orders
12    WHERE order_time >= @start_date AND order_time < @end_date
13),
14
15-- 2. 计算每个用户的 GMV，用于分位数分析
16UserGMV AS (
17    SELECT
18        user_id,
19        SUM(order_amount) AS user_total_gmv
20    FROM orders
21    WHERE order_time >= @start_date AND order_time < @end_date
22    GROUP BY user_id
23),
24
25-- 3. 计算用户 GMV 的百分位分布
26-- 这里我们找出 P90, P95, P99 的 GMV 门槛值
27UserQuantiles AS (
28    SELECT
29        -- 使用 NTILE 找到分位点对应的用户 GMV
30        -- 更精确的方式是 PERCENT_RANK 或 CUME_DIST，但 NTILE 更直观
31        MAX(CASE WHEN gmv_percentile = 90 THEN user_total_gmv END) AS p90_gmv,
32        MAX(CASE WHEN gmv_percentile = 95 THEN user_total_gmv END) AS p95_gmv,
33        MAX(CASE WHEN gmv_percentile = 99 THEN user_total_gmv END) AS p99_gmv
34    FROM (
35        SELECT
36            user_total_gmv,
37            NTILE(100) OVER (ORDER BY user_total_gmv) AS gmv_percentile
38        FROM UserGMV
39    ) AS percentile_calc
40),
41
42-- 4. 计算每小时的订单量，用于寻找峰值
43HourlyOrders AS (
44    SELECT
45        HOUR(order_time) AS hour_of_day,
46        COUNT(order_id) AS order_count
47    FROM orders
48    WHERE order_time >= @start_date AND order_time < @end_date
49    GROUP BY hour_of_day
50),
51
52-- 5. 找出订单量最高的峰值小时
53PeakHour AS (
54    SELECT hour_of_day, order_count
55    FROM (
56        SELECT
57            hour_of_day,
58            order_count,
59            ROW_NUMBER() OVER (ORDER BY order_count DESC) as rn
60        FROM HourlyOrders
61    ) AS ranked_hours
62    WHERE rn = 1
63)
64
65-- 6. 汇总所有分析结果
66SELECT
67    (SELECT total_gmv FROM TotalGMV) AS total_gmv,
68    (SELECT p90_gmv FROM UserQuantiles) AS p90_user_gmv_threshold,
69    (SELECT p95_gmv FROM UserQuantiles) AS p95_user_gmv_threshold,
70    (SELECT p99_gmv FROM UserQuantiles) AS p99_user_gmv_threshold,
71    (SELECT hour_of_day FROM PeakHour) AS peak_order_hour,
72    (SELECT order_count FROM PeakHour) AS peak_hour_order_count;

• 复杂度：
  • 时间复杂度：主要瓶颈在 UserQuantiles 的排序，为 $O(U \log U)$，其中 U 是用户数。以及 HourlyOrders 的聚合，为 $O(N)$，其中 N 是订单数。总复杂度约为 $O(N + U \log U)$。
  • 空间复杂度：CTE 和排序可能需要临时空间，取决于用户数和订单数。
• 索引依赖：在 orders 表的 order_time 列上建立索引是必须的，可以极大加速 WHERE 条件的筛选。复合索引 (order_time, user_id, order_amount) 对 UserGMV 的计算是覆盖索引，性能最佳。

解法 2：使用子查询和 LIMIT（兼容老版本）

在不支持窗口函数的旧版 MySQL（如 5.7）中，需要用其他方式模拟。

• 版本：✅ MySQL 5.7

• 思路：

  • 总 GMV：SUM 聚合，不变。
  • 分位数：通过 COUNT 算出总用户数，然后用 ORDER BY ... LIMIT 1 OFFSET ... 来定位特定分位点的值。这种方法效率低，且需要多次查询。
  • 下单峰值：先用子查询聚合出每小时订单量，再在外部查询中找到最大值。

• SQL 代码：

 1-- 1. 总 GMV
 2SELECT SUM(order_amount) AS total_gmv
 3FROM orders
 4WHERE order_time >= '2023-11-11 00:00:00' AND order_time < '2023-11-12 00:00:00';
 5
 6-- 2. 分位数（以 P90 为例，效率较低）
 7-- a. 先创建临时表存储用户 GMV
 8CREATE TEMPORARY TABLE temp_user_gmv AS
 9SELECT SUM(order_amount) AS user_total_gmv
10FROM orders
11WHERE order_time >= '2023-11-11 00:00:00' AND order_time < '2023-11-12 00:00:00'
12GROUP BY user_id;
13
14-- b. 计算总用户数
15SET @user_count = (SELECT COUNT(*) FROM temp_user_gmv);
16
17-- c. 定位 P90 的值
18SET @p90_offset = FLOOR(0.9 * @user_count);
19SELECT user_total_gmv AS p90_gmv_threshold
20FROM temp_user_gmv
21ORDER BY user_total_gmv
22LIMIT 1 OFFSET @p90_offset;
23
24DROP TEMPORARY TABLE temp_user_gmv;
25
26-- 3. 下单峰值
27SELECT hour_of_day, order_count
28FROM (
29    SELECT
30        HOUR(order_time) AS hour_of_day,
31        COUNT(order_id) AS order_count
32    FROM orders
33    WHERE order_time >= '2023-11-11 00:00:00' AND order_time < '2023-11-12 00:00:00'
34    GROUP BY hour_of_day
35) AS hourly_counts
36ORDER BY order_count DESC
37LIMIT 1;

• 复杂度：分位数计算需要全表排序和 OFFSET，大 OFFSET 性能很差。峰值计算需要对聚合结果排序后取 LIMIT 1，性能尚可。整体不如窗口函数方案。
• 何时用：当且仅当数据库版本过低，不支持窗口函数时。在面试中可以作为备选方案提出，以展示知识广度。

常见低分回答

1. 混淆 COUNT(*) 和 COUNT(column)：在计算订单数时，如果误用 COUNT(nullable_column)，可能会因为 NULL 值导致计数不准。应使用 COUNT(*) 或 COUNT(primary_key)。
2. 在 WHERE 子句中对列使用函数：例如 WHERE YEAR(order_time) = 2023 AND MONTH(order_time) = 11 AND DAY(order_time) = 11。这会导致 order_time 列上的索引失效，造成全表扫描。正确做法是使用范围查询 WHERE order_time >= '2023-11-11' AND order_time < '2023-11-12'。
3. 用 GROUP BY 和 MAX 的错误组合来找峰值：SELECT HOUR(order_time), MAX(COUNT(*)) ... 这是语法错误，聚合函数不能嵌套。正确的做法是先聚合，再从聚合结果中找最大值。
4. 逻辑混乱，写出巨大的嵌套子查询：不使用 CTE，将所有逻辑嵌套在一起，导致 SQL 语句难以阅读和调试。这在面试中是代码风格和工程能力的减分项。
5. 对分位数概念理解不清：试图用 AVG 或 MEDIAN（非标准 SQL）来回答分位数问题，或者用 ORDER BY ... LIMIT 的方式只找到 Top N 用户，而不是找到分位数的“门槛值”。

工程实践与优化

• 索引策略：对于双 11 级别的海量数据，索引是性能的生命线。必须在 orders(order_time) 上建立 B+ 树索引。如果内存允许，orders(order_time, user_id, order_amount) 复合索引能为用户 GMV 计算提供覆盖索引，避免回表，性能提升巨大。
• 执行计划分析：使用 EXPLAIN 分析查询计划。重点关注 type 列，避免出现 ALL（全表扫描）。理想情况是 range（范围扫描）或 index（索引扫描）。关注 Extra 列，避免 Using filesort 和 Using temporary，尤其是在大数据集上。
• 数据倾斜：在计算用户 GMV 时，可能会有超级大卖家，导致 GROUP BY user_id 时数据倾斜。在分布式系统（如 Spark, Hive）中需要特殊处理（如加盐），在单机 MySQL 中虽不直接处理，但能意识到此问题是加分项。
• 物化视图/汇总表：对于这种固定的报表类需求，最佳实践是离线计算。通过定时任务（如每日凌晨）将前一天的数据预聚合到一张汇总表（daily_kpi_summary）中。线上的查询直接查这张小得多的汇总表，响应速度可以从分钟级提升到毫秒级。
• 近似计算：对于分位数，当用户数达到亿级别时，精确计算非常耗时。可以向面试官提及，在一些大数据分析引擎（如 ClickHouse, Presto）中有 approx_percentile 这样的近似计算函数，能以极小的精度损失换来数量级的性能提升。

常见追问与延伸题目

1. 追问：如果数据量增长 100 倍，达到千亿级别，你这套 SQL 还能跑吗？怎么优化？

   • 回答要点 1 (架构层面)：单机 MySQL 肯定无法支撑。需要将数据迁移到分布式数据仓库或数据湖中，如 Hive, Spark SQL, ClickHouse, MaxCompute 等。这些系统通过分布式计算和列式存储，能横向扩展处理 PB 级数据。
   • 回答要点 2 (计算层面)：改用批处理模式。将 SQL 任务提交到离线计算平台（如 Spark），利用其强大的并行处理能力完成计算。计算逻辑（CTE、窗口函数）依然有效，但执行引擎完全不同。
   • 回答要点 3 (结果层面)：采用预计算 + 服务化。将分析结果（总GMV、分位数阈值、峰值小时）存储在高性能的在线数据库（如 Redis, HBase）或报表数据库中，前端应用通过 API 查询这些预计算好的结果，实现实时响应。

2. 追问：NTILE(100) 在用户数不能被 100 整除时，是如何分配的？这公平吗？

   • 回答要点 1 (分配机制)：NTILE(n) 会将 R 行数据尽可能均匀地分到 n 个桶里。如果 R 不能被 n 整除，那么前 R % n 个桶会比后面的桶多分配一个成员。例如 103 个用户分到 100 个桶，前 3 个桶（P1, P2, P3）会各有 2 个用户，其余 97 个桶各有 1 个用户。
   • 回答要点 2 (公平性与替代方案)：这种分配方式可能导致低分位数的桶“边界”略宽，在高精度分析中可能需要注意。如果需要更精确的按比例排名，可以使用 PERCENT_RANK()（计算行的相对排名，值为 [0, 1]）或 CUME_DIST()（计算累积分布，值为 (0, 1]），它们能提供更平滑的分布描述。

3. 追问：如果要分析每个商品品类的下单峰值时段，SQL 该怎么改？

   • 回答要点 1 (思路)：这是一个典型的“分组内求 Top N”问题。我们需要在计算每小时订单数时，增加 category_id 维度，然后在排序时，使用 PARTITION BY category_id 来对每个品类分别进行排序。
   • 回答要点 2 (SQL 实现)：修改 HourlyOrders 和 PeakHour 的 CTE。
     sql

     ⧉⛶

      1-- 假设 orders 表有 category_id 列
      2WITH CategoryHourlyOrders AS (
      3    SELECT
      4        category_id,
      5        HOUR(order_time) AS hour_of_day,
      6        COUNT(order_id) AS order_count
      7    FROM orders
      8    WHERE order_time >= @start_date AND order_time < @end_date
      9    GROUP BY category_id, hour_of_day
     10),
     11CategoryPeakHours AS (
     12    SELECT
     13        category_id,
     14        hour_of_day,
     15        order_count
     16    FROM (
     17        SELECT
     18            *,
     19            ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY order_count DESC) as rn
     20        FROM CategoryHourlyOrders
     21    ) AS ranked
     22    WHERE rn = 1
     23)
     24SELECT * FROM CategoryPeakHours;

   • 关键点：明确指出 PARTITION BY category_id 是解决此问题的关键，它将窗口限定在每个品类内部，从而实现“每个品类的峰值”。