Связаться с намиПодключиться

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

Статья создана
Улучшена
Обновлена 18 июня 2024 г.

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

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

Агрегатные функции можно использовать с константами, например, COUNT(1) или SUM(1). Если в чарте не используются другие показатели и измерения, результатом такого выражения всегда будет значение 1. Это происходит из-за того, что в функции не указано ни одного поля, поэтому DataLens в запросе не обращается ни к одной таблице источника.

Синтаксис

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

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, ...).

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

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

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

MAX(SUM([Sales] FIXED [City]))

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

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

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

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

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

Примеры 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 [City]).

Не используйте конфликтующие 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 ] )
или
ALL_CONCAT( expression [ , separator ] [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

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

ANY

Синтаксис:
ANY( value )
или
ANY( value [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

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

ARG_MAX

Синтаксис:
ARG_MAX( value, expression_to_maximize )
или
ARG_MAX( value, expression_to_maximize [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

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

ARG_MIN

Синтаксис:
ARG_MIN( value, expression_to_minimize )
или
ARG_MIN( value, expression_to_minimize [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

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

AVG

Синтаксис:
AVG( value )
или
AVG( value [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

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

AVG_IF

Синтаксис:
AVG_IF( expression, condition )
или
AVG_IF( expression, condition [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

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

COUNT

Синтаксис:
COUNT( [ value ] )
или
COUNT( [ value ] [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

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

Функцию можно использовать с константами, например, COUNT(1) или COUNT(). Если в чарте не используются другие показатели и измерения, результатом такого выражения всегда будет значение 1. Это происходит из-за того, что в функции не указано ни одного поля, поэтому DataLens в запросе не обращается ни к одной таблице источника.

COUNT_IF

Синтаксис:
COUNT_IF( condition )
или
COUNT_IF( condition [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

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

COUNTD

Синтаксис:
COUNTD( value )
или
COUNTD( value [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Возвращает количество уникальных значений в группе.

См. также COUNTD_APPROX.

COUNTD_APPROX

Синтаксис:
COUNTD_APPROX( value )
или
COUNTD_APPROX( value [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Возвращает приблизительное количество уникальных значений в группе. Работает быстрее функции COUNTD, но не гарантирует точность.

COUNTD_IF

Синтаксис:
COUNTD_IF( expression, condition )
или
COUNTD_IF( expression, condition [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

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

См. также COUNTD_APPROX.

MAX

Синтаксис:
MAX( value )
или
MAX( value [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

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

Если value:

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

MEDIAN

Синтаксис:
MEDIAN( value )
или
MEDIAN( value [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Возвращает медианное значение. При четном количестве элементов возвращает наибольший из соседних элементов в центральной позиции.

MIN

Синтаксис:
MIN( value )
или
MIN( value [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

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

Если value:

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

QUANTILE

Синтаксис:
QUANTILE( value, quant )
или
QUANTILE( value, quant [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Возвращает точный квантиль уровня quant (значение от 0 до 1).

QUANTILE_APPROX

Синтаксис:
QUANTILE_APPROX( value, quant )
или
QUANTILE_APPROX( value, quant [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Возвращает примерный квантиль уровня quant (значение от 0 до 1).

STDEV

Синтаксис:
STDEV( value )
или
STDEV( value [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

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

STDEVP

Синтаксис:
STDEVP( value )
или
STDEVP( value [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

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

SUM

Синтаксис:
SUM( value )
или
SUM( value [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

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

SUM_IF

Синтаксис:
SUM_IF( expression, condition )
или
SUM_IF( expression, condition [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

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

TOP_CONCAT

Синтаксис:
TOP_CONCAT( expression, amount [ , separator ] )
или
TOP_CONCAT( expression, amount [ , separator ] [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

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

VAR

Синтаксис:
VAR( value )
или
VAR( value [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Возвращает статистическую дисперсию всех значений в выражении на основе выборки из совокупности.

VARP

Синтаксис:
VARP( value )
или
VARP( value [ FIXED ... | INCLUDE ... | EXCLUDE ... ] [ BEFORE FILTER BY ... ] )

Возвращает статистическую дисперсию всех значений в выражении по всей совокупности.

Предыдущая
Все функции
Следующая
ALL_CONCAT