Оконные функции: агрегация

Это четвертая статья из серии Оконные функции в картинках. Рекомендую не просто читать, а проходить курс — с ним знания превратятся в навыки.

Агрегация — это когда мы считаем суммарные или средние показатели. Например, среднюю зарплату по каждому региону или количество золотых медалей у каждой страны в зачете Олимпийских игр.

Мы будем агрегировать данные по сотрудникам из таблички 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 — долю зарплаты сотрудника от этой суммы.

1️⃣

2️⃣

3️⃣

4️⃣

5️⃣

и так далее...

Одной гифкой:

Окно состоит из секций по департаментам. При этом порядок записей в секции неважен: мы считаем сумму значений 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`

⌘ ⌘ ⌘

Мы разобрались, как считать фиксированные агрегаты в окнах. В следующей части займемся скользящими агрегатами!

Чтобы закрепить знания на практике — записывайтесь на курс 🚀