Amazon 面试：求每个国家销售额排名变化（同比 / 环比）。

考察要点

这道题是典型的复杂分析查询，面试官想考察你对 SQL 时间序列分析和排名问题的综合处理能力。具体来说，是看你是否掌握以下三点：

1. 窗口函数 (Window Functions)：能否熟练使用 RANK(), DENSE_RANK() 进行分组排名，以及使用 LAG() 或 LEAD() 进行跨行数据获取。
2. 公用表表达式 (CTE)：能否使用 WITH 子句将复杂问题拆解成清晰、可读的逻辑步骤，这是现代 SQL 编程的最佳实践。
3. SQL 性能意识：是否理解不同解法（如 LAG() vs. 自连接）的性能差异，并能为查询设计合适的索引。

核心概念

本题的核心是窗口函数和公用表表达式 (CTE)。

1. 窗口函数 (Window Functions)

窗口函数对一组与当前行相关的表行（这个组称为窗口）执行计算。它与聚合函数的主要区别是，窗口函数在计算后不会将多行合并为一行，而是为每一行都返回一个结果。

• 语法: FUNCTION_NAME() OVER (PARTITION BY ... ORDER BY ...)
• PARTITION BY: 定义窗口的边界，类似于 GROUP BY。计算会在每个分区内独立进行。
• ORDER BY: 定义分区内行的顺序，对于排名和位移函数至关重要。

本题涉及的函数：

• 排名函数:

  • RANK(): 排名相同时，后续排名会跳跃。例如：1, 2, 2, 4。
  • DENSE_RANK(): 排名相同时，后续排名连续。例如：1, 2, 2, 3。通常在业务排名场景更符合直觉。
  • ROW_NUMBER(): 不论值是否相同，都分配一个连续的唯一排名。

• 位移函数:

  • LAG(expr, offset, default): 获取当前行之前第 offset 行的 expr 值。
    • expr: 要获取值的列或表达式。
    • offset: 偏移量，默认为 1。
    • default: 如果前 offset 行不存在，返回的默认值，默认为 NULL。
  • LEAD(expr, offset, default): 获取当前行之后第 offset 行的值。

2. 公用表表达式 (Common Table Expressions - CTE)

CTE 使用 WITH 关键字定义一个临时的、命名的结果集，可以在后续的 SELECT, INSERT, UPDATE, DELETE 语句中引用。

• 优点:

  • 可读性: 将复杂查询分解为多个逻辑清晰的步骤。
  • 可维护性: 修改逻辑时，只需修改对应的 CTE。
  • 递归能力: CTE 支持递归查询，可用于处理层级或图结构数据。

• 示例 DDL 和数据: 假设我们有一张销售订单表 sales_orders：

  sql

  ⧉⛶

   1CREATE TABLE sales_orders (
   2    order_id INT PRIMARY KEY,
   3    country VARCHAR(50),
   4    order_date DATE,
   5    sales_amount DECIMAL(10, 2)
   6);
   7
   8INSERT INTO sales_orders VALUES
   9(1, 'USA', '2022-01-15', 100.00), (2, 'China', '2022-02-20', 150.00),
  10(3, 'USA', '2022-03-10', 200.00), (4, 'Germany', '2022-04-05', 80.00),
  11(5, 'USA', '2023-01-20', 300.00), (6, 'China', '2023-02-25', 120.00),
  12(7, 'Germany', '2023-03-15', 250.00), (8, 'China', '2023-04-10', 180.00);

多种解法

以**同比（Year-over-Year）**为例，求每个国家销售额的年度排名变化。

解法 1：使用 CTE + 窗口函数 DENSE_RANK() 和 LAG()

这是最高效、最优雅的现代 SQL 解法。

• 思路:

  1. CTE 1 yearly_sales: 按国家和年份聚合，计算年度总销售额。
  2. CTE 2 ranked_sales: 使用 DENSE_RANK() 对上一步的结果按年份分区，计算每个国家在当年的销售排名。
  3. 主查询: 使用 LAG() 函数，按国家分区、按年份排序，获取上一年的排名，然后计算排名变化。

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

• SQL 代码:

  sql

  ⧉⛶

   1WITH yearly_sales AS (
   2    -- 步骤1: 计算每个国家每年的总销售额
   3    SELECT
   4        country,
   5        YEAR(order_date) AS sales_year,
   6        SUM(sales_amount) AS total_sales
   7    FROM
   8        sales_orders
   9    GROUP BY
  10        country,
  11        YEAR(order_date)
  12),
  13ranked_sales AS (
  14    -- 步骤2: 计算每个国家在每年的销售排名
  15    SELECT
  16        country,
  17        sales_year,
  18        total_sales,
  19        DENSE_RANK() OVER (PARTITION BY sales_year ORDER BY total_sales DESC) AS sales_rank
  20    FROM
  21        yearly_sales
  22)
  23-- 步骤3: 使用 LAG 获取上一年的排名并计算变化
  24SELECT
  25    country,
  26    sales_year,
  27    sales_rank AS current_rank,
  28    LAG(sales_rank, 1, NULL) OVER (PARTITION BY country ORDER BY sales_year) AS previous_rank,
  29    -- 如果 previous_rank 为 NULL (第一年)，则变化也为 NULL
  30    LAG(sales_rank, 1, NULL) OVER (PARTITION BY country ORDER BY sales_year) - sales_rank AS rank_change
  31FROM
  32    ranked_sales
  33ORDER BY
  34    sales_year,
  35    current_rank;

• 复杂度:

  • 时间复杂度: $O(N \log N)$，主要开销在于 GROUP BY 的哈希聚合和 ORDER BY 触发的排序操作。窗口函数的计算也依赖于排序。
  • 空间复杂度: $O(N)$，需要存储 CTE 的中间结果和排序所需空间。

• 索引依赖: 为了优化第一步的聚合，应在 sales_orders 表上建立复合索引 (order_date, country)。如果查询频率极高， (country, order_date) 也是一个备选项。

解法 2：使用 CTE + 自连接 (Self-Join)

在不支持 LAG() 函数的老版本数据库或需要更明确的连接逻辑时，可以使用自连接。

• 思路:

  1. CTE ranked_sales: 与解法 1 的前两步相同，计算出每个国家每年的销售排名。
  2. 主查询: 将 ranked_sales 表自连接。连接条件是国家相同，且当前年份是上一个年份加一。使用 LEFT JOIN 以保留没有前一年数据的记录（例如数据集中的第一年）。

• 版本: ✅ 几乎所有 SQL 数据库都支持。

• SQL 代码:

  sql

  ⧉⛶

   1WITH ranked_sales AS (
   2    SELECT
   3        country,
   4        YEAR(order_date) AS sales_year,
   5        DENSE_RANK() OVER (PARTITION BY YEAR(order_date) ORDER BY SUM(sales_amount) DESC) AS sales_rank
   6    FROM
   7        sales_orders
   8    GROUP BY
   9        country,
  10        YEAR(order_date)
  11)
  12SELECT
  13    current_period.country,
  14    current_period.sales_year,
  15    current_period.sales_rank AS current_rank,
  16    previous_period.sales_rank AS previous_rank,
  17    previous_period.sales_rank - current_period.sales_rank AS rank_change
  18FROM
  19    ranked_sales AS current_period
  20LEFT JOIN
  21    ranked_sales AS previous_period
  22    ON current_period.country = previous_period.country
  23    AND current_period.sales_year = previous_period.sales_year + 1
  24ORDER BY
  25    current_period.sales_year,
  26    current_period.sales_rank;

• 复杂度:

  • 时间复杂度: 通常高于解法 1。除了排序开销，JOIN 操作本身也有成本，尤其是当中间结果集较大时。复杂度可近似为 $O(N \log N)$。
  • 空间复杂度: $O(N)$。

• 索引依赖: 同解法 1。此外，数据库在执行 JOIN 时，如果能在 ranked_sales 的中间结果上（内存中）建立基于 (country, sales_year) 的哈希索引，性能会更好。

常见低分回答

1. 误用排名函数：使用 ROW_NUMBER() 代替 RANK() 或 DENSE_RANK()。如果两个国家销售额相同，ROW_NUMBER() 会随意分配一个排名，导致结果不准确且不稳定。
2. 窗口分区错误：在 OVER() 子句中忘记 PARTITION BY sales_year。DENSE_RANK() OVER (ORDER BY total_sales DESC) 会对所有年份的所有国家进行一次大排名，完全曲解了题意。
3. 在 WHERE 子句中使用函数：为了获取某年的数据，写出 WHERE YEAR(order_date) = 2023。如果 order_date 列上有索引，这样做会导致索引失效，迫使数据库全表扫描。正确做法是使用范围查询：WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01'。
4. 效率低下的多次查询：分别写两个 SQL 查询，一个查今年的排名，一个查去年的排名，然后在应用层（如 Python/Java）中合并数据。这种方法无法利用数据库的整体优化能力，网络开销大，且代码逻辑复杂，扩展性差。
5. 忽略 NULL 值处理：在使用自连接时，用 INNER JOIN 代替 LEFT JOIN，会导致第一年的数据丢失，因为它们在连接中找不到匹配的上一年度记录。

工程实践与优化

• 预聚合 (Pre-aggregation)：对于 Amazon 这种规模的数据，直接在原始订单表上运行此查询可能非常慢。在生产环境中，通常会有一个预聚合的物化视图或摘要表（Summary Table），例如 daily_country_sales。我们的查询就可以直接跑在这个小得多的表上，性能会提升几个数量级。
• 执行计划分析 (EXPLAIN)：
  • MySQL: 使用 EXPLAIN 或 EXPLAIN ANALYZE。
  • 关注 type 列，理想情况下，初始聚合应为 index 或 range，避免 ALL (全表扫描)。
  • 关注 Extra 列，Using index for group-by 表示 GROUP BY 操作利用了索引，是很好的信号。Using temporary; Using filesort 表示需要创建临时表并进行文件排序，是潜在的性能瓶颈。
  • 对于窗口函数，MySQL 8.0+ 的 EXPLAIN 会显示 Window aggregate 步骤。
• 索引选择:
  • 覆盖索引是终极目标。对于 yearly_sales 这个 CTE，如果存在索引 (order_date, country, sales_amount)，数据库可能可以直接从索引中完成聚合，无需回表查询主键，极大提升性能。
• 处理数据倾斜: 如果某个国家（如 'USA'）的订单量远超其他国家，GROUP BY 和 PARTITION BY 可能会出现数据倾斜，导致单个任务处理时间过长。在分布式数据库（如 Hive/Spark SQL）中，需要考虑加盐或调整并行度等策略来缓解。

常见追问与延伸题目

1. 追问：如果数据量增长 100 倍，达到百亿级别，这条 SQL 会有什么问题？如何优化？

   • 回答要点 1 (瓶颈分析)：直接在 OLTP 数据库（如 MySQL）的原始表上执行此查询会崩溃或严重影响线上业务。瓶颈在于：
     • 全量数据的 GROUP BY 和 ORDER BY 会消耗巨大的 CPU 和 I/O 资源。
     • 窗口函数需要对海量数据进行排序，内存可能不足，导致大量的磁盘 I/O (filesort)。
   • 回答要点 2 (架构级优化)：
     • 数据仓库/湖仓一体: 这是典型的 OLAP (在线分析处理) 场景。应将数据同步到专门的分析型数据库，如 Snowflake, BigQuery, ClickHouse, 或者基于 Spark/Flink 的数据湖。这些系统为大规模并行处理和复杂查询设计。
     • 预计算/物化视图: 如上所述，创建按天或按月的国家销售额摘要表。分析查询直接跑在摘要表上，数据量减少成千上万倍，查询可以在秒级完成。这是最核心、最有效的优化手段。
     • 查询拆分: 如果无法预计算，可以考虑将查询按年份拆分，分批执行，最后在应用层合并。但这只是一个缓解措施。

2. 追问：RANK() 和 DENSE_RANK() 在这里有什么区别？你会选择哪个？

   • 回答要点:
     • 区别: RANK() 在处理并列排名时会产生空缺（例如，两个第 2 名之后是第 4 名）。DENSE_RANK() 则会保持排名连续（两个第 2 名之后是第 3 名）。
     • 选择: 在大多数商业排名场景中，DENSE_RANK() 更符合直觉。用户通常不理解为什么排名会“跳过”一个数字。我会选择 DENSE_RANK()，并向产品经理或业务方说明其含义，以确保符合业务需求。如果业务方有特殊要求（例如，体育比赛排名规则），则可能使用 RANK()。

3. 追问：如果我想看每个国家销售额相比去年同期的增长率，而不是排名变化，SQL 该如何修改？

   • 回答要点: 这个改动很小，再次体现了 LAG() 函数的威力。
     • 只需在解法 1 的最后一个 SELECT 语句中，将 LAG 的目标从 sales_rank 改为 total_sales。
     • SQL 修改如下：
       sql

       ⧉⛶

        1-- 在最后一个 SELECT 阶段
        2SELECT
        3    country,
        4    sales_year,
        5    total_sales AS current_sales,
        6    LAG(total_sales, 1, 0) OVER (PARTITION BY country ORDER BY sales_year) AS previous_sales,
        7    -- 计算增长率，注意处理除零错误
        8    (total_sales - LAG(total_sales, 1, 0) OVER (PARTITION BY country ORDER BY sales_year))
        9        / NULLIF(LAG(total_sales, 1, 0) OVER (PARTITION BY country ORDER BY sales_year), 0) AS yoy_growth_rate
       10FROM
       11    ranked_sales -- 这里其实只需要 yearly_sales CTE 即可
       12ORDER BY
       13    country,
       14    sales_year;

     • 这表明你不仅理解了当前问题，还能灵活运用核心概念解决相关问题，是很好的加分项。