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

Агрегатные функции получают единственный результат из набора входных значений. Встроенные агрегатные функции общего назначения перечислены в Таблице 9.55, а статистические агрегатные функции — в Таблице 9.56. Встроенные внутригрупповые сортирующие агрегатные функции перечислены в Таблице 9.57, встроенные внутригрупповые гипотезирующие — в Таблице 9.58. Группирующие операторы, тесно связанные с агрегатными функциями, перечислены в Таблице 9.59. Особенности синтаксиса агрегатных функцией разъясняются в Подразделе 4.2.7. За дополнительной вводной информацией обратитесь к Разделу 2.7.

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

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

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

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

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

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

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

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

ФункцияТип аргументаТип результатаЧастичный режимОписание
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 precisionДаsum(X^2) - sum(X)^2/N («сумма квадратов» независимой переменной)
regr_sxy(Y, X)double precisiondouble precisionДаsum(X*Y) - sum(X) * sum(Y)/N («сумма произведений» независимых и зависимых переменных)
regr_syy(Y, X)double precisiondouble precisionДаsum(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.57 показаны некоторые агрегатные функции, использующие синтаксис сортирующих агрегатных функций. Иногда такие функции функциями называют функциями «обратного распределения».

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

ФункцияТип непосредственного аргументаТип агрегированного аргументаТип результатаЧастичный режимОписание
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.57, игнорируют значения NULL при сортировке данных. Для функций, принимающих параметр дробь, значение этого параметра должно быть от 0 до 1; в противном случае возникает ошибка. Однако если в этом параметре передаётся NULL, эти функции просто выдают NULL.

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

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

ФункцияТип непосредственного аргументаТип агрегированного аргументаТип результатаЧастичный режимОписание
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.

Таблица 9.59. Операции группировки

ФункцияТип результатаОписание
GROUPING(аргументы...)integerЦелочисленная битовая маска, показывающая, какие аргументы не вошли в текущий набор группирования

Операции группировки применяются в сочетании с наборами группирования (см. Подраздел 7.2.4) для различения результирующих строк. Аргументы операции GROUPING на самом деле не вычисляются, но они должны в точности соответствовать выражениям, заданным в предложении GROUP BY на их уровне запроса. Биты назначаются справа налево (правый аргумент отражается в младшем бите); бит равен 0, если соответствующее выражение вошло в критерий группировки набора группирования, для которого сформирована строка результата, или 1 в противном случае. Например:

=> SELECT * FROM items_sold;
 make  | model | sales
-------+-------+-------
 Foo   | GT    |  10
 Foo   | Tour  |  20
 Bar   | City  |  15
 Bar   | Sport |  5
(4 rows)

=> SELECT make, model, GROUPING(make,model), sum(sales) FROM items_sold GROUP BY ROLLUP(make,model);
 make  | model | grouping | sum
-------+-------+----------+-----
 Foo   | GT    |        0 | 10
 Foo   | Tour  |        0 | 20
 Bar   | City  |        0 | 15
 Bar   | Sport |        0 | 5
 Foo   |       |        1 | 30
 Bar   |       |        1 | 20
       |       |        3 | 50
(7 rows)