Не работает оконная функция

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

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

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

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

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

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

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

Синтаксис

Окно определяется с помощью обязательной инструкции 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», который был бы исключен при применении стандартной модели атрибуции, тоже получил свою долю при распределении ценности.

Источник

SQL-Ex blog

Новости сайта «Упражнения SQL», статьи и переводы

Оконные функции или GROUP BY?

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

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

Цель этой статьи — показать на примерах подводные камни в запросах, влияющие на их производительность, и как переписать такие запросы, чтобы сгенерировать другие планы, которые улучшат производительность. В примерах используется дамп данных StackOverflow 2014, который вы можете использовать у себя для тестов.

Кто впервые заработал каждый значок (badge)

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

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

Оконные функции позволяют просто написать запрос, решающий нашу задачу:

Даже если вы не использовали ранее функцию FIRST_VALUE, этот запрос должен быть легко интерпретирован: для каждого значка Name вернуть первый UserId при сортировке по Date (самая ранняя дата получения значка) и UserId (выбираем наименьший UserId при одной и той же дате).

Этот запрос было легко написать и легко понять. Однако его производительность не выдающаяся: 46 секунд до окончательной выдачи результатов на моей машине.

Замечание: Я предполагаю, что эта таблица имеет следующий индекс:

Почему так медленно?

Если мы включим статистику (SET STATISTICS IO ON), то заметим, что SQL Server считывает 46767 страниц из некластеризованного индекса. Поскольку мы не фильтруем наши данные, мало что можно сделать, чтобы ускориться.

Читая план справа налево, следом мы видим два оператора Segment. Они не добавляют большой нагрузки, поскольку наши данные уже отсортированы по сегментам/группам. Поэтому для SQL Server тривиально определить, когда отсортированные строки изменяют значения.

Следующий оператор Window Spool, который «расширяет каждую строку в набор строк, которые представляют связанное с ней окно.» Хотя этот оператор выглядит невинно из-за низкой относительной стоимости, он записывает 8 миллионов строк/читая 16 миллионов строк (поскольку так работает Window Spool) из tempdb. Ох.

После чего оператор Stream Aggregate и операторы Compute Scalar проверяют, является ли первое значение в каждом окне, возвращаемом из Window Spool, NULL-значением, после чего возвращают первое не-NULL значение. Эти операторы также относительно безболезненны, т.к. потоки данных уже отсортированы.

Затем оператор Hash Match удаляет дубликаты данных для нашего DISTINCT, после чего мы сортируем остальные 2k строк на вывод.

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

Устранение использования tempdb старомодным способом

Когда я говорю «старомодный», я имею в виду переписывание нашей оконной функции на использование традиционных агрегатных функций и GROUP BY:

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

И хотя этот запрос может выглядеть уродливо со стороны, реально имеет значение только то, что лежит под капотом:

Какой замечательно простой план выполнения. И он выполняется практически мгновенно.

Давайте разберемся, что происходит. Сначала мы стартуем с операторов Index Scan и Segment, аналогичных предыдущему запросу.

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

SQL Server может использовать оптимизацию с оператором TOP, который позволяет взять отсортированные данные и вернуть только строки с Name и UserId для топовых значений Name и Date в пределах группы (по существу соответствует логике MIN). Это прекрасный пример того, как оптимизатор может взять декларативный запрос SQL и решить, как эффективно вернуть требуемые данные.

Здесь оператор TOP отфильтровывает из наших 8 миллионов строк около 30k строк. Исключение дубликатов среди 30k выполняется значительно быстрей с помощью оператора Stream Aggregate, и, поскольку данные уже отсортированы, нам не требуется дополнительный оператор Sort.

В целом этот второй запрос выполняется намного лучше оригинального, поскольку SQL Server не должен обращаться в tempdb для выполнения каких-либо операций — все данные предварительно отсортированы в индексе и в таком виде попадают во входной поток.

Так стоит ли использовать оконные функции?

Не обязательно искать компромисс.

Я почти всегда начинаю с оконных функций из-за легкости их написания и чтения. Однако если оконная функция должна писать/читать множество данных в/из tempdb, и это отрицательно влияет на общую производительность вашего запроса, возможно стоит переписать его.

Я, скорее, предпочту использовать громоздкий синтаксис, чтобы увеличить производительность в 2000 раз.

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Источник