MySQL按event_id和viewer_id分组取viewer_count最大值唯一行

我正在为活动门户统计观众数据，观众会多次重连，通过viewer_id关联同一观众的多次记录。每次观众开始观看时会输入姓名和观看人数（含自身）viewer_count。需要按event_id与viewer_id的组合分组，选择每组中viewer_count最大的行；若组内存在多个viewer_count相同的最大值行，任选其一即可。

示例表结构与数据 #

-- Server Version: MySQL 8.0.43
CREATE TABLE `event_viewers` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `event_id` bigint unsigned NOT NULL,
  `viewer_id` bigint unsigned NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `viewer_count` int NOT NULL,
  PRIMARY KEY (`id`)
);
-- Event ID 1
insert into event_viewers (id, event_id, viewer_id, name, viewer_count)
values  (1, 1, 1, 'Bert Kuvalis0', 1),
        (6, 1, 2, 'Wanda Steuber0', 7),
        (11, 1, 3, 'Erick Nienow0', 4),
        (16, 1, 3, 'Erick Nienow1', 3),
        (17, 1, 3, 'Erick Nienow2', 4);
-- Event ID 2
insert into event_viewers (id, event_id, viewer_id, name, viewer_count)
values  (2, 2, 1, 'Bert Kuvalis2', 11),
        (7, 2, 2, 'Wanda Steuber2', 10),
        (12, 2, 3, 'Erick Nienow3', 7),
        (18, 2, 2, 'Wanda Steuber3', 13);

期望查询结果 #

注：同组内viewer_count相同的最大值行（如id11和17）仅保留一行即可，不关心具体保留哪一行。

已尝试的方法 #

GROUP BY

使用GROUP BY和MAX函数能得到正确的viewer_count，但缺少id和name字段：

SELECT
    ev.event_id,
    ev.viewer_id,
    MAX(ev.`viewer_count`) AS `viewer_count`
FROM event_viewers as ev
GROUP BY ev.viewer_id, ev.event_id ORDER BY `event_id`, `viewer_id`;

WHERE NOT EXISTS

该方法会保留同组内viewer_count相同的最大值行，不符合需求：

SELECT DISTINCT ev1.* from event_viewers ev1
WHERE NOT EXISTS (
  SELECT * FROM event_viewers as ev2
  WHERE ev2.viewer_id = ev1.viewer_id
  AND ev2.event_id = ev1.event_id
  AND ev2.viewer_count > ev1.viewer_count
) ORDER BY `event_id`, `viewer_id`;

LEFT JOIN

同样会保留同组内viewer_count相同的最大值行：

SELECT ev1.* FROM event_viewers ev1 
LEFT JOIN event_viewers ev2 
ON ( ev1.viewer_count<ev2.viewer_count AND ev1.viewer_id=ev2.viewer_id AND ev1.event_id=ev2.event_id )
WHERE ev2.id IS null
ORDER BY ev1.event_id, ev1.`viewer_id`;

窗口函数RANK()

使用RANK()窗口函数仍会保留同组内viewer_count相同的最大值行：

WITH w1 AS (
    SELECT *,
           RANK() OVER (PARTITION BY viewer_id, event_id
               ORDER BY viewer_count DESC
               ) AS `Rank`
    FROM event_viewers
)
SELECT id, event_id, viewer_id, name, viewer_count
FROM w1
WHERE `Rank` = 1
ORDER BY `event_id`, `viewer_id`;

解决方案 #

方法1：使用ROW_NUMBER()窗口函数

ROW_NUMBER()会在分组内为每行分配唯一序号，按viewer_count降序排序后，取序号为1的行即可确保每组仅保留一个最大值行：

WITH w1 AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY viewer_id, event_id
               ORDER BY viewer_count DESC, id ASC -- 可选：加id ASC确保结果稳定，不关心则可仅按viewer_count DESC排序
               ) AS row_num
    FROM event_viewers
)
SELECT id, event_id, viewer_id, name, viewer_count
FROM w1
WHERE row_num = 1
ORDER BY event_id, viewer_id;

方法2：GROUP BY结合子查询

先分组获取每组的最大viewer_count，同时任选一个对应行的id，再关联原表获取完整数据：

SELECT ev.*
FROM event_viewers ev
JOIN (
    SELECT event_id, viewer_id, MAX(viewer_count) AS max_count, MIN(id) AS pick_id -- 用MIN(id)或MAX(id)任选一行
    FROM event_viewers
    GROUP BY event_id, viewer_id
) AS grp ON ev.event_id = grp.event_id 
         AND ev.viewer_id = grp.viewer_id 
         AND ev.viewer_count = grp.max_count 
         AND ev.id = grp.pick_id
ORDER BY ev.event_id, ev.viewer_id;

内容的提问来源于Stack Exchange，提问作者William Lightning