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

Оконные функции в DataLens вычисляются как агрегатные функции от показателей. Группировка по измерениям при этом отличается от группировки, заданной в чарте. Параметры группировки указываются при вызове функции в виде списка измерений, которые включаются в группировку (WITHIN ...) или исключаются из нее (AMONG ...).

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

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

SUM(SUM(a) UNION SUM(b)) = SUM(a UNION b)

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

COUNT(COUNT(a) UNION COUNT(b)) = 2

Пример

Где:

• Sales — показатель суммы продаж в категориях;
• Order Count — показатель количества заказов в категориях;
• Sales_Furniture+Technology — общая сумма продаж во всех категориях:

  SUM_IF([Sales], [Category] = 'Furniture' TOTAL) + SUM_IF([Sales], [Category] = 'Technology' TOTAL)
  

• OrderCount_Furniture+Technology — количество значений показателя заказов:

  COUNT(COUNT_IF([Order Count], [Category] = 'Furniture' TOTAL) +  COUNT_IF([Order Count], [Category] = 'Technology' TOTAL) TOTAL)
  

Ограничения использованияОграничения использования

1. Оконные функции могут иметь в качестве первого аргумента только показатели.

   Для оконных функций AVG_IF, COUNT_IF, SUM_IF первым аргументом (expression в описании функции) всегда должен быть показатель.

   Пример:

   AVG_IF([Profit], [Profit] > 5)

   Для остальных оконных функций первым (или единственным) аргументом (value в описании функции) также всегда должен быть показатель.

   Примеры:

   • Корректная формула: SUM(SUM([Profit]) TOTAL).
   • Некорректная формула: RANK([Profit] TOTAL), где [Profit] — неагрегированное выражение.

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

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

   Примеры:

   • Корректная формула: RANK(SUM([Profit]) WITHIN [Category]) в чарте с группировкой по измерениям [Order Date] и [Category]. Измерение [Category] участвует в построении чарта.
   • Допустимая формула: RANK(SUM([Profit]) WITHIN [Category]) в чарте с группировкой по измерениям [Order Date] и [City]. Измерение [Category] не участвует в группировке, поэтому не будет использовано при вычислении. Результат будет равносилен применению формулы RANK(SUM([Profit]) TOTAL).
   • Некорректная формула: RANK(SUM([Profit]) AMONG [City]) в чарте с группировкой по измерениям [Order Date] и [Category]. Выполнение функции вызовет ошибку Unknown dimension for window.

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

4. Для функций, зависящих от порядка сортировки, перечисленные в ORDER BY поля должны участвовать в построении чарта.

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

СинтаксисСинтаксис

Общий синтаксис оконных функций выглядит так:

<WINDOW_FUNCTION_NAME>(
    arg1, arg2, ...

    [ TOTAL
    | WITHIN dim1, dim2, ...
    | AMONG dim1, dim2, ... ]

    [ ORDER BY field1, field2, ... ]

    [ BEFORE FILTER BY filtered_field1, ... ]
)

Как и у обычных функций, вызов начинается с названия функции и аргументов (в данном случае — arg1, arg2, ...).

ГруппировкаГруппировка

За аргументами следует указание группировки окна, которое может быть одним из трех типов:

• TOTAL (равносильно WITHIN без измерений): все записи запроса попадают в одно единственное окно.
• WITHIN dim1, dim2, ... : записи группируются по измерениям dim1, dim2, ...``dim1, dim2, ...
• AMONG dim1, dim2, ... : записи группируются по всем измерениям из запроса, кроме перечисленных. Например, если использовать формулу RSUM(SUM([Sales]) AMONG dim1, dim2) с измерениями dim1, dim2, dim3, dim4 в запросе данных, то записи будут группироваться по dim3 и dim4, так что эта формула будет эквивалентна RSUM([Sales] WITHIN dim3, dim4).

Группировка опциональна. По умолчанию используется тип группировки TOTAL.

СортировкаСортировка

За группировкой следует сортировка (ORDER BY). Она поддерживается только для функций, зависящих от порядка сортировки:

Сортировка для этих функций опциональна.

Результат функции зависит от полей и направления сортировки. Чтобы узнать, как сортировка влияет на расчеты, перейдите к описанию функции.
В сортировке допустимо использовать как измерения, так и показатели. Также поддерживается стандартный синтаксис ASC/DESC для указания направления сортировки: по нарастанию или убыванию (по умолчанию используется ASC):
... ORDER BY [Date] ASC, SUM([Sales]) DESC, [Category] ...

Перечисленные в ORDER BY поля дополняются списком полей из сортировки чарта.
Пример:

• Функция — ... ORDER BY [Date] DESC, [City].
• Чарт — сортировка по Date и Category.
• Результат сортировки — Date (по убыванию), City, Category.

BEFORE FILTER BYBEFORE FILTER BY

Если какие-либо поля перечислены в BEFORE FILTER BY, то эта оконная функция будет рассчитана до фильтрации данных по этим полям.

BEFORE FILTER BY применяется также и ко всем вложенным оконным функциям.

Пример:

• Формула — MAVG(RSUM([Sales]), 10 BEFORE FILTER BY [Date]).
• Эквивалент — MAVG(RSUM([Sales] BEFORE FILTER BY [Date]), 10 BEFORE FILTER BY [Date]).

Не используйте конфликтующие BEFORE FILTER BY в запросе:

• Корректная формула: MAVG(RSUM([Sales] BEFORE FILTER BY [Date], [Category]), 10 BEFORE FILTER BY [Date]) — функции вложены друг в друга, и ([Date]) является подмножеством ([Date], [Category]).
• Корректная формула: MAVG(RSUM([Sales] BEFORE FILTER BY [Category]), 10 BEFORE FILTER BY [Date]) — функции вложены друг в друга, поэтому списки полей комбинируются во второй из функций.
• Корректная формула: RSUM([Sales] BEFORE FILTER BY [Date], [Category]) - RSUM([Sales] BEFORE FILTER BY [Date]) — ([Date]) является подмножеством ([Date], [Category]).
• Некорректная формула: RSUM([Sales] BEFORE FILTER BY [Category]) - RSUM([Sales] BEFORE FILTER BY [Date]) — функции не вложены, и ни одно из ([Category]) и ([Date]) не является подмножеством другого.

Агрегатные функции как оконныеАгрегатные функции как оконные

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

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

Например, выражение SUM([Sales]) / SUM(SUM([Sales]) TOTAL) может быть использовано для расчета доли суммы [Sales] по группе к итоговой сумме [Sales] среди всех записей.

AVGAVG

Синтаксис:
AVG( value TOTAL | WITHIN ... | AMONG ... )
или
AVG( value TOTAL | WITHIN ... | AMONG ... [ BEFORE FILTER BY ... ] )

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

AVG_IFAVG_IF

Синтаксис:
AVG_IF( expression, condition TOTAL | WITHIN ... | AMONG ... )
или
AVG_IF( expression, condition TOTAL | WITHIN ... | AMONG ... [ BEFORE FILTER BY ... ] )

Возвращает среднее для всех значений, которые удовлетворяют условию condition. Если значения отсутствуют, то возвращается NULL. Работает только с числовыми типами данных.

COUNTCOUNT

Синтаксис:
COUNT( [ value ] TOTAL | WITHIN ... | AMONG ... )
или
COUNT( [ value ] TOTAL | WITHIN ... | AMONG ... [ BEFORE FILTER BY ... ] )

Возвращает количество элементов в заданном окне.

COUNT_IFCOUNT_IF

Синтаксис:
COUNT_IF( expression, condition TOTAL | WITHIN ... | AMONG ... )
или
COUNT_IF( expression, condition TOTAL | WITHIN ... | AMONG ... [ BEFORE FILTER BY ... ] )

Возвращает количество элементов в заданном окне, которые удовлетворяют условию expression.

FIRSTFIRST

Синтаксис:
FIRST( value )
или
FIRST( value [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Возвращает значение value из первой строки заданного окна. См. также LAST.

LAGLAG

Синтаксис:
LAG( value [ , offset [ , default ] ] )
или
LAG( value [ , offset [ , default ] ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Возвращает значение value из строки со смещением offset относительно текущей в рамках заданного окна:

• положительное значение offset делает смещение назад;
• отрицательное значение offset делает смещение вперед.

По умолчанию offset равно 1.

Если значение отсутствует (offset ссылается на строку до первой или после последней), то возвращается значение default в качестве результата. Если default не задано, то используется NULL.

См. также AGO в качестве неоконной альтернативы.

LASTLAST

Синтаксис:
LAST( value )
или
LAST( value [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Возвращает значение value из последней строки заданного окна. См. также FIRST.

MAVGMAVG

Синтаксис:
MAVG( value, rows_1 [ , rows_2 ] )
или
MAVG( value, rows_1 [ , rows_2 ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Возвращает скользящее среднее значений по окну записей. Значение определяется порядком сортировки и аргументами:

Аналогичное поведение у оконных функций MSUM, MCOUNT, MMIN, MMAX.

См. также AVG, RAVG.

MAXMAX

Синтаксис:
MAX( value TOTAL | WITHIN ... | AMONG ... )
или
MAX( value TOTAL | WITHIN ... | AMONG ... [ BEFORE FILTER BY ... ] )

Возвращает максимальное значение.

Если value:

• число — возвращает наибольшее число;
• дата — возвращает самую позднюю дату;
• строка — возвращает последнее значение в алфавитном порядке.

MCOUNTMCOUNT

Синтаксис:
MCOUNT( value, rows_1 [ , rows_2 ] )
или
MCOUNT( value, rows_1 [ , rows_2 ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Возвращает количество значений (не равных NULL) по окну записей, которое определяется порядком сортировки и аргументами:

Аналогичное поведение у оконных функций MSUM, MMIN, MMAX, MAVG.

См. также COUNT, RCOUNT.

MINMIN

Синтаксис:
MIN( value TOTAL | WITHIN ... | AMONG ... )
или
MIN( value TOTAL | WITHIN ... | AMONG ... [ BEFORE FILTER BY ... ] )

Возвращает минимальное значение.

Если value:

• число — возвращает наименьшее число;
• дата — возвращает самую раннюю дату;
• строка — возвращает первое значение в алфавитном порядке.

MMAXMMAX

Синтаксис:
MMAX( value, rows_1 [ , rows_2 ] )
или
MMAX( value, rows_1 [ , rows_2 ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Возвращает скользящий максимум значений по окну записей. Значение определяется порядком сортировки и аргументами:

Аналогичное поведение у оконных функций MSUM, MCOUNT, MMIN, MAVG.

См. также MAX, RMAX.

MMINMMIN

Синтаксис:
MMIN( value, rows_1 [ , rows_2 ] )
или
MMIN( value, rows_1 [ , rows_2 ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Возвращает скользящий минимум значений по окну записей, определяемому порядком сортировки и аргументами:

Аналогичное поведение у оконных функций MSUM, MCOUNT, MMAX, MAVG.

См. также MIN, RMIN.

MSUMMSUM

Синтаксис:
MSUM( value, rows_1 [ , rows_2 ] )
или
MSUM( value, rows_1 [ , rows_2 ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Возвращает скользящую сумму значений по окну записей, которое определяется порядком сортировки и аргументами:

Аналогичное поведение у оконных функций MCOUNT, MMIN, MMAX, MAVG.

См. также SUM, RSUM.

RANKRANK

Синтаксис:
RANK( value [ , direction ] )
или
RANK( value [ , direction ] [ TOTAL | WITHIN ... | AMONG ... ] [ BEFORE FILTER BY ... ] )

Выполняет ранжирование значений с пропусками: возвращает порядковый номер строки при сортировке по value. Строки, которые соответствуют одному и тому же значению value, имеют одно и то же значение ранга. Если первые две строки получают ранг 1, то ранг следующей строки (если значение value не совпадает) будет равен 3. Значение 2 в этом случае пропускается.

Если direction равно "desc" или не указано, то ранжирование происходит от большего к меньшему, если "asc", то от меньшего к большему. По умолчанию используется "desc".

См. также RANK_DENSE, RANK_UNIQUE, RANK_PERCENTILE.

RANK_DENSERANK_DENSE

Синтаксис:
RANK_DENSE( value [ , direction ] )
или
RANK_DENSE( value [ , direction ] [ TOTAL | WITHIN ... | AMONG ... ] [ BEFORE FILTER BY ... ] )

Выполняет ранжирование значений без пропусков: возвращает порядковый номер строки при сортировке по value. Строки, которые соответствуют одному и тому же значению value, имеют одно и то же значение ранга. Если первые две строки получают ранг 1, то ранг следующей строки (если значение value не совпадает) будет равен 2. Значения ранга не пропускаются.

Если direction равно "desc" или не указано, то ранжирование происходит от большего к меньшему, если "asc", то от меньшего к большему. По умолчанию используется "desc".

См. также RANK, RANK_DENSE, RANK_PERCENTILE.

RANK_PERCENTILERANK_PERCENTILE

Синтаксис:
RANK_PERCENTILE( value [ , direction ] )
или
RANK_PERCENTILE( value [ , direction ] [ TOTAL | WITHIN ... | AMONG ... ] [ BEFORE FILTER BY ... ] )

Выполняет относительное ранжирование. Возвращает дробный ранг (от 0 до 1). Расчитывается как (RANK(...) - 1) / (количество строк).

Если direction равно "desc" или не указано, то ранжирование происходит от большего к меньшему, если "asc", то от меньшего к большему. По умолчанию используется "desc".

См. также RANK, RANK_DENSE, RANK_UNIQUE.

RANK_UNIQUERANK_UNIQUE

Синтаксис:
RANK_UNIQUE( value [ , direction ] )
или
RANK_UNIQUE( value [ , direction ] [ TOTAL | WITHIN ... | AMONG ... ] [ BEFORE FILTER BY ... ] )

Выполняет уникальное ранжирование. Возвращает порядковый номер строки при сортировке по value. Строки, которые соответствуют одному и тому же значению value, имеют разные значения ранга. Ни для каких двух строк значения не совпадают. Принимает все значения от 1 до значения, которое равно количеству строк.

Если direction равно "desc" или не указано, то ранжирование происходит от большего к меньшему, если "asc", то от меньшего к большему. По умолчанию используется "desc".

См. также RANK, RANK_DENSE, RANK_PERCENTILE.

RAVGRAVG

Синтаксис:
RAVG( value [ , direction ] )
или
RAVG( value [ , direction ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Возвращает среднее арифметическое значений в рамках окна записей, определяемого аргументом direction:

По умолчанию используется значение "asc".

Аналогичное поведение у оконных функций RSUM, RCOUNT, RMIN, RMAX.

См. также AVG, MAVG.

RCOUNTRCOUNT

Синтаксис:
RCOUNT( value [ , direction ] )
или
RCOUNT( value [ , direction ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Возвращает количество значений в рамках окна записей, определяемого порядком сортировки и значением аргумента direction:

По умолчанию используется значение "asc".

Аналогичное поведение у оконных функций RSUM, RMIN, RMAX, RAVG.

См. также COUNT, MCOUNT.

RMAXRMAX

Синтаксис:
RMAX( value [ , direction ] )
или
RMAX( value [ , direction ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Возвращает максимальное из значений в рамках окна записей, определяемого порядком сортировки и значением аргумента direction:

По умолчанию используется значение "asc".

Аналогичное поведение у оконных функций RSUM, RCOUNT, RMIN, RAVG.

См. также MAX, MMAX.

RMINRMIN

Синтаксис:
RMIN( value [ , direction ] )
или
RMIN( value [ , direction ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Возвращает минимальное из значений в рамках окна записей, определяемого порядком сортировки и значением аргумента direction:

По умолчанию используется значение "asc".

Аналогичное поведение у оконных функций RSUM, RCOUNT, RMAX, RAVG.

См. также MIN, MMIN.

RSUMRSUM

Синтаксис:
RSUM( value [ , direction ] )
или
RSUM( value [ , direction ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )

Возвращает сумму значений в рамках окна записей, определяемого порядком сортировки и значением аргумента direction:

По умолчанию используется значение "asc".

Аналогичное поведение у оконных функций RCOUNT, RMIN, RMAX, RAVG.

См. также SUM, MSUM.

SUMSUM

Синтаксис:
SUM( value TOTAL | WITHIN ... | AMONG ... )
или
SUM( value TOTAL | WITHIN ... | AMONG ... [ BEFORE FILTER BY ... ] )

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

SUM_IFSUM_IF

Синтаксис:
SUM_IF( expression, condition TOTAL | WITHIN ... | AMONG ... )
или
SUM_IF( expression, condition TOTAL | WITHIN ... | AMONG ... [ BEFORE FILTER BY ... ] )

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