播面

SparkSQL 面试题：计算寄件场景下快递柜平均等待时长

1. 题目背景与要求

在智能快递柜寄件场景中，用户投递包裹并锁定柜门后，包裹即处于“待揽收”状态，直到快递员开柜将包裹取出并扫码揽收。
请编写一段 SparkSQL 动态计算：从用户锁定柜门，到快递员开柜取出包裹之间，所有包裹的平均等待时长（单位：分钟）。

2. 示例数据

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

记录用户和快递员对快递柜的所有操作事件。

3. 期望输出结果

注：

• PKG_9901 等待时长：8:00 到 10:30 = 150分钟
• PKG_9902 等待时长：8:30 到 12:00 = 210分钟
• PKG_9903 等待时长：9:00 到 10:15 = 75分钟
• 平均值：(150 + 210 + 75) / 3 = 165.0 分钟

4. SparkSQL 参考答案

WITH user_put AS (
    SELECT 
        package_id,
        event_time AS put_time
    FROM tb_cabinet_log
    WHERE event_type = 'LOCK' AND operator_role = 'USER'
),
courier_take AS (
    SELECT 
        package_id,
        event_time AS take_time
    FROM tb_cabinet_log
    WHERE event_type = 'OPEN' AND operator_role = 'COURIER'
)
SELECT 
    COUNT(DISTINCT u.package_id) AS total_packages,
    ROUND(
        AVG(
            (CAST(c.take_time AS LONG) - CAST(u.put_time AS LONG)) / 60
        ), 1
    ) AS avg_waiting_minutes
FROM user_put u
JOIN courier_take c ON u.package_id = c.package_id;

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

在面试中，仅仅给出上述 SQL 代码是不够的，面试官通常会针对 SparkSQL 的执行细节、性能调优和边界情况进行追问。以下是应对面试的深度分析：

(1) 时间差值计算的 Spark 语法选择

在 SparkSQL 中，计算两个 TIMESTAMP 的时间差有多种方式：

• Unix 时间戳法（本题采用）：使用 CAST(time AS LONG) 将时间戳转换为秒级 Unix 时间戳，相减后再除以 60。这种方式在 Spark 3.x 中极其稳定且精度高，适合跨天、跨小时的精确分钟计算。
• 内置函数法：unix_timestamp(take_time) - unix_timestamp(put_time) 也是常用方案，但在 Spark 3.0 之后，出于安全考虑，默认时区处理可能引发异常，推荐直接使用 CAST 转换。

(2) 物理执行计划与 Shuffle 优化 (Join 优化)

面试官可能会问：“如果 tb_cabinet_log 表有百亿级数据，这个 Query 该如何优化？”

• 避免全表扫描（Pushdown Filtering）：在 WITH 子句中，我们先执行了 WHERE event_type = 'LOCK' 和 WHERE event_type = 'OPEN'。Spark 优化器（Catalyst）会进行谓词下推，在 Shuffle 之前过滤掉不相关的数据（如快递员存件、用户取件等日志），极大减少了参与 Join 的数据量。
• 广播连接 (Broadcast Hash Join)：如果“用户投递”和“快递员揽收”中有一方的数据量较小（例如：每日增量分析时），可以使用 /*+ BROADCAST(u) */ 提示，强制将小表广播到各个 Executor，从而避免昂贵的 Shuffle 阶段。

(3) 边界异常与鲁棒性考虑

在生产环境下，数据往往存在缺失，面试时主动提及以下边界情况会极大增加通过率：

• 有存无取（未完结订单）：部分包裹可能在当天未被揽收，导致 courier_take 中无对应记录。本题采用 INNER JOIN 自动过滤掉了未完成揽收的包裹。若要统计“当前仍在柜中滞留的包裹”，则需改用 LEFT JOIN 并处理 take_time IS NULL 的情况。
• 重复数据（同一包裹多次投递）：如果格口异常导致用户重复关门，同一 package_id 可能存在多条 LOCK 记录。在实际数仓建设中，应先通过 ROW_NUMBER() OVER (PARTITION BY package_id ORDER BY event_time DESC) 取最后一次（或第一次）操作时间，确保数据的唯一性。