9.21. Агрегатные функции
Пред. НаверхГлава 9. Функции и операторыНачало След.

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

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

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

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

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

Функция

Описание

Частичный режим

any_value ( anyelement ) → тот же тип, что на входе

Возвращает произвольное значение из входных значений, отличных от NULL.

Да

array_agg ( anynonarray ORDER BY input_sort_columns ) → anyarray

Собирает все входные значения, включая NULL, в массив.

Да

array_agg ( anyarray ORDER BY input_sort_columns ) → anyarray

Собирает все входные массивы в массив с размерностью больше на один. (Входные массивы должны иметь одинаковую размерность и не могут быть пустыми или равны NULL).

Да

avg ( smallint ) → numeric

avg ( integer ) → numeric

avg ( bigint ) → numeric

avg ( numeric ) → numeric

avg ( real ) → double precision

avg ( double precision ) → double precision

avg ( interval ) → interval

Вычисляет арифметическое среднее для всех входных значений, отличных от NULL.

Да

bit_and ( smallint ) → smallint

bit_and ( integer ) → integer

bit_and ( bigint ) → bigint

bit_and ( bit ) → bit

Вычисляет побитовое И для всех входных значений, отличных от NULL.

Да

bit_or ( smallint ) → smallint

bit_or ( integer ) → integer

bit_or ( bigint ) → bigint

bit_or ( bit ) → bit

Вычисляет побитовое ИЛИ для всех входных значений, отличных от NULL.

Да

bit_xor ( smallint ) → smallint

bit_xor ( integer ) → integer

bit_xor ( bigint ) → bigint

bit_xor ( bit ) → bit

Вычисляет побитовое исключающее ИЛИ для всех входных значений, отличных от NULL. Может быть полезна для вычисления контрольной суммы неупорядоченного набора значений.

Да

bool_and ( boolean ) → boolean

Возвращает true, если все входные значения, отличные от NULL, равны true, и false в противном случае.

Да

bool_or ( boolean ) → boolean

Возвращает true, если хотя бы одно входное значение, отличное от NULL, равно true, и false в противном случае.

Да

count ( * ) → bigint

Выдаёт количество входных строк.

Да

count ( "any" ) → bigint

Выдаёт количество входных строк, в которых входное значение отлично от NULL.

Да

every ( boolean ) → boolean

Это соответствующий стандарту SQL аналог bool_and.

Да

json_agg ( anyelement ORDER BY input_sort_columns ) → json

jsonb_agg ( anyelement ORDER BY input_sort_columns ) → jsonb

Собирает все входные значения, включая NULL, в JSON-массив. Значения преобразуются в JSON методом to_json или to_jsonb.

Нет

json_agg_strict ( anyelement ) → json

jsonb_agg_strict ( anyelement ) → jsonb

Собирает все входные значения, исключая NULL, в JSON-массив. Значения преобразуются в JSON методом to_json или to_jsonb.

Нет

json_arrayagg ( [выражение_значения] [ORDER BY выражение_сортировки] [ { NULL | ABSENT } ON NULL] [RETURNING тип_данных [FORMAT JSON [ENCODING UTF8]]])

Ведёт себя так же, как функция json_array, но агрегатная, поэтому принимает только один параметр выражение_значения. С указанием ABSENT ON NULL любые значения NULL пропускаются. С указанием ORDER BY элементы добавляются в массив в заданном порядке, а не в том порядке, в котором они вводятся.

SELECT json_arrayagg(v) FROM (VALUES(2),(1)) t(v) → [2, 1]

Нет

json_objectagg ( [ { выражение_ключа { VALUE | ':' } выражение_значения } ] [ { NULL | ABSENT } ON NULL] [ { WITH | WITHOUT } UNIQUE [KEYS]] [RETURNING тип_данных [FORMAT JSON [ENCODING UTF8]]])

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

SELECT json_objectagg(k:v) FROM (VALUES ('a'::text,current_date),('b',current_date + 1)) AS t(k,v) → { "a" : "2022-05-10", "b" : "2022-05-11" }

Нет

json_object_agg ( key "any", value "any" ORDER BY input_sort_columns ) → json

jsonb_object_agg ( key "any", value "any" ORDER BY input_sort_columns ) → jsonb

Собирает все пары ключ/значение в JSON-объект. Аргументы-ключи приводятся к текстовому типу, а значения преобразуются методом to_json или to_jsonb. В качестве значений, но не ключей, могут передаваться null.

Нет

json_object_agg_strict ( key "any", value "any" ) → json

jsonb_object_agg_strict ( key "any", value "any" ) → jsonb

Собирает все пары ключ/значение в JSON-объект. Аргументы-ключи приводятся к текстовому типу, а значения преобразуются методом to_json или to_jsonb. Аргумент key должен быть отличен от NULL. Если value равно NULL, запись пропускается.

Нет

json_object_agg_unique ( key "any", value "any" ) → json

jsonb_object_agg_unique ( key "any", value "any" ) → jsonb

Собирает все пары ключ/значение в JSON-объект. Аргументы-ключи приводятся к текстовому типу, а значения преобразуются методом to_json или to_jsonb. В качестве значений, но не ключей, могут передаваться null. При наличии повторяющихся ключей выдаётся ошибка.

Нет

json_object_agg_unique_strict ( key "any", value "any" ) → json

jsonb_object_agg_unique_strict ( key "any", value "any" ) → jsonb

Собирает все пары ключ/значение в JSON-объект. Аргументы-ключи приводятся к текстовому типу, а значения преобразуются методом to_json или to_jsonb. В качестве значений, но не ключей, могут передаваться null. Если value равно null, запись пропускается. Если есть повторяющийся ключ, выдаётся ошибка.

Нет

max ( см. описание ) → тот же тип, что на входе

Вычисляет максимальное из всех значений, отличных от NULL. Имеется для всех числовых и строковых типов, типов-перечислений и даты/времени, типов bytea, inet, interval, money, oid, pg_lsn, tid, xid8, а также массивов и составных типов, содержащих сортируемые типы данных.

Да

min ( см. описание ) → тот же тип, что на входе

Вычисляет минимальное из всех значений, отличных от NULL. Имеется для всех числовых и строковых типов, типов-перечислений и даты/времени, типов bytea, inet, interval, money, oid, pg_lsn, tid, xid8, а также массивов и составных типов, содержащих сортируемые типы данных.

Да

range_agg ( value anyrange ) → anymultirange

range_agg ( value anymultirange ) → anymultirange

Вычисляет объединение всех входных значений, отличных от NULL.

Нет

range_intersect_agg ( value anyrange ) → anyrange

range_intersect_agg ( value anymultirange ) → anymultirange

Вычисляет пересечение всех входных значений, отличных от NULL.

Нет

string_agg ( value text, delimiter text ) → text

string_agg ( value bytea, delimiter bytea ORDER BY input_sort_columns ) → bytea

Соединяет все входные значения, отличные от NULL, в строку. Перед каждым значением, кроме первого, добавляется соответствующий разделитель, заданный параметром delimiter (если он отличен от NULL).

Да

sum ( smallint ) → bigint

sum ( integer ) → bigint

sum ( bigint ) → numeric

sum ( numeric ) → numeric

sum ( real ) → real

sum ( double precision ) → double precision

sum ( interval ) → interval

sum ( money ) → money

Вычисляет сумму всех входных значений, отличных от NULL.

Да

xmlagg ( xml ORDER BY input_sort_columns ) → xml

Соединяет вместе входные XML-значения, отличные от NULL (см. также Подраздел 9.15.1.8).

Нет


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

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

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

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

Примечание

Логические агрегатные функции bool_and и bool_or равнозначны описанным в стандарте SQL агрегатам every и any или some. PostgreSQL поддерживает every, но не any/some, так как синтаксис, описанный в стандарте, допускает неоднозначность: 

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

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

Примечание

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

SELECT count(*) FROM sometable;

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

В Таблице 9.63 перечислены агрегатные функции, обычно применяемые в статистическом анализе. (Они выделены просто для того, чтобы не загромождать список наиболее популярных агрегатных функций.) Функции, показанные как принимающие числовой_тип, существуют для типов smallint, integer, bigint, numeric, real, и double precision. В их описании под N подразумевается число входных строк, для которых все входные выражения отличны от NULL. Во всех случаях, когда вычисление не имеет смысла, например, когда N равно нулю, возвращается null.

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

Функция

Описание

Частичный режим

corr ( Y double precision, X double precision ) → double precision

Вычисляет коэффициент корреляции.

Да

covar_pop ( Y double precision, X double precision ) → double precision

Вычисляет ковариацию совокупности.

Да

covar_samp ( Y double precision, X double precision ) → double precision

Вычисляет ковариацию выборки.

Да

regr_avgx ( Y double precision, X double precision ) → double precision

Вычисляет среднее для независимой переменной, sum(X)/N.

Да

regr_avgy ( Y double precision, X double precision ) → double precision

Вычисляет среднее для зависимой переменной, sum(Y)/N.

Да

regr_count ( Y double precision, X double precision ) → bigint

Вычисляет число входных строк, в которых оба выражения отличны от NULL.

Да

regr_intercept ( Y double precision, X double precision ) → double precision

Вычисляет пересечение с осью OY линии, полученной методом наименьших квадратов по парам (X, Y).

Да

regr_r2 ( Y double precision, X double precision ) → double precision

Вычисляет квадрат коэффициента корреляции.

Да

regr_slope ( Y double precision, X double precision ) → double precision

Вычисляет наклон линии, полученной методом наименьших квадратов по парам (X, Y).

Да

regr_sxx ( Y double precision, X double precision ) → double precision

Вычисляет «сумму квадратов» независимой переменной, sum(X^2) - sum(X)^2/N.

Да

regr_sxy ( Y double precision, X double precision ) → double precision

Вычисляет «сумму произведений» независимой и зависимой переменных, sum(X*Y) - sum(X) * sum(Y)/N.

Да

regr_syy ( Y double precision, X double precision ) → double precision

Вычисляет «сумму квадратов» зависимой переменной, sum(Y^2) - sum(Y)^2/N.

Да

stddev ( числовой_тип ) → double precision для real или double precision, иначе numeric

Сохранившийся синоним stddev_samp.

Да

stddev_pop ( numeric_type ) → double precision для real или double precision, иначе numeric

Вычисляет стандартное отклонение по генеральной совокупности входных значений.

Да

stddev_samp ( числовой_тип ) → double precision для real или double precision, иначе numeric

Вычисляет стандартное отклонение по выборке входных значений.

Да

variance ( numeric_type ) → double precision для real или double precision, иначе numeric

Сохранившийся синоним var_samp.

Да

var_pop ( numeric_type ) → double precision для real или double precision, иначе numeric

Вычисляет дисперсию для генеральной совокупности входных значений (квадрат стандартного отклонения).

Да

var_samp ( числовой_тип ) → double precision для real или double precision, иначе numeric

Вычисляет дисперсию по выборке для входных значений (квадрат отклонения по выборке).

Да


В Таблица 9.64 показаны некоторые агрегатные функции, использующие синтаксис сортирующих агрегатных функций. Эти функции иногда называются функциями «обратного распределения». Их агрегированные входные данные формируются указанием ORDER BY, а кроме того они могут принимать не агрегируемый непосредственный аргумент, который вычисляется только один раз. Все эти функции игнорируют значения NULL в агрегируемых данных. Для тех функций, которые принимают параметр fraction, его значение должно быть между 0 и 1; в противном случае выдаётся ошибка. Однако если значение fraction — NULL, они выдают NULL в результате.

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

Функция

Описание

Частичный режим

mode () WITHIN GROUP ( ORDER BY anyelement ) → anyelement

Вычисляет моду — наиболее часто встречающееся в агрегируемом аргументе значение (если одинаково часто встречаются несколько значений, произвольно выбирается первое из них). Агрегируемый аргумент должен быть сортируемого типа.

Нет

percentile_cont ( fraction double precision ) WITHIN GROUP ( ORDER BY double precision ) → double precision

percentile_cont ( fraction double precision ) WITHIN GROUP ( ORDER BY interval ) → interval

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

Нет

percentile_cont ( fractions double precision[] ) WITHIN GROUP ( ORDER BY double precision ) → double precision[]

percentile_cont ( fractions double precision[] ) WITHIN GROUP ( ORDER BY interval ) → interval[]

Вычисляет множественные непрерывные процентили. Возвращает массив той же размерности, что имеет параметр fractions, в котором каждый отличный от NULL элемент заменяется соответствующим данному перцентилю значением (возможно интерполированным).

Нет

percentile_disc ( fraction double precision ) WITHIN GROUP ( ORDER BY anyelement ) → anyelement

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

Нет

percentile_disc ( fractions double precision[] ) WITHIN GROUP ( ORDER BY anyelement ) → anyarray

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

Нет


Все «гипотезирующие» агрегатные функции, перечисленные в Таблице 9.65, связаны с одноимёнными оконными функциями, определёнными в Разделе 9.22. В каждом случае их результат — значение, которое бы вернула связанная оконная функция для «гипотетической» строки, полученной из аргументов, если бы такая строка была добавлена в сортированную группу строк, которую образуют сортирующие_аргументы. Для всех этих функций список непосредственных аргументов, переданный в качестве аргументов, по числу и типу элементов должен соответствовать списку, передаваемому в качестве сортирующих_аргументов. В отличие от большинства встроенных агрегатов, данные агрегаты не являются строгими, то есть они не игнорируют строки, содержащие NULL. Значения NULL сортируются по правилу, заданному в предложении ORDER BY.

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

Функция

Описание

Частичный режим

rank ( аргументы ) WITHIN GROUP ( ORDER BY сортирующие_аргументы ) → bigint

Вычисляет ранг гипотетической строки с пропусками, то есть номер первой родственной ей строки.

Нет

dense_rank ( args ) WITHIN GROUP ( ORDER BY сортируемые_аргументы ) → bigint

Вычисляет ранг гипотетической строки без пропусков; по сути эта функция считает группы родственных строк.

Нет

percent_rank ( аргументы ) WITHIN GROUP ( ORDER BY сортируемые_аргументы ) → double precision

Вычисляет относительный ранг гипотетической строки, то есть (rank - 1) / (общее число строк - 1). Таким образом, результат лежит в интервале от 0 до 1, включая границы.

Нет

cume_dist ( аргументы ) WITHIN GROUP ( ORDER BY сортируемые_аргументы ) → double precision

Вычисляет кумулятивное распределение, то есть (число строк, предшествующих или родственных гипотетической строке) / (общее число строк). Результат лежит в интервале от 1/N до 1.

Нет


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

Функция

Описание

GROUPING ( выражения_group_by ) → integer

Выдаёт битовую маску, показывающую, какие выражения GROUP BY не вошли в текущий набор группирования. Биты назначаются справа налево, то есть самому правому аргументу соответствует самый младший бит; бит равен 0, если соответствующее выражение вошло в критерий группировки набора группирования, для которого сформирована текущая строка результата, или 1 в противном случае.


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

=> 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)

Здесь значение grouping, равное 0 в первых четырёх строках, показывает, что эти строки были сгруппированы обычным образом по обоим группирующим столбцам. Значение 1 в двух предпоследних строках показывает, что столбец model не был группирующим, а значение 3 в последней строке показывает, что при группировании не учитывались ни make, ни model, то есть агрегирование выполнялось по всем входным строкам.


Пред. Наверх След.
9.20. Диапазонные/мультидиапазонные функции и операторы Начало 9.22. Оконные функции

Чтобы сделать работу с сайтом удобнее, мы используем cookie и аналитический сервис «Яндекс.Метрика». Продолжая пользоваться сайтом, вы соглашаетесь с их использованием.