面试题：智能快递柜故障率分析与最差型号定位

1. 背景描述

在物联网（IoT）和新零售场景下，快递柜的稳定运行至关重要。若快递柜的格口（Slot）处于“维修中”或“锁定不可用”状态，会直接影响业务收入和用户体验。现需要通过 SparkSQL 分析各快递柜的格口状态，计算每个柜体的故障率，并找出故障率最高（即质量最差）的柜体型号。

2. 模拟数据表

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

记录快递柜的物理ID及对应的柜体型号。

表2：fact_slot_status（格口实时状态事实表）

记录每个快递柜中各个格口的当前状态。状态值包括：normal（正常）、repair（维修中）、locked（锁定不可用）。

3. 面试需求

1. 需求一：统计每个快递柜处于“维修中（repair）/锁定不可用（locked）”状态的格口数量占其总格口数的比例（保留4位小数）。
2. 需求二：找出故障格口占比最高的柜体型号（Model）。故障率定义为：该型号下所有故障格口总数 / 该型号下所有格口总数。

4. SparkSQL 解决方案

需求一：统计每个快递柜的故障格口比例

SELECT 
    s.cabinet_id,
    c.model,
    COUNT(s.slot_id) AS total_slots,
    SUM(CASE WHEN s.status IN ('repair', 'locked') THEN 1 ELSE 0 END) AS faulty_slots,
    ROUND(
        SUM(CASE WHEN s.status IN ('repair', 'locked') THEN 1 ELSE 0 END) / COUNT(s.slot_id), 
        4
    ) AS cabinet_failure_rate
FROM fact_slot_status s
JOIN dim_cabinet c ON s.cabinet_id = c.cabinet_id
GROUP BY s.cabinet_id, c.model;

需求二：找出故障率最高的柜体型号

WITH model_failure_summary AS (
    SELECT 
        c.model,
        SUM(CASE WHEN s.status IN ('repair', 'locked') THEN 1 ELSE 0 END) AS total_faulty_slots,
        COUNT(s.slot_id) AS total_slots,
        SUM(CASE WHEN s.status IN ('repair', 'locked') THEN 1 ELSE 0 END) / COUNT(s.slot_id) AS model_failure_rate
    FROM fact_slot_status s
    JOIN dim_cabinet c ON s.cabinet_id = c.cabinet_id
    GROUP BY c.model
)
SELECT 
    model, 
    ROUND(model_failure_rate, 4) AS max_failure_rate
FROM model_failure_summary
ORDER BY model_failure_rate DESC
LIMIT 1;

5. 面试官视角：SparkSQL 深度分析与优化

本题看似是一道基础的 JOIN + GROUP BY 聚合题，但优秀的候选人应能主动从 Spark 运行机制、性能优化、数据倾斜等维度进行深入分析。

考点一：Join 策略选择（广播连接）

• 分析：在实际生产中，dim_cabinet（维度表）的数据量通常较小（数万台快递柜），而 fact_slot_status（格口状态表）数据量极大（数百万甚至千万级格口）。
• 优化建议：默认的 Common Join 会产生大量的 Shuffle。应当主动采用 Broadcast Hash Join (MapJoin)。
• SparkSQL 改写：
  sql

  -- 强制使用广播流
  SELECT /*+ BROADCAST(c) */ 
      s.cabinet_id, ...
  FROM fact_slot_status s
  JOIN dim_cabinet c ON s.cabinet_id = c.cabinet_id

  这可以省去 Shuffle Stage，极大地提升执行效率。

考点二：聚合算子的优化（CASE WHEN vs FILTER）

• 分析：传统的 SUM(CASE WHEN ... THEN 1 ELSE 0 END) 表达比较繁琐，Spark 2.0+ 引入了标准 SQL 过滤语法。
• 高级写法：
  sql

  -- 使用 FILTER 语法，代码可读性更高，执行引擎对此类统计有深度优化
  SELECT 
      c.model,
      COUNT(s.slot_id) FILTER (WHERE s.status IN ('repair', 'locked')) / COUNT(s.slot_id) AS failure_rate

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

• 问题：如果某些热门型号的快递柜极其多，或者某些故障型号产生的数据量异常，GROUP BY c.model 可能会导致数据倾斜，导致个别 Reducer 成为长尾任务。
• 解决方案：
  1. 两阶段聚合：如果发现倾斜，可以先给 Group Key 加上随机前缀（如 concat(model, '_', floor(rand() * 10))）进行局部聚合，再去掉前缀进行全局聚合。
  2. 开启 Spark 3.x AQE（自适应查询执行）：
     plaintext

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

考点四：Null 值防范

• 安全建议：在实际业务中，COUNT(s.slot_id) 作为分母时，必须考虑分母为 0 的情况（虽然本场景物理意义上不存在，但数据清洗不干净时可能发生）。应该用 NULLIF(COUNT(s.slot_id), 0) 或 COALESCE 进行安全包裹，防止出现 NaN 或零除异常。