Оконные функции в 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.
Оконные функции могут быть вложены друг в друга. При этом можно указать свою группировку для каждой функции, использующейся в формуле.
Пример
Необходимо упорядочить строки в таблице Selling в зависимости от величины средней суммы продаж за все даты в городе. Среднюю сумму продаж по городу можно вычислить с помощью функции AVG — AVG(SUM([Sales]) WITHIN [City]). Названия городов в таблице повторяются, поэтому для ранжирования лучше использовать функцию RANK_DENSE — она не пропускает порядковые номера для строк с одинаковым значением. В результате получается следующая формула — RANK_DENSE(AVG(SUM([Sales]) WITHIN [City]) TOTAL).
Группировка в оконных функциях
Так же, как и агрегатные функции, оконные функции могут быть вычислены:
- для одного окна;
- для нескольких окон.
Побробнее о группировке в оконных функциях читайте в разделе Группировка.
Группировка для одного окна
При таком варианте группировки функция вычисляется для единственного окна, в которое попадают все строки. Для этого используется тип группировки TOTAL. Этот тип группировки подходит для подсчета итоговых сумм, ранжирования строк и прочих операций, для которых нужна информация о всех исходных данных.
Пример
Необходимо вычислить среднюю сумму продаж (AvgSales) и отклонения от нее для каждой категории в городе (DeltaFromAvg). Для этого подойдет функция AVG:
- AvgSales —
AVG(SUM([Sales]) TOTAL) - DeltaFromAvg —
SUM([Sales]) - [AvgSales]
Группировка для нескольких окон
Иногда оконную функцию нужно вычислить не среди всех записей, а раздельно по группам. В этих случаях используются типы группировки WITHIN и AMONG.
WITHIN
WITHIN — это аналог GROUP BY в SQL. В нем перечисляются все измерения, по которым будет производиться разбиение на окна. Также в WITHIN можно использовать показатели. В этом случае их значения таким же образом будут участвовать в группировке окна.
Важно
В WITHIN игнорируются измерения, которые не участвуют в группировке чарта. Например, в чарте с группировкой по измерениям City и Category для показателя SUM(SUM([Sales]) WITHIN [Date]) измерение Date будет проигнорировано, и он станет равносилен показателю SUM(SUM([Sales]) TOTAL).
Пример
Вычисление доли каждой категории (% 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)
Такой вариант записи существует исключительно для удобства и применяется в тех случаях, когда заранее неизвестно, в каких измерениях будет строиться чарт, но важно исключить из оконной группировки определенные измерения.
Важно
Измерения, перечисленные в AMONG, должны быть добавлены в секции чарта. В другом случае чарт вернет ошибку.
Сортировка
Некоторые оконные функции поддерживают сортировку, направление которой влияет на расчет значения. Указать сортировку для оконной функции можно следующими способами:
- указать измерения или показатели в секции
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]).
Вопросы и ответы
Для правильной работы функций, зависящих от порядка записей в окне (например, 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.