Введение в оконные функции: OVER и PARTITION BY

Введение в оконные функции SQL: предложение OVER и PARTITION BY — вычисления по группе строк без GROUP BY.

Оконная функция — это функция, которая работает с набором строк («окном») относительно текущей строки. Ключевое слово OVER говорит SQL: «применяй функцию не ко всей таблице сразу, а к окну».

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

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

-- Средняя зарплата по всей компании рядом с каждым сотрудником
SELECT
  emp_name,
  salary,
  ROUND(AVG(salary) OVER (), 0) AS company_avg
FROM employees;

Результат:

emp_name     | salary | company_avg
-------------+--------+------------
Ethan Hunt   | 5000   | 6460
Tony Montana | 6500   | 6460
Sarah Connor | 8000   | 6460
Rick Deckard | 7200   | 6460
Martin Blank | 5600   | 6460

OVER () — пустые скобки означают «всё окно целиком» — весь результат без деления на группы.

PARTITION BY — окно по группам

PARTITION BY внутри OVER делит строки на «партиции» (окна). Функция считается отдельно для каждой партиции:

-- Максимальная зарплата в отделе рядом с каждым сотрудником
SELECT
  e.emp_name,
  e.salary,
  e.dept_id,
  MAX(e.salary) OVER (PARTITION BY e.dept_id) AS dept_max
FROM employees AS e
WHERE e.dept_id IS NOT NULL
ORDER BY e.dept_id;

Результат:

emp_name     | salary | dept_id | dept_max
-------------+--------+---------+---------
Tony Montana | 6500   | 1       | 6500
Rick Deckard | 7200   | 3       | 7200
Ethan Hunt   | 5000   | 4       | 5000
Sarah Connor | 8000   | 5       | 8000

Разница зарплаты от максимума по отделу

SELECT
  e.emp_name,
  e.salary,
  MAX(e.salary) OVER (PARTITION BY e.dept_id) - e.salary AS gap_to_max
FROM employees AS e
WHERE e.dept_id IS NOT NULL
ORDER BY gap_to_max DESC;

Результат:

emp_name     | salary | gap_to_max
-------------+--------+-----------
Tony Montana | 6500   | 0
Rick Deckard | 7200   | 0
Ethan Hunt   | 5000   | 0
Sarah Connor | 8000   | 0

В каждом отделе по одному сотруднику, поэтому разница нулевая. В реальной базе с несколькими людьми в отделе вы увидите ненулевые значения.

COUNT по партиции

-- Сколько сотрудников в каждом отделе (для каждой строки)
SELECT
  e.emp_name,
  e.dept_id,
  COUNT(*) OVER (PARTITION BY e.dept_id) AS dept_headcount
FROM employees AS e
WHERE e.dept_id IS NOT NULL
ORDER BY e.dept_id;

Результат:

emp_name     | dept_id | dept_headcount
-------------+---------+---------------
Tony Montana | 1       | 1
Rick Deckard | 3       | 1
Ethan Hunt   | 4       | 1
Sarah Connor | 5       | 1

Коротко

• функция() OVER () — оконная функция по всем строкам.
• OVER (PARTITION BY col) — делит на окна по значению столбца; функция считается внутри каждого окна отдельно.
• Строки не схлопываются — каждая строка видна с добавленным вычислением.
• Можно использовать AVG, SUM, MAX, MIN, COUNT как оконные.