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

COUNT

Сигнатура

COUNT(*)->Uint64
COUNT(T)->Uint64
COUNT(T?)->Uint64

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

Как и другие агрегатные функции, может использоваться в сочетании с GROUP BY для получения статистики по частям таблицы, соответствующим значениям в столбцах, по которым идет группировка. А модификатор DISTINCT позволяет посчитать число уникальных значений.

Примеры

SELECT COUNT(*) FROM my_table;

SELECT key, COUNT(value) FROM my_table GROUP BY key;

SELECT COUNT(DISTINCT value) FROM my_table;

MIN и MAX

Сигнатура

MIN(T?)->T?
MIN(T)->T?
MAX(T?)->T?
MAX(T)->T?

Минимальное или максимальное значение.

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

Примеры

SELECT MIN(value), MAX(value) FROM my_table;

SUM

Сигнатура

SUM(Unsigned?)->Uint64?
SUM(Signed?)->Int64?
SUM(Interval?)->Interval?
SUM(Decimal(N, M)?)->Decimal(35, M)?

Сумма чисел.

В качестве аргумента допустимо произвольное вычислимое выражение с числовым результатом или типом Interval.

Целые числа автоматически расширяются до 64 бит, чтобы уменьшить риск переполнения.

SELECT SUM(value) FROM my_table;

AVG

Сигнатура

AVG(Double?)->Double?
AVG(Interval?)->Interval?
AVG(Decimal(N, M)?)->Decimal(N, M)?

Арифметическое среднее.

В качестве аргумента допустимо произвольное вычислимое выражение с числовым результатом или типом Interval.

Целочисленные значения и интервалы времени автоматически приводятся к Double.

Примеры

SELECT AVG(value) FROM my_table;

COUNT_IF

Сигнатура

COUNT_IF(Bool?)->Uint64?

Количество строк, для которых указанное в качестве аргумента выражение истинно (результат вычисления выражения — true).

Значение NULL приравнивается к false (в случае, если тип аргумента Bool?).

Функция не выполняет неявного приведения типов к булевым для строк и чисел.

Примеры

SELECT
  COUNT_IF(value % 2 == 1) AS odd_count

Примечание

Если нужно посчитать число уникальных значений на строках, где выполняется условие, то в отличие от остальных агрегатных функций модификатор DISTINCT тут не поможет, так как в аргументах нет никаких значений. Для получения данного результата, стоит воспользоваться в подзапросе встроенной функцией IF с двумя аргументами (чтобы в else получился NULL), а снаружи сделать COUNT(DISTINCT ...) по её результату.

SUM_IF и AVG_IF

Сигнатура

SUM_IF(Unsigned?, Bool?)->Uint64?
SUM_IF(Signed?, Bool?)->Int64?
SUM_IF(Interval?, Bool?)->Interval?

AVG_IF(Double?, Bool?)->Double?

Сумма или арифметическое среднее, но только для строк, удовлетворяющих условию, переданному вторым аргументом.

Таким образом, SUM_IF(value, condition) является чуть более короткой записью для SUM(IF(condition, value)), аналогично для AVG. Расширение типа данных аргумента работает так же аналогично одноименным функциям без суффикса.

Примеры

SELECT
    SUM_IF(value, value % 2 == 1) AS odd_sum,
    AVG_IF(value, value % 2 == 1) AS odd_avg,
FROM my_table;

При использовании фабрики агрегационной функции в качестве первого аргумента AGGREGATE_BY передается Tuple из значения и предиката.

Примеры

$sum_if_factory = AggregationFactory("SUM_IF");
$avg_if_factory = AggregationFactory("AVG_IF");

SELECT
    AGGREGATE_BY(AsTuple(value, value % 2 == 1), $sum_if_factory) AS odd_sum,
    AGGREGATE_BY(AsTuple(value, value % 2 == 1), $avg_if_factory) AS odd_avg
FROM my_table;

SOME

Сигнатура

SOME(T?)->T?
SOME(T)->T?

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

Из-за отсутствия гарантий SOME вычислительно дешевле, чем часто использующиеся в подобных ситуациях MIN/MAX.

Примеры

SELECT
  SOME(value)
FROM my_table;

Внимание

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

CountDistinctEstimate, HyperLogLog и HLL

Сигнатура

CountDistinctEstimate(T)->Uint64?
HyperLogLog(T)->Uint64?
HLL(T)->Uint64?

Примерная оценка числа уникальных значений по алгоритму HyperLogLog. Логически делает то же самое, что и COUNT(DISTINCT ...), но работает значительно быстрее ценой некоторой погрешности.

Аргументы:

  1. Значение для оценки;
  2. Точность (от 4 до 18 включительно, по умолчанию 14).

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

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

Примеры

SELECT
  CountDistinctEstimate(my_column)
FROM my_table;

SELECT
  HyperLogLog(my_column, 4)
FROM my_table;

AGGREGATE_LIST

Сигнатура

AGGREGATE_LIST(T? [, limit:Uint64])->List<T>
AGGREGATE_LIST(T [, limit:Uint64])->List<T>
AGGREGATE_LIST_DISTINCT(T? [, limit:Uint64])->List<T>
AGGREGATE_LIST_DISTINCT(T [, limit:Uint64])->List<T>

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

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

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

Чтобы получить список нескольких значений с одной строки, важно НЕ использовать функцию AGGREGATE_LIST несколько раз, а сложить все нужные значения в контейнер, например через AsList или AsTuple и передать этот контейнер в один вызов AGGREGATE_LIST.

Например, можно использовать в сочетании с DISTINCT и функцией String::JoinFromList (аналог ','.join(list) из Python) для распечатки в строку всех значений, которые встретились в столбце после применения GROUP BY.

Примеры

SELECT  
   AGGREGATE_LIST( region ),
   AGGREGATE_LIST( region, 5 ),
   AGGREGATE_LIST( DISTINCT region ),
   AGGREGATE_LIST_DISTINCT( region ),
   AGGREGATE_LIST_DISTINCT( region, 5 )
FROM users

-- Аналог GROUP_CONCAT из MySQL
SELECT
    String::JoinFromList(CAST(AGGREGATE_LIST(region, 2) AS List<String>), ",")
FROM users

Существует также короткая форма записи этих функций - AGG_LIST и AGG_LIST_DISTINCT.

Внимание

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

MAX_BY и MIN_BY

Сигнатура

MAX_BY(T1?, T2)->T1?
MAX_BY(T1, T2)->T1?
MAX_BY(T1, T2, limit:Uint64)->List<T1>?

MIN_BY(T1?, T2)->T1?
MIN_BY(T1, T2)->T1?
MIN_BY(T1, T2, limit:Uint64)->List<T1>?

Вернуть значение первого аргумента для строки таблицы, в которой второй аргумент оказался минимальным/максимальным.

Опционально можно указать третий аргумент N, который влияет на поведение в случае, если в таблице есть несколько строк с одинаковым минимальным или максимальным значением:

  • Если N не указано — будет возвращено значение одной из строк, а остальные отбрасываются.
  • Если N указано — будет возвращен список со всеми значениями, но не более N, все значения после достижения указанного числа отбрасываются.

При выборе значения N рекомендуется не превышать порядка сотен или тысяч, чтобы не возникало проблем с ограниченной доступной памятью на кластерах {{ backend_name }}.

Если для задачи обязательно нужны все значения, и их количество может измеряться десятками тысяч и больше, то вместо данных агрегационных функций следует использовать JOIN исходной таблицы с подзапросом, где по ней же сделан GROUP BY + MIN/MAX на интересующих вас колонках.

Внимание

Если второй аргумент всегда NULL, то результатом агрегации будет NULL.

При использовании фабрики агрегационной функции в качестве первого аргумента AGGREGATE_BY передается Tuple из значения и ключа.

Примеры

SELECT
  MIN_BY(value, LENGTH(value)),
  MAX_BY(value, key, 100)
FROM my_table;

$min_by_factory = AggregationFactory("MIN_BY");
$max_by_factory = AggregationFactory("MAX_BY", 100);

SELECT
    AGGREGATE_BY(AsTuple(value, LENGTH(value)), $min_by_factory),
    AGGREGATE_BY(AsTuple(value, key), $max_by_factory)
FROM my_table;

TOP и BOTTOM

Сигнатура

TOP(T?, limit:Uint32)->List<T>
TOP(T, limit:Uint32)->List<T>
BOTTOM(T?, limit:Uint32)->List<T>
BOTTOM(T, limit:Uint32)->List<T>

Вернуть список максимальных/минимальных значений выражения. Первый аргумент - выражение, второй - ограничение на количество элементов.

Примеры

SELECT
    TOP(key, 3),
    BOTTOM(value, 3)
FROM my_table;

$top_factory = AggregationFactory("TOP", 3);
$bottom_factory = AggregationFactory("BOTTOM", 3);

SELECT
    AGGREGATE_BY(key, $top_factory),
    AGGREGATE_BY(value, $bottom_factory)
FROM my_table;

TOP_BY и BOTTOM_BY

Сигнатура

TOP_BY(T1?, T2, limit:Uint32)->List<T1>
TOP_BY(T1, T2, limit:Uint32)->List<T1>
BOTTOM_BY(T1?, T2, limit:Uint32)->List<T1>
BOTTOM_BY(T1, T2, limit:Uint32)->List<T1>

Вернуть список значений первого аргумента для строк с максимальными/минимальными значениями второго аргумента. Третий аргумент - ограничение на количество элементов в списке.

При использовании фабрики агрегационной функции в качестве первого аргумента AGGREGATE_BY передается Tuple из значения и ключа. Ограничение на количество элементов в этом случае передаётся вторым аргументом при создании фабрики.

Примеры

SELECT
    TOP_BY(value, LENGTH(value), 3),
    BOTTOM_BY(value, key, 3)
FROM my_table;

$top_by_factory = AggregationFactory("TOP_BY", 3);
$bottom_by_factory = AggregationFactory("BOTTOM_BY", 3);

SELECT
    AGGREGATE_BY(AsTuple(value, LENGTH(value)), $top_by_factory),
    AGGREGATE_BY(AsTuple(value, key), $bottom_by_factory)
FROM my_table;

TOPFREQ и MODE

Сигнатура

TOPFREQ(T [, num:Uint32 [, bufSize:Uint32]])->List<Struct<Frequency:Uint64, Value:T>>
MODE(T [, num:Uint32 [, bufSize:Uint32]])->List<Struct<Frequency:Uint64, Value:T>>

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

  • Value— найденное часто встречающееся значение;
  • Frequency — оценка числа упоминаний в таблице.

Обязательный аргумент: само значение.

Опциональные аргументы:

  1. Для TOPFREQ — желаемое число элементов в результате. MODE является алиасом к TOPFREQ с 1 в этом аргументе. У TOPFREQ по умолчанию тоже 1.
  2. Число элементов в используемом буфере, что позволяет разменивать потребление памяти на точность. По умолчанию 100.

Примеры

SELECT
    MODE(my_column),
    TOPFREQ(my_column, 5, 1000)
FROM my_table;

STDDEV и VARIANCE

Сигнатура

STDDEV(Double?)->Double?
STDDEV_POPULATION(Double?)->Double?
POPULATION_STDDEV(Double?)->Double?
STDDEV_SAMPLE(Double?)->Double?
STDDEVSAMP(Double?)->Double?

VARIANCE(Double?)->Double?
VARIANCE_POPULATION(Double?)->Double?
POPULATION_VARIANCE(Double?)->Double?
VARPOP(Double?)->Double?
VARIANCE_SAMPLE(Double?)->Double?

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

По умолчанию вычисляются выборочная дисперсия и стандартное отклонение. Доступны несколько способов записи:

  • с суффиксом/префиксом POPULATION, например: VARIANCE_POPULATION, POPULATION_VARIANCE — вычисляет дисперсию/стандартное отклонение для генеральной совокупности;
  • с суффиксом SAMPLE или без суффикса, например VARIANCE_SAMPLE, SAMPLE_VARIANCE, VARIANCE — вычисляет выборочную дисперсию и стандартное отклонение.

Также определено несколько сокращенных алиасов, например VARPOP или STDDEVSAMP.

Если все переданные значения — NULL, возвращает NULL.

Примеры

SELECT
  STDDEV(numeric_column),
  VARIANCE(numeric_column)
FROM my_table;

CORRELATION и COVARIANCE

Сигнатура

CORRELATION(Double?, Double?)->Double?
COVARIANCE(Double?, Double?)->Double?
COVARIANCE_SAMPLE(Double?, Double?)->Double?
COVARIANCE_POPULATION(Double?, Double?)->Double?

Корреляция и ковариация двух колонок.

Также доступны сокращенные версии CORR или COVAR, а для ковариации - версии с суффиксом SAMPLE / POPULATION по аналогии с описанной выше VARIANCE.

В отличие от большинства других агрегатных функций не пропускают NULL, а считают его за 0.

При использовании фабрики агрегационной функции в качестве первого аргумента AGGREGATE_BY передается Tuple из двух значений.

Примеры

SELECT
  CORRELATION(numeric_column, another_numeric_column),
  COVARIANCE(numeric_column, another_numeric_column)
FROM my_table;

$corr_factory = AggregationFactory("CORRELATION");

SELECT
    AGGREGATE_BY(AsTuple(numeric_column, another_numeric_column), $corr_factory)
FROM my_table;

PERCENTILE и MEDIAN

Сигнатура

PERCENTILE(Double?, Double)->Double?
PERCENTILE(Interval?, Double)->Interval?

MEDIAN(Double? [, Double])->Double?
MEDIAN(Interval? [, Double])->Interval?

Подсчет процентилей по амортизированной версии алгоритма TDigest. MEDIAN — алиас для PERCENTILE(N, 0.5).

Ограничение

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

SELECT
    MEDIAN(numeric_column),
    PERCENTILE(numeric_column, 0.99)
FROM my_table;

HISTOGRAM

Сигнатура

HISTOGRAM(Double?)->HistogramStruct?
HISTOGRAM(Double?, weight:Double)->HistogramStruct?
HISTOGRAM(Double?, intervals:Uint32)->HistogramStruct?
HISTOGRAM(Double?, weight:Double, intervals:Uint32)->HistogramStruct?

В описании сигнатур под HistogramStruct подразумевается результат работы агрегатной функции, который является структурой определенного вида.

Построение примерной гистограммы по числовому выражению с автоматическим выбором корзин.

Вспомогательные функции

Базовые настройки

Ограничение на число корзин можно задать с помощью опционального аргумента, значение по умолчанию — 100. Следует иметь в виду, что дополнительная точность стоит дополнительных вычислительных ресурсов и может негативно сказываться на времени выполнения запроса, а в экстремальных случаях — и на его успешности.

Поддержка весов

Имеется возможность указать «вес» для каждого значения, участвующего в построении гистограммы. Для этого вторым аргументом в агрегатную функцию нужно передать выражение для вычисления веса. По умолчанию всегда используется вес 1.0. Если используются нестандартные веса, ограничение на число корзин можно задать третьим аргументом.

В случае, если передано два аргумента, смысл второго аргумента определяется по его типу (целочисленный литерал — ограничение на число корзин, в противном случае — вес).

Если нужна точная гистограмма

  1. Можно воспользоваться описанными ниже агрегатными функциями с фиксированными сетками корзин: LinearHistogram или LogarithmicHistogram.
  2. Можно самостоятельно вычислить номер корзины для каждой строки и сделать по нему GROUP BY.

При использовании фабрики агрегационной функции в качестве первого аргумента AGGREGATE_BY передается Tuple из значения и веса.

Примеры

SELECT
    HISTOGRAM(numeric_column)
FROM my_table;

SELECT
    Histogram::Print(
        HISTOGRAM(numeric_column, 10),
        50
    )
FROM my_table;

$hist_factory = AggregationFactory("HISTOGRAM");

SELECT
    AGGREGATE_BY(AsTuple(numeric_column, 1.0), $hist_factory)
FROM my_table;

LinearHistogram, LogarithmicHistogram и LogHistogram

Построение гистограммы по явно указанной фиксированной шкале корзин.

Сигнатура

LinearHistogram(Double?)->HistogramStruct?
LinearHistogram(Double? [, binSize:Double [, min:Double [, max:Double]]])->HistogramStruct?

LogarithmicHistogram(Double?)->HistogramStruct?
LogarithmicHistogram(Double? [, logBase:Double [, min:Double [, max:Double]]])->HistogramStruct?
LogHistogram(Double?)->HistogramStruct?
LogHistogram(Double? [, logBase:Double [, min:Double [, max:Double]]])->HistogramStruct?

Аргументы:

  1. Выражение, по значению которого строится гистограмма. Все последующие — опциональны.
  2. Расстояние между корзинами для LinearHistogram или основание логарифма для LogarithmicHistogram / LogHistogram (это алиасы). В обоих случаях значение по умолчанию — 10.
  3. Минимальное значение. По умолчанию минус бесконечность.
  4. Максимальное значение. По умолчанию плюс бесконечность.

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

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

Примеры

SELECT
    LogarithmicHistogram(numeric_column, 2)
FROM my_table;

CDF (cumulative distribution function)

К каждому виду функции Histogram можно приписать суффикс CDF для построения кумулятивной функции распределения. Конструкции

SELECT
    Histogram::ToCumulativeDistributionFunction(Histogram::Normalize(<вид_функции>Histogram(numeric_column)))
FROM my_table;

и

SELECT
    <вид_функции>HistogramCDF(numeric_column)
FROM my_table;

полностью эквивалентны.

BOOL_AND, BOOL_OR и BOOL_XOR

Сигнатура

BOOL_AND(Bool?)->Bool?
BOOL_OR(Bool?)->Bool?
BOOL_XOR(Bool?)->Bool?

Применение соответствующей логической операции (AND/OR/XOR) ко всем значениям булевой колонки или выражения.

Эти функции не пропускают NULL значение при агрегации, единственное NULL значение превратит результат в NULL. Для агрегации с пропуском NULL-ов можно использовать функции MIN/MAX или BIT_AND/BIT_OR/BIT_XOR.

Примеры

SELECT
  BOOL_AND(bool_column),
  BOOL_OR(bool_column),
  BOOL_XOR(bool_column)
FROM my_table;

BIT_AND, BIT_OR и BIT_XOR

Применение соответствующей битовой операции ко всем значениям числовой колонки или выражения.

Примеры

SELECT
    BIT_XOR(unsigned_numeric_value)
FROM my_table;

AGGREGATE_BY и MULTI_AGGREGATE_BY

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

  1. Колонка, DISTINCT колонка или выражение;
  2. Фабрика.

Примеры:

$count_factory = AggregationFactory("COUNT");

SELECT
    AGGREGATE_BY(DISTINCT column, $count_factory) as uniq_count
FROM my_table;

SELECT
    MULTI_AGGREGATE_BY(nums, AggregationFactory("count")) as count,
    MULTI_AGGREGATE_BY(nums, AggregationFactory("min")) as min,
    MULTI_AGGREGATE_BY(nums, AggregationFactory("max")) as max,
    MULTI_AGGREGATE_BY(nums, AggregationFactory("avg")) as avg,
    MULTI_AGGREGATE_BY(nums, AggregationFactory("percentile", 0.9)) as p90
FROM my_table;
Предыдущая
Базовые
Следующая
Оконные