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

Агрегатные функции получают единственный результат из набора входных значений. Встроенные обычные агрегатные функции перечислены в Таблице 9-49 и Таблице 9-50, а сортирующие агрегатные функции — в Таблице 9-51 и Таблице 9-52. Особенности синтаксиса агрегатных функций разъясняются в Подразделе 4.2.7. За дополнительной вводной информацией обратитесь к Разделу 2.7.

Таблица 9-49. Агрегатные функции общего назначения

ФункцияТипы аргументаТип результатаОписание
array_agg(выражение)anyмассив элементов с типом аргументавходные значения, включая NULL, объединяются в массив
avg(выражение)smallint, int, bigint, real, double precision, numeric или intervalnumeric для любых целочисленных аргументов, double precision для аргументов с плавающей точкой, в противном случае тип данных аргументаарифметическое среднее для всех входных значений
bit_and(выражение)smallint, int, bigint или bitтот же, что и тип аргументапобитовое И для всех входных значений, не равных NULL, или NULL, если таких нет
bit_or(выражение)smallint, int, bigint или bitтот же, что и тип аргументапобитовое ИЛИ для всех входных значений, не равных NULL, или NULL, если таких нет
bool_and(выражение)boolbooltrue, если все входные значения равны true, и false в противном случае
bool_or(выражение)boolbooltrue, если хотя бы одно входное значение равно true, и false в противном случае
count(*) bigintколичество входных строк
count(выражение)anybigintколичество входных строк, для которых значение выражения не равно NULL
every(выражение)boolboolсиноним bool_and
json_agg(выражение)anyjsonагрегирует значения в виде массива JSON
json_object_agg(имя, значение)(any, any)jsonагрегирует пары имя/значение в виде объекта JSON
max(выражение)любой массив, число, строка или дата/времятот же, что и тип аргументамаксимальное значение выражения среди всех входных данных
min(выражение)любой массив, число, строка или дата/времятот же, что и тип аргументаминимальное значение выражения среди всех входных данных
string_agg(выражение, разделитель)(text, text) или (bytea, bytea)тот же, что и типы аргументоввходные данные складываются в строку через заданный разделитель
sum(выражение)smallint, int, bigint, real, double precision, numeric, interval или moneybigint для аргументов smallint или int, numeric для аргументов bigint, и тип аргумента в остальных случаяхсумма значений выражения по всем входным данным
xmlagg(выражение)xmlxmlсоединение XML-значений (см. также Подраздел 9.14.1.7)

Следует заметить, что за исключением count, все эти функции возвращают NULL, если для них не была выбрана ни одна строка. В частности, функция sum, не получив строк, возвращает NULL, а не 0, как можно было бы ожидать, и array_agg в этом случае возвращает NULL, а не пустой массив. Если необходимо, подставить в результат 0 или пустой массив вместо NULL можно с помощью функции coalesce.

Замечание: Булевы агрегатные функции bool_and и bool_or соответствуют стандартным SQL-агрегатам every и any или some. Что касается any и some, по стандарту их синтаксис допускает некоторую неоднозначность:

SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;

Здесь ANY можно рассматривать и как объявление подзапроса, и как агрегатную функцию, если этот подзапрос возвращает одну строку с булевым значением. Таким образом, этим агрегатным функциям нельзя было дать стандартные имена.

Замечание: Пользователи с опытом использования других СУБД SQL могут быть недовольны скоростью агрегатной функции count, когда она применяется ко всей таблице. Подобный запрос:

SELECT count(*) FROM sometable;

потребует затрат в количестве, пропорциональном размеру таблицы: PostgreSQL придётся полностью просканировать либо всю таблицу, либо один из индексов, включающий все её строки.

Агрегатные функции array_agg, json_agg, json_object_agg, string_agg и xmlagg так же, как и подобные пользовательские агрегатные функции, выдают разные по смыслу результаты в зависимости от порядка входных данных. По умолчанию порядок не определён, но его можно задать, дополнив вызов агрегатной функции предложением ORDER BY, как описано в Подразделе 4.2.7. Нужного результата также можно добиться, передав агрегатной функции результат подзапроса с сортировкой. Например:

SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

Но учтите, что этот синтаксис не соответствует стандарту SQL и не будет работать в других СУБД.

В Таблице 9-50 перечислены агрегатные функции, обычно применяемые в статистическом анализе. (Они выделены просто для того, чтобы не загромождать список наиболее популярных агрегатных функций.) В их описании под N подразумевается число входных строк, для которых входные выражения не равны NULL. Все эти функции возвращают NULL во всех случаях, когда вычисление бессмысленно, например, когда N равно 0.

Таблица 9-50. Агрегатные функции для статистических вычислений

ФункцияТип аргументаТип результатаОписание
corr(Y, X)double precisiondouble precisionкоэффициент корреляции
covar_pop(Y, X)double precisiondouble precisionковариация совокупности
covar_samp(Y, X)double precisiondouble precisionковариация выборки
regr_avgx(Y, X)double precisiondouble precisionсреднее независимой переменной (sum(X)/N)
regr_avgy(Y, X)double precisiondouble precisionсреднее зависимой переменной (sum(Y)/N)
regr_count(Y, X)double precisionbigintчисло входных строк, в которых оба выражения не NULL
regr_intercept(Y, X)double precisiondouble precisionпересечение с осью OY линии, полученной методом наименьших квадратов по данным (X, Y)
regr_r2(Y, X)double precisiondouble precisionквадрат коэффициента корреляции
regr_slope(Y, X)double precisiondouble precisionнаклон линии, полученной методом наименьших квадратов по данным (X, Y)
regr_sxx(Y, X)double precisiondouble precisionsum(X^2) - sum(X)^2/N ("сумма квадратов" независимой переменной)
regr_sxy(Y, X)double precisiondouble precisionsum(X*Y) - sum(X) * sum(Y)/N ("сумма произведений" независимых и зависимых переменных)
regr_syy(Y, X)double precisiondouble precisionsum(Y^2) - sum(Y)^2/N ("сумма квадратов" зависимой переменной)
stddev(выражение)smallint, int, bigint, real, double precision или numericdouble precision для аргументов с плавающей точкой, numeric для остальныхсохранившийся синоним stddev_samp
stddev_pop(выражение)smallint, int, bigint, real, double precision или numericdouble precision для аргументов с плавающей точкой, numeric для остальныхстандартное отклонение по генеральной совокупности входных значений
stddev_samp(выражение)smallint, int, bigint, real, double precision или numericdouble precision для аргументов с плавающей точкой, numeric для остальныхстандартное отклонение по выборке входных значений
variance(выражение)smallint, int, bigint, real, double precision или numericdouble precision для аргументов с плавающей точкой, numeric для остальныхсохранившийся синоним var_samp
var_pop(выражение)smallint, int, bigint, real, double precision или numericdouble precision для аргументов с плавающей точкой, numeric для остальныхдисперсия для генеральной совокупности входных значений (квадрат стандартного отклонения)
var_samp(выражение)smallint, int, bigint, real, double precision или numericdouble precision для аргументов с плавающей точкой, numeric для остальныхдисперсия по выборке для входных значений (квадрат отклонения по выборке)

В Таблице 9-51 показаны некоторые агрегатные функции, использующие синтаксис сортирующих агрегатных функций. Иногда такие функции функциями называют функциями "обратного распределения".

Таблица 9-51. Сортирующие агрегатные функции

ФункцияТип непосредственного аргументаТип агрегированного аргументаТип результатаОписание
mode() WITHIN GROUP (ORDER BY выражение_сортировки) любой сортируемый типтот же, что у выражения сортировкивозвращает значение, наиболее часто встречающееся во входных данных (если одинаково часто встречаются несколько значений, произвольно выбирается первое из них)
percentile_cont(дробь) WITHIN GROUP (ORDER BY выражение_сортировки)double precisiondouble precision или intervalтот же, что у выражения сортировкинепрерывный перцентиль: возвращает значение, соответствующее заданной дроби по порядку, интерполируя соседние входные значения, если необходимо
percentile_cont(дроби) WITHIN GROUP (ORDER BY выражение_сортировки)double precision[]double precision или intervalмассив типа выражения сортировкимножественный непрерывный перцентиль: возвращает массив результатов, соответствующих значениям в параметре дроби (для каждого элемента не NULL подставляется значение, соответствующее данному перцентилю)
percentile_disc(дробь) WITHIN GROUP (ORDER BY выражение_сортировки)double precisionлюбой сортируемый типтот же, что у выражения сортировкидискретный перцентиль: возвращает первое значение из входных данных, позиция которого по порядку равна или превосходит указанную дробь
percentile_disc(дроби) WITHIN GROUP (ORDER BY выражение_сортировки)double precision[]любой сортируемый типмассив типа выражения сортировкимножественный дискретный перцентиль: возвращает массив результатов, соответствующих значениям в параметре дроби (для каждого элемента не NULL подставляется входное значение, соответствующее данному перцентилю)

Все агрегатные функции, перечисленные в Таблице 9-51, игнорируют значения NULL при сортировке данных. Для функций, принимающих параметр дробь, значение этого параметра должно быть от 0 до 1; в противном случае возникает ошибка. Однако, если в этом параметре передаётся NULL, эти функции просто выдают NULL.

Все агрегатные функции, перечисленные в Таблице 9-52, связаны с одноимёнными оконными функциями, определёнными в Разделе 9.21. В каждом случае их результат — значение, которое вернула бы связанная оконная функция для "гипотетической" строки, полученной из аргументов, если бы такая строка была добавлена в сортированную группу строк, которую образуют сортированные_аргументы.

Таблица 9-52. Гипотезирующие агрегатные функции

ФункцияТип непосредственного аргументаТип агрегированного аргументаТип результатаОписание
rank(аргументы) WITHIN GROUP (ORDER BY сортированные_аргументы)VARIADIC "any"VARIADIC "any"bigintранг гипотетической строки, с пропусками повторяющихся строк
dense_rank(аргументы) WITHIN GROUP (ORDER BY сортированные_аргументы)VARIADIC "any"VARIADIC "any"bigintранг гипотетической строки, без пропусков
percent_rank(аргументы) WITHIN GROUP (ORDER BY сортированные_аргументы)VARIADIC "any"VARIADIC "any"double precisionотносительный ранг гипотетической строки, от 0 до 1
cume_dist(аргументы) WITHIN GROUP (ORDER BY сортированные_аргументы)VARIADIC "any"VARIADIC "any"double precisionотносительный ранг гипотетической строки, от 1/N до 1

Для всех этих гипотезирующих агрегатных функций непосредственные аргументы должны соответствовать (по количеству и типу) сортированным_аргументам. В отличие от встроенных агрегатных функций, они не являются строгими, то есть не отбрасывают входные строки, содержащие NULL. Значения NULL сортируются согласно правилу, указанному в предложении ORDER BY.