LAG и LEAD: сравнение с соседней строкой

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

Комьюнити-менеджер уверяет, что после поражения игроки «тильтуют»: злятся и сразу кидаются в новый матч, а потом проигрывают снова. Если это правда, после поражения нужна пауза-подсказка. Проверь? И заодно покажи, как наш лидер набирал рейтинг.

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

Дневник тренировок

Представь дневник бегуна. Каждая запись это пробежка: дата и время на дистанции. Чтобы понять, растёт ли форма, он смотрит не на одну запись, а на пару соседних: сегодня 25 минут, в прошлый раз 26, значит минута выиграна. А чтобы понять, хватает ли отдыха, сравнивает дату пробежки с датой следующей.

lag(x) это «что было в прошлой записи», а lead(x) это «что будет в следующей». Порядок записей задаёт ORDER BY окна, а PARTITION BY отделяет дневники разных людей.

lag(x)  OVER (PARTITION BY player_id ORDER BY started_at)  -- предыдущий матч игрока
lead(x) OVER (PARTITION BY player_id ORDER BY started_at)  -- следующий матч игрока

У обеих есть необязательные аргументы: lag(x, 2) смотрит на две строки назад, а lag(x, 1, 0) вернёт 0 вместо NULL у первой строки.

Как лидер набирал рейтинг

Возьмём лидера сезона SunBear454 из урока про лидерборды и посмотрим его последние матчи:

SELECT m.started_at, m.ranked, mp.result, mp.rating_after,
       mp.rating_after - lag(mp.rating_after) OVER (ORDER BY m.started_at) AS change
FROM match_players mp
JOIN matches m ON m.id = mp.match_id
JOIN players p ON p.id = mp.player_id
WHERE p.nickname = 'SunBear454'
ORDER BY m.started_at DESC
LIMIT 8;

       started_at       | ranked | result | rating_after | change
------------------------+--------+--------+--------------+--------
 2026-06-29 16:57:38+00 | f      | win    |         1494 |      0
 2026-06-27 16:13:10+00 | f      | win    |         1494 |      0
 2026-06-19 14:35:05+00 | t      | win    |         1494 |     14
 2026-06-16 17:02:30+00 | t      | win    |         1480 |     16
 2026-06-15 17:14:02+00 | t      | win    |         1464 |     15
 2026-06-13 15:49:25+00 | t      | win    |         1449 |     14
 2026-06-12 14:15:29+00 | t      | loss   |         1435 |    -16
 2026-06-06 12:40:36+00 | t      | loss   |         1469 |    -18

Два поражения подряд в начале июня, потом четыре победы и +59 очков. Неранговые матчи рейтинг не двигают: изменение 0.

Здесь нет PARTITION BY, и это не ошибка: WHERE уже оставил матчи одного игрока, а окно считается после WHERE. Сортировка внутри окна (по возрастанию времени) и сортировка всего результата (по убыванию) независимы: lag всегда смотрит в прошлое, как бы ты ни выводил строки.