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

1. 题目背景

在智能快递柜运营中，分析用户在工作日（周一至周五）与周末（周六、周日）的取件行为差异，能够帮助运营团队合理规划投递时间、调整免费暂存时长以及优化逆向物流。本题要求计算并对比每个快递柜在工作日与周末的平均取件耗时（单位：分钟）。

2. 示例数据

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

该表记录了每一快件的投递时间和用户实际取件时间。

3. 期望输出结果

数据解析说明：

• CAB001：
  • 工作日取件：R001耗时600分钟，R002耗时720分钟，平均 (600+720)/2 = 660分钟。
  • 周末取件：R003耗时90分钟，R004耗时120分钟，平均 (90+120)/2 = 105分钟。
• CAB002：
  • 工作日取件：R005耗时1440分钟（跨天，但取件日24号是周二，仍算工作日），平均1440分钟。
  • 周末取件：R006耗时1440分钟（跨天，取件日29号是周日，算周末），平均1440分钟。

4. SparkSQL 核心解法答案

WITH time_extracted AS (
    SELECT 
        cabinet_id,
        -- 计算取件耗时（秒数），除以 60 得到分钟数
        (CAST(pickup_time AS LONG) - CAST(dropoff_time AS LONG)) / 60.0 AS duration_minutes,
        -- 提取取件时间是星期几：1 代表周日，2 代表周一，...，7 代表周六
        dayofweek(pickup_time) as pickup_day_of_week
    FROM t_cabinet_record
    WHERE pickup_time IS NOT NULL AND dropoff_time IS NOT NULL
),
classified_data AS (
    SELECT 
        cabinet_id,
        duration_minutes,
        -- 1(周日) 和 7(周六) 为周末，其余为工作日
        CASE 
            WHEN pickup_day_of_week IN (1, 7) THEN 'weekend'
            ELSE 'weekday'
        END AS day_type
    FROM time_extracted
)
SELECT 
    cabinet_id,
    -- 计算工作日平均耗时
    ROUND(AVG(CASE WHEN day_type = 'weekday' THEN duration_minutes END), 2) AS weekday_avg_minutes,
    -- 计算周末平均耗时
    ROUND(AVG(CASE WHEN day_type = 'weekend' THEN duration_minutes END), 2) AS weekend_avg_minutes,
    -- 计算工作日与周末的差值
    ROUND(
        COALESCE(AVG(CASE WHEN day_type = 'weekday' THEN duration_minutes END), 0) - 
        COALESCE(AVG(CASE WHEN day_type = 'weekend' THEN duration_minutes END), 0), 2
    ) AS diff_minutes
FROM classified_data
GROUP BY cabinet_id;

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

在面试中，仅仅写出上述 SQL 是不够的。面试官往往会针对你的答案进行追问，以考察你的 Spark 底层机制、时间函数坑点及大数据优化思维。以下是针对该题目的核心考点深度解析：

考点一：时间与日期函数的“坑”

1. dayofweek 函数的返回值标准：
   • 在 SparkSQL 中，默认的 dayofweek 函数遵循 标准库（Sunday = 1, Saturday = 7）。
   • 面试加分点：主动提及 date_format(pickup_time, 'u') 也可以用来获取星期几（1 代表周一，7 代表周日）。在多引擎（如 Hive, Presto, Spark）混合环境中，确保时间函数的行为一致性非常重要。
2. 时间差计算：
   • 直接将 TIMESTAMP 转换为 LONG 类型进行相减，得到的是秒数差值。这种做法比使用 unix_timestamp(t1) - unix_timestamp(t2) 性能更好，因为减少了函数调用开销。

考点二：行转列（Pivot）的优雅实现

本题要求对比“工作日”与“周末”两个维度，属于典型的条件聚合（Conditional Aggregation）。

• 为什么用 CASE WHEN 配合 AVG：
  在 Spark SQL 中，SELECT AVG(CASE WHEN ... END) 比先 GROUP BY cabinet_id, day_type 再进行 JOIN 或 PIVOT 操作更高效。因为它只需要一次 Shuffle（按 cabinet_id 分组），而通过多表关联来实现行转列会引入多次 Shuffle 阶段，严重降低性能。

考点三：Spark 性能调优与边界处理

1. 数据倾斜（Data Skew）风险：
   • 痛点：某些“网红小区”或“核心写字楼”的快递柜（如 CAB001）业务量极大，而部分偏远地区的快递柜数据极少。按 cabinet_id 进行 GROUP BY 时，会导致严重的数据倾斜。
   • 解决方案：
     1. 如果确定存在倾斜，可以采用两阶段聚合（加盐局部聚合 + 去盐全局聚合）。
     2. 开启 Spark 3.x 的 AQE（Adaptive Query Execution，自适应查询执行），Spark 会自动合并小分区、处理倾斜的分区。
2. Null 值防范：
   • 用户可能存在“未取件”的情况（即 pickup_time 为空）。在第一步 WHERE 过滤中必须显式排除，否则会导致 NullPointerException 或计算结果不准。
   • 计算差值时，使用 COALESCE(..., 0) 防止某些快递柜在周末或工作日没有数据时，出现 NULL - 100 = NULL 的逻辑错误。