Spark SQL 面试题：快递寄件揽收超时率统计

1. 题目背景

在物流行业中，时效性是衡量服务质量的核心指标。系统定义：“用户投递后 4 小时内（即 $\le 240$ 分钟），快递员未进行开柜揽收”为揽收超时。
现在需要你编写一段 Spark SQL，统计每个快递公司的寄件揽收超时率。

超时率计算公式：
$$\text{揽收超时率} = \frac{\text{超时件数}}{\text{总投递件数}}$$

2. 示例数据

表 1：用户投递记录表 user_delivery

记录用户在快递柜的投递时间及对应的快递公司。

表 2：快递员揽收记录表 courier_pickup

记录快递员开柜揽收的时间（若用户投递后，快递员一直未揽收，则此表无对应投递ID的记录）。

3. 期望输出结果

4. Spark SQL 参考答案

WITH joined_data AS (
    SELECT 
        d.delivery_id,
        d.company_name,
        d.drop_off_time,
        p.pickup_time,
        -- 计算时间差（单位：秒），若未揽收则认为无限大（即超时）
        -- Spark SQL 中可以直接用 unix_timestamp 转换后相减
        (unix_timestamp(p.pickup_time) - unix_timestamp(d.drop_off_time)) / 3600 AS hours_diff
    FROM 
        user_delivery d
    LEFT JOIN 
        courier_pickup p ON d.delivery_id = p.delivery_id
),
timeout_flag_data AS (
    SELECT 
        company_name,
        CASE 
            -- 情况1：一直未揽收（hours_diff 为 NULL） -> 算作超时
            WHEN hours_diff IS NULL THEN 1
            -- 情况2：揽收时间差大于 4 小时 -> 算作超时
            WHEN hours_diff > 4 THEN 1
            -- 情况3：4 小时内揽收 -> 未超时
            ELSE 0
        END AS is_timeout
    FROM 
        joined_data
)
SELECT 
    company_name,
    COUNT(1) AS total_delivery,
    SUM(is_timeout) AS timeout_count,
    -- 格式化输出百分比，保留两位小数
    CONCAT(ROUND(SUM(is_timeout) * 100.0 / COUNT(1), 2), '%') AS timeout_rate
FROM 
    timeout_flag_data
GROUP BY 
    company_name;

5. Spark SQL 核心考点与深度分析

考点一：表关联关系的选择（Left Join 还是 Inner Join？）

• 分析： 必须使用 LEFT JOIN。因为部分快件可能从未被揽收（如示例数据中的 D006）。如果使用 INNER JOIN，未揽收的快件会在关联时被过滤掉，导致“超时数”和“总投递数”的统计全部偏小，无法真实反映业务现状。

考点二：如何定义“未揽收”的边界条件？

• 分析： 这是一个典型的业务细节坑。在 SQL 中，LEFT JOIN 之后未揽收的记录其 pickup_time 值为 NULL。在写 CASE WHEN 逻辑时，必须先将 pickup_time IS NULL（或 hours_diff IS NULL）判定为超时，再判断 hours_diff > 4。
• Spark 优化点： 在 Spark SQL 中，处理 NULL 值时要格外小心，因为任何数与 NULL 进行大小比较（如 NULL > 4）结果都为 NULL，不会走入 ELSE，如果不显式处理 NULL，会导致漏计。

考点三：时间差的计算函数选择

• 分析： Spark SQL 提供了多种计算时间差的方式：
  1. unix_timestamp(t2) - unix_timestamp(t1)：将时间转为秒级时间戳相减，最通用、精度最高，推荐用于需要精确到分/秒的场景。
  2. datediff(t2, t1)：仅计算天数差，不适合本题的“4小时”高精度要求。
  3. cast(t2 as double) - cast(t1 as double)：在 Spark 3.x 中，可以直接将 Timestamp 转换为 Double（单位为秒）进行减法运算。

考点四：数据倾斜与 Spark 性能调优

若在实际面试中面试官追问：“如果某家快递公司（如顺丰）的数据量极大，导致发生数据倾斜，该如何优化？”

• 解决方案：
  1. Broadcast Join（广播连接）： 揽收表（courier_pickup）和投递表（user_delivery）如果其中一个较小（默认 <10MB，可通过 spark.sql.autoBroadcastJoinThreshold 调整），可以使用 broadcast(small_table)，避免 Shuffle 产生的数据倾斜。
  2. 加盐两阶段聚合（Salted Aggregation）： 如果是 GROUP BY company_name 导致的聚合倾斜，可以给 company_name 加上随机前缀（如 SF_1, SF_2），先进行局部聚合，再去掉前缀进行全局聚合。