面试题：快递寄件时段需求分析

题目背景：
在物流和快递行业中，合理调配快递员资源、预测网点压力至关重要。现有一张快递寄件订单明细表 parcel_orders，记录了用户的寄件下单时间。请编写 SparkSQL 查询，统计过去 3 个月内各时间段（按星期和小时段，如：周一 18:00-19:00）的下单总量，并找出寄件需求最旺盛的 Top 5 时段。

1. 样例数据

输入数据表：parcel_orders（寄件订单表）

注：假设当前计算的基准系统时间为 2023-11-01，过去 3 个月的范围是 2023-08-01 至 2023-11-01。以上数据均在此范围内。

2. 期望输出结果

根据样例数据统计，Top 5 热门时段输出如下：

3. SparkSQL 解决方案

WITH prepped_data AS (
    SELECT 
        order_id,
        -- 1. 提取星期几（英文全称，如 Monday）
        date_format(create_time, 'EEEE') AS weekday,
        -- 2. 提取星期几的数字索引（1=周一, 7=周日），用于辅助排序（可选）
        date_format(create_time, 'u') AS weekday_idx,
        -- 3. 构造 24 小时制的时间区间 (如 18:00-19:00)
        concat(
            lpad(hour(create_time), 2, '0'), ':00-', 
            lpad(hour(create_time) + 1, 2, '0'), ':00'
        ) AS hour_interval
    FROM parcel_orders
    -- 4. 过滤过去 3 个月的数据（使用 current_date()，此处为演示可用指定日期代替）
    WHERE create_time >= add_months(current_date(), -3)
)
SELECT 
    weekday,
    hour_interval,
    count(1) AS order_count
FROM prepped_data
GROUP BY weekday, weekday_idx, hour_interval
ORDER BY order_count DESC, weekday_idx ASC
LIMIT 5;

4. SparkSQL 面试核心考点与深度解析

面试官通过这道题，主要考察候选人在 时间函数处理、性能优化 以及 分布式计算原理 方面的功底：

考点一：时间与日期函数的高效运用

• add_months(current_date(), -3)：这是 SparkSQL 中动态获取过去 3 个月边界的标准写法。面试中要避免硬编码日期。
• date_format(create_time, 'EEEE')：EEEE 模式可以直接输出周几的完整英文名称（如 Monday）。如果需要中文，在 Spark 环境下通常需要处理 locale，或者使用 CASE WHEN dayofweek(create_time)... 进行映射。
• lpad(hour, 2, '0')：补零操作。如果不做 lpad，9:00-10:00 格式在排序时会因为字符集顺序产生错乱。使用 lpad 将其规范为 09:00-10:00，保证了格式整齐和排序正确。

考点二：SparkSQL 性能优化（分区剪裁）

• 痛点：在海量快递数据中，直接对 create_time（通常是 Timestamp 类型）进行 WHERE 过滤会导致全表扫描。
• 优化点：面试时应主动向面试官提及 “分区剪裁（Partition Pruning）”。
  • 如果表是以天分区的（如分区字段为 dt），应该先计算出 3 个月前的具体日期 dt_limit，然后在 WHERE 中加上 dt >= dt_limit，这样 Spark 仅会读取对应分区的目录，大幅减少 I/O。

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

• 背景：双十一、双十二或每天傍晚（如 18:00-19:00）是寄件高峰，会导致某些特定 Group By Key 的数据量远大于其他 Key。
• Spark 层面解决方案：
  1. 开启 AQE（Adaptive Query Execution，自适应查询执行）：
     sql

     SET spark.sql.adaptive.enabled = true;
     SET spark.sql.adaptive.skewJoin.enabled = true;

  2. 两阶段聚合（加盐法）：如果发现 Group By weekday, hour_interval 发生严重数据倾斜，可以在 Key 上拼接一个随机数（如 0-9）进行第一次局部聚合，然后再去掉随机数进行第二次全局聚合，从而打散倾斜的 Task。

考点四：聚合算子的物理执行计划

• 面试官可能会追问：这个 SQL 产生了哪些物理聚合算子？
• 解答：Spark 在执行 GROUP BY 时，会根据数据类型和内存情况选择 HashAggregate 或 SortMergeAggregate。
  • 在该查询中，首先在 Map 端进行预聚合（Fast-path HashAggregate），减少 Shuffle 数据传输量。
  • 经过 Shuffle（Exchange 算子）后，在 Reduce 端再次进行 HashAggregate 完成最终计数。