Функции агрегирования — это функции, которые объединяют несколько строк в одно значение. Функции агрегирования отличаются от скалярных функций и функций окон, потому что они изменяют кардинальность результата. Таким образом, функции агрегирования могут использоваться только в SELECT и HAVING предложениях SQL-запроса.
Когда используется предложение DISTINCT, при вычислении агрегата учитываются только уникальные значения. Это обычно используется в сочетании с функцией агрегирования count, чтобы получить количество уникальных элементов; но его можно использовать вместе с любой функцией агрегирования в системе.
Предложение ORDER BY может быть указано после последнего аргумента вызова функции. Обратите внимание на отсутствие разделителя запятой перед предложением.
SELECT ⟨aggregate_function⟩(⟨arg⟩, ⟨sep⟩ ORDER BY ⟨ordering_criteria⟩);
Это предложение гарантирует, что значения, подлежащие агрегированию, сортируются перед применением функции. Большинство функций агрегирования не зависят от порядка, и для них это предложение анализируется и отбрасывается. Однако существуют некоторые зависящие от порядка функции агрегирования, которые могут давать не детерминированные результаты без упорядочивания, например, first, last, list и string_agg / group_concat / listagg. Эти функции могут быть детерминированными при упорядочивании аргументов.
Например:
CREATE TABLE tbl AS
SELECT s FROM range(1, 4) r(s);
SELECT string_agg(s, ', ' ORDER BY s DESC) AS countdown
FROM tbl;
Находит строку с максимальным значением val и вычисляет выражение arg в этой строке. Строки, где значение выражения arg или val равно NULL, игнорируются. Данная функция зависит от порядка сортировки.
Обобщённый случай функции arg_max для n значений: возвращает набор LIST, содержащий выражения arg для верхних n строк, отсортированных по val в порядке убывания. Данная функция зависит от порядка сортировки.
Находит строку с максимальным значением val и вычисляет выражение arg в этой строке. Строки, где выражение val принимает значение NULL, игнорируются. Данная функция зависит от порядка сортировки.
Находит строку с минимальным значением val и вычисляет выражение arg в этой строке. Строки, где значение выражения arg или val равно NULL, игнорируются. Данная функция зависит от порядка сортировки.
Возвращает набор LIST, содержащий выражения arg для нижних n строк, отсортированных по val в порядке возрастания. Данная функция зависит от порядка сортировки.
Находит строку с минимальным значением val и вычисляет выражение arg в этой строке. Строки, где выражение val принимает значение NULL, игнорируются. Данная функция зависит от порядка сортировки.
Возвращает битовую строку, длина которой соответствует диапазону не-NULL (целых) значений, с установленными битами в позиции каждого (уникального) значения.
Находит строку с максимальным значением val и вычисляет выражение arg в этой строке. Строки, где значение выражения arg или val равно NULL, игнорируются. Эта функция подвержена влиянию сортировки порядок.
Обобщённый случай arg_max для множества значений: возвращает LIST, содержащий выражения arg для верхних n строк, отсортированных по val в порядке убывания. Эта функция подвержена влиянию сортировки порядок.
Находит строку с максимальным значением val и вычисляет выражение arg в этой строке. Строки, где выражение val имеет значение NULL, игнорируются. Эта функция подвержена влиянию сортировки порядок.
Находит строку с минимальным значением val и вычисляет выражение arg в этой строке. Строки, где значение выражения arg или val равно NULL, игнорируются. Эта функция подвержена влиянию сортировки порядок.
Обобщённый случай arg_min для множества значений: возвращает LIST, содержащий выражения arg для верхних n строк, отсортированных по val в порядке убывания. Эта функция подвержена влиянию сортировки порядок.
Находит строку с минимальным значением val и вычисляет выражение arg в этой строке. Строки, где выражение val имеет значение NULL, игнорируются. Эта функция подвержена влиянию сортировки порядок.
Возвращает строку битов, длина которой соответствует диапазону ненулевых целочисленных значений, при этом биты устанавливаются в позиции каждого (различного) значения.
Все общие агрегатные функции, за исключением list и first (и их псевдонимов array_agg и arbitrary соответственно), игнорируют значения NULL. Чтобы исключить значения NULL из list, вы можете использовать FILTER условие. Чтобы пропустить значения NULL из first, вы можете использовать any_value агрегацию.
Все общие агрегатные функции, за исключением count, возвращают NULL для пустых групп и групп без значений, отличных от NULL. В частности, list не возвращает пустой список, sum не возвращает ноль, а string_agg не возвращает пустую строку в этом случае.
В таблице ниже приведены доступные статистические агрегатные функции. Все они игнорируют значения NULL (в случае одного входного столбца x) или пары, где хотя бы одно из входных значений равно NULL (в случае двух входных столбцов y и x).
Интерполированное pos-квантиль x для 0 <= pos <= 1, т.е. упорядочивает значения x и возвращает pos * (n_nonnull_values - 1)-й (нумерация с нуля) элемент (или интерполяцию между соседними элементами, если индекс не является целым числом). Если pos является LIST из FLOATs, то результат является LIST соответствующих интерполированных квантилей.
Дискретный pos-квантиль x для 0 <= pos <= 1, т.е. упорядочивает значения x и возвращает greatest(ceil(pos * n_nonnull_values) - 1, 0)-й (нумерация с нуля) элемент. Если pos является LIST из FLOATs, то результат является LIST соответствующих дискретных квантилей.
Квадрат коэффициента корреляции Пирсона между y и x. Также: коэффициент детерминации в линейной регрессии, где x — независимая переменная, а y — зависимая переменная.
Дисперсия генеральной совокупности, включающая поправку на смещение по Бесселю, независимой переменной для пар, не являющихся NULL, где x — независимая переменная, а y — зависимая переменная.
Дисперсия генеральной совокупности, включающая поправку на смещение по Бесселю, зависимой переменной для пар, не являющихся NULL, где x — независимая переменная, а y — зависимая переменная.
Интерполированное pos-квантиль от x для 0 <= pos <= 1, т.е. упорядочивает значения x и возвращает pos * (n_nonnull_values - 1)-й (индексированный с нуля) элемент (или интерполяцию между соседними элементами, если индекс не является целым числом). Если pos является LIST из FLOAT-ов, то результат — LIST соответствующих интерполированных квантилей.
Дискретная pos-квантиль от x для 0 <= pos <= 1, т.е. упорядочивает значения x и возвращает greatest(ceil(pos * n_nonnull_values) - 1, 0)-й (индексированный с нуля) элемент. Если pos является LIST из FLOAT-ов, то результат — LIST соответствующих дискретных квантилей.
Квадрат коэффициента корреляции Пирсона между y и x. Также: коэффициент детерминации в линейной регрессии, где x — независимая переменная, а y — зависимая переменная.
Дисперсия генеральной совокупности, которая включает поправку на смещение Бесселя, независимой переменной для пар, не являющихся NULL, где x — независимая переменная, а y — зависимая переменная.
Дисперсия генеральной совокупности, которая включает поправку на смещение Бесселя, зависимой переменной для пар, не являющихся NULL, где x — независимая переменная, а y — зависимая переменная.
В таблице ниже показаны доступные функции агрегирования «упорядоченных наборов». Эти функции задаются с помощью синтаксиса WITHIN GROUP (ORDER BY sort_expression), и они преобразуются в эквивалентную функцию агрегирования, которая принимает выражение упорядочивания в качестве первого аргумента.
Функция
Эквивалент
mode() WITHIN GROUP (ORDER BY column [(ASC|DESC)])
mode(column ORDER BY column [(ASC|DESC)])
percentile_cont(fraction) WITHIN GROUP (ORDER BY column [(ASC|DESC)])
quantile_cont(column, fraction ORDER BY column [(ASC|DESC)])
percentile_cont(fractions) WITHIN GROUP (ORDER BY column [(ASC|DESC)])
quantile_cont(column, fractions ORDER BY column [(ASC|DESC)])
percentile_disc(fraction) WITHIN GROUP (ORDER BY column [(ASC|DESC)])
quantile_disc(column, fraction ORDER BY column [(ASC|DESC)])
percentile_disc(fractions) WITHIN GROUP (ORDER BY column [(ASC|DESC)])
quantile_disc(column, fractions ORDER BY column [(ASC|DESC)])
Для запросов с GROUP BY и ROLLUP или GROUPING SETS: Возвращает целое число, идентифицирующее, какое из выражений аргументов было использовано для группировки для создания текущей строки супер-агрегата.
