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

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. Оконные функции