播面

SparkSQL 经典面试题：特定消费行为活跃用户分析

一、 题目描述

在电商和平台用户画像分析中，筛选具有特定消费特征的用户是极其常见的需求。请根据给定的用户订单明细表，找出在平台购买过至少 3 种不同商品品类，但从未购买过“数码配件”品类的活跃用户 ID。

二、 示例数据

1. 用户订单明细表 (user_orders)

2. 期望输出结果

数据分析说明：

• U001：购买了食品、美妆、服饰共 3 种品类，且没有数码配件，符合条件。
• U002：购买了 3 种品类，但包含“数码配件”，排除。
• U003：仅购买了 2 种品类，排除。
• U004：购买了 4 种品类，且没有数码配件，符合条件。
• U005：仅购买了“数码配件”，排除。

三、 SparkSQL 解决方案

解法一：单表聚合过滤（推荐，性能最优）

利用 GROUP BY 进行一次性聚合，结合 HAVING 子句中的条件表达式进行过滤。这种方法只需对数据进行一次扫描和一次 Shuffle，效率最高。

SELECT 
    user_id
FROM 
    user_orders
GROUP BY 
    user_id
HAVING 
    COUNT(DISTINCT category) >= 3
    AND SUM(CASE WHEN category = '数码配件' THEN 1 ELSE 0 END) = 0

解法二：子查询排除法（逻辑清晰，适合大表广播优化）

先找出购买过“数码配件”的用户，再通过 NOT IN 或 LEFT ANTI JOIN 进行排除，最后分组筛选出品类数大于等于3的用户。

WITH digital_users AS (
    SELECT DISTINCT user_id 
    FROM user_orders 
    WHERE category = '数码配件'
)
SELECT 
    o.user_id
FROM 
    user_orders o
LEFT ANTI JOIN 
    digital_users d 
ON 
    o.user_id = d.user_id
GROUP BY 
    o.user_id
HAVING 
    COUNT(DISTINCT o.category) >= 3

四、 面试官视角：SparkSQL 深度分析与优化

在实际面试中，仅仅写出 SQL 只能拿到及格分。如果能主动向面试官分析 Spark 底层的执行计划、数据倾斜以及性能优化，将是巨大的加分项。

1. 解法一与解法二的执行计划对比 (Physical Plan)

• 解法一（单表聚合）：
  • 执行流程：FileSourceScan -> HashAggregate (局部聚合) -> Exchange (Shuffle) -> HashAggregate (全局聚合) -> Filter (Having条件)。
  • 优势：只涉及单表操作。Spark 在 Shuffle 前会进行 Map 端局部聚合（Partial Aggregate），大大减少了 Shuffle 传输的数据量。
• 解法二（Anti Join）：
  • 执行流程：生成两个分支。分支 A 过滤出“数码配件”用户，分支 B 为全表。然后进行 BroadcastHashJoin（如果“数码配件”用户量小）或 SortMergeJoin，最后再进行 HashAggregate。
  • 适用场景：如果“数码配件”的购买人数极少，且该表有索引或分区，第一步过滤能过滤掉 99% 的数据，那么利用 Spark 的 Broadcast Anti Join 性能会非常卓越。

2. COUNT(DISTINCT) 带来的数据倾斜风险与优化

在解法一中，使用了 COUNT(DISTINCT category)。如果某个热点用户（如爬虫或企业级采购账号）产生了数千万条订单，COUNT(DISTINCT) 会导致该 user_id 所在的 Reduce 任务处理时间极长，引发数据倾斜。

• 优化方案（双重聚合）：
  如果在大规模数据下发生倾斜，可将一条 SQL 拆分为两步聚合，先去重，再计数。

  sql

  WITH uniq_user_category AS (
      -- 第一步：按用户和品类去重，同时标记是否为数码配件
      SELECT 
          user_id,
          category,
          MAX(CASE WHEN category = '数码配件' THEN 1 ELSE 0 END) as is_digital
      FROM 
          user_orders
      GROUP BY 
          user_id, category
  )
  -- 第二步：二次聚合，此时没有 COUNT(DISTINCT)，只有普通的 COUNT
  SELECT 
      user_id
  FROM 
      uniq_user_category
  GROUP BY 
      user_id
  HAVING 
      COUNT(category) >= 3
      AND SUM(is_digital) = 0

  • 原理：通过将 GROUP BY user_id 改为 GROUP BY user_id, category，把原本倾斜在某一个 user_id 上的数据，分散到了多个 category 的组合上，从而实现负载均衡。

3. 核心知识点总结

• 条件计数技巧：SUM(CASE WHEN ... THEN 1 ELSE 0 END) = 0 是 SQL 中表达“从未有过某行为”的高效替代方案，避免了写复杂的子查询。
• Spark 算子选择：在处理排除逻辑时，SparkSQL 的 LEFT ANTI JOIN 性能通常优于 NOT IN（特别是 NOT IN 遇到 NULL 值时可能会产生非预期结果或退化为全表扫描）。