Агрегатные функции

Агрегатные функции выполняют вычисление на наборе значений и возвращают одиночное значение. Агрегатные функции, за исключением COUNT, не учитывают значения NULL и часто используются совместно с предложением GROUP BY. Агрегатные функции могут быть использованы в качестве выражений только в следующих случаях:

Список выбора инструкции SELECT (вложенный или внешний запрос);
Предложение HAVING.

Любая агрегатная функция может быть использована в качестве оконной.

Общий синтаксис для использования

AGG_FUNC([ALL | DISTINCT] <expr>)
    [OVER ([<partition_exp>] [<order_exp>])]

где,

expr - выражение. Может содержать столбец таблицы, константу, переменную, выражение, неагрегатную функцию или UDF, которая возвращает числовой тип данных. Агрегатные функции в качестве выражения не допускаются.

Параметр ALL применяет агрегатную функцию ко всем значениям. ALL является параметром по умолчанию. Параметр DISTINCT указывает на то, что функция AVG будет выполнена только для одного экземпляра каждого уникального значения, независимо от того, сколько раз встречается это значение.

AVG

Функция AVG возвращает среднее значение для группы. Значения NULL пропускаются. В случае если выборка записей пустая или содержит только значения NULL, результат будет содержать NULL.

Функция AVG возвращает тот же типа данных, что и аргумент функции.

MIN и MAX

Функция MIN (MAX) возвращает минимальный (максимальный) среди элементов выборки, которые не равны NULL. При пустой выборке или при выборке из одних NULL функция возвратит NULL. Если аргумент функции строка, то функция вернёт значение, которое окажется первым (последним) в сортировке при применении COLLATE.

Параметр DISTINCT не имеет смысла при использовании функций MIN и MAX и доступен только для совместимости со стандартом.

Функция MAX возвращает DOUBLE PRECISION или масштабируемый BIGINT в зависимости от типа аргумента функции. Функция MIN возвращает тот же типа данных, что и аргумент функции.

SUM

Функция SUM возвращает сумму элементов выборки, которые не равны NULL. При пустой выборке, или при выборке из одних NULL функция возвратит NULL. ALL является опцией по умолчанию. При ней обрабатываются все значения из выборки, не содержащие NULL. При указании DISTINCT из выборки устраняются дубликаты, после осуществляется подсчёт.

Функция SUM возвращает тот же типа данных, что и аргумент функции.

LIST

Функция имеет параметр, который можно указать через запятую

LIST([ALL | DISTINCT] <expr> [, separator])

, где

separator - разделитель. Выражение строкового типа. По умолчанию разделителем является запятая.

Функция LIST возвращает строку, содержащую значения элементов выборки, которые не равны NULL. При пустой выборке функция возвратит NULL. Тип возвращаемого значения текстовый BLOB за исключением тех случаев, когда выражением являются BLOB других подтипов. ALL является опцией по умолчанию. При ней обрабатываются все значения в выборке, не содержащие NULL. При указании DISTINCT из выборки устраняются дубликаты. Значения выражения expr и разделитель separator поддерживают тип данных BLOB любого размера и набора символов. Поля типа дата / время и числовые перед проведением операции конкатенации преобразуются в строки.

Порядок конкатенации строк определяется порядком чтения записей из источников, который в общем случае не определён. Для придания списку необходимого порядка вы можете предварительно упорядочить источник данных, например с помощью производной таблицы.

COUNT

Функция COUNT возвращает количество значений в группе, которые не являются NULL. При указании DISTINCT из выборки устраняются дубликаты, ALL является значением по умолчанию для всех выборки значений не NULL.

Существует специальная форма оператора COUNT(*). При этом будут подсчитаны все строки. Функция COUNT(*) не принимает параметры и не может использоваться с ключевым словом DISTINCT. Для функции COUNT(*) не нужен параметр expr, так как по определению она не использует сведения о каких-либо конкретных столбцах. Функция COUNT(*) возвращает количество строк в указанной таблице, не отбрасывая дублированные строки. Она подсчитывает каждую строку отдельно. При этом учитываются и строки, содержащие значения NULL.

Функция возвращает тип данных BIGINT. Для пустой выборки данных или если при выборке окажутся одни значения, содержащие NULL, функция COUNT возвратит значение равное 0.

Пример использования аггрегатных функций

SELECT dept_no,
    COUNT(*) AS cnt,
    COUNT(DISTINCT job_country) AS uniq_cnt,
    AVG(salary),
    MIN(salary),
    MAX(salary),
    LIST(JOB_COUNTRY, '; ')
FROM employee
GROUP BY dept_no
FETCH FIRST 5 ROWS ONLY

Выполнение этого запроса на БД employee вернет

DEPT_NO	CNT	UNIQ_CNT	AVG	MIN	MAX	LIST
000	2	1	133321.50	53793.00	212850.00	USA; USA
100	2	1	77631.25	44000.00	111262.50	USA; USA
110	2	1	65221.40	61637.81	68805.00	USA; USA
115	2	1	6740000.00	6000000.00	7480000.00	Japan; Japan
120	3	1	31926.56	22935.00	39224.06	England; England; England

Обратите внимание, что количество уникальных стран, в которых работают сотрудники меньше, чем общее количество. Столбец LIST содержит список стран с указанными нами разделителем „;“. Имена полей, в которых использована аггрегатная функция, но которым не назначены явно имена, используют по умолчанию имя функции.