播面

SparkSQL 面试题：2025 年双重 Top-N 销售分析

1. 题目要求

在电商和零售业务中，经常需要进行多层级的 Top-N 分析。请编写 SparkSQL 完成以下需求：

1. 筛选时间范围：仅统计 2025年 的销售数据。
2. 第一层 Top-N：按省份（province）分组，计算各省总销售额，找出销售额排名前 3 的省份。
3. 第二层 Top-N：对于上述排名前 3 的省份，分别找出其下属城市（city）销售额排名前 3 的城市。

2. 样例数据

输入表：sales_records（销售明细表）

(注：order_id 18 的订单属于 2024 年，计算时应予以过滤。)

3. 期望输出结果

(注：四川省 2025 年总销售额为 1000，排在第 4 位，因此被排除。广东、浙江、江苏各取前 3 名城市。)

4. SparkSQL 解答（基于高可读与高性能优化方案）

WITH city_sales AS (
    -- Step 1: 过滤2025年数据，并计算各省下每个城市的销售总额
    SELECT 
        province,
        city,
        SUM(sales_amount) AS city_sales
    FROM sales_records
    WHERE YEAR(order_date) = 2025
    GROUP BY province, city
),
province_sales AS (
    -- Step 2: 使用窗口函数计算省份总销售额，避免 Join 操作
    SELECT 
        province,
        city,
        city_sales,
        SUM(city_sales) OVER(PARTITION BY province) AS province_total_sales
    FROM city_sales
),
ranked_provinces AS (
    -- Step 3: 对省份进行全局排名
    SELECT 
        province,
        city,
        city_sales,
        DENSE_RANK() OVER(ORDER BY province_total_sales DESC) AS province_rank
    FROM province_sales
),
ranked_cities AS (
    -- Step 4: 过滤出Top 3省份，并在省份内部对城市进行排名
    SELECT 
        province,
        province_rank,
        city,
        city_sales,
        DENSE_RANK() OVER(PARTITION BY province ORDER BY city_sales DESC) AS city_rank
    FROM ranked_provinces
    WHERE province_rank <= 3
)
-- Step 5: 最终过滤各省的前3名城市
SELECT 
    province,
    province_rank,
    city,
    city_sales,
    city_rank
FROM ranked_cities
WHERE city_rank <= 3
ORDER BY province_rank, city_rank;

5. 面试考点解析与 Spark 性能调优

在面试中，仅仅给出正确的 SQL 代码只能算及格。要获得面试官的青睐，需要从窗口函数选择、执行计划优化（避免 Join）和数据倾斜三个维度进行深度解析：

考点一：窗口函数的选择与区别

该题中使用了 DENSE_RANK()。面试官经常会追问 ROW_NUMBER(), RANK(), DENSE_RANK() 的区别：

• ROW_NUMBER()：无脑排序，即使数值相同，排名也递增（如：1, 2, 3）。
• RANK()：数值相同排名相同，但会跳跃（如：1, 2, 2, 4）。
• DENSE_RANK()：数值相同排名相同，且排名连续（如：1, 2, 2, 3）。
• 本题考点：在计算 Top 3 省份和城市时，若存在销售额并列的情况，使用 DENSE_RANK() 能确保并列优秀的省份/城市不会被遗漏，符合严谨的商业分析逻辑。

考点二：极致的性能优化——避免 Self-Join

• 普通写法劣势：很多初学者会先 GROUP BY 省份算出省总额，再与明细表做 JOIN。在 Spark 中，JOIN 会引入一次昂贵的 Shuffle（Exchange），极大地消耗网络和磁盘 I/O。
• 本题解法优势：在 province_sales 步骤中，直接使用 SUM(city_sales) OVER(PARTITION BY province)。它利用了上一步已经按 province 完成 GROUP BY 的分区数据，在本地执行聚合，不产生额外的 Join，减少了一次物理执行计划中的 Shuffle 阶段，执行效率倍增。

考点三：Spark 物理执行计划与 Shuffle 过程

在 SparkSQL 中，上述 SQL 的数据流向如下：

1. Stage 1：读取数据源，根据 YEAR(order_date) = 2025 过滤。然后按 (province, city) 进行 Hash Shuffle 聚合，输出 city_sales。
2. Stage 2：在已按 province 分区的数据上，计算省总额和省排名。由于上一步的数据已经具备分区特征，Spark 会极大地优化此处的窗口函数计算。
3. Stage 3：过滤 province_rank <= 3。最后再按 province 分区（因为之前已经排过序，代价极低），计算各省内部的城市排名。

考点四：防范数据倾斜（Data Skew）

如果面试官进一步追问：“如果某一个省份（如广东省）的订单量是其他省份的一万倍，你的 Spark 任务卡在 DENSE_RANK() 处报 OOM（内存溢出）怎么办？”

• 解决方案：
  1. 两阶段聚合（加盐法）：在第一阶段 GROUP BY 时，将城市 Key 加上随机前缀（如 1_深圳市, 2_深圳市），先局部聚合，再去掉前缀全局聚合。
  2. 过滤倾斜 Key：如果已知广东省数据倾斜，且不影响大盘，可对倾斜省份进行单独过滤处理，然后再通过 UNION ALL 合并。
  3. 提高并行度：合理调大 spark.sql.shuffle.partitions 的数值（默认 200，大任务下建议设为 1000+），让每个 Task 承载更少的数据。