Оконные функции SQL: агрегация
Это четвертая статья из серии Оконные функции SQL. Рекомендую не просто читать, а проходить курс — с ним знания превратятся в навыки.
Агрегация — это когда мы считаем суммарные или средние показатели. Например, среднюю зарплату по каждому региону или количество золотых медалей у каждой страны в зачете Олимпийских игр.
Мы будем агрегировать данные по сотрудникам из таблички employees
:
┌────┬──────────┬────────┬────────────┬────────┐
│ id │ name │ city │ department │ salary │
├────┼──────────┼────────┼────────────┼────────┤
│ 11 │ Дарья │ Самара │ hr │ 70 │
│ 12 │ Борис │ Самара │ hr │ 78 │
│ 21 │ Елена │ Самара │ it │ 84 │
│ 22 │ Ксения │ Москва │ it │ 90 │
│ 23 │ Леонид │ Самара │ it │ 104 │
│ 24 │ Марина │ Москва │ it │ 104 │
│ 25 │ Иван │ Москва │ it │ 120 │
│ 31 │ Вероника │ Москва │ sales │ 96 │
│ 32 │ Григорий │ Самара │ sales │ 96 │
│ 33 │ Анна │ Москва │ sales │ 100 │
└────┴──────────┴────────┴────────────┴────────┘
Все запросы можно повторять в песочнице.
Сравнение с фондом оплаты труда
У каждого департамента есть фонд оплаты труда — денежная сумма, которая ежемесячно уходит на выплату зарплат сотрудникам. Посмотрим, какой процент от этого фонда составляет зарплата каждого сотрудника:


Столбец fund
показывает фонд оплаты труда отдела, а perc
— долю зарплаты сотрудника от этого фонда. Видно, что в HR и продажах все более-менее ровно, а у айтишников есть заметный разброс зарплат.
Как перейти от «было» к «стало»?
Отсортируем таблицу по департаментам:
select
name, department, salary,
null as fund,
null as perc
from employees
order by department, salary, id;
┌──────────┬────────────┬────────┬──────┬──────┐
│ name │ department │ salary │ fund │ perc │
├──────────┼────────────┼────────┼──────┼──────┤
│ Дарья │ hr │ 70 │ │ │
│ Борис │ hr │ 78 │ │ │
│ Елена │ it │ 84 │ │ │
│ Ксения │ it │ 90 │ │ │
│ Леонид │ it │ 104 │ │ │
│ Марина │ it │ 104 │ │ │
│ Иван │ it │ 120 │ │ │
│ Вероника │ sales │ 96 │ │ │
│ Григорий │ sales │ 96 │ │ │
│ Анна │ sales │ 100 │ │ │
└──────────┴────────────┴────────┴──────┴──────┘
Для удобства я добавил сортировку по зарплате и идентификатору. Но, как вы увидите дальше, в данном случае можно обойтись и без них.
Теперь пройдем от первой строчки до последней. На каждом шаге будем считать:
fund
— сумму зарплат по департаменту в целом (она одинакова для всех сотрудников департамента);perc
— долю зарплаты сотрудника от этой суммы.





и так далее...
Одной гифкой:

Окно состоит из секций по департаментам. При этом порядок записей в секции неважен: мы считаем сумму значений salary
, а она не зависит от порядка.
window w as (
partition by department
)
Для расчета fund
подойдет обычная функция sum()
— ее можно использовать поверх окна. А perc
посчитаем как salary / fund
:
select
name, department, salary,
sum(salary) over w as fund,
round(salary * 100.0 / sum(salary) over w) as perc
from employees
window w as (partition by department)
order by department, salary, id;
┌──────────┬────────────┬────────┬──────┬──────┐
│ name │ department │ salary │ fund │ perc │
├──────────┼────────────┼────────┼──────┼──────┤
│ Дарья │ hr │ 70 │ 148 │ 47.0 │
│ Борис │ hr │ 78 │ 148 │ 53.0 │
├──────────┼────────────┼────────┼──────┼──────┤
│ Елена │ it │ 84 │ 502 │ 17.0 │
│ Ксения │ it │ 90 │ 502 │ 18.0 │
│ Леонид │ it │ 104 │ 502 │ 21.0 │
│ Марина │ it │ 104 │ 502 │ 21.0 │
│ Иван │ it │ 120 │ 502 │ 24.0 │
├──────────┼────────────┼────────┼──────┼──────┤
│ Вероника │ sales │ 96 │ 292 │ 33.0 │
│ Григорий │ sales │ 96 │ 292 │ 33.0 │
│ Анна │ sales │ 100 │ 292 │ 34.0 │
└──────────┴────────────┴────────┴──────┴──────┘
Функция sum()
работает без неожиданностей — считает сумму значений по всей секции, которой принадлежит текущая строка.
Фильтрация и порядок выполнения
Вернемся к запросу, который считал фонд оплаты труда по департаменту:
select
name, department, salary,
sum(salary) over w as fund
from employees
window w as (partition by department)
order by department, salary, id;
┌──────────┬────────────┬────────┬──────┐
│ name │ department │ salary │ fund │
├──────────┼────────────┼────────┼──────┤
│ Дарья │ hr │ 70 │ 148 │
│ Борис │ hr │ 78 │ 148 │
│ Елена │ it │ 84 │ 502 │
│ Ксения │ it │ 90 │ 502 │
│ Леонид │ it │ 104 │ 502 │
│ Марина │ it │ 104 │ 502 │
│ Иван │ it │ 120 │ 502 │
│ Вероника │ sales │ 96 │ 292 │
│ Григорий │ sales │ 96 │ 292 │
│ Анна │ sales │ 100 │ 292 │
└──────────┴────────────┴────────┴──────┘
Допустим, мы хотим оставить в отчете только самарских сотрудников. Добавим фильтр:
select
name, salary,
sum(salary) over w as fund
from employees
where city = 'Самара'
window w as (partition by department)
order by department, salary, id;
┌──────────┬────────┬──────┐
│ name │ salary │ fund │
├──────────┼────────┼──────┤
│ Дарья │ 70 │ 148 │
│ Борис │ 78 │ 148 │
│ Елена │ 84 │ 188 │
│ Леонид │ 104 │ 188 │
│ Григорий │ 96 │ 96 │
└──────────┴────────┴──────┘
Фильтр сработал. Вот только значения fund
отличаются от ожидаемых:
┌──────────┬────────┬──────┐
│ name │ salary │ fund │
├──────────┼────────┼──────┤
│ Дарья │ 70 │ 148 │
│ Борис │ 78 │ 148 │
│ Елена │ 84 │ 502 │
│ Леонид │ 104 │ 502 │
│ Григорий │ 96 │ 292 │
└──────────┴────────┴──────┘
┌──────────┬────────┬──────┐
│ name │ salary │ fund │
├──────────┼────────┼──────┤
│ Дарья │ 70 │ 148 │
│ Борис │ 78 │ 148 │
│ Елена │ 84 │ 188 │
│ Леонид │ 104 │ 188 │
│ Григорий │ 96 │ 96 │
└──────────┴────────┴──────┘
Все дело в порядке выполнения операций. Вот в какой последовательности действует движок, когда выполняет запрос:
- Взять нужные таблицы (
from
) и соединить их при необходимости (join
). - Отфильтровать строки (
where
). - Сгруппировать строки (
group by
). - Отфильтровать результат группировки (
having
). - Взять конкретные столбцы из результата (
select
). - Рассчитать значения оконных функций (
function() over window
). - Отсортировать то, что получилось (
order by
).
Таким образом, окна отрабатывают предпоследним шагом, уже после фильтрации и группировки результатов. Поэтому в нашем запросе fund
отражает не сумму всех зарплат по департаменту, а сумму только по самарским сотрудникам.
Решение — использовать подзапрос с окном и фильтровать его в основном запросе:
with emp as (
select
name, city, salary,
sum(salary) over w as fund
from employees
window w as (partition by department)
order by department, salary, id
)
select name, salary, fund
from emp
where city = 'Самара';
┌──────────┬────────┬──────┐
│ name │ salary │ fund │
├──────────┼────────┼──────┤
│ Дарья │ 70 │ 148 │
│ Борис │ 78 │ 148 │
│ Елена │ 84 │ 502 │
│ Леонид │ 104 │ 502 │
│ Григорий │ 96 │ 292 │
└──────────┴────────┴──────┘
Описание окна
До сих пор мы описывали окно в блоке window
и ссылались на него в выражении over
:
select
name, department, salary,
count(*) over w as emp_count,
sum(salary) over w as fund
from employees
window w as (partition by department)
order by department, salary, id;
┌──────────┬────────────┬────────┬───────────┬──────┐
│ name │ department │ salary │ emp_count │ fund │
├──────────┼────────────┼────────┼───────────┼──────┤
│ Дарья │ hr │ 70 │ 2 │ 148 │
│ Борис │ hr │ 78 │ 2 │ 148 │
│ Елена │ it │ 84 │ 5 │ 502 │
│ Ксения │ it │ 90 │ 5 │ 502 │
│ Леонид │ it │ 104 │ 5 │ 502 │
│ Марина │ it │ 104 │ 5 │ 502 │
│ Иван │ it │ 120 │ 5 │ 502 │
│ Вероника │ sales │ 96 │ 3 │ 292 │
│ Григорий │ sales │ 96 │ 3 │ 292 │
│ Анна │ sales │ 100 │ 3 │ 292 │
└──────────┴────────────┴────────┴───────────┴──────┘
Это не единственный способ. SQL разрешает вообще не использовать window
и описывать окно прямо внутри over
:
select
name, department, salary,
count(*) over (partition by department) as emp_count,
sum(salary) over (partition by department) as fund
from employees
order by department, salary, id;
Мне больше нравится вариант с window
— его легче читать, и можно явно переиспользовать окно. Но в документации часто встречается определение окна внутри over
, поэтому не удивляйтесь, когда увидите его.
Кстати, определение окна может быть пустым:
select
name, department, salary,
count(*) over () as emp_count,
sum(salary) over () as fund
from employees
order by department, salary, id;
Такое окно включает все строки, так что emp_count
покажет общее количество сотрудников, а fund
— общий фонд оплаты труда по всем записям employees
.
Функции агрегации
min(value) |
минимальное value среди строк, входящих в окно |
max(value) |
максимальное value |
count(value) |
количество value , не равных null |
avg(value) |
среднее значение по всем value |
sum(value) |
сумма значений value |
group_concat(value, separator) |
строка, которая соединяет значения value через разделитель separator |
⌘ ⌘ ⌘
Мы разобрались, как считать фиксированные агрегаты в окнах. В следующей части займемся скользящими агрегатами!
Чтобы закрепить знания на практике — записывайтесь на курс
Подписывайтесь на канал, чтобы не пропустить новые заметки 🚀