Агрегатные функции
Агрегатные функции (агрегации) — это функции, которые вычисляются от группы значений и объединяют их в одно результирующее.
Если в поле с измерением добавить агрегацию, то поле становится показателем.
Агрегатные функции можно использовать с константами, например, 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 ... ] )
Возвращает статистическую дисперсию всех значений в выражении по всей совокупности.