播面

SparkSQL 面试题：快递寄件取消原因占比统计

1. 题目背景

在智慧物流场景中，用户通过手机 APP 预约快递柜寄件后，在实际“投递入柜”前可能会取消订单。分析这些“预约后、投递前”取消订单的原因占比，能够帮助产品和运营团队发现服务痛点（例如：是否存在常态化满柜、运费定价是否偏高、驿站竞争分流等），从而优化快递柜的布局与定价策略。

2. 样例数据

表 1：寄件订单表 (express_orders)

记录用户寄件订单的生命周期状态。

状态说明 (status)：ordered (已预约/待投递), delivered (已投递入柜/完成), cancelled (已取消)。

表 2：快递柜投递流水表 (cabinet_deliveries)

记录快递员或用户实际将包裹投递放入快递柜的动作。如果用户在投递前取消，则此表无对应记录。

3. 面试要求

请编写一段 SparkSQL 语句，统计在投递前取消的订单中，各种取消原因的出现次数以及占总取消订单的比例（保留2位小数，带百分号），并按占比降序排列。

4. 参考答案 (SparkSQL)

WITH cancelled_before_delivery AS (
    -- 1. 筛选出：已预约、已取消，且在投递流水表中无记录的订单
    SELECT 
        o.order_id,
        COALESCE(o.cancel_reason, '未填写') AS cancel_reason
    FROM 
        express_orders o
    LEFT JOIN 
        cabinet_deliveries d 
    ON 
        o.order_id = d.order_id
    WHERE 
        o.status = 'cancelled' 
        AND d.order_id IS NULL  -- 确保投递前取消（无投递记录）
),
reason_counts AS (
    -- 2. 统计每个原因的频数，并通过窗口函数计算总取消数
    SELECT 
        cancel_reason,
        COUNT(1) AS reason_count,
        SUM(COUNT(1)) OVER() AS total_cancelled_count
    FROM 
        cancelled_before_delivery
    GROUP BY 
        cancel_reason
)
-- 3. 计算占比并格式化输出
SELECT 
    cancel_reason,
    reason_count,
    CONCAT(ROUND((reason_count / total_cancelled_count) * 100, 2), '%') AS proportion
FROM 
    reason_counts
ORDER BY 
    reason_count DESC;

预期输出结果：

(注：ord_007 虽然也是取消状态，但因为样例数据中其取消原因为“填错地址重填”，在此处按逻辑一并计入，未在上述精简表格中体现，实际运行以全量合并为准)

5. SparkSQL 考点与深度分析

本题看似简单，但在实际大厂数据研发面试中，能够考察面试者在数据清洗、逻辑抽象及 Spark 执行优化等多个维度的功底。

1. 业务逻辑的精准翻译（左外连接排他）

• 考点：“投递前取消”的定义。
• 分析：不能仅筛选 status = 'cancelled'。在实际业务中，可能存在“用户已经把快递放入柜中，但由于系统故障或特殊原因后台退单”的情况。因此，必须通过 LEFT JOIN 投递流水表，并过滤 d.order_id IS NULL（即 Anti-Join 逻辑），来确保该订单绝对没有发生过实物投递。

2. 窗口函数（Window Function）的高效应用

• 考点：如何在单条 SQL 中同时计算“明细/分组值”与“全局总和”。
• 分析：新手常使用 GROUP BY 计算完各原因数量后，再 CROSS JOIN 一个 SELECT COUNT(1) FROM table 子查询来求总数，这会导致对表进行二次扫描（Scan）。
• SparkSQL 优化：使用 SUM(COUNT(1)) OVER()。Spark 在执行聚合（HashAggregate）后，直接在内存中通过窗口算子计算出全局总和，避免了重复读取底层 HDFS 数据的开销。

3. SparkSQL 执行计划优化（Join 倾斜与选择）

面试官通常会延伸提问：“如果 express_orders 表有百亿级数据，而 cabinet_deliveries 只有百万级，这个 Join 该如何优化？”

• BroadCastHashJoin (MAPJOIN)：由于投递表相对较小，可以强制使用广播连接：
  sql

  SELECT /*+ BROADCAST(d) */ ... FROM express_orders o LEFT JOIN cabinet_deliveries d ...

  这样可以将小表广播到各个 Executor，避免大表在大网内进行 Shuffle（Exchange），极大提升运行效率。
• 空值（Null）过滤与倾斜：由于是 LEFT JOIN 且关联键是 order_id，如果 order_id 存在大量 NULL 值，会导致 Shuffle 倾斜。在 Join 之前应先通过 WHERE order_id IS NOT NULL 进行谓词下推（Predicate Pushdown）过滤。