跳到主要内容

SQL 全量好友推荐：自连接 + 排除已有好友 + ROW_NUMBER 取 Top N（腾讯面试题）

一、题目背景​

这道题来自腾讯微信事业群的数据开发岗面试。"你可能认识的人"是微信好友推荐的核心功能，背后的算法基础就是共同好友计算——两个非好友用户的共同好友越多，他们现实中认识的概率越大。这道题考察的是如何用一条 SQL 为所有用户批量生成推荐列表，而非只给某一个用户推荐。

业务场景：微信的"朋友推荐"模块每天需要为全量用户更新推荐列表，不能逐用户查一次（那样要上亿次查询）。生产环境通过离线 T+1 批处理，用一条 SQL 为所有用户同时计算出 Top N 推荐好友，写入 Redis 供在线读取。这道题的 SQL 就是该批处理任务的核心逻辑。

二、题目​

现有用户好友关系表 t7_user_friend，包含用户ID及其好友ID，好友关系为双向关注（若A是B的好友，则B也是A的好友）。请为每一位用户推荐潜在好友（非已有好友），按共同好友数量降序排列，取 Top 1。

好友关系表 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          |
+----------+------------+

三、思路分析​

这是典型的多用户共同好友推荐题型，核心是 自连接找共同好友 + LEFT JOIN 排除已有好友 + ROW_NUMBER 分组取 Top N：

  1. 自连接找共同好友：以好友ID为桥梁 t1.friend_id = t2.friend_id，自连接找出任意两个用户（user_a, user_b）共享的好友。t1.user_id != t2.user_id 排除自己和自己的配对
  2. 排除已有好友：用 LEFT JOIN ... WHERE IS NULL 排除已经是直接好友的用户对，只保留陌生人推荐
  3. 分组计数 + 排名：GROUP BY 统计共同好友数，ROW_NUMBER() OVER (PARTITION BY user_a ORDER BY common_friend_cnt DESC) 为每个用户取 Top N
维度评分
题目难度⭐️⭐️⭐️⭐️
题目清晰度⭐️⭐️⭐️⭐️
业务常见度⭐️⭐️⭐️⭐️⭐️

四、逐步推导​

步骤1：自连接找出所有用户对的共同好友​

将好友关系表自连接，连接条件为 t1.friend_id = t2.friend_id（同一个好友作为桥梁），找到每对用户 (user_a, user_b) 之间的共同好友。t1.user_id != t2.user_id 排除自己和自己的无意义配对。

执行SQL

select t1.user_id   as user_a,
       t2.user_id   as user_b,
       t1.friend_id as common_friend
from t7_user_friend t1
         join t7_user_friend t2
              on t1.friend_id = t2.friend_id
where t1.user_id != t2.user_id
order by t1.user_id, t2.user_id

执行结果

+---------+---------+----------------+
| user_a  | user_b  | common_friend  |
+---------+---------+----------------+
| 1       | 2       | 3              |
| 1       | 3       | 2              |
| 1       | 3       | 4              |
| 1       | 4       | 3              |
| 1       | 5       | 2              |
| 1       | 5       | 3              |
| 1       | 5       | 4              |
| 2       | 1       | 3              |
| 2       | 3       | 1              |
| 2       | 3       | 5              |
| 2       | 4       | 1              |
| 2       | 4       | 3              |
| 2       | 4       | 5              |
| 2       | 5       | 3              |
| 3       | 1       | 2              |
| 3       | 1       | 4              |
| 3       | 2       | 1              |
| 3       | 2       | 5              |
| 3       | 4       | 1              |
| 3       | 4       | 5              |
| 3       | 5       | 2              |
| 3       | 5       | 4              |
| 4       | 1       | 3              |
| 4       | 2       | 1              |
| 4       | 2       | 3              |
| 4       | 2       | 5              |
| 4       | 3       | 1              |
| 4       | 3       | 5              |
| 4       | 5       | 3              |
| 5       | 1       | 2              |
| 5       | 1       | 3              |
| 5       | 1       | 4              |
| 5       | 2       | 3              |
| 5       | 3       | 2              |
| 5       | 3       | 4              |
| 5       | 4       | 3              |
+---------+---------+----------------+
36 rows selected (1.073 seconds)(https://www.dwsql.com)

以 user_a=1 为例，common_friend 为 2,3,4，说明用户1通过好友2,3,4分别连接到了其他用户。其中 (1,5) 出现了3次（好友2,3,4都是共同好友），说明1和5的共同好友最多。

步骤2：排除已有好友并统计共同好友数​

步骤1中包含了已经是好友的用户对（如 user_a=1, user_b=2，他们本来就是好友）。通过 LEFT JOIN 原好友关系表，用 WHERE f.user_id IS NULL 排除直接好友，只保留陌生人对。然后 GROUP BY + COUNT(DISTINCT) 统计每对用户的共同好友数。

执行SQL

select t1.user_id                         as user_a,
       t2.user_id                         as user_b,
       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
         left join t7_user_friend f
                   on t1.user_id = f.user_id
                       and t2.user_id = f.friend_id
where t1.user_id != t2.user_id
  and f.user_id is null
group by t1.user_id, t2.user_id
order by t1.user_id, common_friend_cnt desc

执行结果

+---------+---------+--------------------+
| user_a  | user_b  | common_friend_cnt  |
+---------+---------+--------------------+
| 1       | 5       | 3                  |
| 2       | 4       | 3                  |
| 4       | 2       | 3                  |
| 5       | 1       | 3                  |
+---------+---------+--------------------+
4 rows selected (1.254 seconds)(https://www.dwsql.com)

用户3没有出现在 user_a 列中，因为用户3的好友已经覆盖了所有其他用户（1,2,4,5），不存在可推荐的陌生人。其他用户均只有1个陌生人，且共同好友数都是3。

步骤3：用 ROW_NUMBER 为每个用户取 Top N 推荐​

在步骤2的基础上，用 ROW_NUMBER() OVER (PARTITION BY user_a ORDER BY common_friend_cnt DESC) 为每个用户的候选推荐排名，外层取 rn = 1 得到最佳推荐。

执行SQL

select user_a, user_b as recommend_user, common_friend_cnt
from (
    select t1.user_id                         as user_a,
           t2.user_id                         as user_b,
           count(distinct t1.friend_id)       as common_friend_cnt,
           row_number() over (partition by t1.user_id
                              order by count(distinct t1.friend_id) desc) as rn
    from t7_user_friend t1
             join t7_user_friend t2
                  on t1.friend_id = t2.friend_id
             left join t7_user_friend f
                       on t1.user_id = f.user_id
                           and t2.user_id = f.friend_id
    where t1.user_id != t2.user_id
      and f.user_id is null
    group by t1.user_id, t2.user_id
) t
where rn = 1
order by user_a

执行结果

+---------+-----------------+--------------------+
| user_a  | recommend_user  | common_friend_cnt  |
+---------+-----------------+--------------------+
| 1       | 5               | 3                  |
| 2       | 4               | 3                  |
| 4       | 2               | 3                  |
| 5       | 1               | 3                  |
+---------+-----------------+--------------------+
4 rows selected (1.025 seconds)(https://www.dwsql.com)

全量推荐结果：用户1推荐5，用户2推荐4，用户4推荐2，用户5推荐1。用户3无陌生人可推荐（不出现在结果中）。在实际业务中，用户3的推荐位可回退为二度好友或其他推荐策略。

五、常见坑点​

坑1：用 NOT IN 排除已有好友

NOT IN (SELECT ...) 写法虽然直观，但有两大隐患：一是子查询每行都执行，数据量大时性能差；二是子查询结果中若包含 NULL，NOT IN 会返回空集（因为 x NOT IN (1, 2, NULL) 等价于 x <> 1 AND x <> 2 AND x <> NULL，而 x <> NULL 永远为 UNKNOWN）。推荐用 LEFT JOIN ... WHERE IS NULL 代替。

坑2：忘记去重 COUNT(DISTINCT)

自连接时，如果同一对用户有多条共同好友记录（如步骤1中 (1,5) 出现3次），直接 COUNT(*) 会算多次。但共同好友的计算逻辑是"有多少个共同好友"，不是"共同好友关系在表里出现了几次"，必须用 COUNT(DISTINCT t1.friend_id)。

坑3：ROW_NUMBER 在 GROUP BY 后的作用域

ROW_NUMBER() OVER (...) 中可以使用聚合函数的结果（如 ORDER BY count(distinct ...) DESC），窗口函数在 GROUP BY 之后执行，可以正确引用聚合后的列。但注意不能直接在 PARTITION BY 或 ORDER BY 里用别名，需要重复写完整的聚合表达式。

坑4：用户3这类"无陌生人"的边界情况

当某用户的好友覆盖了所有其他用户时（全连接），LEFT JOIN 排除后该用户在 user_a 列为空。结果集中不会出现该用户，业务侧需要对这类用户做降级推荐（如二度好友、热门用户等），否则前端展示空白。

六、举一反三​

  1. 取 Top N 而非 Top 1：将 WHERE rn = 1 改为 WHERE rn <= 3 即可为每个用户推荐最多3个好友，适合"你可能认识的人"列表场景
  2. 最小共同好友数阈值：在步骤2的 HAVING 或步骤3的外层加上 WHERE common_friend_cnt >= 2，过滤共同好友太少的低质量推荐
  3. 双向高相关推荐：在步骤3的结果上，JOIN 反向推荐表，筛选出"A推荐B"且"B也推荐A"的相互推荐对，这类推荐的可信度更高
  4. 加权共同好友：如果好友关系表有时间戳或互动次数字段，可给近期/高频互动的好友更高权重，用加权计数替代 COUNT(DISTINCT)

七、知识点总结​

考点说明
自连接 (Self JOIN)同一张表以好友ID为桥梁 JOIN 自身，找出用户对之间的共同好友
LEFT JOIN 排除已有好友LEFT JOIN + WHERE IS NULL 安全地排除直接好友关系，避免 NOT IN 的 NULL 陷阱
COUNT DISTINCT对共同好友去重计数，确保每个共同好友只算一次
ROW_NUMBER 分组取 Top NPARTITION BY user_a，ORDER BY 共同好友数 DESC，为每个用户独立排名

八、建表语句和数据插入​

点击展开 DDL & DML
CREATE TABLE t7_user_friend (
    user_id   int COMMENT '用户ID',
    friend_id int 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真题

交流微信二维码

你可能还想看

专题
窗口函数专题JOIN 可视化工具
相关题目
01. 向用户推荐好友喜欢的音乐02. 占据好友封面个数05. 连续登录超过N天的用户