4.7.5Применение агрегатных функций и группировки

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

Например, сгруппируем отношение R1 по значению столбца Дисциплина. Мы получим 3 группы, для которых можем вычислить некоторые групповые значения, например количество кортежей в группе, максимальное или минимальное значение столбца Оценка.

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

Таблица 3.2

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

Функция	Результат
COUNT	Количество строк или непустых значений полей, которые выбрал запрос
SUM	Сумма всех выбранных значений данного поля
AVG	Среднеарифметическое значение всех выбранных значений данного поля
MIN	Наименьшее из всех выбранных значений данного поля
MAX	Наибольшее из всех выбранных значений данного поля

Агрегатные функции применяются подобно именам полей в операторе SELECT, но они используют имя поля как аргумент. С функциями SUM И AVG могут использоваться только числовые поля. С функциями COUNT, MAX, MIN могут использоваться как числовые, так и символьные поля. При использовании с символьными полями MAX и MIN будут транслировать их в эквивалент ASCII кода и обрабатывать в алфавитном порядке.

Например, можно вычислить количество студентов, сдавших экзамены по каждой дисциплине. Для этого надо выполнить запрос с группировкой по полю «Дисциплина» и вывести в качестве результата название дисциплины и количество строк в группе по данной дисциплине. Применение символа * в качестве аргумента функции COUNT означает подсчет всех строк в группе.

SELECT R1.Дисциплина, COUNT(*)

FROM R1

GROUP BY R1.Дисциплина;

Результат:

Дисциплина	COUNT(*)
Базы данных	3
Моделирование	3
Сети ЭВМ	3

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

SELECT R1.Дисциплина, COUNT(*)

FROM R1

WHERE R1.Оценка IS NOT NULL

GROUP BY R1.Дисциплина;

Результат:

Дисциплина	COUNT(*)
Базы данных	3
Моделирование	2
Сети ЭВМ	3

В этом случае строка со студентом

Миронов А.В.

Моделирование

Null

не попадет в набор кортежей перед группировкой, поэтому количество кортежей в группе для дисциплины «Моделирование» будет на 1 меньше.

Можно применять агрегатные функции также и без операции предварительной группировки, в этом случае все отношение рассматривается как одна группа и для этой группы можно вычислить одно значение на группу. Найдем количество студентов, успешно сдавших экзамены:

SELECT COUNT(*)

FROM R1

WHERE Оценка > 2;

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

SELECT R1.Дисциплина, COUNT (DISTINCT R1.Оценка) FROM R1

WHERE R1.Оценка IS NOT NULL

GROUP BY R1.Дисциплина;

Результат:

Дисциплина	COUNT(DISTINCT R1.Оценка)
Базы данных	3
Моделирование	2
Сети ЭВМ	2

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

SELECT R2.Группа, R1. Дисциплина, COUNT(*), AVG(Оценка)

FROM R1, R2

WHERE R1.ФИО = R2.ФИО AND

R1.Оценка IS NOT NULL AND R1.Оценка >2

GROUP BY R2.Группа, R1.Дисциплина;

Результат:

Группа	Дисциплина	COUNT(*)	AVG(Оценка)
АИ21	Базы данных	2	3,67
АИ21	Моделирование	2	4,5
АИ22	Сети ЭВМ	3	4,67

Рассмотрим в качестве другого примера отношения-таблицы F и Q из базы данных «Банк», в которой содержится информация о счетах в филиалах некоторого банка:

F = (N, ФИО, Филиал, ДатаОткрытия, ДатаЗакрытия, Остаток);

Q = (Филиал, Город).

Определим суммарный остаток на счетах в филиалах:

SELECT Филиал, SUM(Остаток)

FROM F

GROUP BY Филиал;

GROUP BY применяет агрегатные функции независимо для каждой группы, определяемой с помощью значения поля Филиал. Группа состоит из строк с одинаковым значением поля Филиал, и функция SUM применяется отдельно для каждой такой группы, то есть суммарный остаток на счетах подсчитывается отдельно для каждого филиала. Значение поля, к которому применяется GROUP BY, имеет, по определению, только одно значение на группу вывода, как и результат работы агрегатной функции. Поэтому мы можем совместить в одном запросе агрегат и поле.

Определить суммарные значения остатков на счетах, которые превышают 5000. Чтобы увидеть суммарные остатки свыше 5000, необходимо использовать предложение HAVING. Предложение HAVING определяет критерии, используемые, чтобы удалять определенные группы из вывода, точно так же, как предложение WHERE делает это для индивидуальных записей.

SELECT Филиал, SUM(Остаток)

FROM F

GROUP BY Филиал

HAVING SUM(Остаток) > 5000;

Аргументы в предложении HAVING подчиняются тем же самым правилам, что и в предложении SELECT, где используется GROUP BY. Они должны иметь одно значение на группу вывода.

Определить суммарные остатки на счетах филиалов в Ессентуках, Азове, Таганроге:

SELECT Филиал, SUM(Остаток)

FROM F, Q

WHERE F.Филиал – Q.Филиал

GROUP BY Филиал

HAVING Город IN («Ессентуки», «Азов», «Таганрог»);

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