Агрегатные функции
Агрегатные функции вычисляют единственный результат из набора входных значений. Встроенные агрегатные функции общего назначения перечислены в подразделе Агрегатные функции общего назначения, а статистические агрегаты — в подразделе Агрегатные функции для статистики. Встроенные внутригрупповые упорядочивающие агрегатные функции перечислены в подразделе Упорядочивающие агрегатные функции, а встроенные внутригрупповые гипотезирующие — в подразделе Гипотезирующие агрегатные функции. Операции группировки, тесно связанные с агрегатными функциями, перечислены в подразделе Операции группировки. Особые рекомендации по синтаксису для агрегатных функций приведены в подразделе Агрегатные выражения. Дополнительную вводную информацию см. в разделе Агрегатные функции.
Агрегатные функции, поддерживающие частичный режим, подходят для применения в различных оптимизациях, например, в параллельном агрегировании.
Агрегатные функции общего назначения
any_value
any_value ( anyelement ) → тот же тип, что и на входе
Возвращает произвольное значение из входных данных, отличных от NULL.
Поддерживает частичный режим.
array_agg
array_agg ( anynonarray ) → anyarray
Собирает всех входные значения, включая NULL, в массив.
Поддерживает частичный режим.
array_agg ( anyarray ) → anyarray
Конкатенирует все входные массивы в массив с размерностью больше на один. (Входные
массивы должны иметь одинаковую размерность и не могут быть пустыми или равны NULL.)
Поддерживает частичный режим.
avg
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
bit_and ( smallint ) → smallint
bit_and ( integer ) → integer
bit_and ( bigint ) → bigint
bit_and ( bit ) → bit
Вычисляет побитовое И для всех входных значений, отличных от NULL.
Поддерживает частичный режим.
bit_or
bit_or ( smallint ) → smallint
bit_or ( integer ) → integer
bit_or ( bigint ) → bigint
bit_or ( bit ) → bit
Вычисляет побитовое ИЛИ для всех входных значений, отличных от NULL.
Поддерживает частичный режим.
bit_xor
bit_xor ( smallint ) → smallint
bit_xor ( integer ) → integer
bit_xor ( bigint ) → bigint
bit_xor ( bit ) → bit
Вычисляет побитовое исключающее ИЛИ для всех входных значений, отличных от NULL.
Может быть полезна в качестве контрольной суммы для неупорядоченного набора
значений.
Поддерживает частичный режим.
bool_and
bool_and ( boolean ) → boolean
Возвращает true, если все отличные от NULL входные значения равны true, в
противном случае — false.
Поддерживает частичный режим.
bool_or
bool_or ( boolean ) → boolean
Возвращает true, если какое-либо отличное от NULL входное значение равно true,
в противном случае — false.
Поддерживает частичный режим.
count
count ( * ) → bigint
Вычисляет количество входных строк.
Поддерживает частичный режим.
count ( "any" ) → bigint
Вычисляет количество входных строк, в которых входное значение отлично от NULL.
Поддерживает частичный режим.
every
every ( boolean ) → boolean
Это соответствующий стандарту SQL аналог bool_and.
Поддерживает частичный режим.
json_agg
json_agg ( anyelement ) → json
jsonb_agg ( anyelement ) → jsonb
Собирает все входные значения, включая NULL, в массив JSON. Значения преобразуются
в JSON методом to_json или to_jsonb.
Не поддерживает частичный режим.
json(b)_agg_strict
json_agg_strict ( anyelement ) → json
jsonb_agg_strict ( anyelement ) → jsonb
Собирает все входные значения, пропуская NULL, в массив JSON. Значения преобразуются
в JSON методом to_json или to_jsonb.
Не поддерживает частичный режим.
json_arrayagg
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
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
json_object_agg ( ключ"any",значение "any" ) → json
jsonb_object_agg ( ключ"any",значение "any" ) → jsonb
Собирает все пары ключ/значение в объект JSON. Аргументы-ключи приводятся к
текстовому типу; аргументы-значения преобразуются методом to_json или
to_jsonb. Значения могут быть NULL, но ключи — нет.
Не поддерживает частичный режим.
json(b)_object_agg_strict
json_object_agg_strict ( ключ"any",значение "any" ) → json
jsonb_object_agg_strict ( ключ"any",значение "any" ) → jsonb
Собирает все пары ключ/значение в объект JSON. Аргументы-ключи приводятся к
текстовому типу; аргументы-значения преобразуются методом to_json или
to_jsonb. ключ не может быть равен NULL. Если значение равно NULL,
эта запись пропускается.
Не поддерживает частичный режим.
json(b)_object_agg_unique
json_object_agg_unique ( ключ"any",значение "any" ) → json
jsonb_object_agg_unique ( ключ"any",значение "any" ) → jsonb
Собирает все пары ключ/значение в объект JSON. Аргументы-ключи приводятся к
текстовому типу; аргументы-значения преобразуются методом to_json или
to_jsonb. Значения могут быть равны NULL, но ключи — нет. Если у ключа есть
дубликат, выдается ошибка.
Не поддерживает частичный режим.
json_object_agg_unique_strict
json_object_agg_unique_strict ( ключ"any",значение "any" ) → json
jsonb_object_agg_unique_strict ( ключ"any",значение "any" ) → jsonb
Собирает все пары ключ/значение в объект JSON. Аргументы-ключи приводятся к
текстовому типу; аргументы-значения преобразуются методом to_json или
to_jsonb. ключ не может быть равен NULL. Если значение равно NULL,
эта запись пропускается. Если у ключа есть дубликат, выдается ошибка.
Не поддерживает частичный режим.
max
max ( см. описание) →тот же тип, что и на входе
Вычисляет максимальное из входных значений, отличных от NULL. Имеется для всех
числовых, строковых, перечислимых типов и типов даты/времени, а также для типов
inet, interval, money, oid, pg_lsn, tid, xid8 и массивов любого
из этих типов.
Поддерживает частичный режим.
min
min ( см. описание) →тот же тип, что и на входе
Вычисляет минимальное из входных значений, отличных от NULL. Имеется для всех
числовых, строковых, перечислимых типов и типов даты/времени, а также для типов
inet, interval, money, oid, pg_lsn, tid, xid8 и массивов любого
из этих типов.
Поддерживает частичный режим.
range_agg
range_agg ( значение anyrange ) → anymultirange
range_agg ( значение anymultirange ) → anymultirange
Вычисляет объединение входных значений, отличных от NULL.
Не поддерживает частичный режим.
range_intersect_agg
range_intersect_agg ( значение anyrange ) → anyrange
range_intersect_agg ( значение anymultirange ) → anymultirange
Вычисляет пересечение входных значений, отличных от NULL.
Не поддерживает частичный режим.
string_agg
string_agg ( значениеtext,разделитель text ) → text
string_agg ( значениеbytea,разделитель bytea ) → bytea
Конкатенирует отличные от NULL входные значения в строку. Перед каждым значением,
кроме первого, добавляется соответствующий разделитель (если он отличен от
NULL).
Поддерживает частичный режим.
sum
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
xmlagg ( xml ) → xml
Конкатенирует отличные от NULL входные значения XML (см. подраздел xmlagg).
Не поддерживает частичный режим.
Примечания
Следует отметить, что, за исключением count, эти функции возвращают значение NULL, когда не выбрана ни одна строка. В частности, при отсутствии строк sum возвращает это значение вместо нуля (как можно было бы ожидать), а array_agg — вместо пустого массива. Для замены значения NULL на ноль или пустой массив можно при необходимости воспользоваться функцией coalesce.
Агрегатные функции array_agg, json_agg, jsonb_agg, json_object_agg, jsonb_object_agg, string_agg и xmlagg, а также аналогичные им пользовательские агрегатные функции выдают значимо разные результирующие значения в зависимости от порядка входных значений. Этот порядок не задается по умолчанию, но им можно управлять, написав в вызове агрегата предложение ORDER BY, как показано в подразделе Агрегатные выражения. Как вариант, наверняка сработает предоставление входных значений из подзапроса с сортировкой. Например:
SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
Помните, что этот подход может дать сбой, если внешний уровень запроса содержит дополнительную обработку, например объединение, поскольку это может привести к переупорядочению выходных значений подзапроса перед вычислением агрегата.
Примечание
Логические агрегаты bool_and и bool_or аналогичны описанным в стандарте SQL агрегатам every и any или some. QHB поддерживает every, но не any или some, поскольку в стандартном синтаксисе присутствует неоднозначность:SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;Здесь ANY можно рассматривать и как добавление подзапроса, и как агрегатную функцию, если подзапрос возвращает одну строку с логическим значением. Таким образом, этим агрегатам нельзя было дать стандартные имена.
Примечание
Пользователи, привыкшие работать с другими СУБД SQL, могут быть разочарованы производительностью агрегата count, когда он применяется к целой таблице. Подобный запрос:SELECT count(*) FROM sometable;потребует затрат, пропорциональных размеру таблицы: QHB потребуется просканировать либо таблицу целиком, либо индекс, включающий все строки этой таблицы.
Агрегатные функции для статистики
В этом подразделе приведены агрегатные функции, обычно используемые в статистическом анализе. (Они отделены просто для того, чтобы избежать загромождения списка более широко используемых агрегатов). Функции, показанные как принимающие числовой_тип, имеются для типов smallint, integer, bigint, numeric, real и double precision. Когда в описании упоминается N, это означает количество входных строк, для которых все входные выражения отличны от NULL. Во всех случаях, когда вычисление не имеет смысла, например, когда N равно нулю, возвращается NULL.
corr
corr ( Ydouble precision,X double precision ) → double precision
Вычисляет коэффициент корреляции.
Поддерживает частичный режим.
covar_pop
covar_pop ( Ydouble precision,X double precision ) → double precision
Вычисляет ковариацию совокупности.
Поддерживает частичный режим.
covar_samp
covar_samp ( Ydouble precision,X double precision ) → double precision
Вычисляет выборочную ковариацию.
Поддерживает частичный режим.
regr_avgx
regr_avgx ( Ydouble precision,X double precision ) → double precision
Вычисляет среднее для независимой переменной, sum(X)/N.
Поддерживает частичный режим.
regr_avgy
regr_avgy ( Ydouble precision,X double precision ) → double precision
Вычисляет среднее для зависимой переменной, sum(Y)/N.
Поддерживает частичный режим.
regr_count
regr_count ( Ydouble precision,X double precision ) → bigint
Вычисляет число строк, в которых оба входных значения отличны от NULL.
Поддерживает частичный режим.
regr_intercept
regr_intercept ( Ydouble precision,X double precision ) → double precision
Вычисляет пересечение с осью y линейного уравнения, решенного подбором по методу
наименьших квадратов по парам (X, Y).
Поддерживает частичный режим.
regr_r2
regr_r2 ( Ydouble precision,X double precision ) → double precision
Вычисляет квадрат коэффициента корреляции.
Поддерживает частичный режим.
regr_slope
regr_slope ( Ydouble precision,X double precision ) → double precision
Вычисляет наклон линейного уравнения, решенного подбором по методу наименьших
квадратов по парам (X, Y).
Поддерживает частичный режим.
regr_sxx
regr_sxx ( Ydouble precision,X double precision ) → double precision
Вычисляет «сумму квадратов» независимой переменной, sum(X^2) - sum(X)^2/N.
Поддерживает частичный режим.
regr_sxy
regr_sxy ( Ydouble precision,X double precision ) → double precision
Вычисляет «сумму произведений» независимой и зависимой переменных, sum(X*Y) -
sum(X) * sum(Y)/N.
Поддерживает частичный режим.
regr_syy
regr_syy ( Ydouble precision,X double precision ) → double precision
Вычисляет «сумму квадратов» зависимой переменной, sum(Y^2) - sum(Y)^2/N.
Поддерживает частичный режим.
stddev
stddev ( числовой_тип ) → double precision для real или double precision, иначе numeric
Это исторический псевдоним stddev_samp.
Поддерживает частичный режим.
stddev_pop
stddev_pop ( числовой_тип ) → double precision для real или double precision, иначе numeric
Вычисляет стандартное отклонение по совокупности входных значений.
Поддерживает частичный режим.
stddev_samp
stddev_samp ( числовой_тип ) → double precision для real или double precision, иначе numeric
Вычисляет стандартное отклонение по выборке входных значений.
Поддерживает частичный режим.
variance
variance ( числовой_тип ) → double precision для real или double precision, иначе numeric
Это исторический псевдоним var_samp. Поддерживает частичный режим.
var_pop
var_pop ( числовой_тип ) → double precision для real или double precision, иначе numeric
Вычисляет дисперсию совокупности входных значений (квадрат стандартного отклонения
по совокупности).
Поддерживает частичный режим.
var_samp
var_samp ( числовой_тип ) → double precision для real или double precision, иначе numeric
Вычисляет дисперсию выборки входных значений (квадрат стандартного отклонения по
выборке).
Поддерживает частичный режим.
Упорядочивающие агрегатные функции
В этом подразделе приведены некоторые агрегатные функции, использующие синтаксис упорядочивающих агрегатов. Эти функции иногда называют функциями «обратного распределения». Их агрегированные входные данные добавляются указанием ORDER BY, а кроме того они могут принимать прямой аргумент, который не агрегируется, но вычисляется только один раз. Все эти функции игнорируют значения NULL в агрегируемых входных данных. Для функций, принимающих параметр fraction, его значение должно быть между 0 и 1; в противном случае возникнет ошибка. Однако если fraction имеет значение NULL, то и результат будет NULL.
mode
mode () WITHIN GROUP ( ORDER BY anyelement ) → anyelement
Вычисляет моду, наиболее часто встречающееся значение в агрегируемом аргументе
(если одинаково часто встречаются несколько значений, то произвольно выбирает
первое из них). Агрегируемый аргумент должен быть сортируемого типа.
Не поддерживает частичный режим.
percentile_cont
percentile_cont ( дробь double precision ) WITHIN GROUP ( ORDER BY double precision ) → double precision
percentile_cont ( дробь double precision ) WITHIN GROUP ( ORDER BY interval ) → interval
Вычисляет непрерывный процентиль — значение, соответствующее заданной дроби
в отсортированном множестве значений агрегируемого аргумента. При необходимости
соседние входные элементы будут интерполироваться.
Не поддерживает частичный режим.
percentile_cont ( дроби double precision[] ) WITHIN GROUP ( ORDER BY double precision ) → double precision[]
percentile_cont ( дроби double precision[] ) WITHIN GROUP ( ORDER BY interval ) → interval[]
Вычисляет несколько непрерывных процентилей. Результатом является массив той же
размерности, что и параметр дроби, а все отличные от NULL элементы в нем
заменяются значением (возможно, интерполированным), соответствующим этому
процентилю.
Не поддерживает частичный режим.
percentile_disc
percentile_disc ( дробь double precision ) WITHIN GROUP ( ORDER BY anyelement ) → anyelement
Вычисляет дискретный процентиль, первое значение в упорядоченном множестве
значений агрегируемого аргумента, позиция которого в этом множестве равна или
превышает заданную дробь. Агрегируемый аргумент должен быть сортируемого
типа.
Не поддерживает частичный режим.
percentile_disc ( дроби double precision[] ) WITHIN GROUP ( ORDER BY anyelement ) → anyarray
Вычисляет несколько дискретных процентилей. Результатом является массив той же
размерности, что и параметр дроби, а все отличные от NULL элементы в нем
заменяются входным значением, соответствующим этому процентилю. Агрегируемый
аргумент должен быть сортируемого типа.
Не поддерживает частичный режим.
Гипотезирующие агрегатные функции
Все «гипотезирующие» агрегаты, перечисленные в этом подразделе, связаны с одноименными оконными функциями, определенными в разделе Оконные функции. Во всех случаях результат этих агрегатов представляет собой значение, которое связанная оконная функция вернула бы для «гипотетической» строки, сформированной из аргументов, если бы такая строка была добавлена в упорядоченную группу строк, представленную отсортированными_аргументами. Для каждой из этих функций список непосредственных аргументов, заданный в параметре аргументы, должен соответствовать числу и типу агрегируемых аргументов, заданных в параметре отсортированные_аргументы. В отличие от большинства встроенных агрегатов, эти агрегаты не являются строгими, то есть они не опускают входные строки, содержащие NULL. Значения NULL сортируются согласно правилу, заданному в предложении ORDER BY.
rank
rank ( аргументы) WITHIN GROUP ( ORDER BYотсортированные_аргументы ) → bigint
Вычисляет ранг гипотетической строки с пропусками, то есть номер первой строки в
одноранговой с ней группе.
Не поддерживает частичный режим.
dense_rank
dense_rank ( аргументы) WITHIN GROUP ( ORDER BYотсортированные_аргументы ) → bigint
Вычисляет ранг гипотетической строки без пропусков; в сущности, эта функция
считает одноранговые группы.
Не поддерживает частичный режим.
percent_rank
percent_rank ( аргументы) WITHIN GROUP ( ORDER BYотсортированные_аргументы ) → double precision
Вычисляет относительный ранг гипотетической строки, то есть (rank - 1) / (общее
число строк - 1). Таким образом, это значение лежит в диапазоне от 0 до 1, включая
границы.
Не поддерживает частичный режим.
cume_dist
cume_dist ( аргументы) WITHIN GROUP ( ORDER BYотсортированные_аргументы ) → double precision
Вычисляет кумулятивное распределение, то есть (число строк, предшествующих или
одноранговых с гипотетической строкой) / (общее число строк). Это значение лежит
в диапазоне от 1/N до 1.
Не поддерживает частичный режим.
Операции группировки
GROUPING ( выражение(я)_group_by ) → integer
Возвращает битовую маску, показывающую, какие выражения GROUP BY не включены в текущий группирующий набор. Биты присваиваются самому правому аргументу, соответствующему младшему значащему биту; каждый бит равен 0, если соответствующее выражение включено в критерий группировки группирующего набора, генерирующего текущую результирующую строку, и 1, если оно туда не включено.
Операции группировки, приведенные в этом подразделе, применяются совместно с группирующими наборами (см. подраздел GROUPING SETS, CUBE и ROLLUP) для различения результирующих строк. Аргументы функции 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 (то есть агрегирование проводилось по всем входным строкам).