MySQL使用GROUP BY统计TOP3国家用户数及剩余国家总和实现方法

核心思路 #

• 第一步先按国家聚合计算每个国家的用户数，同时按用户数倒序给国家排名
• 第二步对排名做分支判断：排名前三的保留原国家名，其余所有国家统一归类为「Rest」
• 第三步按新的归类字段二次聚合，得到最终统计结果

通用SQL实现（支持MySQL8.0+、PostgreSQL、SQL Server等所有支持窗口函数的数据库） #

WITH country_user_stats AS (
    SELECT
        country,
        COUNT(userid) AS user_count,
        -- 按用户数倒序排名，用户数相同时按国家名排序，可根据需求调整排序规则
        RANK() OVER(ORDER BY COUNT(userid) DESC, country) AS rank_num
    FROM users
    GROUP BY country
)
SELECT
    CASE WHEN rank_num <= 3 THEN country ELSE 'Rest' END AS country,
    SUM(user_count) AS user_total
FROM country_user_stats
GROUP BY country
-- 排序规则：前三国家按用户数倒序排列，Rest固定排在最后
ORDER BY
    CASE WHEN country = 'Rest' THEN 2 ELSE 1 END,
    user_total DESC;

老版本MySQL（5.7及以下，不支持窗口函数）实现 #

SELECT
    IF(rank_num <= 3, country, 'Rest') AS country,
    SUM(user_count) AS user_total
FROM (
    SELECT
        country,
        user_count,
        (
            SELECT COUNT(DISTINCT user_count) 
            FROM (SELECT COUNT(userid) AS user_count FROM users GROUP BY country) t2 
            WHERE t2.user_count > t1.user_count
        ) + 1 AS rank_num
    FROM (SELECT country, COUNT(userid) AS user_count FROM users GROUP BY country) t1
) t
GROUP BY country
ORDER BY IF(country = 'Rest', 2, 1), user_total DESC;

调整说明 #

如果需要和示例输出的排序完全对齐，只需要调整窗口函数里ORDER BY的第二排序规则即可，比如示例中用户数为1的国家里UK、Japan排在前两位，可自定义排序规则实现对应效果。

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