播面

SparkSQL 面试题：大促优惠券核销率与成本分析

在物流与电商平台的大促活动中，优惠券的核销情况和成本控制是运营团队关注的核心指标。请根据以下给定的数据表，编写 SparkSQL 查询，并进行性能优化分析。

一、 场景数据准备

1. 优惠券维度表 (dim_coupon)

记录优惠券的基本信息。

2. 优惠券领取与使用明细表 (fact_coupon_usage)

记录用户领取和使用优惠券的流水信息（status：1-已领取未用，2-已使用）。

二、 面试需求

编写一个 SparkSQL 语句，统计“首小时免单券”和“寄件立减券”两种券的：

1. 已领取数量（received_cnt）
2. 已使用（核销）数量（used_cnt）
3. 核销率（write_off_rate，保留两位小数，展现为百分比形式，如 50.00%）
4. 抵扣的累计总成本（total_cost，即已使用券的抵扣总金额）

三、 核心代码实现 (SparkSQL)

SELECT 
    d.coupon_name,
    COUNT(1) AS received_cnt,
    SUM(CASE WHEN f.status = 2 THEN 1 ELSE 0 END) AS used_cnt,
    -- 计算核销率：已使用 / 已领取
    CONCAT(
        CAST(
            ROUND(
                SUM(CASE WHEN f.status = 2 THEN 1.0 ELSE 0.0 END) / COUNT(1) * 100, 
                2
            ) AS DECIMAL(10, 2)
        ), 
        '%'
    ) AS write_off_rate,
    -- 累计总成本：仅计算已使用的券金额
    SUM(CASE WHEN f.status = 2 THEN d.discount_amount ELSE 0.0 END) AS total_cost
FROM fact_coupon_usage f
JOIN dim_coupon d ON f.coupon_id = d.coupon_id
WHERE d.coupon_name IN ('首小时免单券', '寄件立减券')
GROUP BY d.coupon_name;

预期输出结果：

四、 SparkSQL 深度分析与面试加分项

在面试中，仅仅写出 SQL 只能拿到基础分。若能主动从 Spark 执行引擎机制 和 性能优化 角度进行以下分析，将极大提升面试通过率：

1. Join 优化：广播连接（Broadcast Hash Join）

• 分析：在本题中，维度表 dim_coupon（优惠券配置表）数据量通常极小（几百到几千条），而事实表 fact_coupon_usage（领取与使用流水表）在双十一大促期间数据量可能达到数亿级。
• 优化方案：避免使用默认的 Shuffle Hash Join，通过显式提示（Hint）或配置将维度表广播到各个 Executor，消除 Shuffle 阶段，提升数倍性能。
  sql

  -- SparkSQL 广播优化写法
  SELECT /*+ BROADCAST(d) */ 
      d.coupon_name, ...
  FROM fact_coupon_usage f
  JOIN dim_coupon d ON f.coupon_id = d.coupon_id

2. 避免数据倾斜（Data Skew）

• 分析：大促期间，“首小时免单券”等爆款券的设计会导致 fact_coupon_usage 表中某些 coupon_id 的记录数远超其他券，Join 和 GROUP BY 时极易发生数据倾斜（某个 Task 运行极慢）。
• 解决方案：
  1. 过滤前置：在 Join 之前，先通过 WHERE d.coupon_name IN (...) 过滤维度表，Spark 的 Catalyst 优化器通常会进行谓词下推（Predicate Pushdown），减少参与 Join 的数据量。
  2. 加盐法（Salting）：如果依然发生严重倾斜，可以对事实表的 coupon_id 加上随机前缀（如 0_C001, 1_C001），将维度表复制多份进行 Join，打散数据后再聚合。

3. 聚合优化（Aggregate Performance）

• 分析：SQL 中使用了 SUM(CASE WHEN...) 配合 GROUP BY。
• 优化机制：SparkSQL 会将其转化为 HashAggregate 算子。在 Shuffle 写入前，Spark 会在 Map 端先进行本地预聚合（类似于 MapReduce 的 Combiner），极大地减少了网络传输的数据量。无需担心数据量大导致 Shuffle 撑爆内存。

4. 精度损失防范

• 分析：在计算成本 total_cost 和核销率时，直接使用 FLOAT 或 DOUBLE 会因为二进制浮点数表示问题导致精度丢失（例如 0.1 + 0.2 = 0.30000000000000004）。
• 避坑指南：在处理金额和比例时，必须使用 DECIMAL(precision, scale) 类型进行转换，确保财务数据的准确性。