如何编写SQL查询获取每个国家评分最多的用户？

数据库表结构 #

CREATE TABLE Country (
    ISO_3166 CHAR(2) PRIMARY KEY,
    CountryName VARCHAR(256),
    CID varchar(16)
);
CREATE TABLE Users (
    UID INT PRIMARY KEY,
    Username VARCHAR(256),
    DoB DATE,
    Age INT,
    ISO_3166 CHAR(2) REFERENCES Country (ISO_3166)
);
CREATE TABLE Book (
    ISBN VARCHAR(17) PRIMARY KEY,
    Title VARCHAR(256),
    Published DATE,
    Pages INT,
    Language VARCHAR(256)
);
CREATE TABLE Rating (
    UID INT REFERENCES Users (UID),
    ISBN VARCHAR(17) REFERENCES Book (ISBN),
    PRIMARY KEY (UID,ISBN),
    Rating int
);

我需要找出每个国家中评分数量最多的用户。目前已经能通过以下查询获取每个用户的评分数量：

SELECT Country.CountryName as CountryName, Users.Username as Username, COUNT(Rating.Rating) as NumRatings
FROM Country
JOIN Users ON Users.ISO_3166 = Country.ISO_3166 
JOIN Rating ON Users.UID = Rating.UID
GROUP BY Country.CID, CountryName, Username
ORDER BY CountryName ASC

该查询返回结果格式如下：

Countryname | Username | Number of Ratings of this user

但我尝试的另一个查询，虽然每个国家返回一个用户，却不是评分最多的那个：

SELECT DISTINCT ON (CountryName)
        CountryName, Username, MAX(NumRatings)
FROM (
    SELECT Country.CountryName as CountryName, Users.Username as Username, COUNT(Rating.Rating) as NumRatings
        FROM Country
        JOIN Users ON Users.ISO_3166 = Country.ISO_3166 
        JOIN Rating ON Users.UID = Rating.UID
        GROUP BY Country.CID, CountryName, Username
        ORDER BY CountryName ASC) AS MyTable
GROUP BY CountryName, Username, NumRatings 
ORDER BY CountryName ASC;

请问如何编写能筛选出每个国家评分最多用户的SQL查询？

解决方案 #

方法一：使用窗口函数（推荐，支持PostgreSQL等现代数据库） #

这种方法简洁高效，还能处理多个用户并列第一的情况：

WITH UserRatings AS (
    SELECT 
        c.CountryName,
        u.Username,
        COUNT(r.Rating) AS NumRatings,
        -- 按国家分组，给用户按评分数量倒序排名，并列第一的用户排名相同
        RANK() OVER (PARTITION BY c.CountryName ORDER BY COUNT(r.Rating) DESC) AS RatingRank
    FROM Country c
    JOIN Users u ON u.ISO_3166 = c.ISO_3166
    JOIN Rating r ON u.UID = r.UID
    GROUP BY c.CID, c.CountryName, u.UID, u.Username
)
SELECT CountryName, Username, NumRatings
FROM UserRatings
WHERE RatingRank = 1
ORDER BY CountryName ASC;

• 如果只需要每个国家返回一个用户（即使有并列），可以把RANK()换成ROW_NUMBER()，它会给并列的用户分配不同的排名，只保留第一条。

方法二：使用子查询关联最大值（兼容旧版数据库） #

如果你的数据库不支持窗口函数，用这种方法也能实现：

SELECT 
    c.CountryName,
    u.Username,
    COUNT(r.Rating) AS NumRatings
FROM Country c
JOIN Users u ON u.ISO_3166 = c.ISO_3166
JOIN Rating r ON u.UID = r.UID
GROUP BY c.CID, c.CountryName, u.UID, u.Username
HAVING COUNT(r.Rating) = (
    SELECT MAX(UserCount)
    FROM (
        SELECT COUNT(r2.Rating) AS UserCount
        FROM Users u2
        JOIN Rating r2 ON u2.UID = r2.UID
        WHERE u2.ISO_3166 = c.ISO_3166
        GROUP BY u2.UID
    ) AS CountryMax
)
ORDER BY c.CountryName ASC;

• 外层查询计算每个用户的评分数量，HAVING子句里的嵌套查询会算出当前国家的用户最大评分数量，只有和最大值相等的用户会被筛选出来。

修复你之前的查询 #

你之前的DISTINCT ON查询问题在于没按评分数量降序排序，所以默认取的是每个国家的第一条记录。调整排序逻辑就能修复：

SELECT DISTINCT ON (CountryName)
    CountryName, Username, NumRatings
FROM (
    SELECT 
        c.CountryName,
        u.Username,
        COUNT(r.Rating) AS NumRatings
    FROM Country c
    JOIN Users u ON u.ISO_3166 = c.ISO_3166
    JOIN Rating r ON u.UID = r.UID
    GROUP BY c.CID, c.CountryName, u.Username
) AS MyTable
ORDER BY CountryName ASC, NumRatings DESC;

• 注意这里ORDER BY先按国家分组，再按NumRatings降序，这样DISTINCT ON会取每个国家评分最高的第一条记录，但这种方法只能返回一个用户，无法处理并列第一的情况。

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