SQL для Tableau: оконные функции

07.04.2022

 

Начнем с определения оконных функций. Отлично подходит определение, данное в документации PostgreSQL :

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

Хорошо, это немного сбивает с толку, но станет намного понятнее, когда мы рассмотрим несколько примеров. Итак, давайте начнем с простого примера использования Superstore на общедоступном SQL Server.

Мы начнем с простой агрегации продаж по регионам.

SELECT [Region], SUM([Sales])
FROM [Orders]
GROUP BY [Region]

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

SELECT [Row ID], [Region], [Sales],
SUM([Sales]) OVER (PARTITION BY [Region]) AS [Region Sales]
FROM [Orders]

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

SUM([Sales]) OVER (PARTITION BY [Region])

Этот оператор начинается с функции — в данном случае агрегации, — за которой следует OVER. По сути, OVER сообщает базе данных, что мы собираемся определить окно. Затем, в круглых скобках, мы определяем окно с помощью выражения PARTITION BY. Это сообщает базе данных, что мы хотим разделить (т.е. сгруппировать) наши записи по регионам. Итак, простыми словами это означало бы “Суммировать продажи для всего региона, указанного в этой строке”.

Примечание. Если вы вообще не хотите разделять свои данные (т. е. получать общие продажи для всего набора данных), вам просто не нужно включать выражение PARTITION BY:

SUM([Sales]) OVER () AS [Region Sales]

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

SELECT [Row ID], [Region], [Sales]/SUM([Sales]) OVER (PARTITION BY [Region]) AS [% of Sales]
FROM [Orders]

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

SELECT [Region], [Customer Name],
SUM([Sales]) AS [Customer Sales],
SUM(SUM([Sales]))
OVER (PARTITION BY [Region]) AS [Region Sales]
FROM [Orders]
GROUP BY [Region], [Customer Name]

Обратите внимание, что нам нужно дополнительно агрегировать расчет окна. Без него мы получим ошибку.

 

Сходство с Tableau

Если вышеизложенное кажется вам знакомым, это потому, что вы, вероятно, делали что-то очень похожее в Tableau. Давайте возьмем приведенный выше вариант использования (процент каждой записи от региональных продаж) и воспользуемся Tableau для его решения. Для этого мы будем использовать либо LOD, либо оконные вычисления. Начнем с оконных вычислений. Мы начнем с создания нашего базового представления:

Тогда мы могли бы написать такой расчет:

Region Sales

// Sales for the overall region.
WINDOW_SUM(SUM([Sales]))

Затем мы добавим это в представление, убедимся, что оно правильно настроено, вот, что мы получим:

Затем, конечно, мы могли бы выполнить следующее вычисление, SUM([Sales])/[Region Sales] , чтобы получить процент.

Если мы внимательно изучим это, мы увидим, насколько оконные функции SQL похожи на эту оконную функцию SUM. Давайте посмотрим на них вместе:

SUM(SUM([Sales])) OVER (PARTITION BY [Region])
WINDOW_SUM(SUM([Sales]))

Нам не нужно сообщать SQL, что это оконное вычисление, из-за предложения OVER, тогда как Tableau нужно знать, что мы используем оконную функцию. Кроме того, в SQL мы указываем, как разбивать наши данные в скрипте, а в Tableau мы указываем это разбиение, когда сообщаем ему, как выполнять расчет таблицы.

Вторым вариантом для этого в Tableau может быть расчет уровня детализации.

Region Sales

// Sales for the overall region.
{FIXED [Region]: SUM([Sales])}

Хотя синтаксически это меньше похоже на SQL, чем оконная сумма, мы определенно можем видеть некоторые сходства. Фиксация региона в LOD аналогична разбиению на регионы в SQL. Затем мы суммируем продажи в этом разделе.

Интересно, что ранее опубликованное определение оконных вычислений включало следующее: «Оконная функция может получить доступ не только к текущей строке результата запроса». Это очень похоже на расчет LOD, и, как мы видели, у них много общего.

Важно отметить, что ни расчеты окна Tableau, ни LOD не являются точными синонимами вычислений окна SQL. Эти расчеты Tableau могут меняться в зависимости от размеров вашего представления, способа их расчета, фильтров и ряда других вещей. Тем не менее, есть много общего, поэтому, учитывая, что вы, вероятно, уже знакомы с этими концепциями. Я думаю, что они полезны для иллюстрации того, как работают вычисления окна SQL.

 

ORDER BY

Мы видели, как мы можем использовать PARTITION BY для разделения наших оконных функций, но есть и другая часть синтаксиса, которая может быть очень полезной, ORDER BY. Чтобы продемонстрировать это, давайте рассмотрим сценарий, в котором мы хотели бы пронумеровать каждую запись. Для этого мы можем использовать оконную функцию ROW_NUMBER:

SELECT [Row ID], [Region], [Customer Name],
ROW_NUMBER() OVER (ORDER BY [Row ID]) AS [Row]
FROM [Orders]

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

SELECT [Row ID], [Region], [Customer Name],
ROW_NUMBER() OVER (ORDER BY [Row ID] DESC) AS [Row]
FROM [Orders]

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

SELECT [Row ID], [Region], [Customer Name], [Sales],
ROW_NUMBER() OVER (ORDER BY [Region] ASC, [Sales] DESC) AS [Row]
FROM [Orders]

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

В этом случае он просто продолжает нумеровать записи, как только попадает в новый регион. Но что, если мы хотим, чтобы он начинался заново в каждом регионе (точно так же, как мы могли бы сделать с опцией «Restarting every» для расчета Tableau)? Здесь мы можем снова ввести PARTITION BY.

SELECT [Row ID], [Region], [Customer Name],
ROW_NUMBER() OVER (PARTITION BY [Region] ORDER BY [Region] ASC, [Sales] DESC) AS [Row]
FROM [Orders]

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

Важно указать на сходство этого синтаксиса с новыми аналитическими функциями Tableau Prep. Одна из функций, которые они сделали доступными, — не что иное, как ROW_NUMBER. В Prep вы бы использовали ту же базовую технику нумерации строк, используя следующий синтаксис:

{ PARTITION [Region] : { ORDERBY [Region] ASC, [Sales] DESC : ROW_NUMBER () }}

Этот синтаксис очень похож на тот, что мы используем в SQL, вплоть до используемых ключевых слов (PARTITION, ORDERBY) — даже больше, чем расчеты окна и LOD, которые мы обсуждали ранее.

 

Дополнительные функции

Есть несколько дополнительных оконных функций, которые мы еще не обсуждали. Итак, вот некоторые из них, с примерами:

RANK/DENSE_RANK

Таким образом, вместо метода нумерации строк, использованного выше, мы могли бы написать это:

SELECT [Row ID], [Region], [Customer Name],
RANK() OVER (PARTITION BY [Region] ORDER BY [Region] ASC, [Sales] DESC) AS [Row]
FROM [Orders]

В отличие от функции номера строки, RANK обрабатывает связи, присваивая обеим строкам одинаковый ранг (так же, как функция RANK в Tableau). Существует также функция DENSE_RANK, который работает так же, как функция Tableau RANK_DENSE.

FIRST_VALUE/LAST_VALUE

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

SELECT [Row ID], [Customer Name], [Order Date], [Sales],
FIRST_VALUE([Sales]) OVER (PARTITION BY [Customer Name] ORDER BY [Order Date]) AS [First Sale Amount]
FROM [Orders]

Точно так же мы можем использовать LAST_VALUE для получения последнего значения в разделе.

LEAD/LAG

Теперь о наших любимых оконных функциях, LEAD и LAG. LEAD позволяет получить значение из более поздней строки в наборе данных, а LAG позволяет получить значение из предыдущей строки. Например, следующий SQL-запрос будет получать предыдущие и следующие суммы продаж клиента:

SELECT [Customer Name], [Row ID], [Order Date], [Sales] ,
LAG([Sales]) OVER (PARTITION BY [Customer Name] ORDER BY [Customer Name], [Order Date]) AS [Prior Sales],
LEAD([Sales]) OVER (PARTITION BY [Customer Name] ORDER BY [Customer Name], [Order Date]) AS [Next Sales]
FROM [Orders]

Хотя это и не показано в приведенном выше примере, функции LEAD и LAG имеют два дополнительных параметра — смещение и значение по умолчанию. Смещение позволяет вам указать количество строк назад или вперед, которое вы перемещаете, а значение по умолчанию позволяет вам указать значение, которое будет возвращаться, когда мы находимся за пределами раздела. Например, значения NULL в приведенном выше примере — с использованием имени клиента в качестве раздела — являются результатом того факта, что у клиента нет предыдущей или следующей записи.

Мы могли бы, например, написать следующее вычисление, которое будет перемещаться назад и вперед на 2 строки и возвращать 0 по умолчанию.

SELECT [Customer Name], [Row ID], [Order Date], [Sales] ,
LAG([Sales], 2, 0) OVER (PARTITION BY [Customer Name] ORDER BY [Customer Name], [Order Date]) AS [Sales 2 Back],
LEAD([Sales], 2, 0) OVER (PARTITION BY [Customer Name] ORDER BY [Customer Name], [Order Date]) AS [Sales 2 Fwd]
FROM [Orders]

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

 

Заключение

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