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

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

---

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

Агрегатные функции вычисляют единственный результат из набора входных значений. Встроенные универсальные агрегатные функции перечислены в Таблица 9.59 в то время как статистические агрегаты находятся в Таблица 9.60. Встроенные агрегатные функции с отсортированным набором внутри группы перечислены в Таблица 9.61 в то время как встроенные функции с гипотетизирующим набором внутри группы находятся в Таблица 9.62. Операции группирования, которые тесно связаны с агрегатными функциями, перечислены в Таблица 9.63. Особые синтаксические соображения для агрегатных функций объясняются в Раздел 4.2.7. Дополнительную вводную информацию см. в Раздел 2.7.

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

Таблица 9.59. Общие агрегатные функции

Функция Описание	Partial Mode
any_value ( anyelement ) → same as input type Возвращает произвольное значение из непустых входных значений.	Yes
array_agg ( anynonarray ) → anyarray Собирает все входные значения, включая пустые значения, в массив.	Yes
array_agg ( anyarray ) → anyarray Объединяет все входные массивы в массив одного более высокого измерения. (Входные данные должны иметь одинаковую размерность и не могут быть пустыми или нулевыми).	Yes
avg ( smallint ) → numeric avg ( integer ) → numeric avg ( bigint ) → numeric avg ( numeric ) → numeric avg ( real ) → double precision avg ( вещественное ) → double precision avg ( interval ) → interval Вычисляет среднее значение (арифметическое среднее) всех ненулевых входных значений.	Yes
bit_and ( smallint ) → smallint bit_and ( integer ) → integer bit_and ( bigint ) → bigint bit_and ( bit ) → bit Вычисляет побитовое И всех ненулевых входных значений.	Yes
bit_or ( smallint ) → smallint bit_or ( integer ) → integer bit_or ( bigint ) → bigint bit_or ( bit ) → bit Вычисляет побитовое ИЛИ всех ненулевых входных значений.	Yes
bit_xor ( smallint ) → smallint bit_xor ( integer ) → integer bit_xor ( bigint ) → bigint bit_xor ( bit ) → bit Вычисляет побитовое исключающее ИЛИ всех ненулевых входных значений. Может быть полезным в качестве контрольной суммы для неупорядоченного набора значений.	Yes
bool_and ( boolean ) → boolean Возвращает true, если все ненулевые входные значения истинны, в противном случае возвращает false.	Yes
bool_or ( boolean ) → boolean Возвращает true, если любое ненулевое входное значение является true, в противном случае возвращает false.	Yes
count ( * ) → bigint Вычисляет количество входных строк.	Yes
count ( "any" ) → bigint Вычисляет количество входных строк, в которых входное значение не является нулевым.	Yes
every ( boolean ) → boolean Это эквивалент стандарта SQL к bool_and.	Yes
json_agg ( anyelement ) → json jsonb_agg ( anyelement ) → jsonb Собирает все входные значения, включая пустые значения, в JSON-массив. Значения преобразуются в JSON с помощью функций to_json или to_jsonb.	No
json_agg_strict ( anyelement ) → json jsonb_agg_strict ( anyelement ) → jsonb Собирает все входные значения, пропуская null, в массив JSON. Значения преобразуются в JSON в соответствии с to_json или to_jsonb.	No
json_arrayagg ( [ value_expression ] [ ORDER BY sort_expression ] [ { NULL | ABSENT } ON NULL ] [ RETURNING data_type [FORMAT JSON [ ENCODING UTF8 ]] ]) Ведет себя так же, как json_array, но как агрегатная функция, поэтому принимает только один параметр value_expression. Если указано ABSENT ON NULL, любые NULL значения будут пропущены. Если указано ORDER BY, элементы будут появляться в массиве в этом порядке, а не в порядке ввода. SELECT json_arrayagg(v) FROM (VALUES(2),(1)) t(v) → [2, 1]	No
json_objectagg ( [ { key_expression { VALUE | ':' } value_expression } ] [ { NULL | ABSENT } ON NULL ] [{ WITH | WITHOUT } UNIQUE [ KEYS ]] [ RETURNING data_type [FORMAT JSON [ ENCODING UTF8 ]] ]) Ведет себя как json_object , но как агрегатная функция, поэтому она принимает только один key_expression и один value_expression параметр. 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" }	No
json_object_agg ( key "any", value "any" ) → json jsonb_object_agg ( key "any", value "any" ) → jsonb Собирает все пары ключ/значение в объект JSON. Аргументы ключа приводятся к тексту; аргументы значения конвертируются в соответствии с to_json или to_jsonb. Значения могут быть null, но ключи не могут.	No
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, то запись пропускается,	No
json_object_agg_unique ( key "any", value "any" ) → json jsonb_object_agg_unique ( key "any", value "any" ) → jsonb Собирает все пары ключ/значение в объект JSON. Аргументы ключей приводятся к тексту; аргументы значений конвертируются в соответствии с to_json или to_jsonb. Значения могут быть null, но ключи не могут. Если есть дублирующийся ключ, возникает ошибка.	No
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. key не может быть null. Если value равно null, то запись пропускается. Если есть дублирующийся ключ, возникает ошибка.	No
max ( see text ) → same as input type Вычисляет максимум из ненулевых входных значений. Доступно для любого числового, строкового, дата/время или перечислимого типа, а также для типов inet, interval, money, oid, pg_lsn, tid, xid8 и массивов любого из этих типов.	Yes
min ( see text ) → same as input type Вычисляет минимальное значение из ненулевых входных значений. Доступно для любого числового, строкового, даты/времени или перечислимого типа, а также для типов inet, interval, money, oid, pg_lsn, tid, xid8 и массивов любого из этих типов.	Yes
range_agg ( value anyrange ) → anymultirange range_agg ( value anymultirange ) → anymultirange Вычисляет объединение ненулевых входных значений.	No
range_intersect_agg ( value anyrange ) → anyrange range_intersect_agg ( value anymultirange ) → anymultirange Вычисляет пересечение ненулевых входных значений.	No
string_agg ( value text, delimiter text ) → text string_agg ( value bytea, delimiter bytea ) → bytea Конкатенирует ненулевые значения ввода в строку. Каждое значение после первого предшествует соответствующему delimiter (если он не является нулевым).	Yes
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 Вычисляет сумму ненулевых входных значений.	Yes
xmlagg ( xml ) → xml Соединяет ненулевые значения входных XML (см. Раздел 9.15.1.7).	No

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

Агрегатные функции 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. Tantor SE-1C поддерживает every, но не any или some, потому что в стандартном синтаксисе есть неоднозначность:

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

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

Примечание

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

SELECT count(*) FROM sometable;

потребуется усилие, пропорциональное размеру таблицы: Tantor SE-1C будет необходимо просканировать либо всю таблицу, либо полностью индекс, который включает все строки в таблице.

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

Таблица 9.60. Агрегатные функции для статистики

Функция Описание	Partial Mode
corr ( Y double precision, X double precision ) → double precision Вычисляет коэффициент корреляции.	Yes
covar_pop ( Y double precision, X double precision ) → double precision Вычисляет ковариацию популяции.	Yes
covar_samp ( Y double precision, X double precision ) → double precision Вычисляет выборочную ковариацию.	Yes
regr_avgx ( Y double precision, X double precision ) → double precision Вычисляет среднее значение независимой переменной, sum(X)/N.	Yes
regr_avgy ( Y double precision, X double precision ) → double precision Вычисляет среднее значение зависимой переменной, sum(Y)/N.	Yes
regr_count ( Y double precision, X double precision ) → bigint Вычисляет количество строк, в которых оба входных значения не являются нулевыми.	Yes
regr_intercept ( Y double precision, X double precision ) → double precision Вычисляет y-перехват линейного уравнения наименьших квадратов, определенного парами (X, Y).	Yes
regr_r2 ( Y double precision, X double precision ) → double precision Вычисляет квадрат коэффициента корреляции.	Yes
regr_slope ( Y double precision, X double precision ) → double precision Вычисляет наклон линейного уравнения наименьших квадратов, определенного парами (X, Y).	Yes
regr_sxx ( Y double precision, X double precision ) → double precision Вычисляет “сумму квадратов” независимой переменной, sum(X^2) - sum(X)^2/N.	Yes
regr_sxy ( Y double precision, X double precision ) → double precision Вычисляет “сумму произведений” независимых переменных, зависящих от времени, sum(X*Y) - sum(X) * sum(Y)/N.	Yes
regr_syy ( Y double precision, X double precision ) → double precision Вычисляет “сумму квадратов” зависимой переменной, sum(Y^2) - sum(Y)^2/N.	Yes
stddev ( numeric_type ) → double precision для real или double precision, в противном случае numeric Это исторический псевдоним для stddev_samp.	Yes
stddev_pop ( numeric_type ) → double precision для real или double precision, в противном случае numeric Вычисляет стандартное отклонение популяции входных значений.	Yes
stddev_samp ( numeric_type ) → double precision для real или double precision, в противном случае numeric Вычисляет выборочное стандартное отклонение входных значений.	Yes
variance ( numeric_type ) → double precision для real или double precision, в противном случае numeric Это исторический псевдоним для var_samp.	Yes
var_pop ( numeric_type ) → double precision для real или double precision, в противном случае numeric Вычисляет дисперсию популяции входных значений (квадрат популяционного стандартного отклонения).	Yes
var_samp ( numeric_type ) → double precision для real или double precision, в противном случае numeric Вычисляет выборочную дисперсию входных значений (квадрат выборочного стандартного отклонения).	Yes

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

Таблица 9.61. Агрегатные функции с отсортированным набором

Функция Описание	Partial Mode
mode () WITHIN GROUP ( ORDER BY anyelement ) → anyelement Вычисляет режим, наиболее часто встречающееся значение агрегированного аргумента (произвольно выбирая первое значение, если есть несколько одинаково часто встречающихся значений). Агрегированный аргумент должен быть сортируемого типа.	No
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 в упорядоченном наборе агрегированных значений аргументов. При необходимости будет произведена интерполяция между соседними входными элементами.	No
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, в котором каждый ненулевой элемент заменяется (возможно, интерполированным) значением, соответствующим этому процентилю.	No
percentile_disc ( fraction double precision ) WITHIN GROUP ( ORDER BY anyelement ) → anyelement Вычисляет дискретный перцентиль, первое значение в упорядоченном наборе агрегированных значений аргумента, позиция которого в упорядочении равна или превышает указанную fraction. Агрегированный аргумент должен быть сортируемого типа.	No
percentile_disc ( fractions double precision[] ) WITHIN GROUP ( ORDER BY anyelement ) → anyarray Вычисляет несколько дискретных процентилей. Результатом является массив той же размерности, что и параметр fractions, в котором каждый ненулевой элемент заменяется входным значением, соответствующим этому процентилю. Агрегированный аргумент должен быть сортируемого типа.	No

Каждая из агрегатных функций “hypothetical-set”, перечисленных в Таблица 9.62, связана с оконной функцией с тем же именем, определенным в Раздел 9.22. В каждом случае результат агрегатной функции является значением, которое связанная оконная функция вернула бы для “hypothetical” строки, созданной из args, если бы такая строка была добавлена в отсортированную группу строк, представленную sorted_args. Для каждой из этих функций список прямых аргументов, указанных в args, должен соответствовать количеству и типам агрегированных аргументов, указанных в sorted_args. В отличие от большинства встроенных агрегатных функций, эти агрегатные функции не являются строгими, то есть они не отбрасывают входные строки, содержащие значения null. Значения null сортируются в соответствии с правилом, указанным в предложении ORDER BY.

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

Функция Описание	Partial Mode
rank ( args ) WITHIN GROUP ( ORDER BY sorted_args ) → bigint Вычисляет порядковый номер гипотетической строки с пропусками, то есть номер строки первой строки в ее группе.	No
dense_rank ( args ) WITHIN GROUP ( ORDER BY sorted_args ) → bigint Вычисляет ранг гипотетической строки без пропусков; эта функция фактически подсчитывает группы одноранговых элементов.	No
percent_rank ( args ) WITHIN GROUP ( ORDER BY sorted_args ) → double precision Вычисляет относительный ранг гипотетической строки, то есть (rank - 1) / (общее количество строк - 1). Таким образом, значение находится в диапазоне от 0 до 1 включительно.	No
cume_dist ( args ) WITHIN GROUP ( ORDER BY sorted_args ) → double precision Вычисляет накопленное распределение, то есть (количество строк, предшествующих или равных гипотетической строке) / (общее количество строк). Значение, таким образом, варьируется от 1/N до 1.	No

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

Функция Описание

GROUPING ( group_by_expression(s) ) → 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 не были сгруппированы в последней строке (которая, следовательно, является агрегатом по всем входным строкам).

---

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