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

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

За исключением count(), count_if(), max_by(), min_by() и approx_distinct(), все эти агрегатные функции игнорируют значения null и возвращают null при отсутствии входных строк или когда все значения равны null. Например, sum() возвращает null, а не ноль, и avg() не включает значения null в подсчет. Функцию coalesce можно использовать, чтобы преобразовать null в ноль.

Упорядочивание при агрегации#

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

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

Фильтрация при агрегации#

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

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

Распространенный и очень полезный пример — использование FILTER, чтобы исключить null из рассмотрения при использовании array_agg:

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

Другой пример: представим, что вы хотите добавить условие в подсчет для цветов Iris, изменив следующий запрос:

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

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

Если использовать только обычное предложение WHERE, часть информации теряется:

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

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

При использовании фильтра сохраняется вся информация:

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]#

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

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

SELECT sum(o.totalprice) as spend,
    any_value(c.name)
FROM tpch.tiny.orders o
JOIN tpch.tiny.customer c
ON o.custkey  = c.custkey
GROUP BY c.custkey;
ORDER BY spend;

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

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

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#: Возвращает количество входных строк.

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

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.

Синтаксис:

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

Note

Значение expression должно вычисляться в строковый тип данных (varchar). Перед использованием с listagg необходимо явно привести нестроковые типы данных к varchar с помощью CAST(expression AS VARCHAR).

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

В простейшем виде функция выглядит так:

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

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

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

Следующий пример приводит столбец v к varchar:

SELECT listagg(CAST(v AS VARCHAR), ',') WITHIN GROUP (ORDER BY v) csv_value
FROM (VALUES 1, 3, 2) t(v);

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

csv_value
-----------
'1,2,3'

Переполнение по умолчанию приводит к ошибке в случае, если длина вывода функции превышает 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);

Если не указано иное, строка-заполнитель для усечения по умолчанию — '...'.

Эту агрегатную функцию также можно использовать в сценарии с группировкой:

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 среди всех входных значений.

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 среди всех входных значений.

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, содержащий количество вхождений каждого входного значения.

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

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

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

Например, возьмем следующую функцию histogram, которая создает несколько map из набора данных Iris:

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 с помощью 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). Ноль возвращается, если все входные значения равны null.

Эта функция должна давать стандартную ошибку 2.3%, которая является стандартным отклонением (приблизительно нормального) распределения ошибки по всем возможным наборам. Это не гарантирует верхнюю границу ошибки для любого конкретного входного набора.

approx_distinct(x, e) → bigint

Возвращает приблизительное количество различных входных значений. Эта функция дает приближение для count(DISTINCT x). Ноль возвращается, если все входные значения равны null.

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

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

Вычисляет наиболее частые значения до buckets элементов приблизительно. Приближенная оценка этой функции позволяет выбирать частые значения с меньшим объемом памяти. Больший capacity повышает точность базового алгоритма ценой расхода памяти. Возвращаемое значение — это map, содержащий верхние элементы с соответствующей оцененной частотой.

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

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

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

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

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

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

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

approx_set(x) → HyperLogLog: См. Функции HyperLogLog.

merge(x) → HyperLogLog: См. Функции HyperLogLog.

merge(qdigest(T)) -> qdigest(T): См. Функции quantile digest.

merge(tdigest) → tdigest: См. Функции T-Digest.

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.

qdigest_agg(x, w) -> qdigest([same as x]): См. Функции quantile digest.

qdigest_agg(x, w, accuracy) -> qdigest([same as x]): См. Функции quantile digest.

tdigest_agg(x) → tdigest: См. Функции T-Digest.

tdigest_agg(x, w) → tdigest: См. Функции T-Digest.

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

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

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

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

kurtosis(x) → double#

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

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#: Возвращает моментный коэффициент Фишера асимметрии всех входных значений.

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#: Возвращает дисперсию выборки всех входных значений.

Лямбда-агрегатные функции#

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

Сворачивает все входные значения в одно значение. inputFunction вызывается для каждого входного значения, не равного null. Помимо входного значения, inputFunction принимает текущее состояние, изначально initialState, и возвращает новое состояние. 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)

Тип состояния должен быть boolean, integer, floating-point, char, varchar или date/time/interval.