基于本文回答

播面 播面

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

统计用户在快递柜寄件时，通过微信小程序、支付宝小程序、App 及开放 API 各渠道产生的订单量及对应的实付运费金额

SparkSQL 面试题：快递寄件多渠道订单及运费统计

题目背景

在智慧物流场景中，用户通过快递柜寄件有多种入口（如微信小程序、支付宝小程序、APP、开放API等）。为了评估各渠道的获客能力和营收贡献，现需要统计每个渠道的总订单量及总实付运费金额。

1. 示例数据

表 1：订单主表 t_express_order

记录用户在快递柜投递的所有寄件订单信息。

order_id (订单ID) user_id (用户ID) channel_code (渠道编码) order_status (订单状态) pay_amount (实付运费/元) create_time (创建时间)
ORD001 U1001 wechat_mini SUCCESS 12.00 2023-10-25 08:30:00
ORD002 U1002 alipay_mini SUCCESS 15.50 2023-10-25 09:15:00
ORD003 U1003 app SUCCESS 18.00 2023-10-25 10:00:00
ORD004 U1004 open_api SUCCESS 120.00 2023-10-25 11:20:00
ORD005 U1001 wechat_mini CANCELLED 0.00 2023-10-25 12:00:00
ORD006 U1005 wechat_mini SUCCESS 10.00 2023-10-25 14:30:00
ORD007 U1002 alipay_mini SUCCESS 15.50 2023-10-25 15:00:00
ORD008 U1006 unknown SUCCESS 8.00 2023-10-25 16:10:00

表 2：渠道维度表 t_channel_dict

记录渠道编码与中文名称的映射关系。

channel_code (渠道编码) channel_name (渠道名称)
wechat_mini 微信小程序
alipay_mini 支付宝小程序
app 手机App
open_api 开放API

2. 面试题要求

编写一条 SparkSQL 语句，统计已支付成功（SUCCESS）的订单中，微信小程序、支付宝小程序、App 及开放 API 各渠道的：

  1. 订单量（order_count）
  2. 实付运费总金额（total_pay_amount，保留两位小数）

注意：过滤掉非上述四个渠道的数据（如示例中的 unknown），结果按实付运费总金额降序排列。

3. 正确 SQL 答案

sql 
SELECT 
    c.channel_name,
    COUNT(o.order_id) AS order_count,
    ROUND(SUM(o.pay_amount), 2) AS total_pay_amount
FROM 
    t_express_order o
JOIN 
    t_channel_dict c ON o.channel_code = c.channel_code
WHERE 
    o.order_status = 'SUCCESS'
    AND o.channel_code IN ('wechat_mini', 'alipay_mini', 'app', 'open_api')
GROUP BY 
    c.channel_name
ORDER BY 
    total_pay_amount DESC;

期望输出结果

channel_name order_count total_pay_amount
开放API 1 120.00
支付宝小程序 2 31.00
微信小程序 2 22.00
手机App 1 18.00

4. SparkSQL 深度分析与面试应对指南

在 SparkSQL 面试中，仅仅写出上述 SQL 只能算及格。面试官通常会针对该 SQL 追问其在 Spark 底层的执行逻辑和优化手段。以下是应对面试的硬核解析：

解析一：执行计划与 Join 优化（Broadcast Hash Join）

  • 面试官追问：“如果 t_express_order 是百亿级大表，而 t_channel_dict 只有几条数据，Spark 会如何执行这个 Join？你会怎么优化？”
  • 通关回答：
    1. 默认机制：Spark 默认会尝试使用 Broadcast Hash Join (BHJ)，将小表（t_channel_dict）广播到所有的 Executor 节点上，在 Map 端直接进行 Join，从而避免了昂贵的 Shuffle 阶段。
    2. 调优参数：可以通过 spark.sql.autoBroadcastJoinThreshold（默认 10MB）来控制自动广播的大小的阈值。
    3. 显式 Hint：在 SQL 中可以显式声明广播提示，确保 Spark 强制执行广播关联，避免因元数据估算不准退化为 SortMergeJoin：
      sql 
      SELECT /*+ BROADCAST(c) */ c.channel_name, ...

解析二：避免数据倾斜（Data Skew）

  • 面试官追问：“如果微信小程序的寄件量占了 90% 以上，在执行 GROUP BY 时出现了数据倾斜（某个 Task 运行极慢），你怎么解决？”
  • 通关回答：
    1. 两阶段聚合（放盐法/两阶段聚合）：
      • 第一阶段：给 Group By 的 Key 加上随机前缀（如 concat(channel_name, '_', cast(rand() * 10 as int))），将倾斜的数据分散到多个 Task 中进行局部聚合。
      • 第二阶段：去掉随机前缀，进行全局聚合。
    2. 启用 Spark 3.x AQE（自适应查询执行）：
      • 开启 spark.sql.adaptive.skewJoin.enabled = true，Spark 会在运行时检测倾斜的分区，并自动将其拆分成多个子分区分别与另一端关联，最后合并结果。

解析三：过滤下推与投影列裁剪（Filter Pushdown & Project Column Pruning）

  • 面试官追问：“SparkSQL 在读取这两个表的数据时，做了哪些优化？”
  • 通关回答：
    1. 谓词下推（Predicate Pushdown）：Spark 优化器（Catalyst）会将 o.order_status = 'SUCCESS' 和 o.channel_code IN (...) 这些过滤条件下推到数据源端（如 Parquet/ORC 文件的读取阶段）。在读取时就过滤掉不符合条件的数据，减少了传输到内存的数据量。
    2. 列裁剪（Column Pruning）：Spark 只会读取 SQL 中用到的列（如 order_id, channel_code, pay_amount, order_status），而不会读取订单表中的 create_time, user_id 等无关列，极大地节省了 I/O 带宽。
00:00
00:00