Разбор оконных функций

Все привет. В этой статье мы разберем оконные функции.

Оконные функции — это мощнейший инструмент, который с легкостью помогает решать множество задач.

Если вам нужно произвести вычисление над заданным набором строк, объединенных каким-то одним признаком, вам на помощь придут именно они.

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

Принцип работы

«Что значит оконные?»

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

Синтаксис

Окно определяется с помощью обязательной инструкции OVER(). Давайте рассмотрим синтаксис этой инструкции:

SELECT Название функции (столбец для вычислений)

OVER (

PARTITION BY столбец для группировки

ORDER BY столбец для сортировки

ROWS или RANGE выражение для ограничения строк в пределах группы

)

Теперь разберем как поведет себя множество строк при использовании того или иного ключевого слова функции. А тренироваться будем на простой табличке содержащей дату, Имя и ранг.

OVER()

Откроем окно при помощи OVER() и просуммируем столбец ранг:

SELECT Date, Name, Rang, SUM(Rang) OVER() AS 'Sum'

FROM Table

Мы использовали инструкцию OVER() без предложений. В таком варианте окном будет весь набор данных и никакая сортировка не применяется. Появился новый столбец «Sum» и для каждой строки выводится одно и то же значение. Это сквозная сумма всех значений колонки ранг.

PARTITION BY

Теперь применим инструкцию PARTITION BY, которая определяет столбец, по которому будет производиться группировка и является ключевой в разделении набора строк на окна:

SELECT Date, Name, Rang, SUM(Rang) OVER(PARTITION BY Date) AS 'Sum'

FROM Table

Инструкция PARTITION BY сгруппировала строки по полю «Date». Теперь для каждой группы рассчитывается своя сумма значений столбца ранг.

ORDER BY

Попробуем отсортировать значения внутри окна при помощи ORDER BY:

SELECT Date, Name, Rang, SUM(Rang) OVER(PARTITION BY Date ORDER BY Name) AS 'Sum'

FROM Table

К предложению PARTITION BY добавилось ORDER BY по полю имя. Таким образом мы указали, что хотим видеть сумму не всех значений в окне, а для каждого значения ранг сумму со всеми предыдущими. То есть мы посчитали нарастающий итог.

ROWS или RANGE

Инструкция ROWS позволяет ограничить строки в окне, указывая фиксированное количество строк, предшествующих или следующих за текущей.

Инструкция RANGE, в отличие от ROWS, работает не со строками, а с диапазоном строк в инструкции ORDER BY. То есть под одной строкой для RANGE могут пониматься несколько физических строк одинаковых по рангу.

Обе инструкции ROWS и RANGE всегда используются вместе с ORDER BY.

В выражении для ограничения строк ROWS или RANGE также можно использовать следующие ключевые слова:

• UNBOUNDED PRECEDING — указывает, что окно начинается с первой строки группы;
• UNBOUNDED FOLLOWING – с помощью данной инструкции можно указать, что окно заканчивается на последней строке группы;
• CURRENT ROW – инструкция указывает, что окно начинается или заканчивается на текущей строке;
• BETWEEN «граница окна» AND «граница окна» — указывает нижнюю и верхнюю границу окна;
• «Значение» PRECEDING – определяет число строк перед текущей строкой (не допускается в предложении RANGE).;
• «Значение» FOLLOWING — определяет число строк после текущей строки (не допускается в предложении RANGE).

Разберем на примере:

SELECT Date, Name, Rang, SUM(Rang) OVER(PARTITION BY Date ORDER BY Rang ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS 'Sum'

FROM Table

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

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

Виды функций

Оконные функции можно подразделить на следующие группы:

• Агрегатные функции;
• Ранжирующие функции;
• Функции смещения;
• Аналитические функции.

В одной инструкции SELECT с одним предложением FROM можно использовать сразу несколько оконных функций. Давайте подробно разберем каждую группу и пройдемся по основным функциям.

Агрегатные функции

Агрегатные функции – это функции, которые выполняют на наборе данных арифметические вычисления и возвращают итоговое значение.

• SUM – возвращает сумму значений в столбце;
• COUNT — вычисляет количество значений в столбце (значения NULL не учитываются);
• AVG — определяет среднее значение в столбце;
• MAX — определяет максимальное значение в столбце;
• MIN — определяет минимальное значение в столбце.

Пример использования агрегатных функций с оконной инструкцией OVER:

SELECT Date, Name, Rang

, SUM(Rang) OVER(PARTITION BY Date) AS 'Sum'

, COUNT(Rang) OVER(PARTITION BY Date) AS 'Count'

, AVG(Rang) OVER(PARTITION BY Date) AS 'Avg'

, MAX(Rang) OVER(PARTITION BY Date) AS 'Max'

, MIN(Rang) OVER(PARTITION BY Date) AS 'Min'

FROM Table

Ранжирующие функции

Ранжирующие функции – это функции, которые ранжируют значение для каждой строки в окне.

Например, их можно использовать для того, чтобы присвоить порядковый номер строке или составить рейтинг.

• ROW_NUMBER – функция возвращает номер строки и используется для нумерации;
• RANK — функция возвращает ранг каждой строки. В данном случае значения уже анализируются и, в случае нахождения одинаковых, возвращает одинаковый ранг с пропуском следующего значения;
• DENSE_RANK — функция возвращает ранг каждой строки. Но в отличие от функции RANK, она для одинаковых значений возвращает ранг, не пропуская следующий;
• NTILE – это функция, которая позволяет определить к какой группе относится текущая строка. Количество групп задается в скобках.

SELECT Date, Name, Rang

, ROW_NUMBER() OVER(PARTITION BY Date ORDER BY Rang) AS 'Row_number'

, RANK() OVER(PARTITION BY Date ORDER BY Rang) AS 'Rank'

, DENSE_RANK() OVER(PARTITION BY Date ORDER BY Rang) AS 'Dense_Rank'

, NTILE(3) OVER(PARTITION BY Date ORDER BY Rang) AS 'Ntile'

FROM Table

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

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

LAG или LEAD – функция LAG обращается к данным из предыдущей строки окна, а LEAD к данным из следующей строки. Функцию можно использовать для того, чтобы сравнивать текущее значение строки с предыдущим или следующим. Имеет три параметра: столбец, значение которого необходимо вернуть, количество строк для смещения (по умолчанию 1), значение, которое необходимо вернуть если после смещения возвращается значение NULL;

FIRST_VALUE или LAST_VALUE — с помощью функции можно получить первое и последнее значение в окне. В качестве параметра принимает столбец, значение которого необходимо вернуть.

SELECT Date, Name, Rang

, LAG(Rang) OVER(PARTITION BY Date ORDER BY Date) AS 'Lag'

, LEAD(Rang) OVER(PARTITION BY Date ORDER BY Date) AS 'Lead'

, FIRST_VALUE(Rang) OVER(PARTITION BY Date ORDER BY Date) AS 'First_Value'

, LAST_VALUE(Rang) OVER(PARTITION BY Date ORDER BY Date) AS 'Last_Value'

FROM Table

Аналитические функции

Аналитические функции — это функции которые возвращают информацию о распределении данных и используются для статистического анализа.

• CUME_DIST — вычисляет интегральное распределение (относительное положение) значений в окне;
• PERCENT_RANK — вычисляет относительный ранг строки в окне;
• PERCENTILE_CONT — вычисляет процентиль на основе постоянного распределения значения столбца. В качестве параметра принимает процентиль, который необходимо вычислить (в этой статье я рассказываю как посчитать медиану, благодаря этой функции);
• PERCENTILE_DISC — вычисляет определенный процентиль для отсортированных значений в наборе данных. В качестве параметра принимает процентиль, который необходимо вычислить.

Важно! У функций PERCENTILE_CONT и PERCENTILE_DISC, столбец, по которому будет происходить сортировка, указывается с помощью ключевого слова WITHIN GROUP.

SELECT Date, Name, Rang

, CUME_DIST() OVER(PARTITION BY Date ORDER BY Rang) AS 'Cume_Dist'

, PERCENT_RANK() OVER(PARTITION BY Date ORDER BY Rang) AS 'Percent_Rank'

, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Rang) OVER(PARTITION BY Date) AS 'Percentile_Cont'

, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Rang) OVER(PARTITION BY Date) AS 'Percentile_Disc'

FROM Table

Почему не GROUP BY и не JOIN

Сразу проясним, что оконные функции — это не то же самое, что GROUP BY. Они не уменьшают количество строк, а возвращают столько же значений, сколько получили на вход. Во-вторых, в отличие от GROUP BY, OVER может обращаться к другим строкам. И в-третьих, они могут считать скользящие средние и кумулятивные суммы.

Примечание Оконные функции не изменяют выборку, а только добавляют некоторую дополнительную информацию о ней. Для простоты понимания можно считать, что SQL сначала выполняет весь запрос (кроме сортировки и limit), а уже потом считает значения окна.

Хорошо, с GROUP BY разобрались. Но в SQL практически всегда можно пойти несколькими путями. К примеру, может возникнуть желание использовать подзапросы или JOIN. Конечно, JOIN по производительности предпочтительнее подзапросов, а производительность конструкций JOIN и OVER окажется одинаковой. Но OVER даёт больше свободы, чем жёсткий JOIN. Да и объём кода в итоге окажется гораздо меньше.

Если статья была Вам полезна, ставьте пальцы вверх и подписывайтесь. Оставляйте свои пожелания и вопросы в комментариях, с удовольствием отвечу.

#it #sql #обучение #курс #программирование