基于本文回答

播面 播面

文图音视，全方位拆解八股文
0
评论
请从左侧选择一个分类

统计每个店铺的退款率（退款订单数 / 总订单数）。输出退款订单数大于 100 且退款率排名前 10 的店铺 ID

SparkSQL 面试题：店铺退款率统计

1. 题目背景与要求

在电商场景中，退款率是衡量商品质量和商户服务的重要指标。请编写 SparkSQL 语句，统计每个店铺的退款率（退款订单数 / 总订单数），并输出满足以下条件的店铺信息：

  1. 退款订单数大于 100；
  2. 按照退款率降序排列，输出前 10 名的店铺 ID、退款订单数、总订单数及退款率（保留4位小数）。

2. 基础数据准备

以下为订单表 t_order_detail 的部分样例数据：

表名：t_order_detail（订单明细表）

order_id (订单ID) shop_id (店铺ID) user_id (用户ID) refund_status (退款状态) order_amount (订单金额)
ord_1001 shop_A usr_01 0（未退款） 100.00
ord_1002 shop_A usr_02 1（已退款） 50.00
ord_1003 shop_B usr_01 1（已退款） 200.00
ord_1004 shop_C usr_03 0（未退款） 120.00
ord_1005 shop_A usr_04 1（已退款） 80.00
ord_1006 shop_B usr_05 0（未退款） 300.00

注：refund_status 为 1 表示退款，0 表示未退款。

3. SparkSQL 核心解法

可以使用 CTE（公用表表达式） 来使结构更加清晰。在计算退款率时，需要注意数据类型转换，防止整除导致精度丢失。

sql 
WITH shop_refund_summary AS (
    SELECT
        shop_id,
        COUNT(1) AS total_order_cnt,
        SUM(CASE WHEN refund_status = 1 THEN 1 ELSE 0 END) AS refund_order_cnt
    FROM t_order_detail
    GROUP BY shop_id
)
SELECT
    shop_id,
    refund_order_cnt,
    total_order_cnt,
    ROUND(CAST(refund_order_cnt AS DOUBLE) / total_order_cnt, 4) AS refund_rate
FROM shop_refund_summary
WHERE refund_order_cnt > 100
ORDER BY refund_rate DESC
LIMIT 10;

4. 面试官视角：深度剖析与考点分析

在实际面试中，写出上述 SQL 只能拿到基础分。面试官通常会针对这段 SQL 进行追问，以下是高频考点及应对策略：

考点一：整除问题与类型转换（精度陷阱）
  • 追问：在 SQL 中直接使用 refund_order_cnt / total_order_cnt 会有什么问题？
  • 解答：如果两个字段在底层被识别为整型（Integer），直接相除在某些 SQL 引擎中会触发整除，导致结果只保留整数部分（例如 1 / 2 = 0）。在 SparkSQL 中，为了确保安全，必须显式将分子或分母 CAST 为 DOUBLE 或 DECIMAL 类型，再进行除法运算。
考点二：聚合函数与 NULL 值处理
  • 追问：SUM(CASE WHEN refund_status = 1 THEN 1 ELSE 0 END) 和 COUNT(refund_status) 有什么区别？
  • 解答：
    • COUNT(column) 会自动过滤掉 NULL 值。
    • 如果 refund_status 存在 NULL 值（如未决订单），使用 CASE WHEN ... THEN 1 ELSE 0 可以显式控制逻辑。
    • 另外，也可以写成更简练的 SUM(IF(refund_status = 1, 1, 0))，在 SparkSQL 中两者执行效率一致。
考点三：SparkSQL 执行计划与数据倾斜（高频高难）
  • 追问：如果大促期间，某些头部店铺（如小米、华为自营店）订单量极大，导致 GROUP BY shop_id 产生严重的数据倾斜（Data Skew），在 Spark 中该如何优化？
  • 解答：
    1. 两阶段聚合（加盐局部聚合 + 全局聚合）：
      • 第一步：给 shop_id 加上随机前缀（如 0_shop_A, 1_shop_A），先进行局部聚合，减少传输的数据量。
      • 第二步：去掉随机前缀，进行二次聚合得到最终结果。
    2. 开启 Spark AQE（自适应查询执行）：
      • 在 Spark 3.x 中，开启 spark.sql.adaptive.enabled=true 和 spark.sql.adaptive.skewJoin.enabled=true，Spark 会自动检测倾斜的 Partition 并进行拆分（Split）。
考点四：ORDER BY 性能优化
  • 追问：最后一步使用了 ORDER BY ... LIMIT 10，Spark 是如何处理这个全局排序的？
  • 解答：
    • 如果仅有 ORDER BY，Spark 需要将所有数据 Shuffle 到一个 Reducer 上进行全局排序，极易造成内存溢出（OOM）。
    • 但是，因为带了 LIMIT 10，Spark 优化器（Catalyst Optimizer）会进行优化：在每个 Executor 局部先找出 Top 10，然后将每个分区仅 10 条数据汇集到 Driver 端进行最后的 Merge Sort（归并排序）。这极大地减少了网络传输的数据量，避免了单点瓶颈。
00:00
00:00