Оконные функции SQL: ранжирование
Это вторая статья из серии Оконные функции SQL. Рекомендую не просто читать, а проходить курс — с ним знания превратятся в навыки.
В этой части будем решать задачу ранжирования. Ранжирование — это всевозможные рейтинги, начиная от призеров чемпионата мира по плаванию и заканчивая Forbes 500. Мы же будем ранжировать сотрудников компании.
Будем работать с игрушечной таблицей 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 │
└────┴──────────┴────────┴────────────┴────────┘
Все запросы можно повторять в песочнице.
Рейтинг зарплат
Составим рейтинг сотрудников по размеру заработной платы:


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





и так далее...
Чтобы проставить ранг, достаточно на каждом шаге смотреть только на значения из столбца salary
, выделенные синей рамкой. Назовем эти значения окном.
Попробуем описать содержимое окна словами:
- Это значения столбца
salary
. - Они упорядочены от большего значения к меньшему.
Сформулируем то же самое на 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;
┌──────┬──────────┬────────────┬────────┐
│ rank │ name │ department │ salary │
├──────┼──────────┼────────────┼────────┤
│ 1 │ Иван │ it │ 120 │
│ 2 │ Леонид │ it │ 104 │
│ 2 │ Марина │ it │ 104 │
│ 3 │ Анна │ sales │ 100 │
│ 4 │ Вероника │ sales │ 96 │
│ 4 │ Григорий │ sales │ 96 │
│ 5 │ Ксения │ it │ 90 │
│ 6 │ Елена │ it │ 84 │
│ 7 │ Борис │ hr │ 78 │
│ 8 │ Дарья │ hr │ 70 │
└──────┴──────────┴────────────┴────────┘
Вот как движок выполняет такой запрос:
- Берет таблицу, указанную в
from
. - Выбирает из нее все записи.
- Для каждой записи рассчитывает значение
dense_rank()
с помощью окнаw
. - Сортирует результат как указано в
order by
.
Вот как отрабатывает шаг 3, на котором назначается ранг:

Рейтинг зарплат по департаментам
Теперь составим рейтинг сотрудников по размеру заработной платы независимо по каждому департаменту:


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





и так далее...
Чтобы проставить ранг, достаточно на каждом шаге смотреть только на значения из столбца salary
, выделенные синей рамкой. Это и есть окно в данном случае.
Видно, что окно меняется в зависимости от того, к какому департаменту относится текущая запись. Опишем словами:
- Окно разбито на несколько независимых секций — по одной на департамент.
- Внутри секции записи упорядочены по убыванию зарплаты.
Сформулируем то же самое на SQL:
window w as (
partition by department
order by salary desc
)
partition by department
указывает, как следует разбить окно на секции;order by salary
desc задает сортировку внутри секции.
Функция расчета ранга остается прежней — dense_rank()
.
Добавим окно и оконную функцию в исходный запрос:
select
dense_rank() over w as rank,
name, department, salary
from employees
window w as (
partition by department
order by salary desc
)
order by department, rank, id;
┌──────┬──────────┬────────────┬────────┐
│ rank │ name │ department │ salary │
├──────┼──────────┼────────────┼────────┤
│ 1 │ Борис │ hr │ 78 │
│ 2 │ Дарья │ hr │ 70 │
├──────┼──────────┼────────────┼────────┤
│ 1 │ Иван │ it │ 120 │
│ 2 │ Леонид │ it │ 104 │
│ 2 │ Марина │ it │ 104 │
│ 3 │ Ксения │ it │ 90 │
│ 4 │ Елена │ it │ 84 │
├──────┼──────────┼────────────┼────────┤
│ 1 │ Анна │ sales │ 100 │
│ 2 │ Вероника │ sales │ 96 │
│ 2 │ Григорий │ sales │ 96 │
└──────┴──────────┴────────────┴────────┘
Вот как движок рассчитывает ранг для каждой записи:

Группы по зарплате
Разобьем сотрудников на три группы в зависимости от размера зарплаты:
- высокооплачиваемые,
- средние,
- низкооплачиваемые.


Как перейти от «было» к «стало»?
Сначала отсортируем таблицу по убыванию зарплаты:
select
null as tile,
name, department, salary
from employees
order by salary desc, id;
┌──────┬──────────┬────────────┬────────┐
│ tile │ name │ department │ salary │
├──────┼──────────┼────────────┼────────┤
│ │ Иван │ it │ 120 │
│ │ Леонид │ it │ 104 │
│ │ Марина │ it │ 104 │
│ │ Анна │ sales │ 100 │
│ │ Вероника │ sales │ 96 │
│ │ Григорий │ sales │ 96 │
│ │ Ксения │ it │ 90 │
│ │ Елена │ it │ 84 │
│ │ Борис │ hr │ 78 │
│ │ Дарья │ hr │ 70 │
└──────┴──────────┴────────────┴────────┘
Всего 10 записей, 3 группы — значит, две группы по 3 записи и одна 4 записи. Например, так:
┌──────┬──────────┬────────────┬────────┐
│ tile │ name │ department │ salary │
├──────┼──────────┼────────────┼────────┤
│ │ Иван │ it │ 120 │
│ │ Леонид │ it │ 104 │
│ │ Марина │ it │ 104 │
│ │ Анна │ sales │ 100 │
├──────┼──────────┼────────────┼────────┤
│ │ Вероника │ sales │ 96 │
│ │ Григорий │ sales │ 96 │
│ │ Ксения │ it │ 90 │
├──────┼──────────┼────────────┼────────┤
│ │ Елена │ it │ 84 │
│ │ Борис │ hr │ 78 │
│ │ Дарья │ hr │ 70 │
└──────┴──────────┴────────────┴────────┘
Чтобы провести границы между группами, придется анализировать все зарплаты, отсортированные по убыванию. Поэтому окно будет таким:
window w as (order by salary desc)
А вот функция потребуется другая — ntile(n)
, где n
— количество групп. В нашем случае:
select
ntile(3) over w as tile,
name, department, salary
from employees
window w as (order by salary desc)
order by salary desc, id;
┌──────┬──────────┬────────────┬────────┐
│ tile │ name │ department │ salary │
├──────┼──────────┼────────────┼────────┤
│ 1 │ Иван │ it │ 120 │
│ 1 │ Леонид │ it │ 104 │
│ 1 │ Марина │ it │ 104 │
│ 1 │ Анна │ sales │ 100 │
├──────┼──────────┼────────────┼────────┤
│ 2 │ Вероника │ sales │ 96 │
│ 2 │ Григорий │ sales │ 96 │
│ 2 │ Ксения │ it │ 90 │
├──────┼──────────┼────────────┼────────┤
│ 3 │ Елена │ it │ 84 │
│ 3 │ Борис │ hr │ 78 │
│ 3 │ Дарья │ hr │ 70 │
└──────┴──────────┴────────────┴────────┘
ntile(n)
разбивает все записи на n групп и возвращает номер группы для каждой записи. Если общее количество записей (10 в нашем случае) не делится на размер группы (3), то первые группы будут крупнее последних.
Функции ранжирования
row_number() |
порядковый номер строки |
dense_rank() |
ранг строки |
rank() |
тоже ранг, но с пропусками |
ntile(n) |
разбивает все строки на n групп и возвращает номер группы, в которую попала строка |
dense_rank()
и ntile()
мы уже разобрали.
row_number()
нумерует строки в порядке, указанном в order by
. Никаких неожиданностей.
rank()
похож на dense_rank()
, a разницу проще всего показать на примере.
select
••• over w as rank,
name, salary
from employees
window w as (order by salary desc)
order by rank, id;
В одном случае вместо •••
укажем dense_rank()
, а в другом — rank()
:
┌──────┬──────────┬────────┐
│ rank │ name │ salary │
├──────┼──────────┼────────┤
│ 1 │ Иван │ 120 │
│ 2 │ Леонид │ 104 │
│ 2 │ Марина │ 104 │
│ 3 │ Анна │ 100 │
│ 4 │ Вероника │ 96 │
│ 4 │ Григорий │ 96 │
│ 5 │ Ксения │ 90 │
│ 6 │ Елена │ 84 │
│ 7 │ Борис │ 78 │
│ 8 │ Дарья │ 70 │
└──────┴──────────┴────────┘
┌──────┬──────────┬────────┐
│ rank │ name │ salary │
├──────┼──────────┼────────┤
│ 1 │ Иван │ 120 │
│ 2 │ Леонид │ 104 │
│ 2 │ Марина │ 104 │
│ 4 │ Анна │ 100 │
│ 5 │ Вероника │ 96 │
│ 5 │ Григорий │ 96 │
│ 7 │ Ксения │ 90 │
│ 8 │ Елена │ 84 │
│ 9 │ Борис │ 78 │
│ 10 │ Дарья │ 70 │
└──────┴──────────┴────────┘
dense_rank()
назначает Анне третье место, а rank()
— четвертое, потому что второе-третье уже заняты Леонидом и Мариной. Аналогично с Ксенией после Вероники и Григория. Вот и вся разница.
⌘ ⌘ ⌘
Мы разобрались, что такое «окно», «оконная функция», и как использовать их для ранжирования данных. В следующей части займемся оконными сравнениями!
Чтобы закрепить знания на практике — записывайтесь на курс
Подписывайтесь на канал, чтобы не пропустить новые заметки 🚀