找出每个产品销量排名前 3 的城市。

考察要点

这道题是典型的 "分组 Top-N" 问题，面试官想考察你是否掌握现代 SQL 的核心利器——窗口函数（Window Functions）。同时，也想看你是否具备举一反三的能力，能否给出在不支持窗口函数的老版本数据库（如 MySQL 5.7）中的替代解法，并能清晰地分析两种方案的优劣和性能差异。这背后是对 SQL 语义、执行计划和优化思维的综合检验。

核心概念

解决本题的核心是理解分区（Partitioning）和排名（Ranking）。我们需要先将数据按 产品 进行分组，再在每个组内按 销量 进行排序和排名。

1. 窗口函数 (Window Functions) 窗口函数对一组与当前行相关的表行（这个组被称为 "窗口"）执行计算。它与聚合函数的主要区别是：聚合函数会把多行聚合成一行，而窗口函数在计算后仍为每行返回一个值。

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

• PARTITION BY product_id: 定义窗口，即按产品 ID 对数据进行分组。计算会在每个产品分组内独立进行。
• ORDER BY sales_amount DESC: 定义窗口内各行的排序规则，即按销量降序排列。

2. 排名函数对比 在 Top-N 问题中，选择哪个排名函数至关重要，因为它决定了如何处理销量并列的情况。

对于本题 "排名前 3 的城市"，如果销量为 (北京:100, 上海:90, 广州:90, 深圳:80)，

• 使用 RANK() 或 DENSE_RANK()，WHERE rank <= 3 会返回北京、上海、广州、深圳。
• 使用 ROW_NUMBER()，WHERE rank <= 3 只会返回北京、上海、广州。 通常 RANK() 更符合 "前三名" 的业务直觉，我们会优先使用它。

3. 关联子查询 (Correlated Subquery) 在没有窗口函数的旧版数据库中，可以通过子查询来模拟排名。其核心思想是：对于表 sales 中的每一行 s1，去计算在同一个产品下，有多少行的销量 s2.sales_amount 大于等于 s1.sales_amount。这个计数值就是 s1 的排名。

 1-- 伪代码
 2SELECT product_id, city_id, sales_amount
 3FROM sales s1
 4WHERE (
 5    SELECT COUNT(DISTINCT s2.sales_amount)
 6    FROM sales s2
 7    WHERE s2.product_id = s1.product_id AND s2.sales_amount >= s1.sales_amount
 8) <= 3;

这种方式逻辑复杂，且性能通常远低于窗口函数。

多种解法

假设我们有如下的 sales 表：

期望输出：

最终筛选 rk <= 3 的结果。

解法 1：窗口函数 (推荐)

这是最现代、最高效、最简洁的解法。使用公用表表达式（CTE）可以使逻辑更清晰。

• 版本：✅ MySQL 8.0+ / PostgreSQL / Oracle / SQL Server；❌ MySQL 5.7 及以下版本不支持。
• SQL 代码：
  sql

  ⧉⛶

   1WITH RankedSales AS (
   2    SELECT
   3        product_id,
   4        city,
   5        sales,
   6        RANK() OVER (PARTITION BY product_id ORDER BY sales DESC) as rk
   7    FROM
   8        sales
   9)
  10SELECT
  11    product_id,
  12    city,
  13    sales,
  14    rk
  15FROM
  16    RankedSales
  17WHERE
  18    rk <= 3;

• 时间/空间复杂度：主要开销在于排序。如果内存充足，复杂度为 $O(N \log N)$，其中 N 是总行数。如果数据量大需要磁盘排序，性能会下降。空间复杂度取决于 PARTITION BY 的 key 的基数和每个分区的大小。
• 索引依赖：在 sales 表上建立复合索引 (product_id, sales DESC) 能极大地优化性能。数据库可以利用该索引的有序性，避免在内存或磁盘中对每个分区进行排序（filesort），从而将排序的开销降到最低。

解法 2：关联子查询 / 自连接

这是一种兼容老版本数据库的经典解法，但性能通常较差，适合在不支持窗口函数的环境或小数据量下使用。

• 版本：✅ 所有主流数据库版本。

• SQL 代码：

  sql

  ⧉⛶

   1SELECT
   2    s1.product_id,
   3    s1.city,
   4    s1.sales
   5FROM
   6    sales s1
   7LEFT JOIN
   8    sales s2 ON s1.product_id = s2.product_id AND s1.sales < s2.sales
   9GROUP BY
  10    s1.product_id, s1.city, s1.sales
  11HAVING
  12    COUNT(DISTINCT s2.sales) < 3;

  逻辑解释：我们将表自连接，条件是产品相同且 s2 的销量比 s1 高。通过 GROUP BY 和 HAVING COUNT(DISTINCT s2.sales) < 3，我们筛选出那些“最多只有 2 个其他城市的销量比它高”的记录，这等价于排名前三（处理并列情况）。

• 时间/空间复杂度：在没有合适索引的情况下，对于 sales 表中的每一行，都需要扫描 sales 表来做比较，复杂度接近 $O(N^2)$。即使有索引，其性能也通常劣于窗口函数。

• 索引依赖：必须在 (product_id, sales) 上建立索引，否则连接操作将导致极低的性能。

常见低分回答

1. 误用 GROUP BY 和 LIMIT：SELECT product_id, city, sales FROM sales GROUP BY product_id ORDER BY sales DESC LIMIT 3; 这种写法是完全错误的。GROUP BY product_id 会导致每个产品只返回一行（具体哪一行取决于 MySQL 的 ONLY_FULL_GROUP_BY 设置），然后对这几行进行排序取前 3，完全不符合题意。
2. 混淆排名函数：不解释为何选择 RANK() 而不是 ROW_NUMBER() 或 DENSE_RANK()。当面试官追问“如果销量并列怎么算”时，无法清晰回答，说明对细节理解不清。
3. 在 WHERE 子句中直接使用窗口函数：SELECT ... FROM sales WHERE RANK() OVER (...) <= 3; 这是非法的 SQL 语法。窗口函数在 SELECT 和 ORDER BY 子句中执行，不能直接用于 WHERE 或 GROUP BY。必须使用 CTE 或子查询来先计算排名，再进行过滤。
4. 关联子查询性能噩梦：写出关联子查询解法，但在被问到性能时，无法指出其潜在的 $O(N^2)$ 风险，以及如何通过索引进行优化，说明缺乏生产经验。
5. 忽略 NULL 值处理：如果 sales 列可能为 NULL，ORDER BY sales DESC 会将 NULL 值排在最前面还是最后面（取决于数据库实现），这会影响排名结果。高分回答应提及这一点并说明如何用 NULLS LAST 或 NULLS FIRST 控制。

工程实践与优化

• 索引是关键：对于解法 1，INDEX(product_id, sales DESC) 是性能的决定性因素。通过 EXPLAIN 查看执行计划，应确保没有 Using filesort，这表示利用了索引的有序性。
• 执行计划分析：使用 EXPLAIN (或 EXPLAIN ANALYZE in MySQL 8.0+/PostgreSQL)。对于窗口函数，你会看到 Window: ... 的信息。关键是看排序是否被优化掉。对于关联子查询，要看 JOIN 的 type 是否为 ref 或 eq_ref，rows 估算是否合理，避免 type=ALL 的全表扫描。
• 数据倾斜：如果某个 product_id 的数据量远超其他产品（例如，一个爆款产品占了 90% 的销量记录），窗口函数的内存消耗会集中在这个大分区上，可能导致 sort_buffer_size 不足而使用磁盘临时文件，性能急剧下降。
• 物化与流式处理：CTE RankedSales 可能会被物化成一个临时表。对于大数据量，这会消耗大量 I/O 和磁盘空间。一些先进的数据库优化器能够以流式方式处理窗口函数，避免完全物化，性能更好。
• 预聚合：在超大规模数据场景（如数仓中的事实表），直接在原始明细表上跑窗口函数可能非常慢。通常的做法是通过 ETL 任务，先按 (product_id, city) 预聚合销量，生成一个更小的聚合表，再在这个小表上执行 Top-N 查询，速度会快几个数量级。

常见追问与延伸题目

1. 追问：如果销量有并列，比如前三名有 5 个城市，都要展示吗？

   • 回答要点：这取决于业务需求。
     • 如果需要展示所有并列的城市，应该使用 RANK() 或 DENSE_RANK()。RANK() <= 3 会把排名为 1, 2, 3 的所有城市都包含进来，即使总数超过 3 个。
     • 如果严格只要 3 个城市，即使有并列也要舍弃，那么应该使用 ROW_NUMBER()。它会为并列的行分配一个随机但唯一的序号。
     • 我在解法中使用了 RANK()，因为它更符合“排名”的直观理解，即分数相同的人名次相同。

2. 追问：如果数据量增长到 10 亿级别，这条 SQL 性能怎样？怎么优化？

   • 回答要点：
     • 性能瓶颈：在 10 亿级别，窗口函数会遇到严峻挑战。主要瓶颈在于排序，即使有索引，单个大分区的排序也可能耗尽内存（sort_buffer_size），导致大量的磁盘 I/O（filesort），查询可能需要几小时甚至失败。
     • 优化方案：
       1. 离线预计算：这是最根本的方案。通过每日或每小时的批处理任务（如 Spark/Flink/Hive），提前计算好每个产品的 Top-N 城市，并将结果存入一个专门的结果表（如 product_top_cities）。线上查询直接读这个结果表，实现毫秒级响应。
       2. 分布式数据库/查询引擎：将数据存储在像 TiDB, OceanBase, Greenplum 这样的分布式数据库，或者使用 Presto, ClickHouse 等列式存储和分析引擎。它们能利用多台机器的 CPU 和内存并行计算，将大规模排序任务分而治之，从而在可接受的时间内完成查询。
       3. 近似查询：如果业务可以接受近似结果，可以使用一些近似查询算法（如 t-digest）来估算分位数，快速找到 Top-N，但这不适用于需要精确排名的场景。

3. 追问：如果只要每个产品销量最高的那个城市，怎么写最高效？

   • 回答要点：这是 "分组 Top-N" 问题中 N=1 的特例，有更高效的特定解法。
     • 窗口函数法：依然可以使用 ROW_NUMBER() ... WHERE rk = 1。在有 (product_id, sales DESC) 索引的情况下，这已经非常高效。
     • 经典 LEFT JOIN ... IS NULL 法：这是一个非常巧妙且高效的解法，尤其在老版本 MySQL 中性能优异。
       sql

       ⧉⛶

        1SELECT s1.*
        2FROM sales s1
        3LEFT JOIN sales s2
        4  ON s1.product_id = s2.product_id AND s1.sales < s2.sales
        5WHERE s2.product_id IS NULL;

       逻辑：对于 s1 中的每一行，尝试去找一个同产品下销量比它更高的 s2。如果找不到（即 s2.product_id IS NULL），说明 s1 就是销量最高的行。在 (product_id, sales) 上有索引时，这个 LEFT JOIN 的效率极高。
     • 性能对比：在 N=1 的场景下，LEFT JOIN 法和优化良好的窗口函数法性能相当，有时 LEFT JOIN 甚至会略胜一筹，因为它不涉及完整的窗口帧计算。但在 N>1 时，窗口函数方案的扩展性、可读性完胜。