Применение агрегатных функций и вложенных запросов в операторе выбора
В SQL добавлены дополнительные функции, которые позволяют вычислять обобщенные групповые значения. Для применения агрегатных функций предполагается предварительная операция группировки. В чем состоит суть операции группировки? При группировке все множество кортежей отношения разбивается на группы, в которых собираются кортежи, имеющие одинаковые значения атрибутов, которые заданы в списке группировки.
Например, сгруппируем отношение R1 по значению столбца Дисциплина. Мы получим 4 группы, для которых можем вычислить некоторые групповые значения, например количество кортежей в группе, максимальное или минимальное значение столбца Оценка.
Это делается с помощью агрегатных функций. Агрегатные функции вычисляют одиночное значение для всей группы таблицы. Список этих функций представлен в таблице 5.7.
Таблица 5.7. Агрегатные функции
Функция
|
Результат
| ||
COUNT
|
Количество строк или непустых значений полей, которые выбрал запрос
| ||
SUM
|
Сумма всех выбранных значений данного поля
| ||
AVG
|
Среднеарифметическое значение всех выбранных значений данного поля
| ||
M1N
|
Наименьшее из всех выбранных значений данного поля
| ||
MAX
|
Наибольшее из всех выоранных значений данного поля
| ||
R1
| |||||
ФИО
|
Дисциплина
|
Оценка
| |||
Группа 1
|
Петров Ф. И.
|
Базы данных
|
5
| ||
Сидоров К. А.
|
Базы данных
|
4
| |||
Миронов А. В.
|
Базы данных
|
2
| |||
Степанова К. Е.
|
Базы данных
|
2
| |||
Крылова Т. С.
|
Базы данных
|
5
| |||
Владимиров В. А.
|
Базы данных
|
5
| |||
Группа 2
|
Сидоров К. А.
|
Теория информации
|
4
| ||
Степанова К. Е.
|
Теория информации
|
2
| |||
Крылова Т. С.
|
Теория информации
|
5
| |||
Миронов А. В.
|
Теория информации
|
Null
| |||
Группа 3
|
Трофимов П. А.
|
Сети и телекоммуникации
|
4
| ||
Иванова Е. А.
|
Сети и телекоммуникации
|
5
| |||
Уткина Н. В.
|
Сети и телекоммуникации
|
5
| |||
Группа 4
|
Владимиров В. А.
|
Английский язык
|
4
| ||
Трофимов П. А.
|
Английский язык
|
5
| |||
Иванова Е. А.
|
Английский язык
|
3
| |||
Петров Ф. И.
|
Английский язык
|
5
i
| |||
Агрегатные функции используются подобно именам полей в операторе SELECT, но с одним исключением: они берут имя поля как аргумент. С функциями SUM и AVG могут использоваться только числовые поля. С функциями COUNT, MAX и MIN могут использоваться как числовые, так и символьные поля. При использовании с символьными полями МАХ и MIN будут транслировать их в эквивалент ASCII кода и обрабатывать в алфавитном порядке. Некоторые СУБД позволяют использовать вложенные агрегаты, но это является отклонением от стандарта ANSI со всеми вытекающими отсюда последствиями.
Например, можно вычислить количество студентов, сдававших экзамены по каждой дисциплине. Для этого надо выполнить запрос с группировкой по полю «Дисциплина» и вывести в качестве результата название дисциплины и количество строк в группе по данной дисциплине. Применение символа * в качестве аргумента функции COUNT означает подсчет всех строк в группе.
SELECT R1.Дисциплина. СОUNТ(*)
FROM R1
GROUP BY R1 Дисциплина
Результат:
Дисциплина
|
COUNT(*)
| ||
Базы данных
|
6
| ||
Теория информации
|
4
| ||
Сети и телекоммуникации
|
3
| ||
Английский язык
|
4
| ||
Если же мы хотим сосчитать количество сдавших экзамен по какой-либо дисциплине, то нам необходимо исключить неопределенные значения из исходного отношения перед группировкой. В этом случае запрос будет выглядеть следующим образом:
SELECT R1.Дисциплина. COUNT(*)
FROM R1 WHERE R1.
Оценка IS NOT NULL
GROUP BY Rl.Дисциплина
Получим результат:
Дисциплина
|
COUNT(*)
| ||
Базы данных
|
6
| ||
Теория информации
|
3
| ||
Сети и телекоммуникации
|
3
| ||
Английский язык
|
4
| ||
В этом случае строка со студентом
Миронов А, В.
|
Теория информации
|
Null
| ||
не попадет в набор кортежей перед группировкой, поэтому количество кортежей в группе для дисциплины «Теория информации» будет на 1 меньше.
Можно применять агрегатные функции также и без операции предварительной группировки, в этом случае все отношение рассматривается как одна группа и для этой группы можно вычислить одно значение на группу.
Обратившись снова к базе данных «Сессия» (таблицы Rl, R2, R3), найдем количество успешно сданных экзаменов:
SELECT COUNT(*)
FROM Rl
WHERE Оценка > 2:
Это, конечно, отличается от выбора поля, поскольку всегда возвращается одиночное значение, независимо от того, сколько строк находится в таблице. Аргументом агрегатных функций могут быть отдельные столбцы таблиц. Но для того, чтобы вычислить, например, количество различных значений некоторого столбца в группе, необходимо применить ключевое слово DISTINCT совместно с именем столбца. Вычислим количество различных оценок, полученных по каждой дисциплине:
SELECT Rl.Дисциплина.
COUNT(DISTINCT R1.Оценка)
FROM R1
WHERE R1.Оценка IS NOT NULL
GROUP BY Rl.Дисциплина
Результат:
Дисциплина
|
COUNT(DISTINCT R1 .Оценка)
| ||
Базы данных
|
3
| ||
Теория информации
|
3
| ||
Сети и телекоммуникации
|
2
| ||
Английский язык
|
3
| ||
В результат можно включить значение поля группировки и несколько агрегатных функций, а в условиях группировки можно использовать несколько полей. При этом группы образуются по набору заданных полей группировки. Операции с агрегатными функциями могут быть применены к объединению множества исходных таблиц. Например, поставим вопрос: определить для каждой группы и каждой дисциплины количество успешно сдавших экзамен и средний балл по дисциплине.
SELECT R2.Группа. R1.Дисциплина. COUNT(*), АVР(Оценка)
FROM R1.R2
WHERE Rl.ФИО = R2.ФИО AND
Rl.Оценка IS NOT NULL AND
Rl.Оценка > 2
GROUP BY R2.Группа. Rl.Дисциплина
Результат:
Дисциплина
|
COUNT(*)
|
АVР(Оценка)
| ||
Базы данных
|
6
|
3.83
| ||
Теория информации
|
3
|
3.67
| ||
Сети и телекоммуникации
|
3
|
4.66
| ||
Английский язык
|
4
|
4.25
| ||
Мы не можем использовать агрегатные функции в предложении WHERE, потому что предикаты оцениваются в терминах одиночной строки, а агрегатные функции — в терминах групп строк.
Предложение GROUP BY позволяет определять подмножество значений в особом поле в терминах другого поля и применять функцию агрегата к подмножеству. Это дает возможность объединять поля и агрегатные функции в едином предложении SELECT. Агрегатные функции могут применяться как в выражении вывода результатов строки SELECT, так и в выражении условия обработки сформированных групп HAVING. В этом случае каждая агрегатная функция вычисляется для каждой выделенной группы. Значения, полученные при вычислении агрегатных функций, могут быть использованы для вывода соответствующих результатов или для условия отбора групп.
Построим запрос, который выводит группы, в которых по одной дисциплине на экзаменах получено больше одной двойки:
SELECT R2.Группа
FROM R1.R2
WHERE Rl.ФИО = R2.ФИО AND
Rl.Оценка = 2
GROUP BY R2.Группа . R1.Дисциплина
HAVING count(*)> 1
В дальнейшем в качестве примера будем работать не с БД «Сессия», а с БД «Банк», состоящей из одной таблицы F, в которой хранится отношение F, содержащее информацию о счетах в филиалах некоторого банка:
F = <N, ФИО, Филиал, ДатаОткрытия, ДатаЗакрытия, Остаток>;
Q = (Филиал, Город);
поскольку на этой базе можно ярче проиллюстрировать работу с агрегатными функциями и группировкой.
Например, предположим, что мы хотим найти суммарный остаток на счетах в филиалах. Можно сделать раздельный запрос для каждого из них, выбрав SUM(Остаток) из таблицы для каждого филиала. GROUP BY, однако, позволит поместить их все в одну команду:
SELECT Филиал, SUM
GROUP BY Филиал:
GROUP BY применяет агрегатные функции независимо для каждой группы, определяемой с помощью значения поля Филиал. Группа состоит из строк с одинаковым значением поля Филиал, и функция SUM применяется отдельно для каждой такой группы, то есть суммарный остаток на счетах подсчитывается отдельно для каждого филиала. Значение поля, к которому применяется GROUP BY, имеет, по определению, только одно значение на группу вывода, как и результат работы агрегатной функции. Поэтому мы можем совместить в одном запросе агрегат и поле. Вы можете также использовать GROUP BY с несколькими полями.
Предположим, что мы хотели бы увидеть только те суммарные значения остатков на счетах, которые превышают $5000. Чтобы увидеть суммарные остатки свыше $5000, необходимо использовать предложение HAVING. Предложение HAVING определяет критерии, используемые, чтобы удалять определенные группы из вывода, точно так же как предложение WHERE делает это для индивидуальных строк.
Правильной командой будет следующая:
SELECT Филиал, SUM(Остаток)
FROM F
GROUP BY Филиал
HAVING SUM(Остаток) > 5000;
Аргументы в предложении HAVING подчиняются тем же самым правилам, что и в предложении SELECT, где используется GROUP BY. Они должны иметь одно значение на группу вывода.
Следующая команда будет запрещена:
SELECT Филиал.SUM(Остаток)
FROM F GROUP BY Филиал
HAVING ДатаОткрытия = 27/12/1999;
Поле ДатаОткрытия не может быть использовано в предложении HAVING, потому что оно может иметь больше чем одно значение на группу вывода. Чтобы избежать такой ситуации, предложение HAVING должно ссылаться только на агрегаты и поля, выбранные GROUP BY. Имеется правильный способ сделать вышеупомянутый запрос:
SELECT Филиал,SUM(Остаток)
FROM F
WHERE ДатаОткрытия = '27/12/1999'
GROUP BY Филиал;
Смысл данного запроса следующий: найти сумму остатков по каждому филиалу счетов, открытых 27 декабря 1999 года.
Как и говорилось ранее, HAVING может использовать только аргументы, которые имеют одно значение на группу вывода. Практически, ссылки на агрегатные функции — наиболее общие, но и поля, выбранные с помощью GROUP BY, также допустимы. Например, мы хотим увидеть суммарные остатки на счетах филиалов в Санкт-Петербурге, Пскове и Урюпинске:
SELECT Филиал.SUМ(Остаток)
FROM F.Q
WHERE F.Филиал = Q.Филиал
GROUP BY Филиал
HAVING Филиал IN ("Санкт-Петербург". "Псков". "Урюпинск");
Поэтому в арифметических выражениях предикатов, входящих в условие выборки раздела HAVING, прямо можно использовать только спецификации столбцов, указанных в качестве столбцов группирования в разделе GROUP BY. Остальные столбцы можно специфицировать только внутри спецификаций агрегатных функций COUNT, SUM, AVG, MIN и MAX, вычисляющих в данном случае некоторое агрегатное значение для всей группы строк. Аналогично обстоит дело с подзапросами, входящими в предикаты условия выборки раздела HAVING: если в подзапросе используется характеристика текущей группы, то она может задаваться только путем ссылки на столбцы группирования.
Результатом выполнения раздела HAVING является сгруппированная таблица, содержащая только те группы строк, для которых результат вычисления условия поиска есть TRUE. В частности, если раздел HAVING присутствует в табличном выражении, не содержащем GROUP BY, то результатом его выполнения будет либо пустая таблица, либо результат выполнения предыдущих разделов табличного выражения, рассматриваемый как одна группа без столбцов группирования.
