播面

SparkSQL 面试题：快递柜工作日与周末用户平均取件耗时对比分析

1. 题目背景与要求

在智慧物流场景中，快递柜的占用率和周转效率是核心运营指标。为了评估用户在不同时间段的取件行为差异，现需要对比计算每个快递柜在工作日（周一至周五）与周末（周六、周日）的用户平均取件耗时（单位：分钟，保留2位小数）。

2. 示例数据

表1：快递柜投递与取件记录表 (express_record)

注：dropoff_time 为投递时间，pickup_time 为用户取件时间。取件耗时 = 取件时间 - 投递时间。

期望输出结果

3. 面试题标准答案 (SparkSQL)

WITH duration_calc AS (
    SELECT 
        cabinet_id,
        -- 计算取件耗时（分钟数）：将时间戳转为秒数相减，再除以60
        (unix_timestamp(pickup_time) - unix_timestamp(dropoff_time)) / 60 AS duration_minutes,
        -- 提取取件时间是星期几：1代表周日，2-6代表周一至周五，7代表周六
        dayofweek(pickup_time) AS pickup_day_of_week
    FROM express_record
    WHERE pickup_time IS NOT NULL AND dropoff_time IS NOT NULL
)
SELECT 
    cabinet_id,
    -- 工作日（周二至周六对应dayofweek的2到6）
    ROUND(
        AVG(CASE WHEN pickup_day_of_week BETWEEN 2 AND 6 THEN duration_minutes ELSE NULL END), 
        2
    ) AS weekday_avg_duration,
    -- 周末（周日对应1，周六对应7）
    ROUND(
        AVG(CASE WHEN pickup_day_of_week IN (1, 7) THEN duration_minutes ELSE NULL END), 
        2
    ) AS weekend_avg_duration
FROM duration_calc
GROUP BY cabinet_id;

4. SparkSQL 深度解析与面试应对策略

在面试中，仅仅写出上述 SQL 是不够的，面试官通常会针对你的设计进行追问。以下是应对面试时需要掌握的底层原理和优化细节：

解析一：日期与时间函数的陷阱（dayofweek vs pmod）

• 标准 dayofweek 函数：SparkSQL 的 dayofweek 返回的是 1 (周日) 到 7 (周六)。很多候选人会直觉地认为 1 是周一、6 是周六，从而导致逻辑错误。在面试中主动指出这一点，能体现你对 Spark 内置函数的精确掌握。
• 替代方案 pmod：如果不确定 dayofweek 的标准，可以使用 pmod(datediff(pickup_time, '1970-01-05'), 7)，1970-01-05 是周一，通过取模运算，0-4 代表周一至周五，5-6 代表周六周日。这是一种更稳健的底层实现方式。

解析二：条件聚合（Conditional Aggregation）的性能优势

• 本题没有使用 UNION ALL 或两次 JOIN 来分别计算工作日和周末，而是使用了 CASE WHEN ... THEN ... ELSE NULL 配合 AVG 进行行转列（Pivot）。
• 性能优势：这种写法只需要对表进行一次扫描（Single Table Scan），避免了多次 Shuffle，极大地节省了集群的 I/O 和计算资源。在处理海量快递数据时，这是最推荐的优化写法。

解析三：Spark 物理执行计划与数据倾斜优化

如果面试官追问：“如果某些热点快递柜（如写字楼下的快递柜）数据量极大，导致 GROUP BY cabinet_id 出现数据倾斜（Data Skew），你该如何优化？”

你可以从以下两个维度回答：

1. 两阶段聚合（放盐法）：
   先对分组 Key 进行加盐打散，聚合一次后再去盐聚合。
   sql

   -- 伪代码示意
   SELECT cabinet_id, AVG(duration) 
   FROM (
       SELECT cabinet_id, split(salted_key, '_')[1], AVG(duration) 
       FROM (SELECT concat(cabinet_id, '_', cast(rand()*10 as int)) as salted_key, ... )
       GROUP BY salted_key
   ) GROUP BY cabinet_id;

2. 启用 Spark 3.x Adaptive Query Execution (AQE)：
   明确指出可以开启 spark.sql.adaptive.skewJoin.enabled = true，Spark 会在运行时自动检测倾斜的分区并进行拆分（Split），避免单个 Task 成为瓶颈。