9.21. Агрегатные функции #
Агрегатные функции получают единственный результат из набора входных значений. Встроенные агрегатные функции общего назначения перечислены в Таблице 9.59, а статистические агрегатные функции — в Таблице 9.60. Встроенные внутригрупповые сортирующие агрегатные функции перечислены в Таблице 9.61, встроенные внутригрупповые гипотезирующие — в Таблице 9.62. Группирующие операторы, тесно связанные с агрегатными функциями, перечислены в Таблице 9.63. Особенности синтаксиса агрегатных функцией разъясняются в Подразделе 4.2.7. За дополнительной вводной информацией обратитесь к Разделу 2.7.
Агрегатные функции, поддерживающие частичный режим, являются кандидатами на участие в различных оптимизациях, например, в параллельном агрегировании.
Таблица 9.59. Агрегатные функции общего назначения
Функция Описание | Частичный режим |
|---|
any_value ( anyelement ) → тот же тип, что на входе Возвращает произвольное значение из входных значений, отличных от NULL. | Да |
array_agg ( anynonarray ) → anyarray Собирает все входные значения, включая NULL, в массив. | Да |
array_agg ( anyarray ) → 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 ) → json jsonb_agg ( anyelement ) → 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" ) → json jsonb_object_agg ( key "any", value "any" ) → 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. Имеется для всех числовых и строковых типов, типов-перечислений и даты/времени, а также типов inet, interval, money, oid, pg_lsn, tid, xid8 и массивов с элементами этих типов. | Да |
min ( см. описание ) → тот же тип, что на входе Вычисляет минимальное из всех значений, отличных от NULL. Имеется для всех числовых и строковых типов, типов-перечислений и даты/времени, а также типов 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 ) → 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 ) → xml Соединяет вместе входные XML-значения, отличные от NULL (см. также Подраздел 9.15.1.7). | Нет |
Следует заметить, что за исключением 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.60 перечислены агрегатные функции, обычно применяемые в статистическом анализе. (Они выделены просто для того, чтобы не загромождать список наиболее популярных агрегатных функций.) Функции, показанные как принимающие числовой_тип, существуют для типов smallint, integer, bigint, numeric, real, и double precision. В их описании под N подразумевается число входных строк, для которых все входные выражения отличны от NULL. Во всех случаях, когда вычисление не имеет смысла, например, когда N равно нулю, возвращается null.
Таблица 9.60. Агрегатные функции для статистических вычислений
Функция Описание | Частичный режим |
|---|
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.61 показаны некоторые агрегатные функции, использующие синтаксис сортирующих агрегатных функций. Эти функции иногда называются функциями «обратного распределения». Их агрегированные входные данные формируются указанием ORDER BY, а кроме того они могут принимать не агрегируемый непосредственный аргумент, который вычисляется только один раз. Все эти функции игнорируют значения NULL в агрегируемых данных. Для тех функций, которые принимают параметр fraction, его значение должно быть между 0 и 1; в противном случае выдаётся ошибка. Однако если значение fraction — NULL, они выдают NULL в результате.
Таблица 9.61. Сортирующие агрегатные функции
Функция Описание | Частичный режим |
|---|
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.62, связаны с одноимёнными оконными функциями, определёнными в Разделе 9.22. В каждом случае их результат — значение, которое бы вернула связанная оконная функция для «гипотетической» строки, полученной из аргументов, если бы такая строка была добавлена в сортированную группу строк, которую образуют сортирующие_аргументы. Для всех этих функций список непосредственных аргументов, переданный в качестве аргументов, по числу и типу элементов должен соответствовать списку, передаваемому в качестве сортирующих_аргументов. В отличие от большинства встроенных агрегатов, данные агрегаты не являются строгими, то есть они не игнорируют строки, содержащие NULL. Значения NULL сортируются по правилу, заданному в предложении ORDER BY.
Таблица 9.62. Гипотезирующие агрегатные функции
Функция Описание | Частичный режим |
|---|
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.63. Операции группировки
Функция Описание |
|---|
GROUPING ( выражения_group_by ) → integer Выдаёт битовую маску, показывающую, какие выражения GROUP BY не вошли в текущий набор группирования. Биты назначаются справа налево, то есть самому правому аргументу соответствует самый младший бит; бит равен 0, если соответствующее выражение вошло в критерий группировки набора группирования, для которого сформирована текущая строка результата, или 1 в противном случае. |
Операции группировки, показанные в Таблице 9.63, применяются в сочетании с наборами группирования (см. Подраздел 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, то есть агрегирование выполнялось по всем входным строкам.