面试题：快递格口竞争频次统计

1. 业务背景

在快递柜日常运营中，存在“派件”（快递员放快递）和“寄件”（用户寄出快递）两种业务模式。在每天的 17:00 - 20:00 派件高峰期，快递员经常面临无箱门可投递的窘境。其中一个重要原因是：部分用户下单的寄件包裹长期占用格口（放入格口后，迟迟未被揽收员取走），导致派件高峰期格口资源紧张，快递员尝试投递失败。

本题旨在通过分析快递员投递失败的记录与寄件占用的重合关系，统计各个格口的竞争频次。

2. 基础数据准备

表 1：快递员派件尝试表 (courier_delivery)

记录快递员尝试将派件放入格口的行为及结果。

表 2：用户寄件记录表 (user_sending)

记录用户投递寄件、以及揽收员取走寄件的时间。

3. 统计规则与判定指标

• 高峰时段：attempt_time 在 17:00:00 至 20:00:00 之间（包含边界）。
• 长期占用：在快递员尝试派件时，该寄件包裹已在格口中累计占用超过 6 小时（即 attempt_time - put_in_time >= 6 hours），且此时该寄件尚未被取走（pick_up_time 为 NULL 或 pick_up_time > attempt_time）。
• 格口竞争发生判定：
  1. 快递员在该时段内尝试派件且状态为 Failed。
  2. 此时，该格口正被满足长期占用条件的寄件包裹霸占。

4. 期望输出结果

解释：

• L001-B01：快递员在 17:30 (A001) 和 19:30 (A004) 两次投递失败。当时该格口被 P1001 占用（从 24 日 10:00 开始，已超 6 小时），计 2 次。
• L001-B03：快递员在 19:00 (A003) 投递失败。当时被 P1003 占用（从 25 日 05:00 开始，已超 14 小时），计 1 次。
• L002-B02：快递员在 17:45 (A006) 投递失败，虽然被 P1004 占用，但 P1004 是 17:15 放入的，仅占用了 30 分钟，不符合“长期占用（>=6小时）”的定义，不计入。
• A005：不在高峰时间段（16:30），不计入。

5. SparkSQL 解决方案

WITH filtered_delivery AS (
    -- 1. 过滤出高峰期内投递失败的记录
    SELECT 
        locker_id,
        box_id,
        attempt_time
    FROM courier_delivery
    WHERE status = 'Failed'
      AND CAST(attempt_time AS TIME) >= '17:00:00'
      AND CAST(attempt_time AS TIME) <= '20:00:00'
)
SELECT 
    d.locker_id,
    d.box_id,
    COUNT(1) AS competition_count
FROM filtered_delivery d
INNER JOIN user_sending u 
    ON d.locker_id = u.locker_id 
   AND d.box_id = u.box_id
WHERE 
    -- 2. 寄件包裹在派件尝试时仍未被取走（或已被取走，但取走时间在派件尝试之后）
    u.put_in_time <= d.attempt_time
    AND (u.pick_up_time IS NULL OR u.pick_up_time > d.attempt_time)
    -- 3. 寄件包裹长期占用：放入时间距离快递员尝试投递时间已超过 6 小时
    -- 使用 Unix 时间戳相减（单位：秒），6 小时 = 21600 秒
    AND (unix_timestamp(d.attempt_time) - unix_timestamp(u.put_in_time)) >= 21600
GROUP BY 
    d.locker_id, 
    d.box_id;

6. SparkSQL 面试考点与深度分析

考点一：非等值 Join（Non-Equi Join）与时序重叠判定

• 原理分析：
  在分布式计算中，传统的 Shuffle Hash Join 和 Sort Merge Join 最擅长处理等值关联（如 ON a.id = b.id）。本题中除了格口 ID 的等值关联外，还包含了时间区间的交叉判定（put_in_time <= attempt_time 且 pick_up_time > attempt_time）。
• Spark 优化应对：
  Spark 在处理非等值 Join 时，可能会退化为极其低效的 Nested Loop Join。
  在实际生产中，我们可以通过以下方式进行优化：
  1. 双重过滤：在 Join 之前，先利用 WHERE 子句尽可能过滤掉不属于 17:00-20:00 范围的派件数据，如 filtered_delivery CTE 的作用，大幅减少参与 Join 的右表数据量。
  2. 广播连接（Broadcast Hash Join）：如果过滤后的派件尝试表或者寄件表有一方小于 spark.sql.autoBroadcastJoinThreshold（默认 10MB），Spark 会自动把小表广播到各个 Executor，从而避免昂贵的 Shuffle，直接在 Map 端完成非等值区间匹配。

考点二：时间戳算术运算与时区敏感性

• 语法陷阱：
  在计算“长期占用超过 6 小时”时，直接使用 d.attempt_time - u.put_in_time 在某些 SQL 引擎中返回的是 Interval 类型，在 Spark SQL 中推荐使用 unix_timestamp 转化为秒级秒差进行精确数学计算：
  unix_timestamp(t1) - unix_timestamp(t2) >= 21600。
  也可以使用 Spark 内置函数 datediff / hour 辅助计算，但 unix_timestamp 是对跨天精度、跨时区计算最稳健的一种方式。

考点三：NULL 值的特殊处理

• 核心细节：
  在用户寄件表中，未被揽收的包裹其 pick_up_time 为 NULL。在判定包裹是否仍占用格口时，必须写成 (u.pick_up_time IS NULL OR u.pick_up_time > d.attempt_time)。
• 面试提分点：
  若直接写 u.pick_up_time > d.attempt_time，根据三值逻辑（Three-valued logic），NULL > attempt_time 会返回 Unknown，导致那些至今仍未被取走的超长占用寄件被过滤掉，造成统计结果严重偏小。

考点四：数据倾斜（Data Skew）防范

• 工业实战场景延伸：
  在实际业务中，某些“网红”快递柜或核心商圈的快递柜（locker_id）使用频次极高，而偏远地区的快递柜极少使用，这会导致以 locker_id 进行 Join 和 Group By 时出现严重的数据倾斜。
• 解决方案：
  如果面试官追问如何优化倾斜，可以回答：加盐法（Salting）。
  将大表（如寄件表）的 locker_id 加上随机前缀（如 1_L001，2_L001），并将小表（派件表）根据相同的范围进行膨胀拷贝，以此打散热点格口，避免单个 Spark Task 撑爆内存（OOM）。