播面

面试真题

题目背景：
在物流末端配送中，快递柜的格口配比（大、中、小格口的数量比例）直接影响网点的服务质量和运营成本。如果某种格口长期爆满，而另一种格口长期空置，说明格口配比不合理。

题目要求：
编写一条 SparkSQL 语句，统计每个快递柜在过去 90 天内，每日 18:00（派件高峰期）大、中、小格口的平均占用率（占用格口数 / 该类格口总数）。

数据准备

1. 快递柜基础信息表：locker_dim（维度表）

记录快递柜物理属性，假设格口总数在 90 天内未发生变更。

样例数据：

2. 快递柜状态历史快照表：locker_status_snapshot（事实表）

每小时记录一次快递柜的占用情况。

样例数据（以 90 天内部分数据为例）：

SparkSQL 解决方案

SELECT
    d.locker_id,
    -- 核心计算：计算过去90天 18:00 快照的平均占用率，使用 NULLIF 防止除以 0 异常
    ROUND(AVG(s.large_occupied * 1.0 / NULLIF(d.large_total, 0)), 4) AS avg_large_occupancy_rate,
    ROUND(AVG(s.medium_occupied * 1.0 / NULLIF(d.medium_total, 0)), 4) AS avg_medium_occupancy_rate,
    ROUND(AVG(s.small_occupied * 1.0 / NULLIF(d.small_total, 0)), 4) AS avg_small_occupancy_rate
FROM
    locker_dim d
JOIN
    locker_status_snapshot s ON d.locker_id = s.locker_id
WHERE
    -- 限制时间范围：过去 90 天内（不含当天）
    s.snapshot_time >= DATE_SUB(CURRENT_DATE(), 90)
    AND s.snapshot_time < CURRENT_DATE()
    -- 筛选每日 18:00 派件高峰期数据
    AND HOUR(s.snapshot_time) = 18 
    AND MINUTE(s.snapshot_time) = 0
GROUP BY
    d.locker_id;

核心考点与深度解析

在面试中，这道题看似简单，但面试官实际在考察以下几个大数据工程实践中的核心能力：

1. 健壮性设计：防除以零（NULLIF 的妙用）

• 面试话术：“在实际生产环境中，快递柜维度表可能存在脏数据（如某柜体未配备大格口，large_total 为 0）。直接相除会导致 Division by zero 报错中断任务。我使用了 NULLIF(d.large_total, 0)，当总数为 0 时将其转为 NULL，这样相除的结果也是 NULL，配合 AVG 函数时会自动忽略这些无效值，确保了任务的健壮性。”

2. 性能优化：广播连接（Broadcast Hash Join）

• 面试话术：“在表关联上，快递柜维度表（locker_dim）的数据量通常较小（十万级），而状态快照表（locker_status_snapshot）是每日每小时记录的流水表，数据量极大（百亿级）。为了避免大表与小表 Join 时产生昂贵的 Shuffle，我会在 Spark 中开启广播连接（Broadcast Hash Join），通过 /*+ BROADCAST(d) */ 提示 Spark 将维度表广播到各 Executor 节点，大幅提升执行效率。”

3. 隐式类型转换与精度控制

• 面试话术：“occupied 和 total 在表中都是 Integer 类型。在 Spark 中，两个 Integer 相除（如 8 / 10）可能会因为整除机制导致结果变为 0（有些 SQL 引擎的特性）。因此我先乘以 1.0 将其显式转化为 Double 类型，再进行除法和 AVG 聚合，最后使用 ROUND(..., 4) 保留四位小数（百分比后保留两位），平衡了存储精度与可读性。”

4. 过滤下推与分区裁剪（Partition Pruning）

• 面试话术：“在实际的物理存储中，快照事实表通常会按照天（如 dt）进行分区。在 WHERE 条件中，我优先使用 DATE_SUB(CURRENT_DATE(), 90) 进行范围限制，这能使 Spark 触发分区裁剪，只扫描过去 90 天的数据目录，避免全表扫描。同时，将 HOUR 和 MINUTE 过滤条件尽量下推到 Scan 阶段，减少参与 Join 的数据量。”