Оконные функции в DataLens

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

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

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

Применение оконных функций

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

В качестве исходных данных рассмотрим таблицу Selling, содержащую данные о продажах в городах:

Пример 1

В чарте на основе таблицы Selling с группировкой по измерениям City и Category необходимо посчитать общую сумму продаж (TotalSales), а также долю каждой категории в городе от общей суммы (% Total). Для этого нужно создать два показателя с помощью оконной функции SUM:

• TotalSales — SUM(SUM([Sales]) TOTAL)
• % Total — SUM([Sales]) / [TotalSales]

На примере чарта Таблица результат будет выглядеть так:

Пример 2

Необходимо упорядочить строки в таблице Selling в зависимости от величины суммы продаж. Для этого можно использовать оконную функцию RANK — RANK(SUM([Sales])). В результате каждой строке будет присвоен свой порядковый номер: строке с наибольшей суммой продаж — номер 1, строке с наименьшей суммой — номер 6.

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

Пример 3

Необходимо упорядочить строки в таблице Selling в зависимости от величины средней суммы продаж за все даты в городе. Среднюю сумму продаж по городу можно вычислить с помощью функции AVG — AVG(SUM([Sales]) WITHIN [City]). Названия городов в таблице повторяются, поэтому для ранжирования лучше использовать функцию RANK_DENSE — она не пропускает порядковые номера для строк с одинаковым значением. В результате получается следующая формула — RANK_DENSE(AVG(SUM([Sales]) WITHIN [City]) TOTAL).

Пример 4

Рассмотрим более сложный пример использования оконных функций. В качестве источника построим датасет на основе подключения к демонстрационной БД (таблица SampleLite). Построим график статистики продаж по подкатегориям товаров. На графике отобразим только те подкатегории, которые хотя бы один раз входили в топ-3 по продажам за день.

Группировка в оконных функциях

Так же, как и агрегатные функции, оконные функции могут быть вычислены:

• для одного окна;
• для нескольких окон.

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

Группировка для одного окна

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

Пример

Необходимо вычислить среднюю сумму продаж (AvgSales) и отклонения от нее для каждой категории в городе (DeltaFromAvg). Для этого подойдет функция AVG:

• AvgSales — AVG(SUM([Sales]) TOTAL)
• DeltaFromAvg — SUM([Sales]) - [AvgSales]

Группировка для нескольких окон

Иногда оконную функцию нужно вычислить не среди всех записей, а раздельно по группам. В этих случаях используются типы группировки WITHIN и AMONG.

WITHIN

WITHIN — это аналог GROUP BY в SQL. В нем перечисляются все измерения, по которым будет производиться разбиение на окна. Также в WITHIN можно использовать показатели. В этом случае их значения таким же образом будут участвовать в группировке окна.

Пример

Вычисление доли каждой категории (% Total) от общей суммы продаж по городу (TotalSales):

• TotalSales — SUM(SUM([Sales]) WITHIN [City])
• % Total — SUM([Sales]) / [TotalSales]

Результат на примере чарта Столбчатая диаграмма:

AMONG

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

Так, например, для чарта с группировкой по измерениям City и Category равносильны показатели:

• SUM(SUM([Sales]) AMONG [Category]) и SUM(SUM([Sales]) WITHIN [City])
• SUM(SUM([Sales]) AMONG [City], [Category])и SUM(SUM([Sales]) TOTAL)

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

Сортировка

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

• указать измерения или показатели в секции ORDER BY;
• в чарте перенести измерения или показатели в секцию Сортировка.

Измерения и показатели для сортировки берутся сначала из секции ORDER BY в формуле, а затем из секции чарта Сортировка.

Пример

Необходимо рассчитать изменение общей суммы продаж (IncTotal) за весь период, начиная от самой ранней даты и заканчивая самой поздней. Для этого можно воспользоваться функцией RSUM с сортировкой по измерению Date — RSUM(SUM([Sales]) TOTAL ORDER BY [Date]).

Результат на примере чарта Линейная диаграмма:

Аналогичный результат можно получить, если задать показатель IncTotal формулой RSUM(SUM([Sales]) TOTAL) и добавить измерение Date в секцию Сортировка.

Фильтрация

Вычисление значений функций в чартах выполняется после применения фильтров по измерениям и показателям, добавленным в секцию Фильтры. У оконных функций можно переопределить этот порядок. Для это нужно указать необходимые измерения или показатели в секции BEFORE FILTER BY формулы. В таком случае значение функции будет вычислено до применения фильтрации.

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

Пример

Необходимо рассчитать изменение общей суммы продаж (IncTotal) за период с 17.01.2014 по 11.03.2014. Если добавить в чарт фильтр по измерению Date и создать показатель RSUM(SUM([Sales]) TOTAL ORDER BY [Date]), то функция будет рассчитана только для тех данных, которые ограничены фильтром:

Чтобы вычислить функцию для всего объема данных, но отобразить результат только в определенном периоде, необходимо добавить измерение Date в секцию BEFORE FILTER BY — RSUM(SUM([Sales]) TOTAL ORDER BY [Date] BEFORE FILTER BY [Date]).

Создание показателя для оконной функции

В качестве первого аргумента (value в описании синтаксиса) оконной функции нельзя напрямую использовать измерение. Сначала нужно применить к нему функцию агрегации, в результате чего измерение станет показателем, который можно использовать в оконной функции.

Например, в чарте с группировкой по измерениям Year и Category вы хотите ранжировать записи продаж по прибыли за весь период. Нельзя для этого использовать формулу RANK([Profit]), где Profit — измерение. Сначала надо применить функцию агрегации, чтобы перевести измерение Profit в показатель. Наиболее подходящей в данном случае будет агрегатная функция SUM, которая вернет сумму прибыли: SUM([Profit]). Теперь к полученному показателю можно применить оконную функцию ранжирования RANK. Итоговая корректная формула: RANK(SUM([Profit])).

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

Чтобы понять, какую именно агрегатную функцию выбрать для перевода измерения в показатель, следует уточнить, какой итоговый показатель вы хотите получить с помощью оконной функции. Например, в чарте с группировкой по категориям товаров (измерение Category) требуется упорядочить записи по продажам (измерение Sales). Если вы хотите упорядочить записи по сумме продаж, то следует выбрать агрегатную функцию SUM: SUM([Sales]), если по количеству продаж — COUNT: COUNT([Sales]).

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

Рассмотрим создание показателя для оконной функции на примерах. В качестве источника построим датасет на основе подключения к демонстрационной БД (таблица MS_SalesMiniTable).

Пример 1

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

1. Выберите тип чарта Таблица. В секцию Столбцы поместите измерения OrderDate и ProductCategory.

2. Чтобы упорядочить записи по дате продажи, поместите измерение OrderDate в секцию Сортировка.

3. Для подсчета количества продаж за день для каждой категории товаров добавьте в чарт показатель cnt_order_date_category. Используйте агрегатную функцию COUNT. Она будет использовать группировку по измерениям, размещенным в секции Столбцы. Итоговая формула: COUNT([OrderID]).

4. Поместите показатель cnt_order_date_category в секцию Столбцы.

5. Для подсчета общего количества продаж за день добавьте в чарт показатель cnt_order_date. Используйте оконную функцию SUM, применив группировку по измерению OrderDate. Чтобы перевести измерение OrderID в показатель, используйте агрегатную функцию COUNT: COUNT([OrderID]). Итоговая формула: SUM(COUNT([OrderID]) WITHIN [OrderDate]).

6. Поместите показатель cnt_order_date_category в секцию Столбцы.

   

Пример 2

Необходимо показать среднюю стоимость продаж по магазину и по категориям товаров в магазине:

1. Выберите тип чарта Таблица. В секцию Столбцы поместите измерения ShopName и ProductCategory.

2. Для подсчета средней стоимости продаж по категориям товаров в магазине добавьте в чарт показатель avg_category_sale. Используйте агрегатную функцию AVG. Она будет использовать группировку по измерениям, размещенным в секции Столбцы. Итоговая формула: AVG([Sales]).

3. Поместите показатель avg_category_sale в секцию Столбцы.

4. Для подсчета средней стоимости продаж по магазину добавьте в чарт показатель avg_shop_sale. Используйте оконную функцию AVG, применив группировку по измерению ShopName. Чтобы перевести измерение Sales в показатель, используйте агрегатную функцию AVG: AVG([Sales]). Итоговая формула: AVG(AVG([Sales]) WITHIN [ShopName]).

5. Поместите показатель avg_shop_sale в секцию Столбцы.

   

Пример 3

Для данного примера в качестве источника построим датасет на основе подключения к демонстрационной БД (таблица samples.MS_SalesFullTable).

Необходимо вывести в сводную таблицу идентификатор последней за день продажи по магазинам:

1. Чтобы иметь возможность группировки данных по дате продажи (без учета времени), добавьте в чарт поле Date с формулой DATE_PARSE(STR([OrderDatetime])).

2. Чтобы иметь возможность сортировки данных по времени продажи, добавьте в чарт поле Time с формулой RIGHT(STR([OrderDatetime]),8).

3. Выберите тип чарта Сводная таблица. В секцию Строки поместите измерения ShopName, OrderDatetime и OrderID.

4. Добавьте в чарт показатель last_shop_order. Используйте оконную функцию LAST, применив группировку по измерению ShopName и сортировку по Time. Для перевода строкового измерения в показатель используйте агрегатную функцию ANY с группировкой INCLUDE (для выдачи уникальных значений): ANY([OrderID] INCLUDE [OrderID]). Итоговая формула: LAST(ANY([OrderID] INCLUDE [OrderID]) WITHIN [ShopName], [Date] ORDER BY [Time]).

5. Поместите показатель last_shop_order в секцию Показатели.

   

Вопросы и ответы