腾讯大数据面试SQL-好友推荐系统：共同好友查询
⚠️ 待修正
一、题目背景
这道题来自腾讯微信事业群的数据分析岗面试。微信好友关系链是腾讯最核心的数据资产之一，"你可能认识的人"和"共同好友推荐"功能背后就是这道题的SQL逻辑。通过计算两个非好友用户之间的共同好友数量，给用户推荐最相关的新朋友。
业务场景：微信的"朋友推荐"模块每天为上亿用户推荐潜在好友。推荐算法的第一步就是"找共同好友最多的非好友用户"——共同好友越多，现实中也认识的概率越大。这道题的SQL就是推荐引擎的基础查询。
二、题目
现有用户好友关系表 t7_user_friend，包含用户ID及其好友ID，请为指定用户（如用户1）推荐潜在好友，按照共同好友数量降序排列，取Top 3。
好友关系表 t7_user_friend：
+----------+------------+
| user_id | friend_id |
+----------+------------+
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 2 | 1 |
| 2 | 3 |
| 2 | 5 |
| 3 | 1 |
| 3 | 2 |
| 3 | 4 |
| 3 | 5 |
| 4 | 1 |
| 4 | 3 |
| 4 | 5 |
| 5 | 2 |
| 5 | 3 |
| 5 | 4 |
+----------+------------+
备注：好友关系为双向关注，即若A是B的好友，则B也是A的好友。
三、思路分析
本题要求为指定用户推荐潜在好友，核心思路是"共同好友"：如果用户A和用户C有很多共同好友，但A和C还不是好友，则C是A的潜在好友推荐对象。
解题步骤：
- 通过自连接，以好友为桥梁，找到与目标用户有共同好友的所有用户；
- 排除已经是目标用户好友的用户以及目标用户自身；
- 按共同好友数量降序排列，取Top N；
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
1. 查询目标用户的所有好友
执行SQL
-- 查询用户1的好友列表
select friend_id
from t7_user_friend
where user_id = 1
执行结果
+------------+
| friend_id |
+------------+
| 2 |
| 3 |
| 4 |
+------------+
2. 通过自连接找到与目标用户有共同好友的用户
执行SQL
-- 以好友为桥梁，找到和目标用户(1)有共同好友的用户
select t1.friend_id as common_friend, -- 共同好友ID
t2.user_id as candidate_user -- 候选推荐用户
from t7_user_friend t1
join t7_user_friend t2
on t1.friend_id = t2.friend_id -- 共同好友为桥梁
where t1.user_id = 1 -- 目标用户为1
and t2.user_id != 1 -- 排除目标用户自身
执行结果
+---------------+----------------+
| common_friend | candidate_user |
+---------------+----------------+
| 2 | 1 |
| 2 | 3 |
| 2 | 5 |
| 3 | 1 |
| 3 | 2 |
| 3 | 4 |
| 3 | 5 |
| 4 | 1 |
| 4 | 3 |
| 4 | 5 |
+---------------+----------------+
3. 排除已经是好友的用户，统计共同好友数量
执行SQL
-- 排除已是好友的用户，统计共同好友数量并排序
select t2.user_id as recommend_user,
count(distinct t1.friend_id) as common_friend_cnt
from t7_user_friend t1
join t7_user_friend t2
on t1.friend_id = t2.friend_id
where t1.user_id = 1
and t2.user_id != 1
and t2.user_id not in ( -- 排除已经是好友的用户
select friend_id
from t7_user_friend
where user_id = 1
)
group by t2.user_id
order by common_friend_cnt desc
limit 3
执行结果
+----------------+--------------------+
| recommend_user | common_friend_cnt |
+----------------+--------------------+
| 5 | 3 |
+----------------+--------------------+
分析：用户5与用户1的共同好友有2、3、4共3人，是最值得推荐的好友对象。
五、常见坑点
坑1：NOT IN 子查询的性能陷阱
WHERE user_id NOT IN (SELECT friend_id FROM ...) 在数据量大时性能较差，因为每行都要执行子查询。可以考虑改用 NOT EXISTS 或 LEFT JOIN ... WHERE IS NULL 替代。当子查询结果集有 NULL 时，NOT IN 还会出现意料之外的空结果。
坑2：好友关系单向 vs 双向
题目假设好友关系是双向的（A是B的好友⇔B是A的好友），表中也确实插入了两行。但如果数据源只保证了一行，需要先用 UNION ALL 将关系对调生成双向数据，否则自连接只能匹配到一侧的共同好友。
坑3：候选人为0时的边界情况
如果目标用户的所有非好友用户都没有共同好友（如新用户），查询返回空结果集。业务上应该返回空列表而非报错，外层加 COALESCE 或判空处理。
六、举一反三
- 双向推荐：对于推荐结果中的用户，反过来也计算"目标用户对候选用户的共同好友数"，判断是否为双向高相关推荐
- 加上亲密度权重：如果好友关系表有
interaction_count（互动次数），用加权共同好友数替代简单计数 - 二度好友推荐：先找共同好友，再通过共同好友的好友扩展推荐池（Graph SQL / 递归CTE实现）
- 实时推荐 vs 离线批处理：生产环境中用离线T+1批处理计算全量用户的推荐列表存Redis，在线直接读取，避免实时自连接大表
七、知识点总结
| 考点 | 说明 |
|---|---|
| 自连接 (Self JOIN) | 同一张表按不同条件JOIN自身，通过共同好友ID关联 |
| NOT IN 排除已有好友 | 排除已经是好友的用户，只推荐陌生人 |
| COUNT DISTINCT | 同一共同好友可能多次出现，去重得到真实共同好友数 |
| ROW_NUMBER / LIMIT | 按共同好友数降序取Top N推荐 |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE t7_user_friend (
user_id bigint COMMENT '用户ID',
friend_id bigint COMMENT '好友ID'
) COMMENT '用户好友关系表';
-- 数据插入
INSERT INTO t7_user_friend VALUES
(1, 2),
(1, 3),
(1, 4),
(2, 1),
(2, 3),
(2, 5),
(3, 1),
(3, 2),
(3, 4),
(3, 5),
(4, 1),
(4, 3),
(4, 5),
(5, 2),
(5, 3),
(5, 4);
「数据仓库技术」文章同步更新，不错过每一篇干货
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
