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 |
+----------+------------+
三、思路分析
二度好友即"好友的好友"，在社交网络中常用于"可能认识的人"推荐。核心分析思路：
- 找到好友的好友：通过自连接，以目标用户的好友为桥梁，找到这些好友的其他好友；
- 排除目标用户自身：
t2.friend_id != t1.user_id防止自己出现在二度好友结果中； - 用 LEFT JOIN 排除已有好友：
LEFT JOIN ... WHERE IS NULL排除已是直接好友的用户，比 NOT IN 更安全（避免 NULL 陷阱）； - 按用户分组统计：
COUNT(DISTINCT)去重后计算每个用户的二度好友数量；
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
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 | 4 |
| 1 | 2 | 3 |
| 1 | 2 | 1 |
| 1 | 3 | 5 |
| 1 | 3 | 2 |
| 1 | 3 | 1 |
| 2 | 1 | 3 |
| 2 | 1 | 2 |
| 2 | 3 | 5 |
| 2 | 3 | 2 |
| 2 | 3 | 1 |
| 2 | 4 | 6 |
| 2 | 4 | 5 |
| 2 | 4 | 2 |
| 3 | 1 | 3 |
| 3 | 1 | 2 |
| 3 | 2 | 4 |
| 3 | 2 | 3 |
| 3 | 2 | 1 |
| 3 | 5 | 6 |
| 3 | 5 | 4 |
| 3 | 5 | 3 |
| 4 | 2 | 4 |
| 4 | 2 | 3 |
| 4 | 2 | 1 |
| 4 | 5 | 6 |
| 4 | 5 | 4 |
| 4 | 5 | 3 |
| 4 | 6 | 5 |
| 4 | 6 | 4 |
| 5 | 3 | 5 |
| 5 | 3 | 2 |
| 5 | 3 | 1 |
| 5 | 4 | 6 |
| 5 | 4 | 5 |
| 5 | 4 | 2 |
| 5 | 6 | 5 |
| 5 | 6 | 4 |
| 6 | 4 | 6 |
| 6 | 4 | 5 |
| 6 | 4 | 2 |
| 6 | 5 | 6 |
| 6 | 5 | 4 |
| 6 | 5 | 3 |
+----------+------------+-------------------+
44 rows selected (0.449 seconds)(https://www.dwsql.com)
2. 排除自身和已是直接好友的用户，查看二度好友明细
步骤1的结果中包含了两类"噪音"：friend_of_friend = user_id（用户自身）和 friend_of_friend 已经是直接好友的。通过 LEFT JOIN 原好友关系表，用 WHERE t3.user_id IS NULL 排除这两类数据，得到纯净的二度好友明细。同时保留 t1.friend_id 作为中间桥梁好友，便于追溯推荐来源。
执行SQL
select t1.user_id,
t1.friend_id as through_friend,
t2.friend_id as second_degree_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 -- 排除自身
left join t14_user_friend t3
on t1.user_id = t3.user_id
and t2.friend_id = t3.friend_id -- 排除已是直接好友的
where t3.user_id is null
order by t1.user_id, t2.friend_id
执行结果
+----------+-----------------+-----------------------+
| user_id | through_friend | second_degree_friend |
+----------+-----------------+-----------------------+
| 1 | 2 | 4 |
| 1 | 3 | 5 |
| 2 | 3 | 5 |
| 2 | 4 | 5 |
| 2 | 4 | 6 |
| 3 | 2 | 4 |
| 3 | 5 | 4 |
| 3 | 5 | 6 |
| 4 | 2 | 1 |
| 4 | 2 | 3 |
| 4 | 5 | 3 |
| 5 | 3 | 1 |
| 5 | 3 | 2 |
| 5 | 4 | 2 |
| 6 | 4 | 2 |
| 6 | 5 | 3 |
+----------+-----------------+-----------------------+
16 rows selected (10.362 seconds)(https://www.dwsql.com)
以用户1为例：通过好友2可触达二度好友4，通过好友3可触达二度好友5。注意用户2本身也是用户1的直接好友，但在步骤1中 (1,2,3) 这条记录（friend_of_friend=3 已是直接好友）已被 LEFT JOIN 排除。
3. 按用户分组统计二度好友数量
在步骤2明细的基础上，按 user_id 分组，用 COUNT(DISTINCT second_degree_friend) 统计每个用户的二度好友数。
执行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
left join t14_user_friend t3
on t1.user_id = t3.user_id
and t2.friend_id = t3.friend_id
where t3.user_id is null
group by t1.user_id
order by t1.user_id
执行结果
+----------+---------------------------+
| user_id | second_degree_friend_cnt |
+----------+---------------------------+
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 4 | 2 |
| 5 | 2 |
| 6 | 2 |
+----------+---------------------------+
6 rows selected (1.305 seconds)(https://www.dwsql.com)
用户1和用户3的二度好友最多（各2个），用户4和5最少（各1个）。二度好友越多，社交拓展潜力越大。
五、常见坑点
坑1：自连接的数据膨胀
好友表有N条记录，自连接产生N×N行。如果好友表有1亿条记录，自连接会产生天文数字。实践中需要先筛选目标用户再加 LIMIT 或使用图数据库。
坑2：NOT IN 子查询的 NULL 陷阱
常见的排除已有好友的写法是 t2.friend_id NOT IN (SELECT friend_id FROM ...)，但如果子查询结果中包含 NULL，NOT IN 会返回空集（因为 x NOT IN (1, 2, NULL) 等价于 x <> 1 AND x <> 2 AND x <> NULL，而任何值与 NULL 比较都为 UNKNOWN）。推荐用 LEFT JOIN ... WHERE IS NULL 替代，逻辑清晰且无 NULL 风险。
坑3：DISTINCT 去重的必要性
通过不同共同好友可能找到相同的二度好友（如用户4既是好友2的好友，也是好友3的好友），需用 COUNT(DISTINCT t2.friend_id) 去重得到正确数量。
坑4：好友关系单向 vs 双向的处理
如果好友表只存了单向关系（A→B），需要 UNION ALL 反向复制确保双向查询都成立。本题假设已双向存储。
六、举一反三
- 三度好友：再加一层自连接，计算"好友的好友的好友"，社交圈继续扩展
- 共同好友加权：对每个二度好友，计算与目标用户共享的好友数量作为推荐权重
- 社区发现：基于二度好友的重叠度，用Louvain等算法做社区聚类
- 路径分析：用递归CTE计算任意两个用户之间的最短好友路径长度（六度分隔理论验证）
七、知识点总结
| 考点 | 说明 |
|---|---|
| 自连接 | 好友表 JOIN 自身，以好友为桥梁找到二度好友（好友的好友） |
| DISTINCT 去重 | 多个共同好友可能指向同一个二度好友 |
| LEFT JOIN 排除已有好友 | LEFT JOIN + WHERE IS NULL 安全排除直接好友，避免 NOT IN 的 NULL 陷阱 |
| 社交图谱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真题
