SparkSQL 面试题：异常开门事件分析

1. 题目背景与业务场景

在智能快递柜寄件业务中，用户在将包裹放入柜子并锁定柜门后，如果因“漏放物品”、“放错柜门”或“撤销寄件”等原因，在10分钟内再次申请打开同一个柜门的，业务上定义为“异常开门事件”。

本题要求通过 SparkSQL 窗口函数或关联查询，找出所有符合该定义的异常开门事件记录。

2. 示例数据

表一：柜门操作流水表 (cabinet_action_log)

记录用户对快递柜箱门的所有操作行为（如：LOCK-锁定，OPEN-打开）。

3. 期望输出结果

根据业务规则，只有 A003（距离上次锁门3分钟）和 A008（距离上次锁门9分59秒）属于 10 分钟内重新开门的异常事件。而 A006 间隔了 15 分钟，属于正常取件或超时操作。

4. SparkSQL 面试题答案

WITH sorted_actions AS (
    -- 1. 使用窗口函数获取同柜同箱同用户下，当前操作的下一次操作类型和时间
    SELECT 
        cabinet_id,
        box_id,
        user_id,
        action_type,
        action_time,
        LEAD(action_type, 1) OVER (
            PARTITION BY cabinet_id, box_id, user_id 
            ORDER BY action_time
        ) AS next_action_type,
        LEAD(action_time, 1) OVER (
            PARTITION BY cabinet_id, box_id, user_id 
            ORDER BY action_time
        ) AS next_action_time
    FROM cabinet_action_log
)
-- 2. 筛选出当前是 LOCK，且下一步是 OPEN，且间隔在 10 分钟（600秒）以内的数据
SELECT 
    cabinet_id,
    box_id,
    user_id,
    action_time AS lock_time,
    next_action_time AS re_open_time,
    -- 计算时间差（秒）
    (unix_timestamp(next_action_time) - unix_timestamp(action_time)) AS duration_seconds
FROM sorted_actions
WHERE action_type = 'LOCK' 
  AND next_action_type = 'OPEN'
  AND (unix_timestamp(next_action_time) - unix_timestamp(action_time)) <= 600
ORDER BY cabinet_id, lock_time;

5. SparkSQL 深度解析（助力面试）

解析一：为什么使用 LEAD 窗口函数？

• 传统思路的弊端：如果使用 JOIN（自关联），需要将表与自身做笛卡尔积，再通过 a.action_time < b.action_time 和时间差来过滤。在大数据量下，这会导致严重的 Shuffle，甚至引发 OOM (内存溢出)。
• 窗口函数的优势：使用 LEAD(col, 1) OVER (PARTITION BY ... ORDER BY ...) 可以在一次 Scan（扫描）中，直接获取同一用户在同一箱门上的“下一次”操作。Spark 内部只需按照 PARTITION BY 字段进行 Shuffle 分区，并在分区内排序，极大地减少了数据传输量。

解析二：Spark 任务执行与优化点

在面试中，主动提及以下优化点可以显著加分：

1. 数据倾斜 (Data Skew)：
   • 在实际业务中，某些热门快递柜（如地铁口、大社区）的操作日志极其庞大，而有些快递柜很少有人用。
   • PARTITION BY cabinet_id, box_id 可能会因为个别热门柜子导致数据倾斜。
   • 解决方案：如果发现倾斜，可以引入 user_id 共同作为分区键（如本题代码所示），因为同一个用户在短时间内连续操作同一个柜子的概率分布相对均匀。
2. 时间戳转换优化：
   • SparkSQL 中 unix_timestamp(string) 会将时间转为秒级时间戳。
   • 如果底层存储格式是 TimestampType，直接使用 cast(next_action_time as double) - cast(action_time as double) 或者 unix_timestamp 均可，但注意在 Spark 3.x 中，推荐使用更加安全的 cast 操作。
3. 过滤下推 (Predicate Pushdown)：
   • 在进入窗口函数前，可以通过 WHERE action_type IN ('LOCK', 'OPEN') 过滤掉其他无关的操作（如：查询状态、报错等），减小参与窗口计算的数据量。