SparkSQL 面试题：计算在线预约 2 小时内快递柜投递转化率

1. 题目背景与业务定义

在智慧物流场景中，用户通过手机 APP 预约寄件后，需要在规定时间内将快递投递至指定的智能快递柜。
2 小时内投递转化率定义为：在统计周期内，成功在 2 小时内完成快递柜投递的订单量 占 总预约订单量 的比例。

2. 示例数据

表 1：用户预约寄件表 t_express_order（仅包含预约成功的记录）

表 2：快递柜投递流水表 t_cabinet_delivery（记录成功锁门投递的数据）

(注：ORD004 未投递；ORD002 投递耗时 2.5小时；ORD005 投递耗时 4.5小时)

3. SparkSQL 核心实现答案

SELECT 
    COUNT(o.order_id) AS total_orders,
    SUM(CASE WHEN d.delivery_time IS NOT NULL AND 
                  (unix_timestamp(d.delivery_time) - unix_timestamp(o.order_time)) <= 7200 
             THEN 1 ELSE 0 END) AS valid_delivery_orders,
    ROUND(
        SUM(CASE WHEN d.delivery_time IS NOT NULL AND 
                      (unix_timestamp(d.delivery_time) - unix_timestamp(o.order_time)) <= 7200 
                 THEN 1 ELSE 0 END) * 100.0 / COUNT(o.order_id), 
        2
    ) AS conversion_rate_pct
FROM 
    t_express_order o
LEFT JOIN 
    t_cabinet_delivery d 
ON 
    o.order_id = d.order_id;

预期输出结果：

(解析：仅 ORD001 耗时1.5h，ORD003 耗时45分钟，符合2小时内要求。ORD002 和 ORD005 超时，ORD004 未投递。转化率为 2/5 = 40.00%)

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

考点一：多表关联的选择（LEFT JOIN vs INNER JOIN）

• 分析：本题计算的是“转化率”，分母是所有预约成功的订单。如果使用 INNER JOIN，未投递的订单（如 ORD004）会在 Join 阶段被过滤掉，导致分母缺失。因此必须以预约表 t_express_order 为主表进行 LEFT JOIN，保留未完成投递的记录。

考点二：时间差值计算的性能与准确性

• 分析：在 SparkSQL 中，计算两个 Timestamp 的差值有多种方式：
  1. unix_timestamp(t1) - unix_timestamp(t2)：将时间转为秒级时间戳相减。推荐在面试中使用，因为它不依赖 Spark 版本，逻辑清晰，可以直接与 7200（秒）做对比。
  2. datediff 或 date_add：这两个函数只精确到“天”，无法处理小时级和分钟级的精细控制。
  3. CAST(t1 AS LONG) - CAST(t2 AS LONG)：在 Spark 3.x 中，直接将 Timestamp 转换成 LONG 型相减也是一种高效的底层处理方式。

考点三：数据倾斜与 Join 优化（Spark 面试加分项）

• 分析：在实际生产环境中，预约表和投递表通常是每日达千万级甚至亿级的大表。
  • 大表 Join 大表优化：两表均通过 order_id 进行关联。若无特殊情况，Spark 会默认采用 SortMergeJoin (SMJ)。为了避免 Shuffle 引起的数据倾斜，面试中可以提到：若已知 order_id 分布均匀，可提前对两表按照 order_id 进行 Colocate Partition（在建表或写入时进行 Bucket 桶操作），从而在 Join 时实现 Bucket-Join，消除 Shuffle 阶段。
  • 广播 Join 可能性：如果是在特定的小时段内（例如做近1小时的准实时转化率监控），投递表过滤后的数据量极小，可以使用 /*+ BROADCAST(d) */ 强制触发 BroadcastHashJoin (BHJ)，将大表 Join 大表转化为 Map 端 Join，极大地提升执行效率。

考点四：Null 值防范

• 分析：在 LEFT JOIN 后，未投递订单的 delivery_time 会为 NULL。在 CASE WHEN 进行时间差计算时，必须先判断 d.delivery_time IS NOT NULL。虽然 SparkSQL 中 NULL - timestamp 的结果仍为 NULL，在 <= 比较中会自动判定为非真，但显式写出 IS NOT NULL 能够体现出候选人严谨的边界处理思维。