OVER, PARTITION BY и ORDER BY

PARTITION BY бьёт таблицу на группы, ORDER BY задаёт порядок внутри окна.

PARTITION BY делит строки на независимые «окна»; функция пересчитывается отдельно в каждом. ORDER BY внутри OVER задаёт порядок строк — от него зависят нумерация, соседи и нарастающие итоги.

PARTITION BY: группы без схлопывания

Добавим PARTITION BY dept — и среднее посчитается отдельно для каждого отдела, но строки сотрудников останутся на месте.

CREATE TABLE emp(name TEXT, dept TEXT, salary INTEGER);
INSERT INTO emp VALUES
 ('Аня','Backend',120),('Борис','Backend',150),('Вика','Backend',90),
 ('Глеб','Frontend',100),('Дина','Frontend',110);

SELECT name, dept, salary,
       AVG(salary) OVER (PARTITION BY dept) AS avg_dept
FROM emp
ORDER BY dept, salary;

Вывод:

name   dept      salary  avg_dept
-----  --------  ------  --------
Вика   Backend   90      120.0
Аня    Backend   120     120.0
Борис  Backend   150     120.0
Глеб   Frontend  100     105.0
Дина   Frontend  110     105.0

У всех сотрудников Backend в колонке avg_dept стоит 120.0, у Frontend — 105.0. Теперь видно, кто получает выше или ниже средней по своему отделу. Это и есть сила окна: контекст группы рядом с деталями.

Насколько ты отстаёшь от лучшего в отделе

PARTITION BY комбинируется с любым агрегатом. Посчитаем максимум по отделу и разрыв до него.

CREATE TABLE emp(name TEXT, dept TEXT, salary INTEGER);
INSERT INTO emp VALUES
 ('Аня','BE',120),('Борис','BE',150),('Глеб','FE',100),('Дина','FE',110);

SELECT name, dept, salary,
       MAX(salary) OVER (PARTITION BY dept)            AS dept_max,
       salary - MAX(salary) OVER (PARTITION BY dept)   AS gap_to_top
FROM emp
ORDER BY dept, salary DESC;

Вывод:

name   dept  salary  dept_max  gap_to_top
-----  ----  ------  --------  ----------
Борис  BE    150     150       0
Аня    BE    120     150       -30
Дина   FE    110     110       0
Глеб   FE    100     110       -10

PARTITION BY vs GROUP BY

ORDER BY внутри окна — это не ORDER BY запроса

Важно не путать два ORDER BY. Тот, что внутри OVER (... ORDER BY ...), влияет на саму функцию: задаёт, в каком порядке нумеровать строки или накапливать сумму. Тот, что в конце запроса, лишь сортирует итоговый вывод на экране. Их значения могут различаться.

Итог

• PARTITION BY — это «GROUP BY без схлопывания»: считает агрегат отдельно в каждой группе.
• Можно показывать детали строки и контекст её группы одновременно.
• ORDER BY внутри OVER управляет функцией, а ORDER BY в конце — только сортировкой вывода.