SQL多字段GROUP BY查询获取各年月胜场最高选手的实现方法

场景说明 #

正在开发乒乓球计分板功能，需要按自然月维度统计每个月胜场数最高的获奖选手。

现有基础统计 #

目前已编写的分组统计SQL可以算出每个选手在对应年月的总胜场数，语句如下：
SELECT COUNT(winner) as wins, winner as player, YEAR(recorded) as year, MONTH(recorded) as month FROM matches GROUP BY winner, YEAR(recorded), MONTH(recorded)
实际执行后的返回结果为：

+------+--------+------+-------+
| wins | player | year | month |
+------+--------+------+-------+
|    7 |     21 | 2021 |     1 |
|    4 |     19 | 2021 |     1 |
|    1 |     20 | 2021 |     1 |
|   78 |     20 | 2021 |     2 |
|   46 |     19 | 2021 |     2 |
|   84 |     21 | 2021 |     2 |
|    4 |     23 | 2021 |     2 |
|    2 |     26 | 2021 |     2 |
|    5 |     20 | 2021 |     3 |
|    9 |     21 | 2021 |     3 |
|    9 |     20 | 2021 |     4 |
|   10 |     21 | 2021 |     4 |
|    8 |     19 | 2021 |     4 |
+------+--------+------+-------+

筛选目标 #

需要从上述统计结果中，筛出每个年份+月份维度下胜场数最高的记录，最终预期输出格式为：

+------+--------+------+-------+
| wins | player | year | month |
+------+--------+------+-------+
|    7 |     21 | 2021 |     1 |
|   84 |     21 | 2021 |     2 |
|    9 |     21 | 2021 |     3 |
|   10 |     21 | 2021 |     4 |
+------+--------+------+-------+

可行SQL实现方案 #

方案1：窗口函数实现（适配MySQL 8.0及以上版本） #

基于已有的分组统计结果，用窗口函数按照年、月维度分区，对胜场数做降序排序，取分区内排名第一的记录即可，逻辑简洁、查询性能更好：

WITH monthly_win_count AS (
    SELECT 
        COUNT(winner) as wins, 
        winner as player, 
        YEAR(recorded) as year, 
        MONTH(recorded) as month 
    FROM `matches` 
    GROUP BY winner, YEAR(recorded), MONTH(recorded)
)
SELECT wins, player, year, month
FROM (
    SELECT 
        *,
        RANK() OVER (PARTITION BY year, month ORDER BY wins DESC) as win_rank
    FROM monthly_win_count
) ranked_result
WHERE win_rank = 1

注意：如果遇到同一月份多个选手胜场数并列最高的情况，RANK()函数会保留所有并列第一的记录，能适配实际比赛中并列获奖的业务场景。如果需要强制只取一条记录，可以把RANK()换成ROW_NUMBER()函数。

方案2：关联最大值匹配（兼容所有MySQL版本，含5.x系列） #

如果使用的是不支持窗口函数的低版本数据库，可以先按年、月分组算出每个月的最高胜场数，再关联回原统计结果匹配对应的选手：

SELECT base.wins, base.player, base.year, base.month
FROM (
    -- 每个选手的月度胜场统计
    SELECT 
        COUNT(winner) as wins, 
        winner as player, 
        YEAR(recorded) as year, 
        MONTH(recorded) as month 
    FROM `matches` 
    GROUP BY winner, YEAR(recorded), MONTH(recorded)
) base
INNER JOIN (
    -- 每个年月对应的最高胜场数
    SELECT year, month, MAX(wins) as max_wins
    FROM (
        SELECT 
            COUNT(winner) as wins, 
            YEAR(recorded) as year, 
            MONTH(recorded) as month 
        FROM `matches` 
        GROUP BY winner, YEAR(recorded), MONTH(recorded)
    ) win_base
    GROUP BY year, month
) month_max
ON base.year = month_max.year 
   AND base.month = month_max.month 
   AND base.wins = month_max.max_wins

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