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

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

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

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

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

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

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

#	City	Category	Date	Sales	Profit	Day's discount
1	Detroit	Office Supplies	2014-01-02	10	7	0,05
2	Portland	Office Supplies	2014-04-05	14	10	0,00
3	Portland	Office Supplies	2014-01-21	20	12	0,20
4	San Francisco	Office Supplies	2014-03-11	8	3	0,10
5	Detroit	Furniture	2014-01-01	12	3	0,00
6	Portland	Furniture	2014-01-21	7	2	0,05
7	San Francisco	Technology	2014-01-02	7	3	0,10
8	San Francisco	Technology	2014-01-17	13	5	0,20

Пример 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 по продажам за день.

Подробнее

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

   • Sales Rank — RANK(SUM([Sales]) WITHIN [Date])

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

   

2. Отметим подкатегории, которые попадают в топ-3 по продажам в рамках одной даты. Для этого создадим показатель:

   • Top-3 — IF([Sales Rank] <= 3, 1, 0)

   Для подкатегорий товаров, которые попадают в топ-3 по продажам в рамках даты, показатель [Top-3] будет равен 1, а для всех остальных категорий в рамках той же даты — 0.

   

3. С помощью показателя [Top-3] мы отметили категории в рамках одной даты. Теперь нужно отметить эти подкатегории в остальных датах. Для этого создадим показатель с помощью оконной функции MAX:

   • [Show Category] — MAX([Top-3] WITHIN [Sub-Category])

   Для каждой подкатегории товаров показатель [Show Category] будет равен 1 не только в рамках той даты, когда она была в топ-3 по продажам, но и для всех остальных дат. Если подкатегория ни в один из дней не входила в топ-3 по продажам, показатель [Show Category] для нее будет равен 0.

   

4. Добавим фильтр в чарт: [Show Category] = 1. Таким образом мы получили список подкатегорий товаров, которые нужно отобразить на графике.

5. Теперь поменяем тип чарта на Линейная диаграмма. Настроим визуализацию:

   • в секцию X перетащим измерение Date;

   • в секцию Y перетащим показатель Sales;

   • в секцию Цвета перетащим измерение Sub-Category;

   • в секции Фильтры оставим фильтр по значению 1 показателя Show Category;

   • в настройках оси Y для опции Пустые значения (null) установим значение Отображать как 0.

     

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

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

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

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

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

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

Пример

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

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

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

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

WITHINWITHIN

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

Пример

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

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

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

AMONGAMONG

В этом случае разбиение на окна будет производиться по всем измерениям, которые участвуют в группировке чарта, но не перечислены в 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_SalesFullTable).

Пример 1

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

1. Выберите тип чарта Таблица.

2. Добавьте в чарт поле OrderDate с формулой DATE([OrderDatetime]).

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

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

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

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

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

8. Поместите показатель 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

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

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 в секцию Показатели.

   

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

Как упорядочить значения при расчете нарастающего итога или скользящего среднего?

Для правильной работы функций, зависящих от порядка записей в окне (например, RSUM, MAVG, LAG, LAST, FIRST), обязательно указывать сортировку. Это можно сделать следующими способами:

• перетащить в секцию Сортировка измерение или показатель, по которым будет отсортирован весь чарт;
• задать сортировку для конкретной функции с помощью ORDER BY.

Как правильно посчитать нарастающий итог после добавления поля в секцию Цвета?

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

Чтобы отобразить изменение суммы продаж по каждой категории товаров, нужно добавить измерение Category в секцию Цвета.

После этого на диаграмме отобразится отдельный график для каждой категории, но итоговые суммы на них посчитаны неверно: Furniture — 49 вместо 19, Office Supplies — 91 вместо 52, Technology — 42 вместо 20. Это связано с тем, что измерение в секции Цвета (Category) участвует в группировке так же, как и измерение в секции X (Date). Чтобы сумма посчиталась корректно, нужно добавить измерение Category в секцию WITHIN или измерение Date в секцию AMONG: RSUM(SUM([Sales]) WITHIN [Category]) или RSUM(SUM([Sales]) AMONG [Date]).

Как правильно вычислить оконную функцию, если в чарте указать группировку для даты?

При добавлении группировки (округления) для даты в чарте исходное поле подменяется на другое, автоматически сгенерированное. Например, при округлении до месяца вместо измерения [Date] подставляется новое поле с формулой DATETRUNC([Date], "month"). Так как исходное поле [Date] исчезает из перечня измерений чарта, то и оконная функция, в которой оно используется, перестает работать. Для корректной работы функции необходимо в формуле округлить исходное измерение [Date] с помощью функции DATETRUNC.