本页内容受版权保护 · 已添加水印 · 禁止任何形式转载
大厂 SQL 面试真题（字节 / 阿里 / 拼多多 / 滴滴 / 小红书 / Meta / Amazon） · 阿里 / 拼多多 / 美团

阿里面试：根据每月成交额排名，找出排名连续两月上升的商家。

Alibaba interview question: Find merchants whose rank, based on monthly transaction amount, has risen

答案语言

考察要点

这道题面试官想看你是否懂：

  1. 复杂逻辑的 SQL 表达能力：能否将“连续两月排名上升”这个业务需求，精确地转化为 SQL 查询逻辑。
  2. 窗口函数的熟练运用：特别是 RANK() 用于排名和 LAG() 用于跨行数据比较，这是解决此类序列分析问题的最优范式。
  3. SQL 优化和工程思维：是否能考虑到大数据量下的性能问题，并提出如预聚合、索引设计等优化方案。

核心概念

本题的核心是处理行与行之间的关系，即在按时间排序的数据中，比较当前行与前几行的数据。

  1. 窗口函数 (Window Functions) 窗口函数对与当前行相关的行集（窗口）进行计算，但不会像 GROUP BY 那样将多行合并为一行。

    • PARTITION BY seller_id：将数据按商家 ID 分成多个独立的“窗口”，窗口函数在每个商家内部独立计算。
    • ORDER BY month：在每个窗口（商家）内，按月份对数据进行排序，这是进行连续性分析的基础。
    • RANK() vs DENSE_RANK()：
      • RANK() OVER (PARTITION BY month ORDER BY gmv DESC): 按月对商家成交额（GMV）进行降序排名。如果 GMV 相同，排名也相同，但后续排名会跳过相应位数（如：1, 2, 2, 4）。
      • DENSE_RANK(): 与 RANK() 类似，但排名是连续的（如：1, 2, 2, 3）。 对于本题“排名上升”（rank 值变小）的判断，两者皆可，RANK() 更符合通常的排名定义。
    • LAG(expression, offset, [default]):
      • 获取在当前行之前的第 offset 行的 expression 的值。
      • LAG(rank, 1) OVER (PARTITION BY seller_id ORDER BY month): 获取同一个商家上一个月的排名。
      • LAG(rank, 2) OVER (PARTITION BY seller_id ORDER BY month): 获取同一个商家上上个月的排名。

  2. 公用表表达式 (CTE - Common Table Expression) 使用 WITH 语句定义的临时命名结果集。CTE 可以让复杂查询的逻辑更清晰、更易读，通过将问题分解为多个步骤来构建最终查询。

    sql
     1WITH monthly_gmv AS (
     2    -- Step 1: Calculate monthly GMV
     3),
     4monthly_ranks AS (
     5    -- Step 2: Calculate ranks based on monthly GMV
     6)
     7-- Final Step: Query from the CTEs
     8SELECT ... FROM monthly_ranks WHERE ...;

  3. 自连接 (Self-Join) 将一张表与其自身进行连接。在没有窗口函数的旧版数据库中，这是实现跨行比较的常用方法。通过为同一张表设置不同别名（如 t1, t2），并设置连接条件（如 t1.seller_id = t2.seller_id AND t1.month = t2.month + 1）来模拟 LAG 函数的功能。

多种解法

假设我们有月度成交额表 monthly_sales：

monthseller_idgmv
1'A'100
1'B'300
2'A'200
2'B'150
3'A'400
3'B'180
4'A'500
4'B'600

分析商家 A 的排名变化：

  • 月份 1: GMV 100, 排名 2
  • 月份 2: GMV 200, 排名 1 (排名从 2 -> 1，上升)
  • 月份 3: GMV 400, 排名 1 (排名从 1 -> 1，持平)
  • 月份 4: GMV 500, 排名 2 (排名从 1 -> 2，下降) 商家 A 不满足“连续两月上升”。

分析商家 B 的排名变化：

  • 月份 1: GMV 300, 排名 1
  • 月份 2: GMV 150, 排名 2 (排名从 1 -> 2，下降)
  • 月份 3: GMV 180, 排名 2 (排名从 2 -> 2，持平)
  • 月份 4: GMV 600, 排名 1 (排名从 2 -> 1，上升) 商家 B 也不满足。

为了演示，我们假设商家 C 满足条件：Rank(m-2)=4, Rank(m-1)=3, Rank(m)=1。

解法 1：窗口函数 RANK() + LAG() (最优解)

这是最现代、最高效、最清晰的解法。

  • 适用版本: ✅ MySQL 8.0+, PostgreSQL, Oracle, SQL Server 等主流数据库。
  • 思路:
    1. 用 CTE monthly_ranks 计算出每个商家每月的 GMV 排名。
    2. 用 CTE rank_changes 并使用 LAG() 函数，将每个商家当前月、上一个月、上上个月的排名放在同一行。
    3. 在最后一步中，筛选出满足 当前排名 < 上月排名 且 上月排名 < 上上月排名 的记录。
sql
 1WITH monthly_ranks AS (
 2    -- 步骤1: 计算每个商家每月的排名
 3    SELECT
 4        month,
 5        seller_id,
 6        RANK() OVER (PARTITION BY month ORDER BY gmv DESC) AS rk
 7    FROM
 8        monthly_sales
 9),
10rank_changes AS (
11    -- 步骤2: 获取前两个月的排名
12    SELECT
13        month,
14        seller_id,
15        rk,
16        LAG(rk, 1) OVER (PARTITION BY seller_id ORDER BY month) AS prev_rk_1,
17        LAG(rk, 2) OVER (PARTITION BY seller_id ORDER BY month) AS prev_rk_2
18    FROM
19        monthly_ranks
20)
21-- 步骤3: 筛选出连续两个月排名上升的商家
22SELECT DISTINCT
23    seller_id
24FROM
25    rank_changes
26WHERE
27    rk < prev_rk_1 AND prev_rk_1 < prev_rk_2;
  • 时间复杂度: O(Nlog⁡N)O(N \log N)，主要开销在于窗口函数的排序操作。其中 N 是 monthly_sales 的总行数。
  • 空间复杂度: O(N)O(N)，需要存储 CTE 的中间结果。
  • 索引依赖: 在 monthly_sales 表上建立 (month, gmv) 的索引，可以加速 RANK() 的计算。

解法 2：自连接 (Self-Join)

此方法用于兼容不支持窗口函数的老版本数据库（如 MySQL 5.7），或展示不同的解题思路。

  • 适用版本: ✅ 所有 SQL 版本。
  • 思路:
    1. 同样先用一个 CTE monthly_ranks 计算出月度排名。
    2. 将 monthly_ranks 表进行三次自连接，分别代表当前月 t1、上一个月 t2、上上个月 t3。
    3. 通过 JOIN 条件关联同一个商家和连续的三个月份。
    4. 在 WHERE 子句中施加排名上升的条件。
sql
 1WITH monthly_ranks AS (
 2    -- 步骤1: 计算每个商家每月的排名 (这一步在 MySQL 5.7 中需要用变量模拟)
 3    -- 假设我们已经有 monthly_ranks 表
 4    SELECT
 5        month,
 6        seller_id,
 7        RANK() OVER (PARTITION BY month ORDER BY gmv DESC) AS rk
 8    FROM
 9        monthly_sales
10)
11-- 步骤2: 三次自连接，筛选满足条件的商家
12SELECT DISTINCT
13    t1.seller_id
14FROM
15    monthly_ranks t1
16JOIN
17    monthly_ranks t2 ON t1.seller_id = t2.seller_id AND t1.month = t2.month + 1
18JOIN
19    monthly_ranks t3 ON t2.seller_id = t3.seller_id AND t2.month = t3.month + 1
20WHERE
21    t1.rk < t2.rk AND t2.rk < t3.rk;

注意：在 MySQL 5.7 及更早版本，RANK() 需要用用户变量来模拟，代码会更复杂。

  • 时间复杂度: 如果 (seller_id, month)上有索引，JOIN 的效率会很高，复杂度接近 O(Nlog⁡N)O(N \log N)。如果没有索引，可能是 O(N2)O(N^2) 甚至更高。
  • 空间复杂度: O(N)O(N)，用于存储 CTE。
  • 索引依赖: 在 monthly_ranks 的中间结果上， (seller_id, month) 的复合索引是必须的，否则多次 JOIN 会导致性能灾难。

常见低分回答

  1. 只比较一个月: WHERE rk < prev_rk_1，只检查了一次排名上升，忽略了“连续两月”的要求。
  2. 错误理解“上升”: WHERE rk > prev_rk_1，误以为排名数字越大越好，实际正好相反。
  3. 使用 ROW_NUMBER(): ROW_NUMBER() 在 GMV 相同时也会强行分配不同排名，不符合“排名”的业务语义，可能导致错误判断。
  4. 使用相关子查询: 在主查询的每一行都执行子查询去获取前两个月的排名，这会导致 O(N2)O(N^2) 级别的性能问题，在数据量稍大时就会超时。
    sql
     1-- 低效的相关子查询，应避免
     2SELECT DISTINCT seller_id FROM monthly_ranks r1
     3WHERE r1.rk < (SELECT r2.rk FROM monthly_ranks r2 WHERE r2.seller_id = r1.seller_id AND r2.month = r1.month - 1)
     4  AND (SELECT r2.rk FROM monthly_ranks r2 WHERE r2.seller_id = r1.seller_id AND r2.month = r1.month - 1) < 
     5      (SELECT r3.rk FROM monthly_ranks r3 WHERE r3.seller_id = r1.seller_id AND r3.month = r1.month - 2);
  5. 忽略月份不连续的情况: 如果商家在 1 月、3 月有数据，但 2 月没有，LAG 函数会直接比较 3 月和 1 月的数据。自连接 t1.month = t2.month + 1 则不会。需要和面试官澄清业务口径：是按自然月连续，还是按有销售记录的月份连续。LAG 的行为更符合后者。

工程实践与优化

  • 数据预聚合: 原始交易流水表（orders）可能非常庞大。在生产环境中，绝不会直接扫描全量流水表。通常会有一个定时任务（如每日/每小时执行的 Spark/Flink 作业）将流水数据聚合成 monthly_sales 这样的月度统计表。查询直接基于这个小得多的结果表，性能会提升几个数量级。
  • 执行计划分析:
    • 对于解法 1，EXPLAIN 结果中应关注 WindowAgg 操作。确保 PARTITION BY 和 ORDER BY 的列有合适的索引，以避免昂贵的 filesort。
    • 对于解法 2，EXPLAIN 结果中应关注 JOIN 的 type 和 key。type 最好是 ref 或 eq_ref，key 必须是 (seller_id, month) 索引，Extra 字段不应出现 Using join buffer (Block Nested Loop)。
  • 处理数据倾斜: 如果某个商家的数据量远超其他商家，PARTITION BY seller_id 可能导致数据倾斜。但在本题场景下，数据已按月聚合，每个商家每月只有一行，所以倾斜问题不大。
  • 物化 CTE: 如果 monthly_ranks 这个中间结果集非常大且被多次引用，可以考虑将其物化为临时表，并创建索引，再进行后续的 LAG 或 JOIN 操作。

常见追问与延伸题目

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

    • 回答要点 1 (架构层面)：首先，必须进行数据预聚合。我会设计一个数据ETL流程，每天凌晨从原始订单表（orders）增量计算前一天的GMV，并更新到一个商家月度GMV聚合表（seller_monthly_gmv）。我的查询将直接跑在这个聚合表上，而不是几十亿的原始订单表。聚合表的数据量可能只有几百万或几千万，查询性能可以得到保障。
    • 回答要点 2 (计算引擎层面)：如果聚合后的表依然巨大，我会考虑使用分布式 SQL 引擎，如 Spark SQL、Presto 或 Hive。这些引擎可以将计算（特别是窗口函数中的排序和分区）分布到多个节点并行执行，利用集群的水平扩展能力来处理海量数据。

  2. 追问：如果要找连续 N 个月排名上升的商家，SQL 怎么写？

    • 回答要点 1 (扩展 LAG)：如果 N 是一个较小的常数（如 3 或 4），可以直接扩展解法 1，增加更多的 LAG 函数，如 LAG(rk, 3), LAG(rk, 4) 等，但这不够灵活。
    • 回答要点 2 (高级解法：Gaps and Islands)：这是一个典型的“连续性问题”（Gaps and Islands）。更通用的解法是：
      1. 创建一个辅助列，标记每次排名是否上升，如 is_increase = CASE WHEN rk < LAG(rk, 1) OVER (...) THEN 1 ELSE 0 END。
      2. 再创建一个分组标识，当 is_increase 为 0 时，开启一个新的分组。这可以通过对 is_increase=0 的次数进行累加求和来实现：SUM(CASE WHEN is_increase = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY seller_id ORDER BY month)。
      3. 最后，按 seller_id 和这个分组标识进行 GROUP BY，然后用 COUNT(*) 统计每个连续上升序列的长度，筛选出长度大于等于 N-1 的即可。这个方法可以优雅地解决 N 为任意值的情况。

  3. 追问：如果换成 ClickHouse 或 TiDB 这样的分布式数据库，这个查询有什么不同？

    • 回答要点 1 (语法兼容性)：ClickHouse 和 TiDB 都支持标准 SQL 和窗口函数，所以解法 1 的 SQL 代码基本可以通用，无需大的修改。
    • 回答要点 2 (分布式执行)：核心区别在于执行层面。TiDB 作为分布式关系型数据库，会自动将 PARTITION BY 的数据根据分片规则（如 seller_id 的 hash）发送到不同节点计算，然后汇总结果。ClickHouse 作为列式存储分析引擎，其 MergeTree 引擎和并行计算能力对这种聚合和排序查询有极高的性能。开发者只需编写标准 SQL，数据库底层会自动进行分布式并行优化，无需手动干预。
    • 回答要点 3 (特定优化)：在 ClickHouse 中，如果表按 (seller_id, month) 排序，那么 PARTITION BY seller_id ORDER BY month 的窗口函数性能会极高，因为它能利用预排序的数据，避免了运行时的再次排序。这体现了物理设计对查询性能的巨大影响。
← Previous
拼多多面试：找出每个分类下销量前 20% 的商品。
Next →
美团面试：求外卖单平均配送时长和分位数（P50 / P90 / P99）。