如何在GROUP BY分组SQL中关联各语言粉丝数最多的用户信息

数据表结构与数据 #

Lang, length, user, followers
-----------------------------
Esp,    10,   user0,   1
Esp,    20,   user1,   5
Eng,     5,   user2,  10
Eng,     2,   user3,  15
Jap,    25,   user4,   1

需求说明 #

按Lang字段分组，返回以下信息：

• 语言标识（Lang）
• 该语言下的记录行数（rows）
• length字段的平均值（avg）
• 该语言分组内粉丝数（followers）最高的用户（user）及其粉丝数

预期结果 #

Lang, rows, avg, user, followers
 Esp,   2,  15, user1,   5
 Eng,   2, 3.5, user3,  15
 Jap,   1,  25, user4,   1

已有SQL语句 #

SELECT 
    lang, COUNT(*) AS rows, 
    AVG(length) AS avg_length 
FROM
    toots 
GROUP BY
    lang 
LIMIT 10

解决方案 #

方法一：使用窗口函数（推荐，支持MySQL 8.0+、PostgreSQL等） #

利用ROW_NUMBER()窗口函数为每个语言分组内的记录按粉丝数降序排名，筛选出每组的第一条记录后再进行统计：

WITH ranked_toots AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Lang ORDER BY followers DESC) AS rank_num
    FROM toots
)
SELECT 
    Lang,
    COUNT(*) AS rows,
    AVG(length) AS avg,
    MAX(CASE WHEN rank_num = 1 THEN user END) AS user,
    MAX(followers) AS followers
FROM ranked_toots
GROUP BY Lang;

如果存在多个用户粉丝数并列最高的情况，可将ROW_NUMBER()替换为RANK()，会返回所有并列第一的用户（此时结果可能出现多行同语言的情况）。

方法二：分组统计关联原表（兼容老版本数据库） #

先分组统计行数、平均值和每组最大粉丝数，再关联原表获取对应用户信息：

SELECT 
    t_group.Lang,
    t_group.rows,
    t_group.avg,
    t_user.user,
    t_user.followers
FROM (
    SELECT 
        Lang,
        COUNT(*) AS rows,
        AVG(length) AS avg,
        MAX(followers) AS max_followers
    FROM toots
    GROUP BY Lang
) t_group
JOIN toots t_user 
    ON t_group.Lang = t_user.Lang 
    AND t_group.max_followers = t_user.followers;

注意：若同一语言下有多个用户粉丝数等于最大值，该方法会返回多条该语言的记录，需根据实际需求调整（比如用DISTINCT或聚合函数处理）。

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