Оконные функции: смещение
Это третья статья из серии Оконные функции в картинках. Рекомендую не просто читать, а проходить курс — с ним знания превратятся в навыки.
Сравнение со смещением — это когда мы смотрим, в чем разница между соседними значениями. Например, сравниваем страны, которые занимают 5 и 6 место в мировом рейтинге ВВП — сильно ли отличаются? А если сравнить 1 и 6 место?
Сюда же попадают задачи, в которых мы сравниваем значение из набора с границами набора. Например, есть 100 лучших теннисисток мира. Мария Саккари занимает в рейтинге 20 место. Как ее показатели соотносятся с Эшли Бартли, которая занимает 1 место? А с Лин Чжоу, которая занимает 100 место?
Мы будем сравнить сотрудников из таблички 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 │
└────┴──────────┴────────┴────────────┴────────┘
Все запросы можно повторять в песочнице.
Разница по зарплате с предыдущим
Упорядочим сотрудников по возрастанию зарплаты и проверим, велик ли разрыв между соседями:
Столбец diff показывает, на сколько процентов зарплата сотрудника отличается от предыдущего коллеги. Видно, что больших разрывов нет. Самые крупные — между Дарьей и Борисом (10%) и Мариной и Иваном (13%).
Как перейти от «было» к «стало»?
Сначала отсортируем таблицу по возрастанию зарплаты:
select
name, department, salary,
null as prev
from employees
order by salary, id;
┌──────────┬────────────┬────────┬──────┐
│ name │ department │ salary │ prev │
├──────────┼────────────┼────────┼──────┤
│ Дарья │ hr │ 70 │ │
│ Борис │ hr │ 78 │ │
│ Елена │ it │ 84 │ │
│ Ксения │ it │ 90 │ │
│ Вероника │ sales │ 96 │ │
│ Григорий │ sales │ 96 │ │
│ Анна │ sales │ 100 │ │
│ Леонид │ it │ 104 │ │
│ Марина │ it │ 104 │ │
│ Иван │ it │ 120 │ │
└──────────┴────────────┴────────┴──────┘
Теперь пройдем от первой строчки до последней, на каждом шаге «подтягивая» зарплату предыдущего сотрудника:
и так далее...
Одной гифкой:
Видно, что окно в данном случае охватывает текущую и предыдущую запись. Оно сдвигается вниз на каждом шаге, скользит. Это логичная трактовка происходящего, и задать скользящее окно в SQL можно. Но у таких окон более сложный синтаксис, поэтому отложим их до статьи о скользящих агрегатах.
Вместо этого возьмем более простое и знакомое нам окно — все записи, упорядоченные по возрастанию salary:
window w as (order by salary)
А чтобы на каждом шаге подтягивать зарплату предыдущего сотрудника, будем использовать оконную функцию lag():
lag(salary, 1) over w
Функция lag() возвращает значение из указанного столбца, отстоящее от текущего на указанное количество записей назад. В нашем случае — salary от предыдущей записи.
Добавим окно и оконную функцию в исходный запрос:
select
id, name, department, salary,
lag(salary, 1) over w as prev
from employees
window w as (order by salary)
order by salary, id;
┌────┬──────────┬────────────┬────────┬──────┐
│ id │ name │ department │ salary │ prev │
├────┼──────────┼────────────┼────────┼──────┤
│ 11 │ Дарья │ hr │ 70 │ │
│ 12 │ Борис │ hr │ 78 │ 70 │
│ 21 │ Елена │ it │ 84 │ 78 │
│ 22 │ Ксения │ it │ 90 │ 84 │
│ 31 │ Вероника │ sales │ 96 │ 90 │
│ 32 │ Григорий │ sales │ 96 │ 96 │
│ 33 │ Анна │ sales │ 100 │ 96 │
│ 23 │ Леонид │ it │ 104 │ 100 │
│ 24 │ Марина │ it │ 104 │ 104 │
│ 25 │ Иван │ it │ 120 │ 104 │
└────┴──────────┴────────────┴────────┴──────┘
Столбец prev показывает зарплату предыдущего сотрудника. Осталось посчитать разницу между prev и salary в процентах:
select
name, department, salary,
round(
(salary - lag(salary, 1) over w)*100.0 / salary
) as diff
from employees
window w as (order by salary)
order by salary, id;
┌──────────┬────────────┬────────┬──────┐
│ name │ department │ salary │ diff │
├──────────┼────────────┼────────┼──────┤
│ Дарья │ hr │ 70 │ │
│ Борис │ hr │ 78 │ 10.0 │
│ Елена │ it │ 84 │ 7.0 │
│ Ксения │ it │ 90 │ 7.0 │
│ Вероника │ sales │ 96 │ 6.0 │
│ Григорий │ sales │ 96 │ 0.0 │
│ Анна │ sales │ 100 │ 4.0 │
│ Леонид │ it │ 104 │ 4.0 │
│ Марина │ it │ 104 │ 0.0 │
│ Иван │ it │ 120 │ 13.0 │
└──────────┴────────────┴────────┴──────┘
Здесь мы заменили prev → lag(salary, 1) over w. Конструкцию вида function_name(...) over window_name движок заменяет на конкретное значение, которое вернула функция. Так что оконную функцию можно вызывать прямо внутри вычислений, и вы не раз встретите такие запросы в документации и примерах.
Диапазон зарплат в департаменте
Посмотрим, как зарплата сотрудника соотносится с минимальной и максимальной зарплатой в его департаменте:
Для каждого сотрудника столбец low показывает минимальную зарплату родного департамента, а столбец high — максимальную.
Как перейти от «было» к «стало»?
Сначала отсортируем таблицу по департаментам, а внутри департамента — по возрастанию зарплаты:
select
name, department, salary,
null as low,
null as high
from employees
order by department, salary, id;
┌──────────┬────────────┬────────┬─────┬──────┐
│ name │ department │ salary │ low │ high │
├──────────┼────────────┼────────┼─────┼──────┤
│ Дарья │ hr │ 70 │ │ │
│ Борис │ hr │ 78 │ │ │
│ Елена │ it │ 84 │ │ │
│ Ксения │ it │ 90 │ │ │
│ Леонид │ it │ 104 │ │ │
│ Марина │ it │ 104 │ │ │
│ Иван │ it │ 120 │ │ │
│ Вероника │ sales │ 96 │ │ │
│ Григорий │ sales │ 96 │ │ │
│ Анна │ sales │ 100 │ │ │
└──────────┴────────────┴────────┴─────┴──────┘
Теперь пройдем от первой строчки до последней, на каждом шаге «подтягивая» наименьшую и наибольшую зарплаты в отделе:
и так далее...
Одной гифкой:
Окно состоит из трех секций. Секция на каждом шаге охватывает весь департамент сотрудника. Записи при этом упорядочены по возрастанию зарплаты внутри департамента, чтобы минимальная и максимальная зарплаты всегда находились на границах секции:
window w as (
partition by department
order by salary
)
Хотелось бы воспользоваться функциями lag() и lead(), чтобы получить диапазон зарплат в отделе. Но они заглядывают на фиксированное количество записей назад или вперед. Нам же требуется нечто другое:
low— зарплата первого сотрудника, входящего в секцию окна;high— зарплата последнего сотрудника, входящего в секцию.
К счастью, есть оконные функции ровно для этого:
first_value(salary) over w as low,
last_value(salary) over w as high
Добавим окно и оконную функцию в исходный запрос:
select
name, department, salary,
first_value(salary) over w as low,
last_value(salary) over w as high
from employees
window w as (
partition by department
order by salary
)
order by department, salary, id;
┌──────────┬────────────┬────────┬─────┬──────┐
│ name │ department │ salary │ low │ high │
├──────────┼────────────┼────────┼─────┼──────┤
│ Дарья │ hr │ 70 │ 70 │ 70 │
│ Борис │ hr │ 78 │ 70 │ 78 │
├──────────┼────────────┼────────┼─────┼──────┤
│ Елена │ it │ 84 │ 84 │ 84 │
│ Ксения │ it │ 90 │ 84 │ 90 │
│ Леонид │ it │ 104 │ 84 │ 104 │
│ Марина │ it │ 104 │ 84 │ 104 │
│ Иван │ it │ 120 │ 84 │ 120 │
├──────────┼────────────┼────────┼─────┼──────┤
│ Вероника │ sales │ 96 │ 96 │ 96 │
│ Григорий │ sales │ 96 │ 96 │ 96 │
│ Анна │ sales │ 100 │ 96 │ 100 │
└──────────┴────────────┴────────┴─────┴──────┘
low рассчитался корректно, а вот с high какая-то ерунда. Вместо того, чтобы равняться максимальной зарплате департамента, он меняется от сотрудника к сотруднику. Что ж, давайте разбираться.
Окно, секция, фрейм
До сих пор все было логично:
- есть окно, которое состоит из одной или нескольких секций;
- внутри секции записи упорядочены по конкретному столбцу.
На предыдущем шаге мы разделили окно на три секции — по департаментам, и упорядочили записи внутри секций по зарплате:
window w as (
partition by department
order by salary
)
Допустим, движок выполняет запрос, и текущая запись — Леонид из it-отдела. Мы ожидаем, что first_value() вернет первую запись it-секции (salary = 84), а last_value() — последнюю (salary = 120):
Вместо этого last_value() возвращает salary = 104:
Дело в том, что функции first_value() и last_value() работают не просто с секцией окна. Они работают с фреймом внутри секции:
Фрейм находится в той же секции, где текущая запись (Леонид):
- начало фрейма = начало секции (Елена);
- конец фрейма = последняя запись со значением
salary, равным текущей записи (Марина).
Секция фиксирована, фрейм же зависит от текущей записи и постоянно меняется:
first_value() возвращает первую строчку фрейма, а не секции. Но поскольку начало фрейма совпадает с началом секции, функция отрабатывает как мы ожидали.
last_value() возвращает последнюю строчку фрейма, а не секции. Именно поэтому в нашем запросе она вернула не максимальную зарплату для каждого отдела, а какую-то ерунду.
Чтобы last_value() работала как мы ожидаем, придется «прибить» границы фрейма к границам секции. Тогда для каждой секции фрейм будет в точности совпадать с ней:
Зачем так сложно-то? 🤦 Если возникла такая реакция — прекрасно вас понимаю. От фреймов есть польза, но зачем авторы стандарта SQL сделали такое неочевидное поведение по умолчанию — я не знаю. Остается только понять и простить.
Подытожим принцип, по которому работают first_value() и last_value():
- Есть окно, которое состоит из одной или нескольких секций (
partition by department). - Внутри секции записи упорядочены по конкретному столбцу (
order by salary). - У каждой записи в секции свой фрейм. По умолчанию начало фрейма совпадает с началом секции, а конец для каждой записи свой.
- Конец фрейма можно приклеить к концу секции, чтобы фрейм в точности совпадал с секцией.
- Функция
first_value()возвращает значение из первой строки фрейма. - Функция
last_value()возвращает значение из последней строки фрейма.
Теперь разберемся, как прибить фрейм к окну — и закончим с запросом по диапазону зарплат в департаментах.
Диапазон зарплат в департаменте, окончание
Возьмем наше окно:
window w as (
partition by department
order by salary
)
И настроим его, чтобы фрейм в точности совпадал с секцией (департаментом):
window w as (
partition by department
order by salary
rows between unbounded preceding and unbounded following
)
Не будем сейчас разбирать конструкцию rows between — ее время придет в статье про скользящие агрегаты. Важно, что благодаря ей фрейм совпадает с секцией, а значит last_value() вернет максимальную зарплату по департаменту:
select
name, department, salary,
first_value(salary) over w as low,
last_value(salary) over w as high
from employees
window w as (
partition by department
order by salary
rows between unbounded preceding and unbounded following
)
order by department, salary, id;
┌──────────┬────────────┬────────┬─────┬──────┐
│ name │ department │ salary │ low │ high │
├──────────┼────────────┼────────┼─────┼──────┤
│ Дарья │ hr │ 70 │ 70 │ 78 │
│ Борис │ hr │ 78 │ 70 │ 78 │
├──────────┼────────────┼────────┼─────┼──────┤
│ Елена │ it │ 84 │ 84 │ 120 │
│ Ксения │ it │ 90 │ 84 │ 120 │
│ Леонид │ it │ 104 │ 84 │ 120 │
│ Марина │ it │ 104 │ 84 │ 120 │
│ Иван │ it │ 120 │ 84 │ 120 │
├──────────┼────────────┼────────┼─────┼──────┤
│ Вероника │ sales │ 96 │ 96 │ 100 │
│ Григорий │ sales │ 96 │ 96 │ 100 │
│ Анна │ sales │ 100 │ 96 │ 100 │
└──────────┴────────────┴────────┴─────┴──────┘
Теперь движок заполняет low и high так же, как мы делали это вручную.
Функции смещения
lag(value, offset) |
значение value из строки, отстоящей на offset строк назад от текущей |
lead(value, offset) |
значение value из строки, отстоящей на offset строк вперед от текущей |
first_value(value) |
значение value из первой строки фрейма |
last_value(value) |
значение value из последней строки фрейма |
nth_value(value, n) |
значение value из n-й строки фрейма |
lag() и lead() действуют относительно текущей строки, заглядывая вперед или назад на указанное количество строк.
first_value(), last_value() и nth_value() действуют относительно границ фрейма, выбирая указанную строку в пределах фрейма.
Чтобы границы фрейма совпадали с границами секции (или всего окна, если секция одна) — используют конструкцию rows between unbounded preceding and unbounded following в определении окна.
⌘ ⌘ ⌘
Мы разобрались, как сравнивать строки с соседями и границами окна. В следующей части займемся агрегацией данных!
Чтобы закрепить знания на практике — записывайтесь на курс 🚀