播面

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

1. 题目背景

在物流行业中，快递入柜后若长时间未被收件人取走，会触发滞留流程。为了释放机柜资源，快递员通常会在一定时间后将快递取出并带回网点。本题旨在考察候选人对时间差计算、窗口函数、多状态流转分析以及 SparkSQL 性能优化的掌握程度。

2. 基础数据准备

表 1：运单主表 tb_express_order

记录运单的基本信息。

表 2：快递柜操作日志表 tb_cabinet_log

记录快递在快递柜中的每一次状态变更（按时间顺序产生）。

3. 面试题要求

编写一条 SparkSQL 语句，找出投递入柜（PUT）到快递员滞留取出（TAKE_OUT）之间的时间间隔超过 72 小时，且最终状态确实为快递员执行了“TAKE_OUT”的运单列表。

期望输出结果：
根据上述数据，只有 Express_001（间隔75小时）和 Express_004（间隔91小时）符合条件。Express_003 虽然被快递员取出，但间隔仅29小时，不符合条件。

4. 标准 SparkSQL 答案

WITH cabinet_lifecycle AS (
    SELECT 
        order_id,
        action_type,
        action_time,
        -- 获取当前操作的下一个操作类型和时间，用于判断状态流转
        LEAD(action_type) OVER (PARTITION BY order_id ORDER BY action_time) AS next_action,
        LEAD(action_time) OVER (PARTITION BY order_id ORDER BY action_time) AS next_action_time
    FROM tb_cabinet_log
)
SELECT 
    cl.order_id,
    cl.action_time AS put_time,
    cl.next_action_time AS take_out_time,
    -- 计算小时差值，Spark 中可将 timestamp 转为 bigint 相减再除以 3600
    ROUND((CAST(cl.next_action_time AS LONG) - CAST(cl.action_time AS LONG)) / 3600.0, 1) AS hours,
    o.branch_id
FROM cabinet_lifecycle cl
JOIN tb_express_order o ON cl.order_id = o.order_id
WHERE cl.action_type = 'PUT' 
  AND cl.next_action = 'TAKE_OUT'
  -- 过滤时间差大于 72 小时 (72 * 3600 秒 = 259200 秒)
  AND (CAST(cl.next_action_time AS LONG) - CAST(cl.action_time AS LONG)) > 259200;

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

1. 核心技术点解析

• 窗口函数 LEAD 的妙用：
  在处理“状态流转”或“生命周期”问题时，最忌讳使用自连接（Self-Join），因为 Join 会导致 Spark 产生大量的 Shuffle。使用 LEAD(action_type) OVER (PARTITION BY order_id ORDER BY action_time) 可以将同一运单的下一步操作直接拉取到当前行，在单次扫描（Single Scan）和局部排序后即可完成计算，极大地提高了执行效率。
• 时间差值的精准计算：
  在 SparkSQL 中，直接使用 datediff 只能精确到天。对于要求“72小时”这种精确到小时的业务场景，推荐将 Timestamp 转换为 Long（Unix时间戳，单位为秒）进行减法计算，再换算为小时。

2. 面试官追问：如何进行性能优化？

如果面试官看到你写出了上述代码，可能会追问：“如果 tb_cabinet_log 表每天有数十亿条数据，这个任务出现了数据倾斜或运行极慢，你该如何优化？”

你可以从以下几个维度进行回答，展现大数据专家的深度：

• 避免全局窗口，提前过滤数据：
  窗口函数 OVER (PARTITION BY order_id) 会导致相同 order_id 的数据 Shuffle 到同一个 Executor。如果某些快递柜有异常数据产生大量重复日志，会导致倾斜。
  • 优化方案：在子查询 cabinet_lifecycle 中，只保留 action_type IN ('PUT', 'TAKE_OUT') 的日志，过滤掉其他无关日志（如 PICKUP、短信发送日志等），减少参与 Shuffle 的数据量。
• 利用 Broadcast Join（广播连接）：
  运单主表 tb_express_order 通常比日志表小得多。如果主表过滤后的大小小于 spark.sql.autoBroadcastJoinThreshold（默认10MB，可调大），可以使用 BROADCAST 提示：
  sql

  -- 强制广播小表，避免大表与小表 Join 时的 Shuffle
  SELECT /*+ BROADCAST(o) */ ...

• 两阶段聚合/加盐（针对极端倾斜）：
  如果某个热点 order_id 产生了百万级日志（如设备故障测试数据），可以对 order_id 进行加盐（加随机前缀）后进行窗口计算，但在此业务场景下，通常在数据接入端（Kafka/Flink）过滤脏数据是更优的选择。