Агрегатные функции
Агрегатные функции вычисляют единственный результат из набора входных значений. Встроенные агрегатные функции общего назначения перечислены в Таблице 57, а статистические агрегаты — в Таблице 58. Встроенные внутригрупповые упорядочивающие агрегатные функции перечислены в Таблице 59, а встроенные внутригрупповые гипотезирующие — в Таблице 60. Операции группировки, тесно связанные с агрегатными функциями, перечислены в Таблице 61. Особые рекомендации по синтаксису для агрегатных функций приведены в подразделе Агрегатные выражения. Дополнительную вводную информацию см. в разделе Что такое агрегатные функции.
Агрегатные функции, поддерживающие частичный режим, подходят для участия в различных оптимизациях, например в параллельном агрегировании.
Таблица 57. Агрегатные функции общего назначения
Функция |
Частичный режим | ||
|---|---|---|---|
Описание |
|||
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_object_agg ( ключ "any", значение "any" ) → json jsonb_object_agg ( ключ "any", значение "any" ) → jsonb |
Нет | ||
Собирает все пары ключ/значение в объект JSON. Аргументы-ключи приводятся к текстовому типу; аргументы-значения преобразуются методом to_json или to_jsonb. Значения могут быть NULL, но ключи — нет. |
|||
max ( см. описание ) → тот же тип, что и на входе |
Да | ||
Вычисляет максимальное из входных значений, отличных от NULL. Имеется для всех числовых, строковых, перечислимых типов и типов даты/времени, а также для типов inet, interval, money, oid, pg_lsn, tid и массивов любого из этих типов. |
|||
min ( см. описание ) → тот же тип, что и на входе |
Да | ||
Вычисляет минимальное из входных значений, отличных от NULL. Имеется для всех числовых, строковых, перечислимых типов и типов даты/времени, а также для типов inet, interval, money, oid, pg_lsn, tid и массивов любого из этих типов. |
|||
range_agg ( значение anyrange ) → anymultirange |
Нет | ||
Вычисляет объединение входных значений, отличных от NULL. |
|||
range_intersect_agg ( значение anyrange ) → anyrange range_intersect_agg ( значение anymultirange ) → anymultirange |
Нет | ||
Вычисляет пересечение входных значений, отличных от NULL. |
|||
string_agg ( значение text, разделитель text ) → text string_agg ( значение bytea, разделитель bytea ) → bytea |
Нет | ||
Конкатенирует отличные от NULL входные значения в строку. Перед каждым значением, кроме первого, добавляется соответствующий разделитель (если он отличен от 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 |
Нет | ||
Конкатенирует отличные от 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 потребуется просканировать либо таблицу целиком, либо индекс, включающий все строки этой таблицы.
В Таблице 58 приведены агрегатные функции, обычно используемые в статистическом анализе. (Они отделены просто для того, чтобы избежать загромождения списка более часто используемых агрегатных функций). Функции, показанные как принимающие числовой_тип, имеются для типов smallint, integer, bigint, numeric, real и double precision. Когда в описании упоминается N, это означает количество входных строк, для которых все входные выражения отличны от NULL. Во всех случаях, когда вычисление не имеет смысла, например, когда N равно нулю, возвращается NULL.
Таблица 58. Агрегатные функции для статистики
Функция |
Частичный режим | ||
|---|---|---|---|
Описание |
|||
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 |
Да | ||
Вычисляет пересечение с осью y линейного уравнения, решенного подбором по методу наименьших квадратов по парам (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 ( числовой_тип ) → double precision для real или double precision, иначе numeric |
Да | ||
Вычисляет стандартное отклонение по совокупности входных значений. |
|||
stddev_samp ( числовой_тип ) → double precision для real или double precision, иначе numeric |
Да | ||
Вычисляет стандартное отклонение по выборке входных значений. |
|||
variance ( числовой_тип ) → double precision для real или double precision, иначе numeric |
Да | ||
Это исторический псевдоним var_samp. |
|||
var_pop ( числовой_тип ) → double precision для real или double precision, иначе numeric |
Да | ||
Вычисляет дисперсию совокупности входных значений (квадрат стандартного отклонения по совокупности). |
|||
var_samp ( числовой_тип ) → double precision для real или double precision, иначе numeric |
Да | ||
Вычисляет дисперсию выборки входных значений (квадрат стандартного отклонения по выборке). |
|||
В Таблице 59 приведены некоторые агрегатные функции, использующие синтаксис упорядочивающих агрегатов. Эти функции иногда называют функциями «обратного распределения». Их агрегированные входные данные добавляются указанием ORDER BY, а кроме того они могут принимать непосредственный аргумент, который не агрегируется, но вычисляется только один раз. Все эти функции игнорируют значения NULL в агрегируемых входных данных. Для функций, принимающих параметр fraction, его значение должно быть между 0 и 1; в противном случае возникнет ошибка. Однако если fraction имеет значение NULL, то и результат будет NULL.
Таблица 59. Упорядочивающие функции
Функция |
Частичный режим | ||
|---|---|---|---|
Описание |
|||
mode () WITHIN GROUP ( ORDER BY anyelement ) → anyelement |
Нет | ||
Вычисляет моду, наиболее часто встречающееся значение в агрегируемом аргументе (если одинаково часто встречаются несколько значений, произвольно выбирая первое из них). Агрегируемый аргумент должен быть сортируемого типа. |
|||
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 ( дробь double precision ) WITHIN GROUP ( ORDER BY anyelement ) → anyelement |
Нет | ||
Вычисляет дискретный процентиль, первое значение в упорядоченном множестве значений агрегируемого аргумента, позиция которого в этом множестве равна или превышает заданную дробь. Агрегируемый аргумент должен быть сортируемого типа. |
|||
percentile_disc ( дроби double precision[] ) WITHIN GROUP ( ORDER BY anyelement ) → anyarray |
Нет | ||
Вычисляет несколько дискретных процентилей. Результатом является массив той же размерности, что и параметр дроби, а все отличные от NULL элементы в нем заменяются входным значением, соответствующим этому процентилю. Агрегируемый аргумент должен быть сортируемого типа. |
|||
Все «гипотезирующие» агрегаты, перечисленные в Таблице 60, связаны с одноименными оконными функциями, определенными в разделе Оконные функции. Во всех случаях результат этих агрегатов представляет собой значение, которое связанная оконная функция вернула бы для «гипотетической» строки, сформированной из аргументов, если бы такая строка была добавлена в упорядоченную группу строк, представленную отсортированными_аргументами. Для каждой из этих функций список непосредственных аргументов, заданный в параметре аргументы, должен соответствовать числу и типу агрегируемых аргументов, заданных в параметре отсортированные_аргументы. В отличие от большинства встроенных агрегатов, эти агрегаты не являются строгими, то есть они не опускают входные строки, содержащие NULL. Значения NULL сортируются согласно правилу, заданному в предложении ORDER BY.
Таблица 60. Гипотезирующие агрегатные функции
Функция |
Частичный режим | ||
|---|---|---|---|
Описание |
|||
rank ( аргументы ) WITHIN GROUP ( ORDER BY отсортированные_аргументы ) → bigint |
Нет | ||
Вычисляет ранг гипотетической строки с пропусками, то есть номер первой строки в одноранговой с ней группе. |
|||
dense_rank ( аргументы ) 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. |
|||
Таблица 61. Операции группировки
Функция |
||
|---|---|---|
Описание |
||
GROUPING ( выражение(я)_group_by ) → integer |
||
Возвращает битовую маску, показывающую, какие выражения GROUP BY не включены в текущий группирующий набор. Биты присваиваются самому правому аргументу, соответствующему младшему значащему биту; каждый бит равен 0, если соответствующее выражение включено в критерий группировки группирующего набора, генерирующего текущую результирующую строку, и 1, если оно туда не включено. |
||
Операции группировки, приведенные в Таблице 61, применяются совместно с группирующими наборами (см. подраздел 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 (то есть агрегирование проводилось по всем входным строкам).