[recovery mode] Оконные функции SQL простым языком с примерами

Привет всем!

Сразу хочется отметить, что данная статья написана исключительно для людей, начинающих свой путь в изучении SQL и оконных функций. Здесь могут быть не разобраны сложные применения функций и могут не использоваться сложные формулировки определений — все написано максимально простым языком для базового понимания. 

P.S. Если автор что-то не разобрал и не написал, значит он посчитал это не обязательным в рамках этой статьи))) 

Для примеров будем использовать небольшую таблицу, которая показывает оценки учеников по разным предметам. В БД табличка выглядит следующим образом

select * 
from student_grades;

1b5e947f6d4f5fc522ea300e671ce1b2.png

SQL часто используется для вычислений в данных различных метрик или агрегаций значений по измерениям. Помимо функций агрегации для этого широко используются оконные функции. 

Оконная функция в SQL — функция, которая работает с выделенным набором строк (окном, партицией) и выполняет вычисление для этого набора строк в отдельном столбце. 

Партиции (окна из набора строк) — это набор строк, указанный для оконной функции по одному из столбцов или группе столбцов таблицы. Партиции для каждой оконной функции в запросе могут быть разделены по различным колонкам таблицы.

3e5dd68c86651bdd7774dd6b15fb5288.png

В чем заключается главное отличие оконных функций от функций агрегации с группировкой?  

При использовании агрегирующих функций предложение GROUP BY сокращает количество строк в запросе с помощью их группировки.

90a39588318aad8a733e87647d797af1.png

При использовании оконных функций количество строк в запросе не уменьшается по сравнении с исходной таблицей.

696a8dcb16b5c8e4499961dab2b8f19a.png

Порядок расчета оконных функций в SQL запросе

3c5829ff6fdce67e967d07515eee3bc5.png

Сначала выполняется команда выборки таблиц, их объединения и возможные подзапросы под командой FROM.

Далее выполняются условия фильтрации WHERE, группировки GROUP BY и возможная фильтрация c HAVING

Только потом применяется команда выборки столбцов SELECT и расчет оконных функций под выборкой. 

После этого идет условие сортировки ORDER BY, где тоже можно указать столбец расчета оконной функции для сортировки. 

Здесь важно уточнить, что партиции или окна оконных функций создаются после разделения таблицы на группы с помощью команды GROUP BY, если эта команда используется в запросе. 

Синтаксис оконных функций

Синтаксис оконных функций вне зависимости от их класса будет так или иначе состоять из идентичных команд.

69b77ca8275ff1a615bcacdd5607fb1a.png

Оконные функции можно прописывать как под командой SELECT, так и в отдельном ключевом слове WINDOW, где окну дается алиас (псевдоним), к которому можно обращаться в SELECT выборке.

a850c17e02bad1ffc7b82ba896ed74e4.png

Классы Оконных функций

Множество оконных функций можно разделять на 3 класса:

  • Агрегирующие (Aggregate)

  • Ранжирующие (Ranking)

  • Функции смещения (Value)

dbc8adcb9ffeaddfca54a01cd101e755.png

Агрегирующие:

Можно применять любую из агрегирующих функций — SUM, AVG, COUNT, MIN, MAX

select name, subject, grade,
sum(grade) over (partition by name) as sum_grade,
avg(grade) over (partition by name) as avg_grade,
count(grade) over (partition by name) as count_grade,
min(grade) over (partition by name) as min_grade,
max(grade) over (partition by name) as max_grade
from student_grades;

0fe1fdc331511bfdd7ec7c81710a1814.png

Ранжирующие:

В ранжирующих функция под ключевым словом OVER обязательным идет указание условия ORDER BY, по которому будет происходить сортировка ранжирования. 

ROW_NUMBER () — функция вычисляет последовательность ранг (порядковый номер) строк внутри партиции, НЕЗАВИСИМО от того, есть ли в строках повторяющиеся значения или нет.

RANK () — функция вычисляет ранг каждой строки внутри партиции. Если есть повторяющиеся значения, функция возвращает одинаковый ранг для таких строчек, пропуская при этом следующий числовой ранг. 

DENSE_RANK () — то же самое что и RANK, только в случае одинаковых значений DENSE_RANK не пропускает следующий числовой ранг, а идет последовательно.

select name, subject, grade,
row_number() over (partition by name order by grade desc),
rank() over (partition by name order by grade desc),
dense_rank() over (partition by name order by grade desc)
from student_grades;

6e1c1f72fa9ec7e50269113ef89851fe.png

Про NULL в случае ранжирования:

Для SQL пустые NULL значения будут определяться одинаковым рангом

Функции смещения:

Это функции, которые позволяют перемещаясь по выделенной партиции таблицы обращаться к предыдущему значению строки или крайним значениям строк в партиции.

LAG () — функция, возвращающая предыдущее значение столбца по порядку сортировки.

LEAD () — функция, возвращающая следующее значение столбца по порядку сортировки.

На простом примере видно, как можно в одной строке получить текущую оценку, предыдущую и следующую оценки Пети в четвертях.

select *
from grades_quartal;

ca43448a518288209383666bd0af0b06.png

select name, quartal, subject, grade, 
lag(grade) over (order by quartal) as previous_grade,
lead(grade) over (order by quartal) as next_grade
from grades_quartal;

90ae578c56fb275d47cc67a3160491cd.png

FIRST_VALUE ()/LAST_VALUE () — функции возвращающие первое или последнее значение столбца в указанной партиции. В качестве аргумента указывает столбец, значение которого нужно вернуть. В оконной функции под словом OVER обязательное указание ORDER BY условия. 

В следующей версии статьи разберем отдельно такое понятие как фрейм окна функции или window frame и рассмотрим на простых примерах как он используется. 

Telegram канал про аналитику данных и бизнес-анализ

© Habrahabr.ru