播面

SparkSQL 面试题：寻找同日特定搜索行为的用户

1. 题目背景

在电商或内容平台上，用户的跨品类/跨商品搜索行为能够反映出其强烈的购买意向。例如，用户在同一天内既搜索了高价值的核心设备（如“iPhone 16”），又搜索了其关联配件（如“手机壳”），这通常是精准营销（如配件交叉销售）的重要信号。

本题旨在考察候选人对 SparkSQL 中数据过滤、分组聚合、自连接（Self-Join）以及条件控制（Conditional Aggregation）的掌握程度和性能优化意识。

2. 示例数据

用户搜索日志表 (user_search_log)

期望输出结果：

解释：

• 101 在 2024-10-01 同时搜索了两个词，符合条件。
• 102 只搜索了 iPhone 16，没有搜索手机壳，不符合。
• 103 虽然两个都搜了，但分别在 10-01 和 10-02（跨天），不符合。
• 104 在 2024-10-01 同时搜索了两个词，符合条件。

3. 经典解答方案

方案一：分组聚合 + 分支过滤（推荐：性能最优）

通过按用户和日期分组，利用 CASE WHEN 或 INT 转换来判断两条记录是否同时存在。这种方法只需要对数据进行一次扫描（Single Scan），避免了昂贵的 Join 操作。

SELECT user_id
FROM user_search_log
WHERE search_keyword IN ('iPhone 16', '手机壳')
GROUP BY user_id, search_date
HAVING COUNT(DISTINCT search_keyword) = 2;

(注意：如果业务保证了单人单日单搜索词的唯一性，COUNT(DISTINCT ...) 可以简化为 COUNT(1) = 2 或使用 MAX/MIN 组合判断以提升性能。)

更高效的变种（避免 COUNT DISTINCT）：

SELECT user_id
FROM user_search_log
WHERE search_keyword IN ('iPhone 16', '手机壳')
GROUP BY user_id, search_date
HAVING MAX(CASE WHEN search_keyword = 'iPhone 16' THEN 1 ELSE 0 END) = 1
   AND MAX(CASE WHEN search_keyword = '手机壳' THEN 1 ELSE 0 END) = 1;

方案二：自连接（Self-Join）

通过将表与其自身连接，匹配相同用户且相同日期，但搜索词不同的记录。

SELECT DISTINCT a.user_id
FROM user_search_log a
JOIN user_search_log b 
  ON a.user_id = b.user_id 
 AND a.search_date = b.search_date
WHERE a.search_keyword = 'iPhone 16'
  AND b.search_keyword = '手机壳';

4. SparkSQL 深度分析与面试官视角

在 SparkSQL 面试中，仅仅给出上述 SQL 只能算及格。面试官通常会针对 Spark 的底层执行引擎（Catalyst）和分布式架构进行追问。以下是帮助你应对面试的深度分析：

① 方案对比与 Shuffle 开销（核心考点）

• 方案一（Group By）的优势：
  • Shuffle 较小：首先通过 WHERE search_keyword IN (...) 过滤掉了绝大部分不相关的搜索记录（减少了输入数据量）。
  • 聚合下推：Spark 可以利用 HashAggregate 在 Map 端先进行局部聚合（Map-side Combine），减少 Shuffle 传输的数据量。
• 方案二（Self-Join）的劣势：
  • Shuffle 剧烈：Join 操作在 Spark 中通常会触发 SortMergeJoin 或 ShuffleHashJoin。即使过滤了关键字，将同一个大表自关联依然会产生两份数据的 Shuffle Read/Write。
  • 数据倾斜风险：如果某些热门日期或热门用户（如爬虫）有极多记录，Join 操作极易导致 Spark 节点内存溢出（OOM）。

② Spark 优化器（Catalyst）行为

在方案一中，Spark 的 Catalyst 优化器会进行谓词下推（Pushdown Predicates）。即先执行 search_keyword IN ('iPhone 16', '手机壳') 过滤，再进行 Exchange（Shuffle）重新分区。这确保了网络传输的数据量降到最低。

③ 追问：如果“手机壳”是维表，数据量极小，如何优化？

如果题目演变为：一边是大表（用户行为），一边是小表（如：特定促销商品清单）。

• 回答：可以采用 Broadcast Hash Join (MapJoin)。通过将小表广播到每个 Executor，避免大表的 Shuffle，从而将 Join 操作的时间复杂度降到 O(N)。
• 在 Spark 中可以通过 Hint 强制指定：/*+ BROADCAST(small_table) */。