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

Оконные функции вычисляются аналогично агрегатным, но не объединяют несколько записей в одну, сохраняя их независимость. В некоторых ситуациях это приводит к дублированию значений среди записей в одной группе (например, SUM(... TOTAL)).

Агрегатные функции вычисляются от групп значений, которые формируются с помощью измерений в запросе данных: записи с совпадающими значениями всех измерений попадают в одну группу. Оконные функции также вычисляются от групп значений, которые называются окнами. Для них параметры группировки указываются в вызове функции в виде списка измерений на включение в группировку (WITHIN ...) или же на исключение из нее (AMONG ...).

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

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

   Примеры:

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

2. Вызовы оконных функций не могут быть вложены один в другой.

   Пример:

   • некорректная формула: RSUM(RANK(SUM([Profit]) WITHIN [Order Date]) TOTAL).

3. Ключевое слово AMONG не может быть использовано с измерениями, которые не входят в запрос данных.

   Пример:

   • некорректная формула: RANK(SUM([Profit]) AMONG [City]) с измерениями [Order Date] и [Category].

Синтаксис

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

<WINDOW_FUNCTION_NAME>(
    arg1, arg2, ...
    [ TOTAL
    | WITHIN dim1, dim2, ...
    | AMONG dim1, dim2, ... ]
)

Значения 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.

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

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

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

Пример:

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

AVG

Синтаксис:AVG( value )

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

AVG_IF

Синтаксис:AVG_IF( expression, condition )

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

COUNT

Синтаксис:COUNT( [ value ] )

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

COUNT_IF

Синтаксис:COUNT_IF( condition )

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

MAVG

Синтаксис:MAVG( value, rows_1 [ , rows_2 ] [ TOTAL | WITHIN [ dim1, ... ] | AMONG [ dim1, ... ] ] )

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

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

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

MAX

Синтаксис:MAX( value )

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

Если value:

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

MCOUNT

Синтаксис:MCOUNT( value, rows_1 [ , rows_2 ] [ TOTAL | WITHIN [ dim1, ... ] | AMONG [ dim1, ... ] ] )

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

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

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

MIN

Синтаксис:MIN( value )

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

Если value:

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

MMAX

Синтаксис:MMAX( value, rows_1 [ , rows_2 ] [ TOTAL | WITHIN [ dim1, ... ] | AMONG [ dim1, ... ] ] )

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

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

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

MMIN

Синтаксис:MMIN( value, rows_1 [ , rows_2 ] [ TOTAL | WITHIN [ dim1, ... ] | AMONG [ dim1, ... ] ] )

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

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

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

MSUM

Синтаксис:MSUM( value, rows_1 [ , rows_2 ] [ TOTAL | WITHIN [ dim1, ... ] | AMONG [ dim1, ... ] ] )

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

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

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

RANK

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

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

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

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

RANK_DENSE

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

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

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

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

RANK_PERCENTILE

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

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

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

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

RANK_UNIQUE

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

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

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

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

RAVG

Синтаксис:RAVG( value [ , direction ] [ TOTAL | WITHIN [ dim1, ... ] | AMONG [ dim1, ... ] ] )

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

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

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

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

RCOUNT

Синтаксис:RCOUNT( value [ , direction ] [ TOTAL | WITHIN [ dim1, ... ] | AMONG [ dim1, ... ] ] )

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

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

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

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

RMAX

Синтаксис:RMAX( value [ , direction ] [ TOTAL | WITHIN [ dim1, ... ] | AMONG [ dim1, ... ] ] )

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

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

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

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

RMIN

Синтаксис:RMIN( value [ , direction ] [ TOTAL | WITHIN [ dim1, ... ] | AMONG [ dim1, ... ] ] )

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

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

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

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

RSUM

Синтаксис:RSUM( value [ , direction ] [ TOTAL | WITHIN [ dim1, ... ] | AMONG [ dim1, ... ] ] )

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

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

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

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

SUM

Синтаксис:SUM( value )

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

SUM_IF

Синтаксис:SUM_IF( expression, condition )

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