FIRST_VALUE, LAST_VALUE и NTILE

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

После патча 1.4 все бегут в маги, я это чувствую. Сколько игроков начинали одной фракцией, а сейчас играют другой? И кто куда ушёл? И ещё: говорят, быстрые руки решают. Раздели игроков по скорости и покажи, правда ли быстрые побеждают чаще.

В прошлом уроке мы смотрели на соседний матч: LAG брал предыдущую строку окна. Сегодня заглянем в самые края окна, в первую и последнюю строку, и научимся раскладывать игроков на равные группы. Это три функции: FIRST_VALUE, LAST_VALUE и NTILE.

Первая страница журнала

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

FIRST_VALUE(выражение) возвращает значение из первой строки окна, LAST_VALUE(выражение) из последней. Окно мы уже умеем задавать: разделить по игроку, упорядочить по времени.

SELECT mp.player_id,
       m.started_at::date AS day,
       mp.faction_id,
       first_value(mp.faction_id) OVER w AS first_f
FROM match_players mp
JOIN matches m ON m.id = mp.match_id
WHERE mp.player_id = 37
WINDOW w AS (PARTITION BY mp.player_id ORDER BY m.started_at, m.id)
ORDER BY m.started_at, m.id;

 player_id |    day     | faction_id | first_f
-----------+------------+------------+---------
        37 | 2026-01-27 |          4 |       4
        37 | 2026-01-28 |          3 |       4
        37 | 2026-02-11 |          3 |       4
        37 | 2026-02-15 |          3 |       4
        37 | 2026-02-18 |          5 |       4

Игрок 37 начал за Лесной Союз (4), потом играл Северными Кланами (3) и последний матч провёл за Нежить (5). first_f в каждой строке одинаковый: это фракция первого матча.

WINDOW w AS (…) даёт окну имя, чтобы не повторять одно и то же определение в каждой функции. Второй ключ сортировки m.id страхует от двух матчей с одинаковым временем: без него порядок таких строк не определён, и «первый» матч мог бы меняться от запуска к запуску.

Ловушка последней страницы

Добавим LAST_VALUE с тем же окном и посмотрим внимательно:

SELECT m.started_at::date AS day,
       mp.faction_id,
       last_value(mp.faction_id) OVER w AS last_naive,
       last_value(mp.faction_id) OVER (w ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND UNBOUNDED FOLLOWING) AS last_real
FROM match_players mp
JOIN matches m ON m.id = mp.match_id
WHERE mp.player_id = 37
WINDOW w AS (PARTITION BY mp.player_id ORDER BY m.started_at, m.id)
ORDER BY m.started_at, m.id;

    day     | faction_id | last_naive | last_real
------------+------------+------------+-----------
 2026-01-27 |          4 |          4 |         5
 2026-01-28 |          3 |          3 |         5
 2026-02-11 |          3 |          3 |         5
 2026-02-15 |          3 |          3 |         5
 2026-02-18 |          5 |          5 |         5

last_naive повторяет фракцию текущей строки. Дело в рамке, которую мы разбирали в уроке про нарастающий итог. Когда в окне есть ORDER BY, рамка по умолчанию заканчивается на текущей строке. Для нарастающего итога это ровно то, что нужно. Но для LAST_VALUE «последней строкой рамки» оказывается сама текущая строка.

У FIRST_VALUE этой проблемы нет: рамка по умолчанию всегда начинается с первой строки раздела. Поэтому ловушка срабатывает только на последнем значении.

Лечится явной рамкой на весь раздел: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Тогда last_real в каждой строке смотрит на последний матч игрока.

Теперь ответим Ильве. Свернём окно до одной строки на игрока через DISTINCT и сравним первую и текущую фракцию:

WITH f AS (
  SELECT DISTINCT mp.player_id,
         first_value(mp.faction_id) OVER w AS first_f,
         last_value(mp.faction_id) OVER (w ROWS BETWEEN UNBOUNDED PRECEDING
                                             AND UNBOUNDED FOLLOWING) AS last_f
  FROM match_players mp
  JOIN matches m ON m.id = mp.match_id
  WINDOW w AS (PARTITION BY mp.player_id ORDER BY m.started_at, m.id)
)
SELECT count(*) FILTER (WHERE first_f <> last_f)               AS switched,
       count(*) FILTER (WHERE first_f <> 2 AND last_f = 2)     AS to_mages,
       count(*) FILTER (WHERE first_f = 2 AND last_f <> 2)     AS from_mages,
       count(*) FILTER (WHERE last_f = 6)                      AS to_caliphate
FROM f;

 switched | to_mages | from_mages | to_caliphate
----------+----------+------------+--------------
      490 |      106 |         99 |           49

Из 2191 игрока с матчами фракцию сменили 490. В Орден Магов пришли 106, ушли из него 99. Массового бегства в маги по этой цифре не видно. Если взять наивный LAST_VALUE, «сменивших» окажется 1423: туда попадёт каждый, кто хоть раз сыграл другой фракцией.