Острова и пропуски: серии подряд

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

Готовим пост в сообщество «Герои сезона». Мне нужны две номинации: самая длинная серия побед подряд и самый преданный игрок, который заходил в игру больше всего дней подряд. Только чтобы без ошибок: в прошлый раз мы наградили не того, и было неловко.

Обе номинации про одно и то же: найти в последовательности куски, где условие выполняется подряд. Победы подряд, дни подряд. Такие куски называют островами, а разрывы между ними пропусками. Короткая версия этой задачи уже встречалась в уроке про пропуски для собеседования. Здесь разберём приём подробно и применим к двум номинациям Ильвы.

Острова в журнале матчей

Возьмём игрока 5, у него восемь матчей. Пронумеруем их двумя способами: сначала все матчи по порядку, потом отдельно победы и отдельно поражения.

SELECT m.started_at::date AS day,
       mp.result,
       row_number() OVER (PARTITION BY mp.player_id ORDER BY m.started_at, m.id) AS rn_all,
       row_number() OVER (PARTITION BY mp.player_id, mp.result
                          ORDER BY m.started_at, m.id) AS rn_result,
       row_number() OVER (PARTITION BY mp.player_id ORDER BY m.started_at, m.id)
     - row_number() OVER (PARTITION BY mp.player_id, mp.result
                          ORDER BY m.started_at, m.id) AS grp
FROM match_players mp
JOIN matches m ON m.id = mp.match_id
WHERE mp.player_id = 5
ORDER BY m.started_at, m.id;

    day     | result | rn_all | rn_result | grp
------------+--------+--------+-----------+-----
 2026-01-09 | loss   |      1 |         1 |   0
 2026-01-10 | loss   |      2 |         2 |   0
 2026-01-11 | win    |      3 |         1 |   2
 2026-01-13 | loss   |      4 |         3 |   1
 2026-01-19 | loss   |      5 |         4 |   1
 2026-01-23 | win    |      6 |         2 |   4
 2026-01-27 | win    |      7 |         3 |   4
 2026-01-29 | win    |      8 |         4 |   4

Колонка grp это rn_all - rn_result. Посмотри на последние три победы: rn_all растёт 6, 7, 8, rn_result растёт 2, 3, 4. Оба номера шагают вместе, и разность остаётся 4. Пока серия не прерывается, разность постоянна.

Как только между победами вклинивается поражение, общий номер убегает вперёд, а номер внутри побед нет. Разность меняется, и следующая серия получает другую метку. Одиночная победа 11 января получила grp = 2, а три победы в конце grp = 4.

Пара «результат, grp» однозначно метит серию. Значение grp само по себе ничего не значит: у поражений и побед могут случайно совпасть метки, поэтому группируем всегда по обоим полям.

Самая длинная серия побед

Остальное это GROUP BY, который ты знаешь с блока C. Сначала считаем длину каждой серии побед, потом берём лучшую серию игрока:

WITH r AS (
  SELECT mp.player_id,
         mp.result,
         row_number() OVER (PARTITION BY mp.player_id ORDER BY m.started_at, m.id)
       - row_number() OVER (PARTITION BY mp.player_id, mp.result
                            ORDER BY m.started_at, m.id) AS grp
  FROM match_players mp
  JOIN matches m ON m.id = mp.match_id
),
streaks AS (
  SELECT player_id, grp, count(*) AS streak
  FROM r
  WHERE result = 'win'
  GROUP BY player_id, grp
)
SELECT s.player_id, p.nickname, max(s.streak) AS longest_streak
FROM streaks s
JOIN players p ON p.id = s.player_id
GROUP BY s.player_id, p.nickname
ORDER BY longest_streak DESC, s.player_id
LIMIT 5;

 player_id |   nickname   | longest_streak
-----------+--------------+----------------
        46 | MadDuke      |             17
       215 | GrimFox      |             15
        78 | MadWolf      |             13
        51 | WiseBear     |             12
       116 | DarkOwl688   |             12

Фильтр result = 'win' стоит после нумерации, а не до неё. Если выбросить поражения заранее, победы через поражение станут соседями, и серии слипнутся. Нумеровать нужно полный ряд, а отбирать острова потом.

MadDuke выиграл 17 матчей подряд с 23 января по 24 февраля. Всего у него 37 матчей и 25 побед, так что серия занимает почти половину его истории.