拼多多面试：找出每个分类下销量前 20% 的商品。

考察要点

这道题是典型的 "Top-N per Group" 问题的变种，面试官想考察你对 SQL 分析能力的掌握程度，具体来说是：

1. 窗口函数（Window Functions）：是否熟练使用 NTILE, RANK, ROW_NUMBER 等窗口函数进行组内排名和切片。这是解决此类问题的标准现代 SQL 方法。
2. 复杂查询构建能力：是否能使用公用表表达式（CTE）或子查询来组织逻辑，因为窗口函数的结果不能直接在 WHERE 子句中过滤。
3. 性能优化意识：是否考虑到大数据量下的性能问题，并能提出基于索引的优化方案。

核心概念

解决本题的核心是理解窗口函数如何在每个分组（分区）内进行计算。

1. 表结构假设 我们假设有如下商品表 products：

2. 窗口函数 (Window Function) 窗口函数对一组与当前行相关的表行执行计算。它与聚合函数（如 SUM, COUNT）的关键区别在于：聚合函数会将多行合并为一行，而窗口函数会为每一行都返回一个值。

基本语法：FUNCTION_NAME() OVER (PARTITION BY ... ORDER BY ...)

• PARTITION BY category_id: 这是“每个分类下”的关键。它将数据按 category_id 拆分成多个逻辑分区（窗口），函数在每个分区内独立计算。
• ORDER BY sales DESC: 这是“销量前”的关键。它定义了分区内行的排序方式，这里是按销量从高到低排。

3. 关键函数辨析

• NTILE(n): 将每个分区中的有序行分配到 n 个大致相等的桶中，并返回桶编号（从 1 开始）。对于“前 20%”，我们可以使用 NTILE(5)，它将数据分为 5 个桶，每个桶占 20%。第一个桶 (NTILE(...) = 1) 就是我们需要的。

  • 注意：如果分区内的行数不能被 n 整除，NTILE 会让前面的桶比后面的桶多一个成员。例如，11 行分 5 桶，会分为 3, 2, 2, 2, 2。

• RANK() vs ROW_NUMBER():

  • RANK(): 排名函数。如果销量相同，排名也相同，但后续排名会跳跃。例如：1, 2, 2, 4。
  • ROW_NUMBER(): 行号函数。无论销量是否相同，都分配一个连续唯一的序号。例如：1, 2, 3, 4。
  • COUNT(*) OVER (PARTITION BY ...): 窗口化的 COUNT，可以计算出每个分区内的总行数，而不会折叠行。

多种解法

解法 1：使用 NTILE() 函数（最直接）

这种方法语义最清晰，直接表达了“将数据分为 5 份，取第 1 份”的意图。

• 版本：✅ MySQL 8.0+ / PostgreSQL / SQL Server / Oracle；❌ MySQL 5.7 不支持窗口函数。
• SQL 代码：
  sql

  ⧉⛶

   1WITH ranked_products AS (
   2    SELECT
   3        product_id,
   4        product_name,
   5        category_id,
   6        sales,
   7        NTILE(5) OVER (PARTITION BY category_id ORDER BY sales DESC) AS sales_quintile
   8    FROM
   9        products
  10)
  11SELECT
  12    product_id,
  13    product_name,
  14    category_id,
  15    sales
  16FROM
  17    ranked_products
  18WHERE
  19    sales_quintile = 1;

• 复杂度：时间复杂度为 $O(N \log N)$，其中 N 是总行数。主要开销在于 PARTITION BY ... ORDER BY ... 涉及的排序操作。空间复杂度为 $O(N)$，用于存储中间结果。
• 索引依赖：在 (category_id, sales DESC) 上创建复合索引可以极大地提升性能。数据库可以利用该索引，避免对每个分区进行显式的排序操作。

解法 2：使用 RANK() 和 COUNT() 组合（更灵活）

这种方法通过计算排名和总数，然后判断排名是否在总数的前 20% 范围内。它在处理“并列”情况时行为更明确。

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

  ⧉⛶

   1WITH product_stats AS (
   2    SELECT
   3        product_id,
   4        product_name,
   5        category_id,
   6        sales,
   7        RANK() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rnk,
   8        COUNT(*) OVER (PARTITION BY category_id) AS total_in_category
   9    FROM
  10        products
  11)
  12SELECT
  13    product_id,
  14    product_name,
  15    category_id,
  16    sales
  17FROM
  18    product_stats
  19WHERE
  20    rnk <= total_in_category * 0.2;

• 复杂度：时间复杂度 $O(N \log N)$，空间复杂度 $O(N)$，与解法 1 类似。
• 索引依赖：同样， (category_id, sales DESC) 上的复合索引是性能关键。
• 何时使用：当对边界情况（如销量并列）有精确要求时，此方法更优。例如，如果第 20% 和 21% 的商品销量相同，RANK() 会将它们都包含进来，这通常更符合业务逻辑。而 NTILE() 的行为可能因数据库实现而异，可能只包含一个。

解法 3：使用关联子查询（兼容老版本）

在不支持窗口函数的旧版 MySQL（如 5.7）中，只能使用这种效率较低的方法。

• 版本：✅ 所有 SQL 版本，包括 MySQL 5.7。
• SQL 代码：
  sql

  ⧉⛶

   1SELECT
   2    p1.product_id,
   3    p1.product_name,
   4    p1.category_id,
   5    p1.sales
   6FROM
   7    products p1
   8WHERE (
   9    SELECT COUNT(*)
  10    FROM products p2
  11    WHERE p2.category_id = p1.category_id AND p2.sales >= p1.sales
  12) <= (
  13    SELECT COUNT(*)
  14    FROM products p3
  15    WHERE p3.category_id = p1.category_id
  16) * 0.2;

• 复杂度：时间复杂度近似 $O(N^2)$。对于 p1 表的每一行，都需要执行两次关联子查询，性能极差，在数据量稍大时完全不可用。
• 索引依赖： (category_id, sales DESC) 上的索引是必须的，否则查询将无法完成。即使有索引，性能也远不如窗口函数。

常见低分回答

1. 在 WHERE 子句中直接使用窗口函数：

   sql

   ⧉⛶

    1-- 错误！SQL 语法不允许
    2SELECT ... FROM products WHERE NTILE(5) OVER (...) = 1;

   原因：SQL 查询的逻辑执行顺序是 FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> WINDOW -> ORDER BY。WHERE 子句在窗口函数计算之前执行，所以它看不到窗口函数的结果。必须使用 CTE 或子查询。

2. 忘记 PARTITION BY：

   sql

   ⧉⛶

    1-- 错误！在所有商品中排名，而不是分类内
    2NTILE(5) OVER (ORDER BY sales DESC)

   这会导致对整个表进行排名，而不是按分类进行，完全曲解了题目要求。

3. 混淆 RANK() 和 ROW_NUMBER()： 使用 ROW_NUMBER() 可能会在销量并列时，随机地将一个商品排在 20% 的门槛内，另一个排在门槛外，这通常不符合业务直觉。RANK() 或 DENSE_RANK() 能更好地处理并列情况。

4. 对 NTILE 的不均匀分配理解不清： 候选人可能认为 NTILE(5) 总是精确地划分 20% 的数据。面试官可能会追问：“如果一个分类有 7 个商品，你的查询会返回几个？” 正确答案是 NTILE(5) 会创建大小为 2, 2, 1, 1, 1 的桶，查询会返回 2 个商品（约 28.6%），并能解释这个行为。

工程实践与优化

• 索引是第一要义：对于此查询，CREATE INDEX idx_category_sales ON products (category_id, sales DESC); 是最重要的优化。它让数据库引擎能直接从索引中获取已分区和排序的数据，避免了昂贵的“filesort”操作。
• 执行计划分析 (EXPLAIN)：
  • 优化前：EXPLAIN 的 Extra 列可能会显示 Using temporary; Using filesort，表示需要创建临时表并进行全量排序，成本很高。
  • 优化后（有 (category_id, sales DESC) 索引）：Extra 列可能什么都不显示，或者显示 Using index（如果构成覆盖索引），表示排序操作被完全避免，性能极高。
• 数据倾斜问题：在真实生产中（如拼多多），某个大类的商品数量可能是其他类的数万倍。查询性能会被这个最大的分类拖垮。解决方案通常是：
  1. 离线计算：对于这种分析类查询，不在 OLTP 数据库（如 MySQL）上实时跑。而是通过 Spark/Flink 等大数据引擎，每天在数据仓库（如 Hive/ClickHouse）中预计算好排名，将结果存入一个结果表或 KV 存储（如 Redis），供线上业务快速查询。
  2. 查询拆分：如果必须在线查，可以识别出超大分类，对其采用特殊逻辑（如只允许查 Top 500），或者引导用户缩小范围。
• 物化 CTE：MySQL 8.0.21+ 对 CTE 的处理有优化，但旧版本可能会将 CTE 的结果“物化”成一个内部临时表。如果 products 表非常大，这个临时表会消耗大量内存和磁盘 I/O。

常见追问与延伸题目

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

   • 回答要点 1 (瓶颈分析)：在线数据库（MySQL/PostgreSQL）将无法承受。$O(N \log N)$ 的排序复杂度在十亿级别数据上是灾难性的。数据库内存会爆，磁盘 I/O 会成为瓶颈，查询会超时或拖垮整个实例。
   • 回答要点 2 (架构级优化)：必须采用离线/近线分析架构。
     • 数据同步：通过 CDC (Change Data Capture) 工具如 Canal/Debezium 将业务库数据实时/准实时同步到数据湖或数据仓库（如 Hudi/Iceberg on S3/HDFS）。
     • 离线计算：使用分布式计算框架（Spark SQL, Flink SQL）每日或每小时执行上述窗口函数逻辑。这些框架为大规模排序做了优化。
     • 结果服务化：将计算出的 Top 20% 商品列表存入高性能的线上服务系统，如 Redis (用 ZSET 按分类存)、Elasticsearch 或 ClickHouse。线上应用直接查询这些预计算好的结果，实现毫秒级响应。

2. 追问：如果 sales 列不在 products 表，而在一个巨大的 order_items 订单明细表里，该如何查询？

   • 回答要点 1 (查询改写)：这考察了 JOIN 和 GROUP BY 的能力。查询需要分步走：
     1. 首先，从 order_items 表中按 product_id 聚合，计算出每个商品的总销量。
     2. 然后，将聚合结果与 products 表连接，以获取 category_id。
     3. 最后，将上述结果作为整体，应用我们之前讨论的窗口函数。
   • 回答要点 2 (SQL 示例)：
     sql

     ⧉⛶

      1WITH product_total_sales AS (
      2    SELECT
      3        product_id,
      4        SUM(quantity) AS total_sales -- 或 SUM(price * quantity)
      5    FROM
      6        order_items
      7    -- 实际场景可能还需要按时间过滤，如近30天
      8    -- WHERE order_time > NOW() - INTERVAL 30 DAY
      9    GROUP BY
     10        product_id
     11),
     12ranked_products AS (
     13    SELECT
     14        p.product_id,
     15        p.category_id,
     16        pts.total_sales,
     17        NTILE(5) OVER (PARTITION BY p.category_id ORDER BY pts.total_sales DESC) AS sales_quintile
     18    FROM
     19        products p
     20    JOIN
     21        product_total_sales pts ON p.product_id = pts.product_id
     22)
     23SELECT ... FROM ranked_products WHERE sales_quintile = 1;

3. 追问：如果换成 ClickHouse 这种 OLAP 数据库，答案有什么不同？

   • 回答要点 1 (语法一致性)：SQL 语法基本一致。ClickHouse 对窗口函数的支持也很好，解法 1 和解法 2 都可以直接运行。
   • 回答要点 2 (性能和引擎差异)：ClickHouse 的性能会比 MySQL 好几个数量级。
     • 列式存储：ClickHouse 是列存数据库，对于只涉及 category_id, sales, product_id 几列的分析查询，它只需读取这些列的数据，I/O 大幅减少。
     • 向量化执行：它的查询引擎是向量化执行的，CPU 效率极高。
     • 排序算法：其排序和聚合算法专为大规模数据设计。
   • 回答要点 3 (特定函数)：可以提及 ClickHouse 提供的一些特殊函数，例如 topK 聚合函数，虽然不能直接解决“百分比”问题，但体现了你对 OLAP 数据库特性的了解。对于近似查询，ClickHouse 也有 quantile 等函数，可以非常高效地估算分位数。总之，在 ClickHouse 上，这个查询即使数据量巨大，也可能实现亚秒级响应，可以直接在线执行，无需复杂的离线预计算。