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

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

Общий синтаксис для использования агрегатных функций

<агрегатная функция>([ALL | DISTINCT] <выражение>)

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

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

Основные агрегатные функции:

• COUNT() — подсчитывает количество строк.
• SUM() — вычисляет сумму значений.
• AVG() — вычисляет среднее значение.
• MIN() — находит минимальное значение.
• MAX() — находит максимальное значение.
• LIST() — выполняет конкатенацию строк аргументов.

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

COUNT()

Подсчитывает количество строк группы.

Например, найдем общее количество сотрудников.

SELECT COUNT(*) AS TOTAL_EMPLOYEES
  FROM EMPLOYEES;

Результат:

Найдем количество сотрудников в отделе D01.

SELECT COUNT(*) AS EMPLOYEES_IN_D01
  FROM EMPLOYEES
  WHERE DEPT_NO = 'D01';

Результат:

SUM()

Вычисляет сумму значений в столбце.

Найдем общую сумму зарплат всех сотрудников.

SELECT SUM(SALARY) AS TOTAL_SALARY
  FROM EMPLOYEES;

Результат:

Найдем сумму зарплат в отделе D02.

SELECT SUM(SALARY) AS TOTAL_SALARY_D02
  FROM EMPLOYEES
  WHERE DEPT_NO = 'D02';

AVG()

Вычисляет среднее значение в столбце.

Найдем среднюю зарплату всех сотрудников.

SELECT AVG(SALARY) AS AVG_SALARY
  FROM EMPLOYEES;

Результат:

Найдем среднюю зарплату в отделе D01.

SELECT AVG(SALARY) AS AVG_SALARY_D01
  FROM EMPLOYEES
  WHERE DEPT_NO = 'D01';

Результат:

MIN()

Находит минимальное значение в столбце.

Найдем минимальную зарплату среди всех сотрудников.

SELECT MIN(SALARY) AS MIN_SALARY
  FROM EMPLOYEES;

Результат:

Найдем минимальную зарплату в отделе D02.

SELECT MIN(SALARY) AS MIN_SALARY_D02
  FROM EMPLOYEES
  WHERE DEPT_NO = 'D02';

Результат:

MAX()

Находит максимальное значение в столбце.

Найдем максимальную зарплату среди всех сотрудников.

SELECT MAX(SALARY) AS MAX_SALARY
  FROM EMPLOYEES;

Результат:

Найдем максимальную зарплату в отделе D01.

SELECT MAX(SALARY) AS MAX_SALARY_D01
  FROM EMPLOYEES
  WHERE DEPT_NO = 'D01';

Результат:

LIST

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

LIST([ALL | DISTINCT] <выражение> [, <разделитель>])

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

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

Получим все страны в одной строке.

SELECT LIST(COUNTRY)
  FROM COUNTRY;

Результат:

Добавим пробел после разделяющей запятой.

SELECT LIST(COUNTRY)
  FROM COUNTRY;

Результат:

Выберем все валюты, используемые в указанных странах, исключим дубликаты.

SELECT LIST(DISTINCT CURRENCY, ', ')
  FROM COUNTRY;

Результат:

Примеры с GROUP BY

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

Количество сотрудников в каждом отделе:

SELECT DEPT_NO, COUNT(*) AS EMPLOYEE_COUNT
  FROM EMPLOYEES
  GROUP BY DEPT_NO;

Результат:

Средняя зарплата в каждом отделе:

SELECT DEPT_NO, AVG(SALARY) AS AVG_SALARY
  FROM EMPLOYEES
  GROUP BY DEPT_NO;

Результат:

Максимальная зарплата по должностям:

SELECT JOB_TITLE, MAX(SALARY) AS MAX_SALARY
  FROM EMPLOYEES
  GROUP BY JOB_TITLE;

Результат:

Контрольные вопросы

1. Что представляют собой агрегатные функции?
2. Как обрабатываются NULL значения в агрегатных функциях?
3. Для чего используется опция DISTINCT?
4. Назовите основные агрегатные функции?
5. Какие особенности есть у агрегатной функции COUNT?
6. Какие особенности есть у агрегатной функции LIST?