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

Агрегатные функции в SQL позволяют забирать обобщающую информацию из группы строк и проводить систематизацию данных. Список агрегатных функций приведен в таблице 8.7. Агрегатные функции почти во всех реализациях SQL носят одинаковые имена. Различие в наименование для Oracle дано через косую черту.

Таблица 8.7. Агрегатные функции

Функция	Описание
AVG(X) = AVG(ALLX) AVG(DISTINCTX)	Вычисляет среднее значение аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTINCT подавляет дубликаты
COUNT(*) COUNT(X) = COUNT(ALL X) COUNT(DISTINCT X)	Вычисляет число итемов. При указании * всегда возвращается число строк в таблице. Указание DISTINCT подавляет дубликаты
MAX(X) = MAX(ALL X) MAX(DISTINCT X)	Вычисляет максимальное значение аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTI NCT подавляет дубликаты
MIN(X) = MIN(ALLX) MIN(DISTINCTX)	Вычисляет минимальное значение аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTINCT подавляет дубликаты
SUM(X) = SUM(ALL X) SU INDISTINCT X)	Вычисляет сумму значений аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTINCT подавляет дубли каты
STDDEV([DISTINCT|ALL| X)	Вычисляет стандартное отклонение на множестве значений аргумента, который может быть выражением любого типа. Нуль-значения игно­рируются, ключевое слово DISTINCT подавляет дубликаты
VARIANCE([DIST1NCT|ALL1 X)	Вычисляет квадрат дисперсии

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

SELECT SUM(SAL)

FROM EMPLOYEE;

вы узнаете итоговую сумму зарплаты по организации, а из запроса

SELECT AVG(SAL), STODEV(SAL)

FROM EMPLOYEE;

— среднюю зарплату no организации и ее разброс (дисперсию).

Однако наиболее часто требуется подобная итоговая информация не для таблицы в целом, а для определённых наборов (групп) строк таблицы.

Для того чтобы группировать строки таблицы по какому-либо признаку, в команде SELECT существует специальное предложение GROUP BY, которое задаст колонку (или колонки) для проведения группировки. Это предложение группирует строки таблицы по значениям колонок группировки с последующим подавлением дублирующих значений в колонках группировки, т.е. позволяет определять подмножество значений некоторой колонки в терминах другой колонки и применять к полученным подмножествам функции агрегирования.

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

SELECT DEPNO, MIN(SAL), MAX(SAL)

FROM EMPLOYEE

GROUP BY DEPNO;

Предложение GROUP BY должно следовать после предложения WHERE, если последнее присутствует в команде SELECT. Каждая строка результирующей таблицы относится к одной группе строк. Число групп определяется числом различных значений в колонке группировки (в данном случае DEPNO). Агрегатные функции применяются к каждой группе как к отдельному множеству.

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

SELECT DNAME, JOB, SUM(SAL), C0UNT(*), AVG(SAL)

FROM EMPLOYEE, DEPARTAMENT

WHERE EMPLOYEE.DEPNO=DEPARTAMENT.DEPNO

GROUP BY DNAME, JOB;

Функции SUM( ), C0UNT( ), AVG( ) вычисляют суммы, число строк в группе и среднее значение в группе строк.

В SQL можно задавать условия поиска для группы строк. Для этого в команде SELECT существует предложение HAVING, которое должно следовать за предложением GROUP BY. HAVING задает условие поиска для группы строк.

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

SELECT DNAME, JOB, SUM(SAL), AVG(SAL)

FROM EMPLOYEE, DEPARTAMENT

WHERE EMPLOYEE.DEPNO=DEPARTAMENT. DEPNO

GROUP BY DNAME, JOB

HAVING C0UNT(*)>=2;

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

Таким образом, вы познакомились с различными вариантами использования команды SQL SELECT.