Оконные функции против GROUP BY

Главное отличие

GROUP BY сворачивает строки: из каждой группы остаётся одна строка итога, а подробности пропадают. Оконная функция (OVER) считает тот же итог, но строки не трогает: итог группы приписывается к каждой её строке отдельной колонкой. Похоже на классный журнал: GROUP BY — это сводка «в 7А 25 учеников, средний балл 4,2», а оконная функция — тот же журнал со всеми учениками, где рядом с каждой фамилией дописан средний балл класса.

Разберём на одном вопросе: когда в каждом отделе наняли первого сотрудника — и кто это был?

Через GROUP BY: итог по отделу

SELECT department, COUNT(*) AS staff, MIN(hired_at) AS first_hiredFROM employeesGROUP BY departmentORDER BY department

 department | staff | first_hired
------------+-------+-------------
 Дирекция   |     1 | 2018-03-01
 Поддержка  |     4 | 2020-01-20
 Продажи    |     6 | 2019-06-15
 Склад      |     4 | 2019-09-01
(4 rows)

15 сотрудников свернулись в четыре строки — по одной на отдел. Дату первого найма MIN(hired_at) мы знаем, а вот кто этот сотрудник — нет. Попробуем добавить имя:

SELECT department, name, MIN(hired_at) AS first_hiredFROM employeesGROUP BY department

ERROR: column "employees.name" must appear in the GROUP BY clause or be used in an aggregate function

В отделе несколько сотрудников, а строка на отдел одна — какое имя в неё поставить, PostgreSQL не знает. MIN(hired_at) не «указывает» на строку, где нашлась минимальная дата: это просто значение, посчитанное по стопке. Добавить name в GROUP BY тоже не выход — тогда группой станет каждый сотрудник, и «первый в отделе» потеряет смысл (статья «Агрегаты и группировка по шагам»).

Через оконную функцию: итог рядом с каждой строкой

SELECT department, name, hired_at,       COUNT(*) OVER (PARTITION BY department) AS staff,       MIN(hired_at) OVER (PARTITION BY department) AS first_hired,       hired_at - MIN(hired_at) OVER (PARTITION BY department) AS days_after_firstFROM employeesWHERE department IN ('Поддержка', 'Склад')ORDER BY department, hired_at

 department |      name      |  hired_at  | staff | first_hired | days_after_first
------------+----------------+------------+-------+-------------+------------------
 Поддержка  | Ксения Белова  | 2020-01-20 |     4 | 2020-01-20  |                0
 Поддержка  | Алина Крылова  | 2021-04-12 |     4 | 2020-01-20  |              448
 Поддержка  | Глеб Сорокин   | 2023-01-09 |     4 | 2020-01-20  |             1085
 Поддержка  | Вера Макарова  | 2023-07-03 |     4 | 2020-01-20  |             1260
 Склад      | Роман Зайцев   | 2019-09-01 |     4 | 2019-09-01  |                0
 Склад      | Полина Егорова | 2022-03-14 |     4 | 2019-09-01  |              925
 Склад      | Егор Тихонов   | 2022-12-31 |     4 | 2019-09-01  |             1217
 Склад      | Марк Осипов    | 2024-09-15 |     4 | 2019-09-01  |             1841
(8 rows)

Те же COUNT(*) и MIN(hired_at), но с OVER (PARTITION BY department) вместо GROUP BY department. PARTITION BY делит строки на группы так же, как GROUP BY, только не сворачивает их: каждый сотрудник остался на месте, а числа отдела повторяются в каждой его строке. Поэтому с итогом можно сравнивать или считать прямо в строке: days_after_first — сколько дней прошло от первого найма в отделе. Первый сотрудник — тот, у кого эта разница 0. WHERE оставил два отдела, чтобы результат уместился, — оконная функция считается после WHERE и видит только их (статья «Как читается SQL-запрос»). Подробнее — карточка over.

Оставить только первых: два пути к одному ответу

Осталось отобрать строки, где hired_at совпадает с первой датой отдела. Написать оконную функцию в WHERE нельзя, поэтому её считают в подзапросе, а фильтруют снаружи:

SELECT department, name, hired_atFROM (  SELECT department, name, hired_at,         MIN(hired_at) OVER (PARTITION BY department) AS first_hired  FROM employees) tWHERE hired_at = first_hiredORDER BY department

 department |      name       |  hired_at
------------+-----------------+------------
 Дирекция   | Виктор Орлов    | 2018-03-01
 Поддержка  | Ксения Белова   | 2020-01-20
 Продажи    | Светлана Гусева | 2019-06-15
 Склад      | Роман Зайцев    | 2019-09-01
(4 rows)

Первые в своих отделах — их руководители, что неудивительно. Тот же ответ даёт и GROUP BY, если потом соединить итог с исходной таблицей — найти строки, где дата совпала с минимальной:

SELECT e.department, e.name, e.hired_atFROM employees eJOIN (  SELECT department, MIN(hired_at) AS first_hired  FROM employees  GROUP BY department) d ON d.department = e.department AND d.first_hired = e.hired_atORDER BY e.department

 department |      name       |  hired_at
------------+-----------------+------------
 Дирекция   | Виктор Орлов    | 2018-03-01
 Поддержка  | Ксения Белова   | 2020-01-20
 Продажи    | Светлана Гусева | 2019-06-15
 Склад      | Роман Зайцев    | 2019-09-01
(4 rows)

Результаты совпали. Оба запроса при равных датах вернут всех, кого наняли в первый день отдела, — это честный ответ на вопрос «кто первый». Если нужен ровно один сотрудник на отдел, бери ROW_NUMBER() OVER (PARTITION BY department ORDER BY hired_at, id) и оставляй номер 1 (карточка row-number) — второй ключ id решает, кого взять при равенстве.

Что выбрать

• Нужен только итог по группам (сколько, сумма, среднее, минимум) — GROUP BY. Он короче и сразу даёт одну строку на группу.
• Нужны строки вместе с итогом их группы: сравнить значение с итогом, посчитать долю, найти первого или последнего, пронумеровать — оконная функция.
• Нужно отфильтровать по результату оконной функции — подзапрос или WITH вокруг неё (карточки subquery и with).
• GROUP BY и оконные функции можно сочетать в одном запросе: окно считается после группировки и тогда работает уже со строками-группами. Например, SUM(COUNT(*)) OVER (ORDER BY …) поверх GROUP BY по кварталам: внутренний COUNT(*) считает строки группы, внешний SUM … OVER накапливает эти итоги (карточка running-total).