SparkSQL 面试题：计算快递柜平均格口日使用率

1. 题目背景

在物流行业中，快递柜（如丰巢）的格口使用率是衡量资产运营效率的核心指标。本题旨在考察候选人对 SparkSQL 中多表关联、数据聚合（Grouping）、时间差计算以及指标拆解的综合运用能力。

2. 基础数据准备

表 1：快递柜基础信息表 (tbl_cabinet_info)

记录快递柜的物理属性，包括格口总数。

• 测试数据：

表 2：派件投递记录表 (tbl_delivery_record)

记录快递员往快递柜投递入库的记录。

• 测试数据：

表 3：寄件（自寄）投递记录表 (tbl_pickup_record)

记录用户自主到快递柜寄件投递的记录。

• 测试数据：

3. 面试题要求

请编写一段 SparkSQL，计算每个快递柜平均每个格口每天被使用的次数。

• 时间范围：统计 2023-10-01 至 2023-10-02（共 2 天）。
• 使用次数定义：使用次数 = 派件投递件数 + 寄件投递件数。
• 计算公式：$\text{平均单格日使用次数} = \frac{\text{总使用次数}}{\text{格口总数} \times \text{统计天数}}$

期望输出结果：

4. 官方 SparkSQL 参考答案

WITH date_range AS (
    -- 定义统计的天数
    SELECT CAST(datediff(to_date('2023-10-02'), to_date('2023-10-01')) + 1 AS INT) AS stat_days
),
union_records AS (
    -- 合并派件和寄件数据，并过滤在指定时间范围内的数据
    SELECT cabinet_id, CAST(put_time AS DATE) AS put_date FROM tbl_delivery_record
    WHERE put_time >= '2023-10-01 00:00:00' AND put_time <= '2023-10-02 23:59:59'
    
    UNION ALL
    
    SELECT cabinet_id, CAST(put_time AS DATE) AS put_date FROM tbl_pickup_record
    WHERE put_time >= '2023-10-01 00:00:00' AND put_time <= '2023-10-02 23:59:59'
),
use_count_agg AS (
    -- 按快递柜统计总使用次数
    SELECT 
        cabinet_id,
        COUNT(1) AS total_use_count
    FROM union_records
    GROUP BY cabinet_id
)
-- 关联基础表计算最终指标
SELECT 
    c.cabinet_id,
    c.total_cells,
    COALESCE(u.total_use_count, 0) AS total_use_count,
    d.stat_days,
    -- 核心计算：总次数 / (格口数 * 天数) 保留4位小数
    ROUND(
        COALESCE(u.total_use_count, 0) / (c.total_cells * d.stat_days), 
        4
    ) AS avg_daily_cell_usage
FROM tbl_cabinet_info c
CROSS JOIN date_range d -- 引入常量天数
LEFT JOIN use_count_agg u ON c.cabinet_id = u.cabinet_id;

5. SparkSQL 考察点剖析与面试官调优方向

在面试中，仅仅写出上述 SQL 只能达到及格线。面试官通常会针对 Spark 的底层原理进行追问，以下是针对本题的深度剖析和应对策略：

剖析一：Union All vs Union

• 知识点：数据合并的性能损耗。
• 面试应对：明确向面试官指出使用 UNION ALL 而不是 UNION。因为 UNION 会在 Spark 中引入一次额外的 Distinct 操作（涉及 Shuffle 重分区去重），而派件和寄件是独立的业务行为，不需要去重，使用 UNION ALL 可以在 Map 阶段直接合并数据，避免 Shuffle 提高性能。

剖析二：如何避免数据倾斜（Data Skew）

• 场景：现实中，某些“网红”或核心地段的快递柜（如 CAB001）投递量极大，而偏远柜子几乎没有数据。在 GROUP BY cabinet_id 时会发生数据倾斜。
• 面试应答（双重聚合方案）：
  如果发生倾斜，可以使用加盐局部聚合方案：
  1. 先给 cabinet_id 加上随机前缀（如 0_CAB001, 1_CAB001）进行第一次 GROUP BY 聚合。
  2. 去掉前缀进行第二次 GROUP BY 全局聚合。
  3. 或者在 Spark 中开启 Adaptive Query Execution (AQE)（spark.sql.adaptive.skewJoin.enabled=true）让引擎自动处理倾斜连接。

剖析三：Join 的选择与优化

• 场景：tbl_cabinet_info（柜子基础信息表）通常是一张百/千级别行的小表，而 tbl_delivery_record 是亿级的大表。
• 面试应答（MapJoin/BroadcastJoin）：
  在最后一步 tbl_cabinet_info 关联聚合结果时，应该显式或隐式地使用 Broadcast Hash Join (MapJoin)。
  • 原理：将小表广播到每个 Executor 的内存中，在 Map 端完成关联，彻底消除 Shuffle 阶段。
  • 代码示范：在 SparkSQL 中可以使用 Hint：SELECT /*+ BROADCAST(c) */ ...。

剖析四：时间过滤的最佳实践（分区剪裁）

• 场景：大表通常会按照天（如 dt）进行分区。
• 面试应答：
  在实际生产中，绝对不能直接对 Timestamp 类型的字段进行 CAST(put_time AS DATE) 后再做过滤，这会导致分区索引失效（全表扫描）。应该在 WHERE 条件中直接使用分区字段（如 dt >= '2023-10-01' AND dt <= '2023-10-02'）来实现分区剪裁（Partition Pruning），从而只读取对应日期目录下的数据，极大地减少 I/O。