Оконные функции 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 меньше, чем у предыдущей записи:

1️⃣

2️⃣

3️⃣

4️⃣

5️⃣

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

Чтобы проставить ранг, достаточно на каждом шаге смотреть только на значения из столбца 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:

1️⃣

2️⃣

3️⃣

4️⃣

5️⃣

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

Чтобы проставить ранг, достаточно на каждом шаге смотреть только на значения из столбца 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():

dense_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()

┌──────┬──────────┬────────┐
│ 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() — четвертое, потому что второе-третье уже заняты Леонидом и Мариной. Аналогично с Ксенией после Вероники и Григория. Вот и вся разница.

⌘ ⌘ ⌘

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

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

Подписывайтесь на канал, чтобы не пропустить новые заметки 🚀