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

Агрегатные функции позволяют получить результирующее значение на основе значений колонок из нескольких записей.

Обработка NULL#

За исключением max_by(), min_by(), array_agg() и approx_distinct(), агрегатные функции игнорируют значения NULL при расчете финального значения.

Данные функции возвращают NULL, если все агрегированные значения были NULL.

При необходимости вы можете использовать функцию COALESCE для преобразования значения NULL в ноль.

Сортировка записей#

Результат некоторых агрегатных функций, таких как array_agg(), зависит от порядка агрегируемых записей. Порядок записей можно задать с помощью выражения ORDER BY:

array_agg(x ORDER BY y DESC)
array_agg(x ORDER BY x, y, z)

Фильтрация записей#

Ключевое слово FILTER можно использовать для игнорирования части записей в процессе агрегации.

aggregate_function(...) FILTER (WHERE <condition>)

Распространенным примером использования FILTER является удаление NULL значений для функции array_agg:

SELECT array_agg(name) FILTER (WHERE name IS NOT NULL)
FROM region;

Рассмотрим другой пример на основе набора данных «Ирисы Фишера», который содержит данные о 150 экземплярах ириса, включая их вид (setosa - щетинистый, virginica - виргинский, versicolor - разноцветный), а также отдельные характеристики каждого цветка.

Следующий запрос возвращает количество ирисов каждого вида:

SELECT species, count(*) AS count
FROM iris
GROUP BY species;

species    | count
-----------+-------
setosa     |   50
virginica  |   50
versicolor |   50

Предположим, что мы хотим посчитать количество ирисов с определенной характеристикой (petal_length_cm > 4) в каждой группе. Если мы применим данное условие в качестве предиката WHERE, запись с видом setosa не будет возвращена, так как ни один конкретный цветок данного вида не подходит под условие:

SELECT species,
    count(*) AS count
FROM iris
WHERE petal_length_cm > 4
GROUP BY species;

species    | count
-----------+-------
virginica  |   50
versicolor |   34

Если же мы передадим условие в качестве фильтра агрегатной функции, запрос вернет записи для каждого вида, включая setosa:

SELECT species,
       count(*) FILTER (where petal_length_cm > 4) AS count
FROM iris
GROUP BY species;

species    | count
-----------+-------
virginica  |   50
setosa     |    0
versicolor |   34

Общие функции#

any_value(x) → [same as input]#: Возвращает произвольное ненулевое значение, выбранное среди значений группы.

arbitrary(x) → [same as input]#: Псевдоним any_value().

array_agg(x) → array<[same as input]>#: Возвращает массив, созданный из входных элементов x. Значения NULL будут включены в результат.

avg(x) → double#: Возвращает среднее арифметическое значение всех входных значений.

avg(time interval type) → time interval type: Возвращает среднюю длину интервала всех входных значений.

bool_and(boolean) → boolean#: Возвращает TRUE, если каждое входное значение равно TRUE, в противном случае FALSE.

bool_or(boolean) → boolean#: Возвращает TRUE, если какое-либо входное значение равно TRUE, в противном случае FALSE.

checksum(x) → varbinary#: Возвращает контрольную сумму, рассчитанную на основе входных значений. Значение контрольной суммы не зависит от порядка входных значений.

count(*) → bigint#: Возвращает количество входных записей. Запись будет учтена, даже если она содержит только NULL значения.

count(x) → bigint: Возвращает количество входных значений отличных от NULL.

count_if(x) → bigint#: Возвращает количество входных значений TRUE. Эквивалентна count(CASE WHEN x THEN 1 END).

every(boolean) → boolean#: Псевдоним bool_and().

geometric_mean(x) → double#: Возвращает среднее геометрическое всех входных значений.

listagg(x, separator) → varchar#

Возвращает объединенные входные значения, разделенные separator. Значения NULL не будут включены в результат.

Синтаксис:

LISTAGG( expression [, separator] [ON OVERFLOW overflow_behaviour])
    WITHIN GROUP (ORDER BY sort_item, [...]) [FILTER (WHERE condition)]

Если separator не указан, в качестве разделителя будет использована пустая строка.

Пример:

SELECT listagg(value, ',') WITHIN GROUP (ORDER BY value) csv_value
FROM (VALUES 'a', 'c', 'b') t(value);

csv_value
-----------
'a,b,c'

Поведение по умолчанию вызывает ошибку в случае, если длина результирующей строки превышает 1048576 байт:

SELECT listagg(value, ',' ON OVERFLOW ERROR) WITHIN GROUP (ORDER BY value) csv_value
FROM (VALUES 'a', 'b', 'c') t(value);

Существует возможность обрезать вывод WITH COUNT или WITHOUT COUNT для пропущенных ненулевых значений в случае, если длина вывода функции превышает 1048576 байт:

SELECT listagg(value, ',' ON OVERFLOW TRUNCATE '.....' WITH COUNT) WITHIN GROUP (ORDER BY value)
FROM (VALUES 'a', 'b', 'c') t(value);

По умолчанию в качестве аргумента TRUNCATE будет использована строка '...'.

ORDER BY может быть применен к колонкам или выражениям, отличным от аргумента LISTAGG:

SELECT id, listagg(value, ',') WITHIN GROUP (ORDER BY o) csv_value
FROM (VALUES
    (100, 1, 'a'),
    (200, 3, 'c'),
    (200, 2, 'b')
) t(id, o, value)
GROUP BY id
ORDER BY id;

 id  | csv_value
-----+-----------
 100 | a
 200 | b,c

Данная функция поддерживает фильтрацию во время агрегации.

Следующий запрос

SELECT 
    country,
    listagg(city, ',')
        WITHIN GROUP (ORDER BY population DESC)
        FILTER (WHERE population >= 10_000_000) megacities
FROM (VALUES 
    ('India', 'Bangalore', 13_700_000),
    ('India', 'Chennai', 12_200_000),
    ('India', 'Ranchi', 1_547_000),
    ('Austria', 'Vienna', 1_897_000),
    ('Poland', 'Warsaw', 1_765_000)
) t(country, city, population)
GROUP BY country
ORDER BY country;

дает результат:

 country |    megacities     
---------+-------------------
 Austria | NULL              
 India   | Bangalore,Chennai 
 Poland  | NULL

Текущая реализация функции LISTAGG не поддерживает фреймы оконных функций.

max(x) → [same as input]#: Возвращает максимальное значение всех входных значений.

max(x, n) → array<[same as x]>: Возвращает n наибольших значений из всех входных значений x.

max_by(x, y) → [same as x]#: Возвращает значение x, связанное с наибольшим значением y. Если наибольшее значение y присутствует в нескольких записях, будет возвращено значение x одной из данных записей.

max_by(x, y, n) → array<[same as x]>: Возвращает n значений x, связанных с n наибольших значений y в порядке убывания y.

min(x) → [same as input]#: Возвращает минимальное значение всех входных значений.

min(x, n) → array<[same as x]>: Возвращает n наименьших значений из всех входных значений x.

min_by(x, y) → [same as x]#: Возвращает значение x, связанное с наименьшим значением y. Если наименьшее значение y присутствует в нескольких записях, будет возвращено значение x одной из данных записей.

min_by(x, y, n) → array<[same as x]>: Возвращает n значений x, связанных с n наименьших значений y в порядке возрастания y.

sum(x) → [same as input]#: Возвращает сумму входных значений.

Побитовые функции#

bitwise_and_agg(x) → bigint#: Возвращает побитовое AND всех входных значений, отличных от NULL. Возвращает NULL, если все значения группы равны NULL или группа пуста.

bitwise_or_agg(x) → bigint#: Возвращает побитовое OR всех входных значений, отличных от NULL. Возвращает NULL, если все значения группы равны NULL или группа пуста.

bitwise_xor_agg(x) → bigint#: Возвращает побитовое XOR всех входных значений, отличных от NULL. Возвращает NULL, если все значения группы равны NULL или группа пуста.

Функции, возвращающие словари (MAP)#

histogram(x) → map<K,bigint>#: Возвращает словарь, содержащий количество повторений каждого входного значения.

map_agg(key, value) → map<K,V>#: Возвращает словарь, созданный из входных пар key / value.

map_union(x(K, V)) → map<K,V>#

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

Рассмотрим функцию histogram, которая создает несколько словарей из набора данных «Ирисы Фишера»:

SELECT histogram(floor(petal_length_cm)) petal_data
FROM memory.default.iris
GROUP BY species;

 petal_data
------------
 {4.0=6, 5.0=33, 6.0=11}
 {4.0=37, 5.0=2, 3.0=11}
 {1.0=50}

Вы можете объединить результирующие словари, используя функцию map_union:

SELECT map_union(petal_data) petal_data_union
FROM (
   SELECT histogram(floor(petal_length_cm)) petal_data
   FROM memory.default.iris
   GROUP BY species
);

 petal_data_union
------------------ 
 {4.0=6, 5.0=2, 6.0=11, 1.0=50, 3.0=11}

multimap_agg(key, value) → map<K,array(V)>#: Возвращает мультисловарь (multimap), созданный из входных пар key / value.

Приближенные функции#

approx_distinct(x) → bigint#

Возвращает приблизительное количество уникальных входных значений. Является приблизительным эквивалентом count(DISTINCT x).

Функция выдает стандартную ошибку 2,35% и не гарантирует верхнюю границу ошибки.

approx_distinct(x, e) → bigint

Возвращает приблизительное количество уникальных входных значений. Является приблизительным эквивалентом count(DISTINCT x).

Функция выдает стандартную ошибку e и не гарантирует верхнюю границу ошибки. Текущая реализация этой функции требует, чтобы значение e находилось в диапазоне [0.0040625, 0.26000].

approx_most_frequent(buckets, value, capacity) → map<[same as value], bigint>#

Приблизительно вычисляет наиболее часто встречающиеся значения среди входных значений value. Результатом работы функции является словарь размером не более buckets. Для формирования словаря будет использовано не более capacity входных значений. Увеличение значения capacity повышает точность, но может увеличить потребление памяти.

buckets и capacity должны иметь тип bigint. value может быть числовым или строковым типом.

Функция использует алгоритм, предложенный в статье Efficient Computation of Frequent and Top-k Elements in Data Streams by A. Metwalley, D. Agrawl and A. Abbadi.

approx_percentile(x, percentage) → [same as x]#: Возвращает приблизительный процентиль для всех входных значений x в заданном проценте percentage. Значение percentage должно быть в диапазоне от нуля до единицы.

approx_percentile(x, percentages) → array<[same as x]>: Возвращает приблизительный процентиль для всех входных значений x для каждого из указанных значений процентов percentages. Значение каждого элемента массива percentages должно быть в диапазоне от нуля до единицы.

approx_percentile(x, w, percentage) → [same as x]: Возвращает приблизительный процентиль для всех входных значений x в заданном проценте percentage, используя вес элемента w. Значение percentage должно быть в диапазоне от нуля до единицы. Значение w должно быть больше или равно единицы.

approx_percentile(x, w, percentages) → array<[same as x]>: Возвращает приблизительный процентиль для всех входных значений x для каждого из указанных значений процентов percentages, используя вес элемента w. Значение каждого элемента массива percentages должно быть в диапазоне от нуля до единицы. Значение w должно быть больше или равно единицы.

approx_set(x) → HyperLogLog: См. HyperLogLog functions.

merge(x) → HyperLogLog: См. HyperLogLog functions.

merge(qdigest(T)) -> qdigest(T): См. Quantile digest functions.

merge(tdigest) → tdigest: См. T-Digest functions.

numeric_histogram(buckets, value) → map<double, double>: Вычисляет приблизительную гистограмму с количеством сегментов до buckets для всех value. Эта функция эквивалентна варианту numeric_histogram(), который принимает weight с весом каждого элемента 1.

numeric_histogram(buckets, value, weight) → map<double, double>#

Вычисляет приблизительную гистограмму с количеством сегментов до buckets для всех value с весом элемента weight. Алгоритм основан на:

Yael Ben-Haim and Elad Tom-Tov, "A streaming parallel decision tree algorithm",
J. Machine Learning Research 11 (2010), pp. 849--872.

buckets должен быть bigint. value и weight должны быть числовыми.

qdigest_agg(x) -> qdigest([same as x]): См. Quantile digest functions.

qdigest_agg(x, w) -> qdigest([same as x]): См. Quantile digest functions.

qdigest_agg(x, w, accuracy) -> qdigest([same as x]): См. Quantile digest functions.

tdigest_agg(x) → tdigest: См. T-Digest functions.

tdigest_agg(x, w) → tdigest: См. T-Digest functions.

Статистические функции#

corr(y, x) → double#: Возвращает коэффициент Пирсона корреляции числовых пар входных значений.

covar_pop(y, x) → double#: Возвращает ковариацию генеральной совокупности числовых пар входных значений.

covar_samp(y, x) → double#: Возвращает ковариацию выборки числовых пар входных значений.

kurtosis(x) → double#

Возвращает kurtosis входных значений, рассчитанный по формуле:

kurtosis(x) = n(n+1)/((n-1)(n-2)(n-3))sum[(x_i-mean)^4]/stddev(x)^4-3(n-1)^2/((n-2)(n-3))

regr_intercept(y, x) → double#: Возвращает перехват линии линейной регрессии входных значений. y - зависимое значение, x - независимое значение.

regr_slope(y, x) → double#: Возвращает наклон линии линейной регрессии входных значений. y - зависимое значение, x - независимое значение.

skewness(x) → double#: Возвращает skewness входных значений.

stddev(x) → double#: Псевдоним stddev_samp().

stddev_pop(x) → double#: Возвращает стандартное отклонение входных значений.

stddev_samp(x) → double#: Возвращает выборочное стандартное отклонение входных значений.

variance(x) → double#: Псевдоним var_samp().

var_pop(x) → double#: Возвращает дисперсию входных значений.

var_samp(x) → double#: Возвращает выборочную дисперсию входных значений.

Lambda-функции#

reduce_agg(inputValue T, initialState S, inputFunction(S, T, S), combineFunction(S, S, S)) → S#

Позволяет выполнить агрегацию на основе произвольных функций.

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

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

Пример:

SELECT id, reduce_agg(value, 0, (a, b) -> a + b, (a, b) -> a + b)
FROM (
    VALUES
        (1, 3),
        (1, 4),
        (1, 5),
        (2, 6),
        (2, 7)
) AS t(id, value)
GROUP BY id;
-- (1, 12)
-- (2, 13)

SELECT id, reduce_agg(value, 1, (a, b) -> a * b, (a, b) -> a * b)
FROM (
    VALUES
        (1, 3),
        (1, 4),
        (1, 5),
        (2, 6),
        (2, 7)
) AS t(id, value)
GROUP BY id;

-- (1, 60)
-- (2, 42)

Допустимые типы состояния: