如何通过SQL查询与指定用户拥有最多共同好友的用户
实现方案
现有好友关系表
RELATION,包含user1、user2两个字段,两个用户为好友时表中仅存储一条无顺序的关联记录,无重复数据。要求查询与用户A共同好友数量最多的用户。
实现思路
- 先提取用户A的全部好友集合,兼容A出现在
user1或user2两种场景 - 生成全量双向好友映射表,把原本单向的存储记录转为双向映射,方便匹配任意用户的全部好友
- 将其他用户的好友列表和A的好友列表关联,统计共同好友数量,倒序排序后取最高值即可
基础查询SQL(仅取排名第一的用户)
SELECT ar.u AS target_user, COUNT(*) AS common_friend_num FROM -- 生成双向好友映射 (SELECT user1 AS u, user2 AS friend FROM RELATION UNION ALL SELECT user2 AS u, user1 AS friend FROM RELATION) ar INNER JOIN -- 提取用户A的全部好友 (SELECT IF(user1 = 'A', user2, user1) AS a_friend FROM RELATION WHERE user1 = 'A' OR user2 = 'A') af ON ar.friend = af.a_friend -- 排除用户A自身 WHERE ar.u != 'A' GROUP BY ar.u ORDER BY common_friend_num DESC LIMIT 1;
用示例数据执行该SQL,返回结果为target_user: C, common_friend_num: 3,和预期结果一致。
拓展版本(支持获取所有并列最高的用户)
如果存在多个用户和A的共同好友数量并列最高,可以用窗口函数实现:
WITH all_relation AS ( SELECT user1 AS u, user2 AS friend FROM RELATION UNION ALL SELECT user2 AS u, user1 AS friend FROM RELATION ), a_friends AS ( SELECT IF(user1 = 'A', user2, user1) AS a_friend FROM RELATION WHERE user1 = 'A' OR user2 = 'A' ), common_rank AS ( SELECT ar.u AS target_user, COUNT(*) AS common_friend_num, RANK() OVER(ORDER BY COUNT(*) DESC) AS rk FROM all_relation ar INNER JOIN a_friends af ON ar.friend = af.a_friend WHERE ar.u != 'A' GROUP BY ar.u ) SELECT target_user, common_friend_num FROM common_rank WHERE rk = 1;
内容的提问来源于stack exchange,提问作者devyoon
相关产品推荐
相关产品推荐

