播面

SparkSQL 面试题：找出 2025 年“可能流失”的高价值用户

题目背景

在电商、新零售等行业中，识别高价值流失用户是精准营销的关键。本题要求从用户交易明细中，找出在 2025 年上半年（1-6月）累计消费金额超过 5000 元，但在下半年（7-12月）消费金额为 0（或无消费记录）的“可能流失”用户。

一、 示例数据

1. 用户表（users）

2. 交易订单表（orders）

预期输出结果

注：

• 张三 (101)：上半年消费 5500，但下半年消费了 500，不符合“下半年消费为0”的条件。
• 李四 (102)：上半年消费 6000，下半年无订单，但未与用户表关联时容易遗漏，正确SQL应包含其个人信息（若要求输出用户名）。
• 王五 (103)：上半年消费 1500 + 4000 = 5500 > 5000，下半年无订单，符合条件。
• 赵六 (104)：上半年仅消费 800，不满足 > 5000 条件。
• 钱七 (105)：上半年消费 5500 > 5000，下半年有一笔 0 元订单（或无订单），符合条件。

二、 SparkSQL 面试题答案

解法一：条件聚合（Conditional Aggregation）—— 推荐解法（最高效）

利用 CASE WHEN 配合 SUM 进行条件聚合，一次扫描表即可完成数据分组与筛选，性能最优。

SELECT 
    u.user_id,
    u.user_name,
    t.h1_amount,
    COALESCE(t.h2_amount, 0.00) AS h2_amount
FROM users u
JOIN (
    SELECT 
        user_id,
        SUM(CASE WHEN order_date >= '2025-01-01' AND order_date <= '2025-06-30' THEN order_amount ELSE 0 END) AS h1_amount,
        SUM(CASE WHEN order_date >= '2025-07-01' AND order_date <= '2025-12-31' THEN order_amount ELSE 0 END) AS h2_amount
    FROM orders
    WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31'
    GROUP BY user_id
) t ON u.user_id = t.user_id
WHERE t.h1_amount > 5000 
  AND (t.h2_amount IS NULL OR t.h2_amount = 0);

解法二：LEFT JOIN 差集法 —— 思路最直观

分别计算出上半年和下半年的消费汇总表，然后通过 LEFT JOIN 进行关联排除。

WITH h1_sales AS (
    SELECT user_id, SUM(order_amount) AS h1_amount
    FROM orders
    WHERE order_date >= '2025-01-01' AND order_date <= '2025-06-30'
    GROUP BY user_id
    HAVING SUM(order_amount) > 5000
),
h2_sales AS (
    SELECT user_id, SUM(order_amount) AS h2_amount
    FROM orders
    WHERE order_date >= '2025-07-01' AND order_date <= '2025-12-31'
    GROUP BY user_id
    HAVING SUM(order_amount) > 0
)
SELECT 
    u.user_id,
    u.user_name,
    h1.h1_amount,
    0.00 AS h2_amount
FROM h1_sales h1
INNER JOIN users u ON h1.user_id = u.user_id
LEFT JOIN h2_sales h2 ON h1.user_id = h2.user_id
WHERE h2.user_id IS NULL;

三、 SparkSQL 深度分析与面试加分项

在面试中，仅仅写出 SQL 是不够的，能够针对 SparkSQL 的执行机制、性能优化进行剖析，才能拿到高薪 Offer。以下是针对本题的深度技术分析：

1. 为什么“解法一（条件聚合）”性能远优于“解法二”？

• 减少 Shuffle 阶段：
  • 解法二（LEFT JOIN 差集）需要对 orders 表进行两次分组聚合（产生两次 Stage 划分和两次 Shuffle Write/Read），最后还要做一次 JOIN 关联（再次产生 Shuffle）。
  • 解法一（条件聚合）只需要对 orders 表进行一次过滤和一次 GROUP BY。在 Spark 引擎中，这只会触发一次 Shuffle 过程，大大减少了磁盘 I/O 和网络传输。
• 扫描次数：解法一通过 WHERE order_date BETWEEN... 限定了全年的数据范围，只对 orders 表做了一次全表/分区扫描；而解法二扫描了两次表。

2. SparkSQL 执行计划与优化（Catalyst Optimizer）

在向面试官解释时，可以提到 SparkSQL 的 Catalyst 优化器是如何处理该查询的：

• 谓词下推（Predicate Pushdown）：Spark 会优先将 order_date BETWEEN '2025-01-01' AND '2025-12-31' 的过滤条件推送到数据源端。如果是 Parquet 或 ORC 格式，能直接利用列式存储的 Metadata 进行 Row Group 过滤，甚至避免读取无关的文件块。
• 常量折叠与 Projection 剪枝：不需要的列（如非统计维度的其他订单属性）在读取时会被直接忽略，减少内存占用。

3. 数据倾斜（Data Skew）应对策略

如果 orders 表非常庞大（TB级），且某些大促期间或某些头部渠道的用户产生海量订单，GROUP BY user_id 可能会导致数据倾斜（某些 Task 处理的数据量远大于其他 Task）。

• 解决方案（两阶段聚合）：
  • 先对 user_id 加随机前缀进行局部聚合：GROUP BY user_id, floor(rand() * 10)
  • 去掉前缀进行全局聚合。
• 广播连接（Broadcast Hash Join, BHJ）：
  • 由于 users 表（用户维表）通常相对较小，而计算后的流失用户结果集也很小。在最后与 users 表关联时，Spark 会默认（或通过 /*+ BROADCAST(u) */ Hint 提示）将维表广播到各个 Executor，从而避免了大表之间的 Shuffle Hash Join，将 JOIN 转化为本地 Map 端连接。

4. 边界漏洞防范（Null 值处理）

面试官非常看重候选人对边界情况的严谨度：

• 0元订单与无订单：下半年“无消费”有两种可能：
  1. 下半年完全没有订单记录（LEFT JOIN 结果为 NULL）。
  2. 下半年有订单但金额为 0（如积分兑换、退款单，SUM 结果为 0）。
• 防御性代码：在解法一中，使用 (t.h2_amount IS NULL OR t.h2_amount = 0) 来同时兼容这两种情况；并使用 COALESCE(t.h2_amount, 0.00) 保证报表输出的友好性。