播面

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

1. 题目背景与要求

在物流行业中，快递柜是最后 100 米投递的重要基础设施。快递柜的格口通常分为大（L）、中（M）、小（S）三种规格。分析快递员对不同规格格口的投递偏好，可以帮助运营团队进行快递柜格口配置的优化或对快递员进行画像。

分析需求：
请编写 SparkSQL 语句，计算每个快递员投递的包裹在大、中、小三种规格格口中的占比分布（结果保留 4 位小数，并以百分比形式展示，例如 50.00%）。

2. 示例数据

表 1：快递员基础信息表 (t_courier)

t_courier 数据样例：

表 2：快递柜投递记录表 (t_delivery_record)

t_delivery_record 数据样例：

3. 期望输出结果

4. SparkSQL 核心解法

在 SparkSQL 中，推荐使用 条件聚合（Conditional Aggregation） 来进行行列转换（Pivot）。这种方式在 Spark 引擎中执行效率最高，避免了复杂的 Join 操作。

SELECT 
    c.courier_id,
    c.courier_name,
    COUNT(r.record_id) AS total_count,
    -- 计算大箱子占比
    CONCAT(
        ROUND(COALESCE(SUM(CASE WHEN r.box_size = 'L' THEN 1 ELSE 0 END), 0) * 100.0 / COUNT(r.record_id), 2), 
        '%'
    ) AS L_ratio,
    -- 计算中箱子占比
    CONCAT(
        ROUND(COALESCE(SUM(CASE WHEN r.box_size = 'M' THEN 1 ELSE 0 END), 0) * 100.0 / COUNT(r.record_id), 2), 
        '%'
    ) AS M_ratio,
    -- 计算小箱子占比
    CONCAT(
        ROUND(COALESCE(SUM(CASE WHEN r.box_size = 'S' THEN 1 ELSE 0 END), 0) * 100.0 / COUNT(r.record_id), 2), 
        '%'
    ) AS S_ratio
FROM 
    t_courier c
LEFT JOIN 
    t_delivery_record r ON c.courier_id = r.courier_id
GROUP BY 
    c.courier_id, 
    c.courier_name
HAVING 
    total_count > 0;

5. SparkSQL 深度分析与面试加分项

在面试中，仅仅写出 SQL 只能算及格。能够针对 Spark 引擎的底层原理进行优化分析，才能拿到高分：

① 避免数据倾斜（Data Skew）

• 痛点： 头部快递员（如大区劳模、网点核心人员）的投递量可能极高，导致在 GROUP BY courier_id 时，这部分 key 分发到同一个 Reduce 任务中，引发数据倾斜。
• 解决方案：
  • 如果数据量极大，可以采用两阶段聚合。第一阶段给 courier_id 加随机前缀（如 1_C001, 2_C001）进行局部聚合，第二阶段去掉前缀进行全局聚合。
  • 确保 Spark 的 spark.sql.adaptive.enabled（AQE 自动倾斜连接/聚合优化）处于开启状态。

② 广播连接（Broadcast Join）的应用

• 场景分析： 快递员基础信息表（t_courier）通常是维度表，数据量相对较小（万级以下）；而投递记录表（t_delivery_record）是事实表，数据量极大（千万级甚至亿级）。
• Spark 优化：
  • 应当显式使用广播连接，避免大表在 Shuffle 阶段带来的网络和磁盘 I/O 损耗：
    sql

    SELECT /*+ BROADCAST(c) */ ... FROM t_courier c LEFT JOIN t_delivery_record r ...

③ 计算精度的处理

• 在 SparkSQL 中，整数除法（如 3 / 4）默认会返回 Double 类型。但在金融和精确统计场景下，为了防止 Double 的精度丢失，建议使用 * 100.0（隐式转换为 Decimal）或者显式使用 CAST(expr AS DECIMAL(10,4)) 进行精度控制。

④ 零值防范与外连接（Left Join）

• 使用 LEFT JOIN 是为了防止漏掉没有投递记录的快递员（虽然在实际业务中没投递过的快递员可能不需要统计，所以加上了 HAVING total_count > 0）。
• 在计算分子时，使用 COALESCE(..., 0) 可以有效防止因数据缺失导致的 NULL 值传递，保证了报表数据的健壮性。