Агрегатные функции
- COUNT
- MIN и MAX
- SUM
- AVG
- COUNT_IF
- SUM_IF и AVG_IF
- CountDistinctEstimate, HyperLogLog и HLL
- SOME
- LIST
- MAX_BY и MIN_BY
- TOP и BOTTOM
- TOP_BY и BOTTOM_BY
- TOPFREQ и MODE
- STDDEV и VARIANCE
- CORRELATION и COVARIANCE
- PERCENTILE и MEDIAN
- HISTOGRAM
- LinearHistogram, LogarithmicHistogram и LogHistogram
- BOOL_AND и BOOL_OR
- BIT_AND, BIT_OR и BIT_XOR
- AGGREGATE_BY и MULTI_AGGREGATE_BY
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
Примечание
Если нужно посчитать число уникальных значений на строках, где выполняется условие, то в отличие от остальных агрегатных функций модификатор DISTINCT тут не поможет, так как в аргументах нет никаких значений. Для получения данного результата, стоит воспользоваться в подзапросе встроенной функцией IF с двумя аргументами (чтобы в else получился NULL
), а снаружи сделать COUNT(DISTINCT ...) по её результату.
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 ...), но работает значительно быстрее ценой некоторой погрешности.
Аргументы:
- Значение для оценки;
- Точность (от 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;
Внимание
При вызове агрегатной функции SOME
несколько раз не гарантируется, что все значения результатов будут взяты с одной строки исходной таблицы. Для получения данной гарантии, нужно запаковать значения в какой-либо из контейнеров и передавать в SOME
уже его. Например, для структуры это можно сделать с помощью AsStruct
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 рекомендуется не превышать порядка сотен или тысяч, чтобы не возникало проблем с ограниченной доступной памятью на кластерах.
Если для задачи обязательно нужны все значения, и их количество может измеряться десятками тысяч и больше, то вместо данных агрегационных функций следует использовать 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 = 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
— оценка числа упоминаний в таблице.
Обязательный аргумент: само значение.
Опциональные аргументы:
- Для
TOPFREQ
— желаемое число элементов в результате.MODE
является алиасом кTOPFREQ
с 1 в этом аргументе. УTOPFREQ
по умолчанию тоже 1. - Число элементов в используемом буфере, что позволяет разменивать потребление памяти на точность. По умолчанию 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
Внимание
Первый аргумент (N) должен быть именем колонки таблицы. Если это ограничение необходимо обойти, можно использовать подзапрос. Ограничение введено для упрощения вычислений, поскольку в реализации несколько вызовов с одинаковым первым аргументом (N) склеиваются в один проход.
SELECT
MEDIAN(numeric_column),
PERCENTILE(numeric_column, 0.99)
FROM my_table;
HISTOGRAM
Построение примерной гистограммы по числовому выражению с автоматическим выбором корзин.
Вспомогательные функции перечислены в разделе Histogram UDF.
Базовые настройки
Ограничение на число корзин можно задать с помощью опционального аргумента, значение по умолчанию — 100. Следует иметь в виду, что дополнительная точность стоит дополнительных вычислительных ресурсов и может негативно сказываться на времени выполнения запроса, а в экстремальных случаях — и на его успешности.
Поддержка весов
Имеется возможность указать «вес» для каждого значения, участвующего в построении гистограммы. Для этого вторым аргументом в агрегатную функцию нужно передать выражение для вычисления веса. По умолчанию всегда используется вес 1.0
. Если используются нестандартные веса, ограничение на число корзин можно задать третьим аргументом.
В случае, если передано два аргумента, смысл второго аргумента определяется по его типу (целочисленный литерал — ограничение на число корзин, в противном случае — вес).
Если нужна точная гистограмма
- Можно воспользоваться описанными ниже агрегатными функциями с фиксированными сетками корзин: LinearHistogram или LogarithmicHistogram.
- Можно самостоятельно вычислить номер корзины для каждой строки и сделать по нему 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
Построение гистограммы по явно указанной фиксированной шкале корзин.
Аргументы:
- Выражение, по значению которого строится гистограмма. Все последующие — опциональны.
- Расстояние между корзинами для
LinearHistogram
или основание логарифма дляLogarithmicHistogram
/LogHistogram
(это алиасы). В обоих случаях значение по умолчанию — 10. - Минимальное значение. По умолчанию минус бесконечность.
- Максимальное значение. По умолчанию плюс бесконечность.
Формат результата полностью аналогичен адаптивным гистограммам, что позволяет использовать тот же набор вспомогательных функций.
Если разброс входных значений неконтролируемо велик, рекомендуется указывать минимальное и максимальное значение для предотвращения потенциальных падений из-за высокого потребления памяти.
Примеры
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
требует, чтобы в значении колонки или выражения была структура, и применяет фабрику поэлементно, размещая результат в структуре с теми же полями.
- Колонка,
DISTINCT
колонка или выражение; - Фабрика.
Примеры
$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;