Оконные функции. Часть 1. Ранжирование.
Нет более обманчивого раздела SQL, чем «оконные функции». Когда слышишь эти слова, думаешь «наверно, просто придумали какие-то дополнительные функции». А вот и нет! «Оконные функции» — это отдельный язык, встроенный в обычный SQL. И он сложнее, чем все, что вы знали о селектах, вместе взятое.
Будем работать в СУБД Postgres с игрушечной таблицей сотрудников employees, создадим еë запустив следующий код:
create table employees ( id integer primary key, name varchar(50), city varchar(50), department varchar(50), salary integer );
insert into employees (id, name, city, department, salary) values (24, 'Марина', 'Москва', 'it', 104), (21, 'Елена', 'Самара', 'it', 84), (22, 'Ксения', 'Москва', 'it', 90), (25, 'Иван', 'Москва', 'it', 120), (23, 'Леонид', 'Самара', 'it', 104), (11, 'Дарья', 'Самара', 'hr', 70), (12, 'Борис', 'Самара', 'hr', 78), (31, 'Вероника', 'Москва', 'sales', 96), (33, 'Анна', 'Москва', 'sales', 100), (32, 'Григорий', 'Самара', 'sales', 96);
Таблица будет иметь следующий вид:
Попробуем решить задачу ранжирования. *Ранжирование* — это всевозможные рейтинги, начиная от призеров различных соревнований и заканчивая Forbes 500. Мы будем ранжировать сотрудников организации.
Составим рейтинг сотрудников по размеру заработной платы:
Обратите внимание — сотрудники с одинаковой зарплатой получили один и тот же ранг (Леонид и Марина, Вероника и Григорий).
Как перейти от «было» к «стало»?
Сначала отсортируем таблицу по убыванию зарплаты:
select null as rank, name, department, salary from employees order by salary desc, id;
Теперь пройдем от первой строчки до последней и проставим ранг каждой записи. Начнем с 1-й и будем увеличивать ранг каждый раз, когда значение salary меньше, чем у предыдущей записи:
Чтобы проставить ранг, достаточно на каждом шаге смотреть только на значения из столбца salary, выделенные синей рамкой. Назовем эти значения _окном_.
Попробуем описать содержимое окна словами:
1. Это значения столбца salary.
2. Они упорядочены от большего значения к меньшему.
Сформулируем то же самое на SQL:
window w as (order by salary desc)
• window — ключевое слово, которое показывает, что дальше будет определение окна;
• w — название окна (может быть любым);
• (order by salary desc) — описание окна («значения столбца salary, упорядоченные по убыванию»).
Задача — посчитать ранг по окну w. На SQL это записывается как dense_rank() over w.
dense_rank() — это оконная функция, которая считает ранг по указанному окну. Логика `dense_rank()| такая же, как была у нас при ручном подсчете — начать с 1-й строки и увеличивать ранг каждый раз, когда очередное значение из окна отличается от предыдущего.
Добавим окно и оконную функцию в исходный запрос:
select dense_rank() over w as rank, name, department, salary from employees window w as (order by salary desc) order by rank, id;
Под капотом движок СУБД выполнит такой запрос следуя шагам:
1. Берет таблицу, указанную в from.
2. Выбирает из нее все записи.
3. Для каждой записи рассчитывает значение dense_rank() с помощью окна w.
4. Сортирует результат как указано в order by.
Так отработает шаг 3, на котором назначается ранг:
Конструкция window сама по себе ничего не делает с результатами запроса. Она только определяет окно, которое можно использовать (или не использовать) в запросе. Если убрать вызов dense_rank(), запрос отработает, как будто нет никаких окон:
select null as rank, name, department, salaryfrom employeeswindow w as (order by salary desc)order by salary desc, id;
Окно начинает работать только тогда, когда в select появляется оконная функция, которая его использует.
*Запросы с окном в СУБД Oracle и MS SQL Server*
Ни Oracle, ни SQL Server не поддерживают конструкцию window. Чтобы заставить работать оконный запрос в этих СУБД, перенесите описание окна внутрь инструкции over.
select dense_rank() over ( order by salary desc ) as rank, name, department, salary from employees order by rank, id;
Предположим, мы хотим ранжировать сотрудников по имени (по алфавиту от А к Я), тогда можем использовать следующий запрос:
select dense_rank() over w as rank, name, department, salaryfrom employeeswindow w as (order by name)order by rank, id;