PostgreSQL：统计用户间的共同好友数量

核心思路 #

要统计不同用户间的共同好友数量，关键是先生成不重复的用户对，再通过好友表匹配两个用户共同的好友ID并计数。

完整SQL实现 #

with users (user_id, user_name)
as (values 
(7,' Adam'),
(5,' Tom'),
(35,' Bob'),
(72,' Charlie'),
(2,' Maria'),
(10,' Isabel')
),

friendships (user_id, friend_id)
as ( values
(7, 101),
(7, 102),
(7, 103),
(7, 104),
(7, 105),
(35, 101),
(35, 102),
(35, 103),
(35, 104),
(35, 105),
(7, 201),
(7, 202),
(7, 203),
(2, 201),
(2, 202),
(2, 203),
(7, 301),
(7, 302),
(72, 301),
(72, 302),
(5, 401),
(5, 402),
(5, 403),
(5, 404),
(5, 405),
(5, 406),
(2, 401),
(2, 402),
(2, 403),
(2, 404),
(2, 405),
(2, 406),
(5, 501),
(5, 502),
(5, 503),
(5, 504),
(10, 501),
(10, 502),
(10, 503),
(10, 504),
(5, 601),
(35, 601),
(35, 602),
(35, 603)
)
-- 核心查询部分
SELECT
    u1.user_id AS id_1,
    u1.user_name AS name_1,
    u2.user_id AS id_2,
    u2.user_name AS name_2,
    COUNT(f1.friend_id) AS common_friends_count
FROM users u1
JOIN users u2 ON u1.user_id < u2.user_id  -- 生成不重复的用户对，避免重复统计
JOIN friendships f1 ON u1.user_id = f1.user_id
JOIN friendships f2 ON u2.user_id = f2.user_id AND f1.friend_id = f2.friend_id  -- 匹配共同好友
GROUP BY u1.user_id, u1.user_name, u2.user_id, u2.user_name
ORDER BY id_1, id_2;

代码解释 #

• 生成不重复用户对：通过users u1 JOIN users u2 ON u1.user_id < u2.user_id，确保每对用户只出现一次（比如用户7和35不会同时以35和7的形式出现），避免重复计算。
• 匹配共同好友：将两个用户的好友表通过friend_id连接，找到同时存在于两个用户好友列表中的ID。
• 计数与分组：按用户对分组，统计共同好友的数量，最后关联用户表获取用户名。

输出结果 #

执行上述SQL后，会得到符合需求的结果：

id_1      name_1      id_2      name_2      common_friends_count
7         Adam        2         Maria       3
7         Adam        35        Bob         5
7         Adam        72        Charlie     2
5         Tom         2         Maria       6
5         Tom         10        Isabel      4
5         Tom         35        Bob         1
...

注：若需要过滤掉共同好友数为0的用户对，可在查询末尾添加HAVING COUNT(f1.friend_id) > 0。

内容的提问来源于stack exchange，提问作者Joehat