播面

SparkSQL 面试题：找出超期滞留并由快递员取回的运单

1. 业务背景

在物流配送业务中，快递员将快递放入自提柜（或驿站）后，如果收件人长时间未取，快递员需要将快递取出并带回网点重新处理。本题旨在考察候选人对时间差计算、多状态流转分析（Status Transition）以及复杂窗口函数或表连接的掌握程度。

2. 示例数据

表 1：运单主表 waybill_info（记录运单基本信息）

表 2：运单轨迹明细表 waybill_trace（记录运单生命周期中的关键动作）

3. 期望输出结果

根据业务规则，只有 WB001 和 WB004 满足条件：

• WB001：入柜 10-01 10:00，取出 10-04 11:00，间隔 73 小时（> 72小时），且为滞留取出。
• WB002：非快递员取出（用户取走）。
• WB003：入柜 10-01 14:00，取出 10-03 14:00，间隔 48 小时（<= 72小时）。
• WB004：入柜 10-01 15:00，取出 10-05 10:00，间隔 91 小时（> 72小时），且为滞留取出。

4. 面试题标准答案 (SparkSQL)

解法一：使用窗口函数（推荐，单表扫描，性能较好）

WITH trace_ranked AS (
    SELECT 
        waybill_no,
        op_name,
        op_time,
        op_user,
        -- 获取当前操作的下一个操作名称和时间
        LEAD(op_name, 1) OVER (PARTITION BY waybill_no ORDER BY op_time) AS next_op_name,
        LEAD(op_time, 1) OVER (PARTITION BY waybill_no ORDER BY op_time) AS next_op_time,
        LEAD(op_user, 1) OVER (PARTITION BY waybill_no ORDER BY op_time) AS next_op_user
    FROM waybill_trace
    WHERE op_name IN ('投递入柜', '滞留取出', '用户取件')
)
SELECT 
    waybill_no,
    op_time AS put_in_time,
    next_op_time AS take_out_time,
    -- 计算时间差（小时）
    ROUND((CAST(next_op_time AS LONG) - CAST(op_time AS LONG)) / 3600, 1) AS duration_hours,
    next_op_user AS op_user
FROM trace_ranked
WHERE op_name = '投递入柜' 
  AND next_op_name = '滞留取出'
  -- 过滤出大于 72 小时的记录 (72小时 = 259200秒)
  AND (CAST(next_op_time AS LONG) - CAST(op_time AS LONG)) > 259200;

解法二：自关联（适合理解，在大数据集下可能存在 Shuffle 压力）

SELECT 
    t1.waybill_no,
    t1.op_time AS put_in_time,
    t2.op_time AS take_out_time,
    ROUND((CAST(t2.op_time AS LONG) - CAST(t1.op_time AS LONG)) / 3600, 1) AS duration_hours,
    t2.op_user
FROM waybill_trace t1
JOIN waybill_trace t2 ON t1.waybill_no = t2.waybill_no
WHERE t1.op_name = '投递入柜' 
  AND t2.op_name = '滞留取出'
  AND t2.op_time > t1.op_time
  AND (CAST(t2.op_time AS LONG) - CAST(t1.op_time AS LONG)) > 259200;

5. SparkSQL 深度分析与面试应对指南

在面试中，仅仅写出 SQL 只能拿到基础分。如果能主动对 Spark 引擎在该 SQL 下的执行机制进行分析，将大大增加通过率。以下是针对本题的深度技术拆解：

1. 时间差计算的陷阱（Time Calculations）

• 陷阱：在 SparkSQL 中，直接使用 datediff 计算的是天数，无法精确到小时。
• 最佳实践：
  • 将 TIMESTAMP 转换为 LONG 类型（Unix时间戳，单位为秒），进行减法计算后除以 3600。
  • 或者使用 Spark 3.x 提供的 unix_timestamp(time_str) 函数进行转换。
  • 避免在 JOIN 或 WHERE 条件中频繁使用复杂的字符串格式化函数（如 date_format），这会阻止 Spark 进行谓词下推（Predicate Pushdown）。

2. 窗口函数（LEAD/LAG） vs 自关联（Self-Join）

这是大厂面试最喜欢追问的对比点：

• 自关联（Join）的劣势：
  • 当 waybill_trace 表数据量达到亿级时，按 waybill_no 进行自关联会触发大量的 Shuffle (Shuffle Hash Join 或 Sort Merge Join)，导致数据在网络中大量传输，极易产生数据倾斜（Data Skew）。
• 窗口函数的优势：
  • LEAD 窗口函数只需要一次 Shuffle (Exchange)。Spark 会按照 PARTITION BY waybill_no 将相同运单的数据分发到同一个 Executor，并在分区内按照 op_time 进行 Sort (SortAggregate/SortWithinPartitions)。
  • 由于避免了双表关联，内存开销和计算时间都会大幅降低。

3. 性能优化与执行计划（Execution Plan）

若面试官问：“如何让这个 Spark 任务运行得更快？”可以从以下几个维度回答：

• 过滤条件前置（谓词下推）：在窗口函数的外层或 CTE 中，先用 WHERE op_name IN ('投递入柜', '滞留取出') 过滤掉不相关的轨迹数据（如“装车”、“派送中”），极大地减少参与 Window 排序的数据量。
• 避免数据倾斜：如果某几个大客户的运单轨迹特别多，或者有些运单异常产生了几万条轨迹，会造成数据倾斜。解决方案是对 waybill_no 加随机前缀进行两阶段聚合，或者开启 Spark 3.x 的 AQE（Adaptive Query Execution，自适应查询执行） 中的 skewJoin 优化配置。