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

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

― count – подсчет количества значений;

― sum – сумма значений;

― avg – вычисление среднего;

― min – поиск минимального значения;

― max – поиск максимального значения.

Определить количество студентов в университете можно, воспользовавшись следующим запросом:

select count(*) from Студент

go

количество групп на четвертом факультете – запросом:

select count(Id) from Группа

where Факультет = 4

go

а средний балл по университету – запросом:

select avg(Оценка) from Успеваемость

go

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

select Номер, count(Ном_Зач) as 'Количество студентов'

from Группа, Студент

where Группа = Id

group by Номер

go

позволит получить количество студентов в каждой из групп, а запрос:

select Номер, ФИО, avg(Оценка) as 'Ср. балл'

from Группа, Студент, Успеваемость

where Группа = Группа.Id and

Студент.Ном_Зач = Успеваемость.Ном_Зач

group by Номер, ФИО, Успеваемость.Ном_Зач

order by Номер, ФИО

go

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

В общем случае использование агрегатной функции предполагает следующий синтаксис <имя функции> ([ all | distinct ] <выражение>). Особенности функции count:

— count(*) подсчитывает все значения, в том числе null;

— count(all <выражение>) подсчитывает все значения, отличные от null;

— count(distinct <выражение>) подсчитывает все различные значения, отличные от null;

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

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

Конструкция having играет ту же роль по отношению к группам записей, что и where для отдельных строк и позволяет наложить условие на значение, сформированное агрегатной функцией. Для получения перечня групп, в которых числится 25 и более студентов можно воспользоваться запросом:

select Номер, count(Ном_Зач) as ' >= 25 студентов'

from Группа, Студент

where Группа = Id

group by Номер

having count(Ном_Зач) >= 25

go

а для нахождения групп, в которых есть студенты со средним баллом 5.0 – запросом:

select Номер, ФИО

from Группа, Студент, Успеваемость

where Группа = Группа.Id and

Студент.Ном_Зач = Успеваемость.Ном_Зач

group by Номер, ФИО, Успеваемость.Ном_Зач

having avg(Оценка) = 5

order by Номер, ФИО

go

В общем случае, запрос, содержащий where, group by, having и агрегатные функции выполняется в следующей последовательности:

(i) отбираются записи таблицы в соответствии с условием, заданным во WHERE;

(ii) записи группируются в соответствии с порядком, определенным в GROUP BY;

(iii) для каждой группы записей вычисляется значение агрегатной функции;

(iv) на полученные значения накладывается условие отбора, определенное в HAVING.

Дата добавления: 2015-05-09