基于本文回答

播面 播面

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

统计用户在支付超期费和寄件费时，微信支付、支付宝、银联等不同渠道产生的渠道扣率（手续费）总额及占比

SparkSQL 面试题：支付渠道扣率与占比统计

1. 题目背景

在快递物流或电商场景中，用户在支付“超期费”（如快递滞留件超期管理费）和“寄件费”时，会通过不同的支付渠道（如微信支付、支付宝、银联等）进行结算。每个渠道会根据不同的业务类型收取不同比例的渠道扣率（手续费）。

本题要求编写 SparkSQL 统计：在支付“超期费”和“寄件费”时，微信支付、支付宝、银联等不同渠道产生的渠道扣率（手续费）总额，以及各渠道手续费占总手续费的比例。

2. 基础数据准备

表 1：订单支付流水表 t_order_payment

记录用户的每一笔支付明细。

字段名 类型 说明
order_id String 订单ID
user_id String 用户ID
fee_type String 费用类型：超期费、寄件费、保价费等
pay_channel String 支付渠道：微信支付、支付宝、银联
pay_amount Decimal(10,2) 支付金额（元）
pay_time String 支付时间

表数据示例：

order_id user_id fee_type pay_channel pay_amount pay_time
O001 U101 超期费 微信支付 5.00 2023-10-01 10:00:00
O002 U102 寄件费 支付宝 15.00 2023-10-01 10:05:00
O003 U103 保价费 银联 10.00 2023-10-01 10:10:00
O004 O104 寄件费 微信支付 20.00 2023-10-01 10:15:00
O005 U101 超期费 银联 3.00 2023-10-01 10:20:00
O006 U105 寄件费 支付宝 12.00 2023-10-01 10:25:00
O007 U102 超期费 支付宝 2.00 2023-10-01 10:30:00
表 2：渠道扣率配置表 t_channel_rate

记录不同支付渠道针对不同费用类型收取的扣率（手续费率）。

字段名 类型 说明
pay_channel String 支付渠道
fee_type String 费用类型
rate Decimal(5,4) 扣率（如 0.006 表示 0.6%）

表数据示例：

pay_channel fee_type rate
微信支付 超期费 0.0060
微信支付 寄件费 0.0050
支付宝 超期费 0.0055
支付宝 寄件费 0.0045
银联 超期费 0.0040
银联 寄件费 0.0030

3. 期望输出结果

分析过滤出“超期费”和“寄件费”后，计算出的各渠道手续费及占比如下：

pay_channel total_fee_amount (渠道手续费总额) fee_ratio (手续费占比)
微信支付 0.1300 50.19%
支付宝 0.0885 34.17%
银联 0.0405 15.64%

(注：计算过程：微信 = 50.006 + 200.005 = 0.13；支付宝 = 150.0045 + 120.0045 + 20.0055 = 0.0885；银联 = 30.004 + 100.003但保价费剔除，剩余银联超期费 30.004 = 0.0120，此数据仅作参考)

4. SparkSQL 核心查询答案

sql 
WITH prep_fee AS (
    -- 1. 筛选特定费用类型，并关联扣率表计算每笔订单的手续费
    SELECT 
        p.pay_channel,
        (p.pay_amount * r.rate) AS single_fee
    FROM t_order_payment p
    JOIN t_channel_rate r 
      ON p.pay_channel = r.pay_channel 
     AND p.fee_type = r.fee_type
    WHERE p.fee_type IN ('超期费', '寄件费')
),
channel_summary AS (
    -- 2. 按渠道汇总手续费
    SELECT 
        pay_channel,
        SUM(single_fee) AS channel_fee_sum
    FROM prep_fee
    GROUP BY pay_channel
)
-- 3. 结合窗口函数计算总手续费，并求出占比
SELECT 
    pay_channel,
    ROUND(channel_fee_sum, 4) AS total_fee_amount,
    CONCAT(ROUND(channel_fee_sum / SUM(channel_fee_sum) OVER() * 100, 2), '%') AS fee_ratio
FROM channel_summary
ORDER BY total_fee_amount DESC;

5. SparkSQL 深度剖析与面试应对技巧

为了在面试中脱颖而出，仅仅写出 SQL 是不够的，你需要向面试官展示你对 SparkSQL 执行机制、性能调优和边界情况的深度理解。可以从以下几个维度进行阐述：

① 窗口函数（Window Function）的妙用
  • 分析：在计算占比时，传统 SQL 需要通过子查询（JOIN 一个 GROUP BY 后的总和表）来获取全局总手续费。
  • Spark 优化：这里使用了窗口函数 SUM(channel_fee_sum) OVER()。这避免了额外的 JOIN 操作。在 Spark 内部，由于 channel_summary 已经是聚合后的少量数据，使用全局无 PARTITION BY 的窗口函数开销极小。
② 避免数据倾斜（Data Skew）
  • 分析：如果平台业务量极大，微信或支付宝的支付数据可能远多于银联，导致在 JOIN 和 GROUP BY 时出现数据倾斜。
  • 解决方案：
    • Broadcast Hash Join (BHJ)：由于配置表 t_channel_rate 数据量极小（通常只有几十行），Spark 默认会触发广播连接。在面试中可以主动提及：“因为扣率配置表是极小表，Spark 会将其广播到每个 Executor，从而将 Shuffle Hash Join 转化为 Map-side Join，彻底避免了 JOIN 阶段的数据倾斜。”
    • 两阶段聚合：如果 GROUP BY pay_channel 出现倾斜，可以在大表 t_order_payment 上先加随机前缀进行局部聚合，再去掉前缀全局聚合。但由于本题渠道数极少（仅微信、支付宝、银联），直接 GROUP BY 即可，Shuffle 数据量很小。
③ 精度丢失问题（Decimal vs Double）
  • 分析：在金融和收单业务中，手续费计算对精度要求极高。
  • Spark 最佳实践：不要使用 Float 或 Double，必须使用 Decimal 类型。在 SQL 中，pay_amount * rate 会自动将精度提升。最终展示时，使用 ROUND(value, 4) 保留 4 位小数，避免因为浮点数科学计数法或精度丢失导致财务对账不平。
④ 过滤下推（Predicate Pushdown）
  • 分析：在 prep_fee 临时表中，WHERE p.fee_type IN ('超期费', '寄件费') 这一过滤条件至关重要。
  • Spark 优化：Spark 催化剂优化器（Catalyst Optimizer）会自动进行“谓词下推”，在读取 t_order_payment 数据源时就过滤掉不需要的行（如保价费），从而减少参与 JOIN 的数据量，极大地节省了 I/O 和内存带宽。
00:00
00:00