播面

面试题：整点秒杀活动前 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)

表 2：订单明细表 (orders)

预期输出结果

注：

• 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 参考答案

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 加上随机前缀，打散后进行聚合，最后再去掉前缀二次聚合。