腾讯大数据面试SQL-用户好友关系链分析
⚠️ 待修正
一、题目背景
这道题来自腾讯微信/QQ的社交图谱数据分析岗面试。社交关系链是腾讯最核心的数据资产——"好友的好友"（二度好友）是好友推荐、社交广告投放和社区发现的基础。分析每个用户的二度好友规模可以量化其"社交拓展潜力"。
业务场景：微信的"你可能认识的人"推荐列表，核心逻辑就是"好友的好友"。如果A和B有很多共同好友，但A和B还不是好友，那么系统就会把B推荐给A。二度好友关系链分析就是这个推荐逻辑的第一步。
二、题目
现有一张用户好友关系表 t14_user_friend，记录了用户之间的好友关系（好友关系是双向的，表中每条记录都表示一条单向关系，但实际好友关系两边都有记录）。请分析每个用户的二度好友（好友的好友）数量，即通过一个中间好友可以触达、但尚未成为直接好友的用户数。
好友关系表 t14_user_friend：
+----------+------------+
| user_id | friend_id |
+----------+------------+
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 3 |
| 2 | 4 |
| 3 | 1 |
| 3 | 2 |
| 3 | 5 |
| 4 | 2 |
| 4 | 5 |
| 4 | 6 |
| 5 | 3 |
| 5 | 4 |
| 5 | 6 |
| 6 | 4 |
| 6 | 5 |
+----------+------------+
三、思路分析
二度好友即"好友的好友"，在社交网络中常用于"可能认识的人"推荐。核心分析思路：
- 找到好友的好友：通过自连接，以目标用户的好友为桥梁，找到这些好友的其他好友；
- 排除已是直接好友的：过滤掉已经是目标用户直接好友的用户；
- 排除目标用户自身：防止自己出现在结果中；
- 按用户分组统计：计算每个用户的潜在好友（二度好友）数量；
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
1. 通过自连接找到每个用户的好友的好友
执行SQL
-- 以好友为桥梁进行自连接
select t1.user_id, -- 目标用户
t1.friend_id, -- 直接好友（中间桥梁）
t2.friend_id as friend_of_friend -- 好友的好友
from t14_user_friend t1
join t14_user_friend t2
on t1.friend_id = t2.user_id -- t1的好友 = t2的用户
执行结果（部分）
+----------+------------+------------------+
| user_id | friend_id | friend_of_friend |
+----------+------------+------------------+
| 1 | 2 | 1 |
| 1 | 2 | 3 |
| 1 | 2 | 4 |
| 1 | 3 | 1 |
| 1 | 3 | 2 |
| 1 | 3 | 5 |
| 2 | 1 | 2 |
| 2 | 1 | 3 |
| 2 | 3 | 1 |
| 2 | 3 | 2 |
| 2 | 3 | 5 |
| 2 | 4 | 2 |
| 2 | 4 | 5 |
| 2 | 4 | 6 |
...
2. 排除自身和已经是直接好友的用户，统计二度好友数量
执行SQL
select t1.user_id,
count(distinct t2.friend_id) as second_degree_friend_cnt
from t14_user_friend t1
join t14_user_friend t2
on t1.friend_id = t2.user_id -- 好友的好友
and t2.friend_id != t1.user_id -- 排除自身
and t2.friend_id not in ( -- 排除已是直接好友的
select friend_id
from t14_user_friend t3
where t3.user_id = t1.user_id
)
group by t1.user_id
order by t1.user_id
执行结果
+----------+---------------------------+
| user_id | second_degree_friend_cnt |
+----------+---------------------------+
| 1 | 2 |
| 2 | 1 |
| 3 | 2 |
| 4 | 1 |
| 5 | 1 |
| 6 | 2 |
+----------+---------------------------+
3. 展开查看每个用户的二度好友详情
执行SQL
select t1.user_id,
t2.friend_id as second_degree_friend,
t1.friend_id as through_friend
from t14_user_friend t1
join t14_user_friend t2
on t1.friend_id = t2.user_id
and t2.friend_id != t1.user_id
and t2.friend_id not in (
select friend_id
from t14_user_friend t3
where t3.user_id = t1.user_id
)
order by t1.user_id, t2.friend_id
执行结果
+----------+----------------------+----------------+
| user_id | second_degree_friend | through_friend |
+----------+----------------------+----------------+
| 1 | 4 | 2 |
| 1 | 5 | 3 |
| 2 | 5 | 3 |
| 3 | 4 | 2 |
| 3 | 6 | 5 |
| 4 | 3 | 2 |
| 5 | 2 | 3 |
| 6 | 2 | 4 |
| 6 | 3 | 5 |
+----------+----------------------+----------------+
例如用户1：通过好友2可触达用户4（二度好友），通过好友3可触达用户5（二度好友），共2个潜在好友。
五、常见坑点
坑1：自连接的数据膨胀
好友表有N条记录，自连接产生N×N行。如果好友表有1亿条记录，自连接会产生天文数字。实践中需要先筛选目标用户再加 LIMIT 或使用图数据库。
坑2：DISTINCT 去重的必要性
通过不同共同好友可能找到相同的二度好友（如用户4既是好友2的好友，也是好友3的好友），需用 COUNT(DISTINCT f2.friend_id) 去重得到正确数量。
坑3：好友关系单向 vs 双向的处理
如果好友表只存了单向关系（A→B），需要 UNION ALL 反向复制确保双向查询都成立。本题假设已双向存储。
六、举一反三
- 三度好友：再加一层自连接，计算"好友的好友的好友"，社交圈继续扩展
- 共同好友加权：对每个二度好友，计算与目标用户共享的好友数量作为推荐权重
- 社区发现：基于二度好友的重叠度，用Louvain等算法做社区聚类
- 路径分析：用递归CTE计算任意两个用户之间的最短好友路径长度（六度分隔理论验证）
七、知识点总结
| 考点 | 说明 |
|---|---|
| 自连接 | 好友表JOIN自身，以共同好友为桥梁找到二度好友 |
| DISTINCT 去重 | 多个共同好友可能指向同一个二度好友 |
| NOT IN / NOT EXISTS | 排除自身和已有好友 |
| 社交图谱SQL | 图遍历在关系型数据库中的模拟实现 |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE t14_user_friend (
user_id bigint COMMENT '用户ID',
friend_id bigint COMMENT '好友ID'
) COMMENT '用户好友关系表';
-- 数据插入
INSERT INTO t14_user_friend VALUES
(1, 2),
(1, 3),
(2, 1),
(2, 3),
(2, 4),
(3, 1),
(3, 2),
(3, 5),
(4, 2),
(4, 5),
(4, 6),
(5, 3),
(5, 4),
(5, 6),
(6, 4),
(6, 5);
「数据仓库技术」文章同步更新，不错过每一篇干货
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
