9.21. Агрегатные функции
Агрегатные функции получают единственный результат из набора входных значений. Встроенные агрегатные функции общего назначения перечислены в Таблице 9.57, а статистические агрегатные функции — в Таблице 9.58. Встроенные внутригрупповые сортирующие агрегатные функции перечислены в Таблице 9.59, встроенные внутригрупповые гипотезирующие — в Таблице 9.60. Группирующие операторы, тесно связанные с агрегатными функциями, перечислены в Таблице 9.61. Особенности синтаксиса агрегатных функцией разъясняются в Подразделе 4.2.7. За дополнительной вводной информацией обратитесь к Разделу 2.7.
Агрегатные функции, поддерживающие частичный режим, являются кандидатами на участие в различных оптимизациях, например, в параллельном агрегировании.
Таблица 9.57. Агрегатные функции общего назначения
Функция Описание | Частичный режим |
|---|---|
Собирает все входные значения, включая NULL, в массив. | Нет |
Собирает все входные массивы в массив с размерностью больше на один. (Входные массивы должны иметь одинаковую размерность и не могут быть пустыми или равны NULL). | Нет |
Вычисляет арифметическое среднее для всех входных значений, отличных от NULL. | Да |
Вычисляет побитовое И для всех входных значений, отличных от NULL. | Да |
Вычисляет побитовое ИЛИ для всех входных значений, отличных от NULL. | Да |
Вычисляет побитовое исключающее ИЛИ для всех входных значений, отличных от NULL. Может быть полезна для вычисления контрольной суммы неупорядоченного набора значений. | Да |
Возвращает true, если все входные значения, отличные от NULL, равны true, и false в противном случае. | Да |
Возвращает true, если хотя бы одно входное значение, отличное от NULL, равно true, и false в противном случае. | Да |
Выдаёт количество входных строк. | Да |
Выдаёт количество входных строк, в которых входное значение отлично от NULL. | Да |
Это соответствующий стандарту SQL аналог | Да |
Собирает все входные значения, включая NULL, в JSON-массив. Значения преобразуются в JSON методом | Нет |
Собирает все пары ключ/значение в JSON-объект. Аргументы-ключи приводятся к текстовому типу, а значения преобразуются методом | Нет |
Вычисляет максимальное из всех значений, отличных от NULL. Имеется для всех числовых и строковых типов, типов-перечислений и даты/времени, а также типов | Да |
Вычисляет минимальное из всех значений, отличных от NULL. Имеется для всех числовых и строковых типов, типов-перечислений и даты/времени, а также типов | Да |
Вычисляет объединение всех входных значений, отличных от NULL. | Нет |
Вычисляет пересечение всех входных значений, отличных от NULL. | Нет |
Соединяет все входные значения, отличные от NULL, в строку. Перед каждым значением, кроме первого, добавляется соответствующий разделитель, заданный параметром | Нет |
Вычисляет сумму всех входных значений, отличных от NULL. | Да |
Соединяет вместе входные XML-значения, отличные от NULL (см. также Подраздел 9.15.1.7). | Нет |
Следует заметить, что за исключением count, все эти функции возвращают NULL, если для них не была выбрана ни одна строка. В частности, функция sum, не получив строк, возвращает NULL, а не 0, как можно было бы ожидать, и array_agg в этом случае возвращает NULL, а не пустой массив. Если необходимо, подставить в результат 0 или пустой массив вместо NULL можно с помощью функции coalesce.
Агрегатные функции array_agg, json_agg, jsonb_agg, json_object_agg, jsonb_object_agg, 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. Postgres Pro поддерживает every, но не any/some, так как синтаксис, описанный в стандарте, допускает неоднозначность:
SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;
Здесь ANY можно рассматривать и как объявление подзапроса, и как агрегатную функцию, если этот подзапрос возвращает одну строку с булевым значением. Таким образом, этим агрегатным функциям нельзя было дать стандартные имена.
Примечание
Пользователи с опытом использования других СУБД SQL могут быть недовольны скоростью агрегатной функции count, когда она применяется ко всей таблице. Подобный запрос:
SELECT count(*) FROM sometable;
потребует затрат в количестве, пропорциональном размеру таблицы: Postgres Pro придётся полностью просканировать либо всю таблицу, либо один из индексов, включающий все её строки.
В Таблице 9.58 перечислены агрегатные функции, обычно применяемые в статистическом анализе. (Они выделены просто для того, чтобы не загромождать список наиболее популярных агрегатных функций.) Функции, показанные как принимающие числовой_тип, существуют для типов smallint, integer, bigint, numeric, real, и double precision. В их описании под N подразумевается число входных строк, для которых все входные выражения отличны от NULL. Во всех случаях, когда вычисление не имеет смысла, например, когда N равно нулю, возвращается null.
Таблица 9.58. Агрегатные функции для статистических вычислений
В Таблица 9.59 показаны некоторые агрегатные функции, использующие синтаксис сортирующих агрегатных функций. Эти функции иногда называются функциями «обратного распределения». Их агрегированные входные данные формируются указанием ORDER BY, а кроме того они могут принимать не агрегируемый непосредственный аргумент, который вычисляется только один раз. Все эти функции игнорируют значения NULL в агрегируемых данных. Для тех функций, которые принимают параметр fraction, его значение должно быть между 0 и 1; в противном случае выдаётся ошибка. Однако если значение fraction — NULL, они выдают NULL в результате.
Таблица 9.59. Сортирующие агрегатные функции
Все «гипотезирующие» агрегатные функции, перечисленные в Таблице 9.60, связаны с одноимёнными оконными функциями, определёнными в Разделе 9.22. В каждом случае их результат — значение, которое бы вернула связанная оконная функция для «гипотетической» строки, полученной из аргументов, если бы такая строка была добавлена в сортированную группу строк, которую образуют сортирующие_аргументы. Для всех этих функций список непосредственных аргументов, переданный в качестве аргументов, по числу и типу элементов должен соответствовать списку, передаваемому в качестве сортирующих_аргументов. В отличие от большинства встроенных агрегатов, данные агрегаты не являются строгими, то есть они не игнорируют строки, содержащие NULL. Значения NULL сортируются по правилу, заданному в предложении ORDER BY.
Таблица 9.60. Гипотезирующие агрегатные функции
Таблица 9.61. Операции группировки
Операции группировки, показанные в Таблице 9.61, применяются в сочетании с наборами группирования (см. Подраздел 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, то есть агрегирование выполнялось по всем входным строкам.