PostgreSQL如何查询拥有最多好友的所有用户（含并列情况）

表结构 #

CREATE TABLE Friendship (
    User1 INT,
    User2 INT,
    PRIMARY KEY (User1, User2),
    CONSTRAINT columns_cannot_equal CHECK (User1 <> User2)
);

示例数据 #

user_id,friend_id
134443,5888
232309,8909
232309,185595
232309,172634
232309,118037
80195,46650
80195,80429
80195,216751
80195,224625
175987,175483
175987,243131
175987,141773
175987,226862
247382,68616
247382,183025
101421,91108
101421,159948
101421,12395
101421,55391
201263,104651
201263,175709
201263,243725
201263,141645
241625,266922
241625,184460
241625,103497
241625,16108
4860,175056
4860,103123
4860,184967

原有查询（仅返回单个用户） #

之前的查询语句只能返回好友数最多的单个用户：

SELECT user_id, COUNT(*) AS friend_count
FROM (
    SELECT User1 AS user_id
    FROM Friendship
    UNION ALL
    SELECT User2 AS user_id
    FROM Friendship
) AS all_users
GROUP BY user_id
ORDER BY friend_count DESC
LIMIT 1;

需求说明 #

需要返回所有拥有最高好友数的用户，当多个用户好友数同为最大值时，全部列出。

解决方案 #

方法1：使用窗口函数（推荐）

利用RANK()窗口函数直接筛选排名第一的所有用户：

WITH user_friend_counts AS (
    SELECT user_id, COUNT(*) AS friend_count
    FROM (
        SELECT User1 AS user_id FROM Friendship
        UNION ALL
        SELECT User2 AS user_id FROM Friendship
    ) AS all_users
    GROUP BY user_id
)
SELECT user_id, friend_count
FROM (
    SELECT 
        user_id, 
        friend_count,
        RANK() OVER (ORDER BY friend_count DESC) AS rnk
    FROM user_friend_counts
) AS ranked_users
WHERE rnk = 1;

方法2：先查询最大好友数再关联

先计算最高好友数，再筛选出所有好友数等于该值的用户：

WITH user_friend_counts AS (
    SELECT user_id, COUNT(*) AS friend_count
    FROM (
        SELECT User1 AS user_id FROM Friendship
        UNION ALL
        SELECT User2 AS user_id FROM Friendship
    ) AS all_users
    GROUP BY user_id
),
max_friend_count AS (
    SELECT MAX(friend_count) AS max_count FROM user_friend_counts
)
SELECT user_id, friend_count
FROM user_friend_counts, max_friend_count
WHERE friend_count = max_count;

对您尝试代码的说明 #

您之前的代码仅获取了最大好友数，但未关联回对应的用户ID。上述两种方法既获取了最大值，又匹配到所有符合条件的用户。

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