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

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

Например, сгруппируем отношение ПОЕЗДКИ по значению столбца Личный_номер. Мы получим 7 групп, для которых можем вычислить некоторые групповые значения, например количество кортежей в группе, максимальное или минимальное значение столбца Километраж. Это делается с помощью агрегатных функций. Агрегатные функции вычисляют одиночное значение для всей группы таблицы. Список этих функций представлен в табл. 8.

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

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

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

Отыскать общий километраж поездок каждого водителя

SELECT Фамилия, SUM(Километраж)

FROM Водители LEFT JOIN Поездки ON (Водители.Личный_номер = Поездки.Личный_номер_вод) GROUP BY Фамилия

Подсчитать сколько водителей было отправлено в поездки в текущем месяце.

 

SELECT count(DIstinct Личный_номер_вод) FROM Поездки WHERE MONTH(Дата) = '09'

Подсчитать общее число поездок для каждого водителя.

 

SELECT Фамилия,Count(*)As ‘Количество поездок’

FROM Поездки, Водители

WHERE Личный_номер = Личный_номер_вод

Group BY Личный_номер_вод, Фамилия

Определить сколько машин ежедневно отправляют в поездки.

 

SELECT Дата,Count(*)

FROM Поездки

GROUP BY Дата

 

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

Определить средний стаж водителей.

 

SELECT AVG(Стаж) FROM Водители

 

Отыскать, сколько автомобилей требуют ремонта

 

SELECT COUNT(*) FROM Транспорт WHERE Состояние = 'ремонт'

 

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

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