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

COUNT

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

Как и другие агрегатные функции, может использоваться в сочетании с 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

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

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

Примеры

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

SUM

Сумма чисел.

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

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

SELECT SUM(value) FROM my_table;

AVG

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

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

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

Примеры

SELECT AVG(value) FROM my_table;

COUNT_IF

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

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

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

Примеры

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

SUM_IF и AVG_IF

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

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

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

Примеры

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

$sum_if_factory = AGGREGATION_FACTORY("SUM_IF");
$avg_if_factory = AGGREGATION_FACTORY("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;

CountDistinctEstimate, HyperLogLog и HLL

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

Аргументы:

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

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

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

Примеры

SELECT
  CountDistinctEstimate(my_column)
FROM my_table;

SELECT
  HyperLogLog(my_column, 4)
FROM my_table;

SOME

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

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

Примеры

SELECT
  SOME(value)
FROM my_table;

LIST

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

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

Стоит учитывать, что результатом выполнения функции может быть List или List? (опциональный), в зависимости от контекста.

Примеры

-- В контексте GROUP BY — тип List
SELECT LIST(ip, 1)
FROM [//home/yql/tutorial/users]
GROUP BY age

-- В контексте без GROUP BY — опциональный тип List?
SELECT LIST(ip, 1)
FROM [//home/yql/tutorial/users]

MAX_BY и MIN_BY

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

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

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

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

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

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

Примеры

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

$min_by_factory = AGGREGATION_FACTORY("MIN_BY");
$max_by_factory = AGGREGATION_FACTORY("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

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

Примеры

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

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

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

TOP_BY и BOTTOM_BY

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

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

Примеры

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

$top_by_factory = AGGREGATION_FACTORY("TOP_BY", 3);
$bottom_by_factory = AGGREGATION_FACTORY("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

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

• 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

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

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

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

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

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

Примеры

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

CORRELATION и COVARIANCE

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

Также доступны сокращенные версии CORR или COVAR, а для ковариации - версии c суффиксом 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 = AGGREGATION_FACTORY("CORRELATION");

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

PERCENTILE и MEDIAN

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

HISTOGRAM

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

Вспомогательные функции перечислены в разделе Histogram UDF.

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

Ограничение на число корзин можно задать с помощью опционального аргумента, значение по умолчанию — 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 = AGGREGATION_FACTORY("HISTOGRAM");

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

LinearHistogram, LogarithmicHistogram и LogHistogram

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

Аргументы:

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

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

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

Примеры

SELECT
    LogarithmicHistogram(numeric_column, 2)
FROM my_table;

BOOL_AND и BOOL_OR

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

Логика работы эквивалентна Python функциям all() и any() соответственно.

Примеры

SELECT
  BOOL_AND(bool_column),
  BOOL_OR(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 = AGGREGATION_FACTORY("COUNT");

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

SELECT
    MULTI_AGGREGATE_BY(nums, AGGREGATION_FACTORY("count")) as count,
    MULTI_AGGREGATE_BY(nums, AGGREGATION_FACTORY("min")) as min,
    MULTI_AGGREGATE_BY(nums, AGGREGATION_FACTORY("max")) as max,
    MULTI_AGGREGATE_BY(nums, AGGREGATION_FACTORY("avg")) as avg,
    MULTI_AGGREGATE_BY(nums, AGGREGATION_FACTORY("percentile", 0.9)) as p90
FROM my_table;