播面

SparkSQL 面试题：异常取件与快速重投分析

1. 题目背景与业务场景

在智能快递柜业务中，存在一种异常场景：用户输入了正确的取件码，柜门也打开了，但由于用户分神、临时有事或柜门卡阻，用户并未真正拿走快递。快递柜的重力/红外传感器检测到物体仍在格口内，并在一段时间后自动重新锁定柜门（此时系统记录状态为“未取出”）。
为了保障快递安全，快递员通常会在收到系统预警后，在短时间内（本题设定为 2 小时内）前往快递柜，取出该件并重新投递。

本题要求通过 SparkSQL 找出所有满足上述“用户开门未取 -> 2小时内快递员取出并重新投递”的异常闭环记录。

2. 示例数据

表 1：快递柜操作日志表 (cabinet_log)

记录快递柜格口的所有物理操作事件（开门、锁门、传感器状态）。

数据分析说明：

• PKG_001：用户 10:00 开门，10:01 锁门且未取出；快递员于 11:30 取出（间隔 1.5 小时，在 2 小时内），并在 11:32 重新投递。（符合条件）
• PKG_002：用户正常取走，锁门状态为 TAKEN。（不符合）
• PKG_003：用户未取出，但快递员在 17:00 才取出（间隔 3 小时，超过 2 小时）。（不符合）

3. SparkSQL 期望输出结果

4. SparkSQL 标准答案

WITH base_events AS (
    -- 1. 提取每个快递单号的关键生命周期节点
    SELECT 
        package_id,
        event_type,
        event_time,
        sensor_status,
        operator_type
    FROM cabinet_log
),
user_abnormal_leave AS (
    -- 2. 识别用户开门但未取走，系统重新锁定的记录
    SELECT 
        o.package_id,
        o.event_time AS user_open_time,
        l.event_time AS system_lock_time
    FROM base_events o
    JOIN base_events l 
      ON o.package_id = l.package_id
     AND o.event_type = 'OPEN' AND o.operator_type = 'USER'
     AND l.event_type = 'LOCK' AND l.operator_type = 'SYSTEM' AND l.sensor_status = 'NOT_TAKEN'
     AND l.event_time > o.event_time
     -- 确保是紧邻的锁门事件（防止同一件快递多次循环干扰）
     AND l.event_time <= o.event_time + INTERVAL 5 MINUTES 
),
courier_rescue AS (
    -- 3. 识别快递员取出并重新投递的记录
    SELECT 
        t.package_id,
        t.event_time AS courier_take_time,
        r.event_time AS courier_redeliver_time
    FROM base_events t
    JOIN base_events r 
      ON t.package_id = r.package_id
     AND t.event_type = 'TAKE_OUT' AND t.operator_type = 'COURIER'
     AND r.event_type = 'REDELIVER' AND r.operator_type = 'COURIER'
     AND r.event_time >= t.event_time
     AND r.event_time <= t.event_time + INTERVAL 10 MINUTES
)
-- 4. 关联异常记录与救援记录，并过滤 2 小时内的时效
SELECT 
    a.package_id,
    a.user_open_time,
    a.system_lock_time,
    c.courier_take_time,
    c.courier_redeliver_time,
    ROUND((CAST(c.courier_take_time AS LONG) - CAST(a.system_lock_time AS LONG)) / 60, 1) AS duration_minutes
FROM user_abnormal_leave a
JOIN courier_rescue c 
  ON a.package_id = c.package_id
 AND c.courier_take_time > a.system_lock_time
 -- 限制在系统锁定后 2 小时内，快递员执行了取出操作
 AND c.courier_take_time <= a.system_lock_time + INTERVAL 2 HOURS;

5. 核心考点与 SparkSQL 深度分析

在面试中，本题不仅考察基础的 SQL 编写能力，更侧重于考察候选人对复杂时序数据处理、性能优化以及 Spark 引擎特性的理解。

考点一：时序状态机（State Machine）的表达

• 业务痛点：该场景是一个典型的状态流转：USER_OPEN -> SYSTEM_LOCK(NOT_TAKEN) -> COURIER_TAKE_OUT -> COURIER_REDELIVER。
• 解题思路：
  • 不能简单地使用 GROUP BY，因为同一个快递柜或快递单号可能在历史上有多次投递记录。
  • 本题采用 CTE (Common Table Expressions) 拆分法，将复杂的四步状态流拆解为两个子双步：user_abnormal_leave（用户异常留存）和 courier_rescue（快递员救援）。最后通过 package_id 和时间窗口限制进行内连接（Inner Join）。

考点二：SparkSQL 时间间隔计算与时效过滤

• 在 SparkSQL 中，时间差计算有多种方式。本题采用了 INTERVAL 关键字（如 + INTERVAL 2 HOURS），这是 SparkSQL 推荐的标准写法，可读性极佳。
• 在计算分钟差时，使用了 CAST(time AS LONG) 将 Timestamp 转换为 Epoch 秒，相减后再除以 60。这种方法在 Spark 引擎中执行效率极高，避免了调用复杂的 UDF。

考点三：Spark 性能优化（面试加分项）

若面试官追问：“如果 cabinet_log 表每天有数亿条数据，这个 Query 该如何优化？” 候选人应从以下几个维度回答：

1. 时间分区裁剪（Partition Pruning）：
   • 在实际生产中，必须在最底层的 base_events 中加入日期分区过滤（例如 dt = '2023-10-25'），避免全表扫描。
2. 避免大表 Join 产生的数据倾斜（Data Skew）：
   • 由于是基于 package_id 进行 Join，如果某个热点快递柜/单号有极多操作，会导致 Shuffle 倾斜。
   • 优化方案：可以使用 distribute by rand() 或者是双重聚合。但更有效的是利用时间窗口（Interval Join）。Spark SQL 对带有时间范围限制的 Join（如 c.courier_take_time <= a.system_lock_time + INTERVAL 2 HOURS）有专门的优化器支持（Range Join / Interval Join），它会生成特定的物理执行计划，避免全量数据的笛卡尔积，极大地减少 Shuffle 磁盘溢写。
3. 窗口函数（Window Function）替代方案：
   • 如果不用 JOIN，也可以使用 LEAD / LAG 窗口函数按照 package_id 和 event_time 进行排序，通过在一行内获取相邻事件的状态来判断。但在有频繁重试、乱序数据的场景下，JOIN 配合严格的时间窗口限制往往比单纯的 LAG 更鲁棒。