Учимся применять оконные функции

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

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

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

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

У вас может возникнуть вопрос – «Что значит оконные?»

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

Синтаксис

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

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

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

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

PARTITION BY

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

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

ORDER BY

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

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

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).

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

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

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

Виды функций

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Кейс. Модели атрибуции

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

У нас есть таблица с id посетителя (им может быть Client ID, номер телефона и тп.), датами и количеством посещений сайта, а также с информацией о достигнутых конверсиях.

Первый клик

В Google Analytics стандартной моделью атрибуции является последний непрямой клик. И в данном случае 100% ценности конверсии присваивается последнему каналу в цепочке взаимодействий.

Попробуем посчитать модель по первому взаимодействию, когда 100% ценности конверсии присваивается первому каналу в цепочке при помощи функции FIRST_VALUE.

Рядом со столбцом «Medium» появился новый столбец «First_Click», в котором указан канал в первый раз приведший посетителя к нам на сайт и вся ценность зачтена данному каналу.

Произведем агрегацию и получим отчет.

С учетом давности взаимодействий

В этом случае работает правило: чем ближе к конверсии находится точка взаимодействия, тем более ценной она считается. Попробуем рассчитать эту модель при помощи функции DENSE_RANK.

Рядом со столбцом «Medium» появился новый столбец «Ranks», в котором указан ранг каждой строки в зависимости от близости к дате конверсии.

Теперь используем этот запрос для того, чтобы распределить ценность равную 1 (100%) по всем точкам на пути к конверсии.

Рядом со столбцом «Medium» появился новый столбец «Time_Decay» с распределенной ценностью.

И теперь, если сделать агрегацию, можно увидеть как распределилась ценность по каналам.

Из получившегося отчета видно, что самым весомым каналом является канал «cpc», а канал «cpa», который был бы исключен при применении стандартной модели атрибуции, тоже получил свою долю при распределении ценности.

Оконные функции в T-SQL – инструкция OVER

В языке Transact-SQL существует очень полезный и мощный инструмент для формирования различных аналитических отчетов – это инструкция OVER, которая работает совместно с так называемыми «оконными функциями», именно об этом мы сегодня с Вами и поговорим.

Инструкция OVER в Transact-SQL

OVER – это инструкция T-SQL, которая определяет окно для применения оконной функции. «Окно» в Microsoft SQL Server – это контекст, в котором работает функция с определённым набором строк, относящихся к текущей строке.

Оконная функция – это функция, которая соответственно работает с окном, т.е. набором строк, и возвращает значение на основе неких вычислений.

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

Честно скажу это очень удобный и полезный функционал Microsoft SQL Server. Впервые поддержка оконных функций появилась в версии Microsoft SQL Server 2005, в которой была реализованы базовая функциональность. В Microsoft SQL Server 2012 функционал оконных функций был расширен, и теперь он с лёгкостью решает много задач, которые до этого решались написанием дополнительного, в некоторых случаях, сложного, непонятного кода (вложенные запросы и т.д.).

Упрощенный синтаксис инструкции OVER

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

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

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

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

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

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

Примечание! Чтобы указать выражение для дополнительного ограничения строк (ROWS или RANGE) в окне должна быть указана инструкция ORDER BY.

А сейчас давайте рассмотрим оконные функции, которые существуют в Transact-SQL.

Оконные функции в Transact-SQL

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

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

В одной инструкции SELECT с одним предложением FROM можно использовать несколько оконных функций. Если инструкция PARTITION BY не указана, функция будет обрабатывать все строки результирующего набора. Некоторые функции не поддерживают инструкцию ORDER BY, ROWS или RANGE.

Исходные данные для примеров

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

В качестве сервера у меня будет выступать Microsoft SQL Server 2016 Express.

Допустим, у нас будет таблица TestTable, которая содержит список товаров с некоторыми характеристиками.

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

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

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

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

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

В этом примере продемонстрировано простое применение некоторых агрегатных оконных функций.

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

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

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

В Microsoft SQL Server существуют следующие ранжирующие функции:

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

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

В данном примере мы пронумеруем строки в каждой категории, при этом используем сортировку по столбцу ProductId, а также выставим ранг каждому товару в категории на основе его цены.

Более детально про ранжирующие функции мы говорили в материале – Функции ранжирования и нумерации в Transact-SQL.

Оконные функции смещения

Функции смещения – это функции, которые позволяют перемещаться и, соответственно, обращаться к разным строкам в наборе данных (окне) относительно текущей строки или просто обращаться к значениям в начале или в конце окна. Эти функции появились в Microsoft SQL Server 2012.

К функциям смещения в T-SQL относятся:

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

Пример использования оконных функций смещения в T-SQL.

В этом примере сначала мы вернем следующее и предыдущее значение идентификатора товара в категории. Затем с помощью FIRST_VALUE и LAST_VALUE получим первое и последнее значение идентификатора товара в категории, при этом в качестве примера я покажу, как используется синтаксис дополнительного ограничения строк. А потом, используя необязательные параметры функций LEAD и LAG, мы сместимся уже на 2 строки относительно текущей, при этом, если после смещения функцией LAG такой строки не окажется, нам вернется 0, так как мы укажем третий необязательный параметр со значением 0.

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

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

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

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

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

Оконные функции языка T-SQL мы рассмотрели, некоторые из них, как я уже говорил, очень полезны и значительно упрощают написание SQL запросов, всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL, которую написал я, и в которой я подробно, и в то же время простым языком, рассказываю о языке SQL, у меня на этом все, пока!

Заметка! Все возможности языка SQL и T-SQL очень подробно рассматриваются в моих видеокурсах по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать на T-SQL в Microsoft SQL Server.