SparkSQL 面试题：快递员投递包裹规格占比分析

1. 题目背景与要求

在物流行业中，分析快递员的投递习惯对于优化快递柜的格口配置（大、中、小格口的比例）至关重要。
请编写一段 SparkSQL 逻辑，计算每个快递员投递的包裹在大、中、小三种规格格口中的占比分布（结果保留4位小数，并以百分比形式或小数形式呈现）。

2. 示例数据

表1：包裹投递明细表 (t_delivery_record)

记录了每次投递的流水信息。

期望输出结果

3. SparkSQL 核心解法

SELECT 
    courier_id,
    COUNT(1) AS total_count,
    -- 使用 CASE WHEN 配合 SUM 进行行转列与条件计数
    ROUND(SUM(CASE WHEN box_size = '大 (Large)' THEN 1 ELSE 0 END) / COUNT(1), 4) AS large_ratio,
    ROUND(SUM(CASE WHEN box_size = '中 (Medium)' THEN 1 ELSE 0 END) / COUNT(1), 4) AS medium_ratio,
    ROUND(SUM(CASE WHEN box_size = '小 (Small)' THEN 1 ELSE 0 END) / COUNT(1), 4) AS small_ratio
FROM 
    t_delivery_record
GROUP BY 
    courier_id;

4. SparkSQL 深度剖析与面试加分项

① 核心技术点：行转列（Pivot）的经典实现

本题是典型的“行转列”应用场景。在分布式计算中，频繁使用 JOIN 会导致多次 Shuffle，严重影响性能。

• 推荐做法：使用 GROUP BY + CASE WHEN + SUM。这种方式只需要对数据进行一次全表扫描和一次 Shuffle（Group By），即可同时计算出总数以及各个维度的子项数。
• Spark 专属优化：在 SparkSQL 中，也可以使用原生 PIVOT 语法，但手写 CASE WHEN 具有更好的跨引擎兼容性（Hive/Presto/FlinkSQL 通用）。

② 避免“除以零（Division by Zero）”的健壮性设计

在实际生产环境中，如果 total_count 为 0（虽然在此业务场景下由于 GROUP BY 保证了至少为1，但如果是关联外部表则可能为0），直接相除会导致 NaN 或报错。

• 面试官加分点：展示你对数据脏值的敏感度。可以使用 NULLIF(COUNT(1), 0) 来规避除零异常：
  sql

  ROUND(SUM(CASE WHEN box_size = '大 (Large)' THEN 1 ELSE 0 END) / NULLIF(COUNT(1), 0), 4)

③ Spark 执行计划与性能调优分析

• 数据倾斜（Data Skew）：如果某些“头部快递员”每天投递几万件，而普通快递员只投递几十件，GROUP BY courier_id 会导致严重的数据倾斜。
  • 解决方案：
    1. 双重聚合（两阶段聚合）：先对 courier_id 加随机前缀进行局部聚合，再去掉前缀进行全局聚合。
    2. 启用 Spark 3.x Adaptive Query Execution (AQE)：设置 spark.sql.adaptive.skewJoin.enabled = true，Spark 会自动检测倾斜的分区并进行拆分。