Что такое окно: OVER()

Переговорка «Эпохи Корон» похожа на штаб перед битвой: на столе карта Трёх Корон и пластиковые фигурки шести фракций, маг с посохом стоит в центре. На экране письмо Кима: «маги на 1.4 выигрывают 53,3%, нерфить».

Блок E закончился боссом про опоздания «Гонца»: Отто признал, что дашборд врал, и агентство взяло курс на тендер «Монетки». Для тендера нужны два готовых кейса, и Ильва может стать вторым, если ответ окажется честным, а не удобным. Она отвечает за баланс онлайн-стратегии «Эпоха Корон»: шесть фракций, патчи каждые полтора месяца и вечный спор игроков, кто сильнее. База empires хранит полгода матчей: matches описывает сам матч (карта, режим, ранговый или нет, патч, длительность), а match_players хранит по строке на каждого участника: фракцию, команду, результат, APM, рейтинг до и после.

Цифру Кима мы пока не трогаем: проверим её в боссе блока, когда соберём все инструменты. Весь блок про один инструмент, оконные функции. Они закрывают задачи, которые без них требуют громоздких подзапросов: доли, рейтинги, нарастающие итоги, серии. Сегодня разберёмся, чем окно отличается от GROUP BY.

Таблица в классном журнале

Учитель ставит оценки за контрольную. Можно составить сводку «средний балл по классу 3,8». Это GROUP BY: тридцать строк превратились в одну, и кто что получил, уже не видно.

А можно оставить журнал как есть и дописать справа столбец «средний по классу». В каждой строке тот же 3,8, но рядом с оценкой каждого ученика. Теперь видно, кто выше среднего, а кто ниже, и ни одна строка не пропала.

Оконная функция делает второе. Она считает по группе строк и дописывает ответ в каждую строку, ничего не схлопывая. Группа строк, по которой идёт счёт, называется окном.

Сначала по-старому

Средний APM по фракциям Ильва уже видела. Это обычная группировка из урока про GROUP BY:

SELECT f.name AS faction, round(avg(mp.apm), 1) AS avg_apm
FROM match_players mp
JOIN factions f ON f.id = mp.faction_id
GROUP BY f.name
ORDER BY avg_apm DESC;

 faction           | avg_apm
-------------------+---------
 Лесной Союз       |   101.3
 Северные Кланы    |    99.8
 Пустынный Халифат |    98.2
 Империя           |    98.2
 Орден Магов       |    97.6
 Нежить            |    97.6

Шесть строк вместо 31 752. Игроки исчезли, а Ильве нужны они.

OVER(): итог в каждой строке

Возьмём ту же avg и допишем после неё OVER (). Пустые скобки значат «окно это все строки результата»:

SELECT match_id, player_id, apm,
       count(*)              OVER () AS all_rows,
       round(avg(apm) OVER (), 1) AS avg_apm
FROM match_players
ORDER BY match_id, player_id
LIMIT 4;

 match_id | player_id | apm | all_rows | avg_apm
----------+-----------+-----+----------+---------
        1 |       679 | 121 |    31752 |    98.8
        1 |       833 | 119 |    31752 |    98.8
        1 |      1648 |  68 |    31752 |    98.8
        1 |      2117 | 105 |    31752 |    98.8

Строки на месте, а справа в каждой стоит итог по всей таблице: всего строк 31 752, средний APM 98,8. GROUP BY здесь нет, и он не нужен.