Нарастающий итог и скользящее среднее: рамки ROWS и RANGE

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

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

В прошлом уроке окно приносило в строку итог всей её группы. Сегодня окно получает порядок и границы. Тогда итог считается не по всей группе, а по строкам «до этой» или «за последние семь дней», и из таблицы получается кривая во времени.

Копилка

Каждый день кладёшь в копилку сколько-то монет. Сумма, которая в ней лежит сегодня, это всё, что ты положил с первого дня по сегодня. Это нарастающий итог.

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

ORDER BY в окне: нарастающий итог

Самый активный ранговый игрок у нас LuckyWolf435 (player_id = 435), 88 ранговых матчей. В каждом матче рейтинг меняется на rating_after - rating_before. Сложим эти изменения от первого матча до текущего:

SELECT m.started_at::date AS day, mp.match_id, mp.rating_before,
       mp.rating_after - mp.rating_before AS delta,
       sum(mp.rating_after - mp.rating_before)
         OVER (ORDER BY m.started_at, mp.match_id) AS running_delta
FROM match_players mp
JOIN matches m ON m.id = mp.match_id
WHERE mp.player_id = 435 AND m.ranked
ORDER BY m.started_at, mp.match_id;

 day        | match_id | rating_before | delta | running_delta
------------+----------+---------------+-------+---------------
 2026-01-03 |      226 |          1098 |   -16 |           -16
 2026-01-04 |      319 |          1082 |    15 |            -1
 2026-01-04 |      380 |          1097 |   -15 |           -16
 2026-01-06 |      500 |          1082 |    16 |             0
 2026-01-09 |      767 |          1098 |    15 |            15
 2026-01-11 |      983 |          1113 |    16 |            31
 ...

Стоило дописать ORDER BY внутри OVER, и sum стал считать не всё окно, а строки от первой до текущей. Стартовый рейтинг 1098 плюс running_delta даёт рейтинг после каждого матча: игрок проваливался на 65 очков ниже старта, взлетал на 137 выше и закончил полгода с плюсом 90.

WHERE здесь стоит до окна, и так и надо: копилка одного игрока считается только по его ранговым матчам.