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

Статья создана

Yandex.Cloud

улучшена

amatol

Синтаксис
- Уровень детализации (LOD)
- BEFORE FILTER BY
Ограничения использования
ALL_CONCAT
ANY
ARG_MAX
ARG_MIN
AVG
AVG_IF
COUNT
COUNT_IF
COUNTD
COUNTD_APPROX
COUNTD_IF
MAX
MEDIAN
MIN
QUANTILE
QUANTILE_APPROX
STDEV
STDEVP
SUM
SUM_IF
TOP_CONCAT
VAR
VARP

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

Если в поле с измерением добавить агрегацию, то поле становится показателем.

Синтаксис

В большинстве случаев у агрегатных функций такой же синтаксис, как и у обычных функций:

AGGREGATE_FUNCTION_NAME(arg1, [arg2, ...])

Для использования нестандартного уровня детализации (LOD) понадобится расширенный вариант:

<AGGREGATE_FUNCTION_NAME>(
    arg1, [arg2, ...]

    [ FIXED dim1, dim2, ...
    | INCLUDE dim1, dim2, ...
    | EXCLUDE dim1, dim2, ... ]

    [ BEFORE FILTER BY filtered_field1, ... ]
)

Уровень детализации (LOD)

Управление уровнем детализации (англ. level of detail - LOD) позволяет создавать вложенные агрегации и агрегации над всеми данными или группами, отличающимися от группировки, заданной на уровне чарта.

Уровень детализации может быть задан с помощью одного из трех ключевых слов:

FIXED — данные группируются по перечисленным измерениям (dim1, dim2, ...) вне зависимости от того, какие измерения используются в чарте;
INCLUDE — перечисленные измерения (dim1, dim2, ...) добавляются к измерениям чарта;
EXCLUDE — используются все измерения чарта, кроме перечисленных (dim1, dim2, ...).

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

Наследование измерений

Вложенные агрегации наследуют измерения от тех агрегаций, внутри которых находятся. Выражение

AVG(MAX(SUM([Sales] INCLUDE [City]) INCLUDE [Category]))

в чарте с дополнительным измерением [Date] эквивалентно

AVG(MAX(SUM([Sales] FIXED [City], [Category], [Date]) FIXED [Category], [Date]) FIXED [Date])

INCLUDE или EXCLUDE без списка измерений равносильно измерениям внешней агрегации или измерениям чарта, если над текущей агрегацией нет других. FIXED без списка означает, что все данные будут агрегированы в одной группе, например, для расчетов итоговых значений.

Примеры LOD

средняя дневная сумма [Sales]: AVG(SUM([Sales] INCLUDE [Date]));
отношение (дневной) суммы [Sales] к общей сумме: SUM([Sales]) / SUM([Sales] FIXED);
сумма [Sales] всех заказов, которые меньше среднего: SUM_IF(SUM([Sales] INCLUDE [Order ID]), SUM([Sales] INCLUDE [Order ID]) < AVG([Sales] FIXED)).

Совместимость измерений

Если несколько агрегаций с разными наборами измерений (LOD) находятся внутри другой агрегации, то их наборы измерений должны быть совместимы. То есть один из этих наборов должен содержать все остальные.

Некорректное выражение:

SUM(AVG([Sales] INCLUDE [City]) - AVG([Sales] INCLUDE [Category]))

У одной из вложенных агрегаций измерение [City], а у другой — [Category]. При этом нет другой агрегации, которая содержала бы оба эти измерения.

Корректное выражение:

SUM(
    AVG([Sales] INCLUDE [City], [Category])
    - (AVG([Sales] INCLUDE [City]) + AVG([Sales] INCLUDE [Category])) / 2
)

У одной из вложенных агрегаций множество измерений содержит остальные.

Другое важное ограничение состоит в том, что верхнеуровневые агрегации не должны содержать измерений, которых нет в чарте.

BEFORE FILTER BY

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

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

функция — AVG(SUM([Sales] INCLUDE [Date] BEFORE FILTER BY [City]));
эквивалент — AVG(SUM([Sales] INCLUDE [Date] BEFORE FILTER BY [City]) BEFORE FILTER BY [Category]).

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

корректная формула: AVG(SUM([Sales] INCLUDE [Date] BEFORE FILTER BY [City], [Category]) BEFORE FILTER BY [City]) — функции вложены друг в друга, и ([City]) является подмножеством ([City], [Category]);
корректная формула: AVG(SUM([Sales] INCLUDE [Date] BEFORE FILTER BY [Category]) BEFORE FILTER BY [City]) — функции вложены друг в друга, поэтому списки полей комбинируются во второй из функций;
корректная формула: SUM([Sales] BEFORE FILTER BY [City], [Category]) - SUM([Sales] BEFORE FILTER BY [City]) — ([City]) является подмножеством ([City], [Category]);
некорректная формула: SUM([Sales] BEFORE FILTER BY [Category]) - SUM([Sales] BEFORE FILTER BY [City]) — функции не вложены, и ни одно из ([Category]) и ([City]) не является подмножеством другого.

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

Существуют следующие особенности использования агрегаций:

Функция или оператор не может иметь среди своих аргументов одновременно агрегированные и неагрегированные выражения. Выражение CONCAT([Profit], SUM([Profit])) не поддерживается.

ALL_CONCAT

Синтаксис:ALL_CONCAT( expression [ , separator ] )

Возвращает строку, которая содержит все попавшие в группу значения expression, с разделителем separator (по умолчанию разделитель — запятая).

ANY

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

Возвращает произвольное значение value из группы. Это недетерминированная агрегация — результат может различаться от запроса к запросу на одних и тех же входных данных.

ARG_MAX

Синтаксис:ARG_MAX( value, comp )

Возвращает значение value, соответствующее максимальному значению comp. Если есть несколько значений value, соответствующих максимальному значению comp, то возвращет первое попавшееся из них. Это делает функцию недетерминированной.

ARG_MIN

Синтаксис:ARG_MIN( value, comp )

Возвращает значение value, соответствующее минимальному значению comp. Если есть несколько значений value, соответствующих минимальному значению comp, то возвращет первое попавшееся из них. Это делает функцию недетерминированной.

AVG

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

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

AVG_IF

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

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