基于本文回答

播面 播面

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

给定整点秒杀活动商品表（商品ID、秒杀开始时间）和订单表，统计秒杀开始后前 5 分钟内对应商品的售出数量和交易额

面试题：整点秒杀活动前 5 分钟销量及销售额统计

1. 题目描述

在电商业务中，秒杀活动具有瞬间高并发、时效性强的特点。现给定两张表：

  1. 秒杀活动商品表 (flash_sale_products)：记录了参与整点秒杀的商品及其对应的秒杀开始时间。
  2. 订单明细表 (orders)：记录了所有商品的下单流水。

请编写 SparkSQL 语句，统计每个秒杀商品在秒杀开始后前 5 分钟内（含第 5 分钟整，即 [start_time, start_time + 5min]）的累计售出数量（total_sold_quantity）和累计交易额（total_amount）。若某商品在秒杀期间无销售记录，销量和交易额显示为 0。

2. 数据样例

表 1：秒杀活动商品表 (flash_sale_products)
product_id (商品ID) start_time (秒杀开始时间)
P001 2023-11-11 10:00:00
P002 2023-11-11 14:00:00
P003 2023-11-11 20:00:00
表 2：订单明细表 (orders)
order_id (订单ID) product_id (商品ID) order_time (下单时间) quantity (购买数量) price (单价)
O_101 P001 2023-11-11 10:01:30 2 100.0
O_102 P001 2023-11-11 10:04:59 1 100.0
O_103 P001 2023-11-11 10:05:00 5 100.0
O_104 P001 2023-11-11 10:06:00 3 100.0
O_105 P001 2023-11-11 09:59:00 1 100.0
O_106 P002 2023-11-11 14:02:00 10 50.0
O_107 P002 2023-11-11 14:05:01 4 50.0
预期输出结果
product_id start_time total_sold_quantity total_amount
P001 2023-11-11 10:00:00 8 800.0
P002 2023-11-11 14:00:00 10 500.0
P003 2023-11-11 20:00:00 0 0.0

注：

  • P001 的订单 O_101, O_102, O_103 在 5 分钟区间内（包含 10:05:00），共 8 件；O_104 超时，O_105 在活动前，不予统计。
  • P002 的订单 O_107 超过了 14:05:00，不予统计。
  • P003 无对应订单，结果补 0。

3. SparkSQL 参考答案

sql 
SELECT
    f.product_id,
    f.start_time,
    COALESCE(SUM(o.quantity), 0) AS total_sold_quantity,
    COALESCE(SUM(o.quantity * o.price), 0.0) AS total_amount
FROM flash_sale_products f
LEFT JOIN orders o 
ON f.product_id = o.product_id
   AND o.order_time >= f.start_time
   AND o.order_time <= f.start_time + INTERVAL 5 MINUTES
GROUP BY 
    f.product_id, 
    f.start_time;

4. SparkSQL 面试考点深度解析

在 SparkSQL 面试中，这道题目看似简单，但涵盖了时间窗口过滤、关联优化、数据倾斜预防等核心大数据处理考察点。以下是针对本题的深度解析：

考点一：时间间隔计算（Interval 表达式）
  • 解析：SparkSQL 支持直观的 INTERVAL 语法。在计算“前 5 分钟”时，使用 f.start_time + INTERVAL 5 MINUTES 相比于传统的 UNIX_TIMESTAMP 转换更具可读性，且能享受 Spark 优化器（Catalyst）的断言下推（Predicate Pushdown）优化。
  • 备选方案：也可以使用时间函数 date_add（对天）或 timestampadd(minute, 5, f.start_time)。
考点二：JOIN 的连接条件放置与 LEFT JOIN 细节
  • 非等值连接（Non-Equi Join）：本题的关联条件除了 product_id 外，还包含了时间范围。
  • 关键点：必须使用 LEFT JOIN，因为秒杀商品可能存在零销量的情况（如样例中的 P003）。
  • 陷阱提示：过滤时间范围的条件 o.order_time >= ... 必须写在 ON 子句中，而不能写在 WHERE 子句中。如果写在 WHERE 中，LEFT JOIN 会退化为 INNER JOIN，导致没有销量的秒杀商品被过滤掉。
考点三：SparkSQL 执行性能优化（高频面试追问）

秒杀场景是典型的高并发、大吞吐场景，面试官通常会追问：“如果 orders 表有数十亿条数据，而秒杀商品表只有几百条，如何优化这个 SQL？”

  1. 广播连接（Broadcast Hash Join, BHJ）：

    • 原理：秒杀商品表 flash_sale_products 通常极小（通常在几百到几千级别），可以将其广播到每个 Executor 节点，避免对海量订单表 orders 进行 Shuffle。
    • 优化代码：
      sql 
      SELECT /*+ BROADCAST(f) */ 
    f.product_id, ...
FROM flash_sale_products f
LEFT JOIN orders o ON ...

  2. 分区裁剪（Partition Pruning）与过滤：

    • 原理：订单表通常会按天或按小时分区（例如 dt 字段）。如果知道秒杀活动发生的日期，应当在关联前先对 orders 表进行分区过滤，减少扫描的数据量。
    • 优化代码：
      sql 
      FROM flash_sale_products f
LEFT JOIN (
    SELECT * FROM orders WHERE dt = '2023-11-11'
) o ON ...
考点四：数据倾斜（Data Skew）处理

秒杀活动往往伴随着极度的数据倾斜（某些爆款商品的订单量是普通商品的百万倍）。

  • 为什么广播连接能解决倾斜：如果采用了 Broadcast Hash Join，由于不需要对大表按 product_id 进行 Shuffle 重分区，也就彻底规避了因为 Shuffle 引起的数据倾斜问题。
  • 如果是两个大表关联：如果无法使用 Broadcast，则需要采用“加盐”（Salting）技术，给热点商品 ID 加上随机前缀，打散后进行聚合，最后再去掉前缀二次聚合。
00:00
00:00