找出在同一天内既搜索过“iPhone 16”，又搜索过“手机壳”的用户 ID

面试真题：寻找同日关联搜索用户

1. 背景介绍

在电商或搜索推荐场景中，用户的关联搜索行为（例如：先搜了手机，紧接着又搜了手机壳）能够极大地帮助平台理解用户的购买意图。本题旨在考察候选人对 分组聚合（GROUP BY）、过滤条件（HAVING） 以及 时间处理 的掌握程度。

2. 示例数据

我们拥有一张用户搜索日志表 search_log，记录了用户的搜索行为：

表名：search_log

log_id	user_id	search_keyword	search_time
1	101	iPhone 16	2024-09-20 10:00:00
2	101	手机壳	2024-09-20 10:05:00
3	102	iPhone 16	2024-09-20 11:00:00
4	102	手机壳	2024-09-21 11:00:00
5	103	iPhone 16	2024-09-20 12:00:00
6	103	钢化膜	2024-09-20 13:00:00
7	104	手机壳	2024-09-21 14:00:00
8	104	iPhone 16	2024-09-21 14:30:00
9	105	手机壳	2024-09-22 09:00:00

数据解析说明：

用户 101：在 2024-09-20 同一天内搜索了 "iPhone 16" 和 "手机壳"（满足条件）。
用户 102：在 2024-09-20 搜索了 "iPhone 16"，在 2024-09-21 搜索了 "手机壳"（跨天，不满足条件）。
用户 103：只搜索了 "iPhone 16"，没有搜索 "手机壳"（不满足条件）。
用户 104：在 2024-09-21 同一天内搜索了 "手机壳" 和 "iPhone 16"（满足条件）。

3. 期望输出结果

user_id
101
104

4. SQL 解答与深度分析

针对这道题，面试中通常有两种经典解法。

解法一：分组聚合（GROUP BY + HAVING）—— 推荐解法

这是最高效、最优雅的写法语义。

sql

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

代码分析与解题步骤：

过滤数据（WHERE）：首先通过 WHERE search_keyword IN ('iPhone 16', '手机壳') 缩小数据范围，只保留与目标关键词相关的记录。这一步能极大减少后续分组的数据量。
提取日期并分组（GROUP BY）：使用 DATE(search_time)（在 Hive 中可用 to_date()，在 Postgres 中可用 CAST(search_time AS DATE)）将精确时间转化为“天”。通过 GROUP BY user_id, DATE(search_time) 把同一个用户在同一天的行为聚集在一起。
条件过滤（HAVING）：使用 HAVING COUNT(DISTINCT search_keyword) = 2。因为我们在第一步已经限制了关键词只有两种，如果去重后的关键词数量等于 2，说明该用户在这一天既搜了 A 也搜了 B。

解法二：自连接（SELF JOIN）

这种方法符合直觉，但在大表上性能较差。

sql

SELECT DISTINCT a.user_id
FROM search_log a
JOIN search_log b 
  ON a.user_id = b.user_id 
 AND DATE(a.search_time) = DATE(b.search_time)
WHERE a.search_keyword = 'iPhone 16'
  AND b.search_keyword = '手机壳';

代码分析与解题步骤：

将 search_log 表自关联（命名为 a 表和 b 表）。
关联条件：必须是同一个用户（a.user_id = b.user_id）且在同一天（DATE(a.search_time) = DATE(b.search_time)）。
筛选条件（WHERE）：限制 a 表搜索的是 "iPhone 16"，b 表搜索的是 "手机壳"。
去重（DISTINCT）：防止同一个用户在同一天多次搜索这两个词导致结果重复。

5. 面试官避坑提示与追问

考点一：时间戳转日期的处理

面试官会观察你是否注意到了 search_time 是包含时分秒的 TIMESTAMP。如果你直接用 ON a.search_time = b.search_time，则变成了“在同一秒内既搜索了...又搜索了...”，这显然是不符合常理的。必须使用 DATE() 函数或格式化函数（如 DATE_FORMAT）将时间截断到天。

考点二：性能优化（自连接 vs 分组聚合）

如果面试官问：“如果 search_log 表每天有数十亿行数据，这两个 SQL 哪个更好？”

回答：解法一（GROUP BY + HAVING）更好。
原因：自连接（Self Join）会产生笛卡尔积，在分布式计算系统（如 Spark/Hive）中会引起严重的数据倾斜（Data Skew）和 Shuffle 阶段内存溢出（OOM）。而解法一只需要进行一次 Map 端的预过滤，再进行标准的 Reduce 分组聚合，执行效率和资源消耗都远优于自连接。

追问：如果要求“先搜索 iPhone 16，再搜索手机壳”（有先后顺序）怎么办？

如果在同一天的基础上，还要求有先后顺序，自连接的优势就体现出来了。我们只需在解法二的基础上加上时间先后限制：

sql

SELECT DISTINCT a.user_id
FROM search_log a
JOIN search_log b 
  ON a.user_id = b.user_id 
 AND DATE(a.search_time) = DATE(b.search_time)
WHERE a.search_keyword = 'iPhone 16'
  AND b.search_keyword = '手机壳'
  AND a.search_time < b.search_time; -- 限制了先后顺序

右滑查看面试常问

播面