Зачем нужны оконные функции

Зачем нужны оконные функции и чем они отличаются от обычных агрегатов.

Оконная функция — функция, которая считает значение по группе строк («окну»), но не схлопывает их: каждая исходная строка остаётся на месте, рядом просто появляется агрегат.

Проблема обычного GROUP BY

Вы уже умеете считать агрегаты. Запрос SELECT dept, AVG(salary) FROM emp GROUP BY dept вернёт по одной строке на отдел — детали отдельных сотрудников исчезнут. А что если нужно показать каждого сотрудника и рядом среднюю по его отделу, чтобы сравнить? С GROUP BY так нельзя: он схлопывает строки. Раньше пришлось бы делать подзапрос или self-join. Оконные функции решают это одной строкой.

Первое окно: агрегат без схлопывания

Ключевое слово — OVER. Оно превращает обычный агрегат (SUM, AVG, COUNT…) в оконный. Пустые скобки OVER () означают «окно — это вся таблица».

CREATE TABLE sale(product TEXT, amount INTEGER);
INSERT INTO sale VALUES ('Мышь',20),('Клава',40),('Коврик',10);

SELECT product, amount,
       SUM(amount) OVER ()                              AS total,
       ROUND(100.0 * amount / SUM(amount) OVER (), 1)   AS pct
FROM sale;

Вывод:

product  amount  total  pct
-------  ------  -----  ----
Мышь     20      70     28.6
Клава    40      70     57.1
Коврик   10      70     14.3

Каждая строка осталась на месте, а SUM(amount) OVER () добавил рядом общую сумму (70) — и мы тут же посчитали долю каждого товара в процентах. С GROUP BY такое в один запрос не уместить.

Анатомия оконного выражения

Полный синтаксис: функция() OVER (PARTITION BY ... ORDER BY ... рамка). Все три части необязательны:

Часть	Что задаёт
`PARTITION BY`	на какие группы бить таблицу (аналог `GROUP BY`, но без схлопывания)
`ORDER BY`	порядок строк внутри окна (важен для нумерации, `LAG`, нарастающих итогов)
рамка (`ROWS BETWEEN…`)	какие именно строки окна участвуют (скользящие окна)

Где оконные функции писать можно, а где нельзя

Оконные функции вычисляются после WHERE, GROUP BY и HAVING, но до ORDER BY всего запроса. Практический вывод: их нельзя использовать в WHERE той же выборки. Чтобы отфильтровать по результату окна (например, «оставить только первые места»), оконную функцию считают в подзапросе/CTE, а фильтр вешают снаружи — этот приём мы разберём дальше.

Итог

Оконная функция = агрегат + OVER (...); строки не схлопываются.
OVER () — окно по всей таблице; рядом с каждой строкой появляется агрегат.
Окно считается после WHERE/GROUP BY, поэтому в WHERE его звать нельзя — нужен подзапрос.