Топ-N в группе

Ильва · геймдизайнер баланса «Эпохи Корон»

Хочу позвать на закрытый тест нового патча лучших игроков. Но не десятку сервера, там одни маги и кланы, а по трое от каждой фракции. И заодно глянь, на какой карте самая затянутая игра: жалуются, что на Пепельной Долине матчи идут вечность.

В прошлом уроке места раздавались по всему серверу, одна таблица на всех. Теперь таблиц нужно несколько: своя у каждой фракции, своя у каждой карты. В каждой мы ищем верхушку. Это одна из самых частых задач аналитика, у неё даже есть имя: топ-N в группе.

Пьедестал в каждом виде спорта

На Олимпиаде нет одного пьедестала на всех. В биатлоне свои медалисты, в фигурном катании свои, и в каждом виде места считают заново, с единицы. Лучший биатлонист не соревнуется с лучшей фигуристкой.

В SQL это PARTITION BY из урока про доли плюс ранжирующая функция. PARTITION BY делит строки на «виды спорта», а row_number() или rank() раздаёт места внутри каждого. Нумерация начинается заново в каждой группе.

Рецепт из трёх шагов

Любой топ-N в группе собирается одинаково:

1. Посчитать то, по чему ранжируем: победы, длительность, выручку.
2. В подзапросе или CTE пронумеровать строки внутри групп.
3. Снаружи оставить строки с номером не больше N.

Третий шаг нельзя слить со вторым. Окна считаются после WHERE, поэтому WHERE rn <= 3 на том же уровне упадёт с ошибкой «столбец rn не существует». Это шаблон с внешним фильтром, и он встречается всюду, где фильтруют по оконной функции.

Самая длинная игра на каждой карте

Начнём с вопроса про карты. Для каждой карты нужен самый длинный матч целиком: его id и длительность.

Первое, что приходит в голову, это GROUP BY map и max(duration_sec). Длину рекорда так получим, а сам матч нет: id у каждой строки группы свой, и вытащить «id той строки, где максимум» агрегат не умеет. Окно строки не схлопывает, поэтому с ним всё проще:

SELECT map, id AS match_id, duration_sec
FROM (
  SELECT map, id, duration_sec,
         row_number() OVER (PARTITION BY map ORDER BY duration_sec DESC, id) AS rn
  FROM matches
) t
WHERE rn = 1
ORDER BY map;

       map        | match_id | duration_sec
------------------+----------+--------------
 Ледяной Предел   |    11542 |         5666
 Пепельная Долина |    12070 |         6525
 Перевал Ветров   |     6948 |         5701
 Солёное Озеро    |     5674 |         5070
 Старый Тракт     |     2577 |         5948
 Три Короны       |     4449 |         5697

Жалоба на Пепельную Долину подтверждается рекордом: 6525 секунд, почти 109 минут, на 10 минут дольше рекорда любой другой карты. Но рекорд это одна игра. Типичная длина карты видна по среднему или медиане, а не по максимуму.

id в ORDER BY окна это тай-брейк. Длительность в секундах совпадает часто: пары «карта, длительность» повторяются почти три тысячи раз. Без тай-брейка при равных рекордах row_number() выберет любой из них, и от запуска к запуску ответ может меняться.