ROW_NUMBER, RANK, DENSE_RANK: лидерборды

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

К концу сезона вешаем на сайт таблицу лидеров и раздаём три приза. Только без сюрпризов, как в прошлый раз: двое игроков с одинаковым рейтингом, а приз получил один. Кто на каком месте?

В прошлых уроках окно считало суммы и средние рядом с каждой строкой: итог по группе и нарастающий итог. Сегодня окно будет раздавать места. Звучит просто, пока два игрока не наберут одинаковый рейтинг. Тогда приходится решать, что такое «третье место», и у SQL на этот вопрос три разных ответа.

Финиш забега

Представь финиш забега. Двое пересекли линию одновременно, судья смотрит на табло. Можно записать их вторым и третьим по номеру нагрудника: места разные, хотя бежали одинаково. Можно дать обоим второе место, тогда следующий бегун будет четвёртым, как на Олимпиаде. А можно дать обоим второе, а следующему третье: так считают призовые ступени.

Эти три способа и есть три функции ранжирования. Все они оконные: пишутся с OVER (ORDER BY ...) и не схлопывают строки.

• row_number() даёт номера подряд без повторов: 1, 2, 3, 4.
• rank() даёт равным одинаковое место и пропускает следующие: 1, 2, 2, 4.
• dense_rank() даёт равным одинаковое место без пропусков: 1, 2, 2, 3.

Текущий рейтинг

Сначала нужен сам рейтинг. В match_players он записан после каждого матча: rating_before и rating_after. Текущий рейтинг игрока это rating_after его последнего матча. Последнюю строку каждого игрока достанем той же row_number(), только с разбиением по игроку:

WITH current AS (
  SELECT player_id, rating_after AS rating
  FROM (
    SELECT mp.player_id, mp.rating_after,
           row_number() OVER (PARTITION BY mp.player_id ORDER BY m.started_at DESC) AS rn
    FROM match_players mp
    JOIN matches m ON m.id = mp.match_id
  ) t
  WHERE rn = 1
)
SELECT count(*) AS players, count(DISTINCT rating) AS distinct_ratings
FROM current;

 players | distinct_ratings
---------+------------------
    2191 |              411

Внутри каждого игрока матчи пронумерованы от самого позднего, и rn = 1 оставляет последний. Этот приём «одна лучшая строка в каждой группе» разберём подробно в следующем уроке, пока бери его как готовый шаг.

Посмотри на вторую цифру. 2191 игрок и всего 411 разных рейтингов: рейтинг целый и меняется на 10-20 очков за матч, поэтому совпадения на каждом шагу. Ничьи в лидерборде не исключение, а правило.