Применение агрегатных функций и вложенных запросов в операторе выбора SELECT

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

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

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

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

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

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

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

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

SELECT R1.Дисциплина, COUNT(*) FROM R1 WHERE R1.Оценка IS NOT NULL
GROUP BY R1.Дисциплина

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

SELECT COUNT(*) FROM R1 WHERE Оценка > 2

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

SELECT R1.Дисциплина, COUNT(DISTINCT R1.Оценка) FROM R1 WHERE R1.Оценка IS NOT NULL GROUP BY R1.Дисциплина

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

SELECT R1.Дисциплина, COUNT(*), AVG(Оценка) FROM R1, R2 WHERE R1.ФИО = R2.ФИО AND R1.Оценка IS NOT NULL AND R1.Оценка > 2 GROUP BY R1.Оценка, R1.Дисциплина

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

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

SELECT R1.Оценка, R1.Дисциплина FROM R1, R2 WHERE R1.ФИО = R2.ФИО AND R1.Оценка = 2 GROUP BY R1.Оценка, R1.Дисциплина HAVING COUNT(*)> 1

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

F = <N, ФИО, Филиал, ДатаОткрытия, ДатаЗакрытия, Остаток>; Q = <Филиал, Город>

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

SELECT Филиал, SUM(Остаток) FROM F GROUP BY Филиал

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

SELECT Филиал, SUM(Остаток) FROM F GROUP BY Филиал HAVING SUM(Остаток) > 5000

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

Пример неправильно составленного запроса:

SELECT SUM(Остаток) FROM F GROUP BY Филиал HAVING ДатаОткрытия = ‘27/12/2009’

Поле ДатаОткрытия не может быть использовано в предложении HAVING, потому что оно может иметь больше чем одно значение на группу вывода. Предложение HAVING должно ссылаться только на агрегаты и поля, выбранные GROUP BY. Правильный запрос:

SELECT SUM(Остаток) FROM F WHERE ДатаОткрытия = '27/12/2009' GROUP BY Филиал

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

HAVING может использовать только аргументы, которые имеют одно значение на группу вывода. Например, суммарные остатки на счетах филиалов в Санкт-Петербурге, Пскове и Москве:

SELECT Филиал, SUM(Остаток) FROM F, Q WHERE F.Филиал = Q.Филиал GROUP BY Филиал HAVING Город IN (‘Санкт-Петербург’, ‘Псков’, ‘Москва’)

Вложенные запросы

С помощью SQL можно вкладывать запросы внутрь друг друга. Обычно внутренний запрос генерирует значение, которое проверяется в предикате внешнего запроса (в предложении WHERE или HAVING), определяющего, верно оно или нет. Совместно с подзапросом можно использовать предикат EXISTS, который возвращает истину, если результат подзапроса не пуст.

В части FROM оператора SELECT допустимо применять синонимы к именам таблицы, если при формировании запроса нам требуется более чем один экземпляр одной и той же таблицы. Синонимы задаются с использованием необязательного ключевого слова AS:

FROM R1 AS A, R1 AS B или FROM R1 A, R1 B

1) На примере БД «Сессия». Список тех, кто сдал все положенные экзамены:

SELECT ФИО FROM R1 as a WHERE Оценка > 2 GROUP BY ФИО HAVING COUNT(*) = (SELECT COUNT(*) FROM R2, R3 WHERE R2.Группа=R3.Группа AND R2.ФИО=a.ФИО)

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

2) Список тех, кто должен был сдавать экзамен по БД, но пока еще не сдавал.

SELECT ФИО FROM R2 a, R3 WHERE а.Группа=R3.Группа AND R3.Дисциплина = ‘БД’ AND NOT EXISTS (SELECT ФИО FROM R1 WHERE R1.ФИО=a.ФИО AND R1.Дисциплина = ‘БД’)

Предикат EXISTS (SubQuery) истинен, когда результат подзапроса SubQuery не пуст, то есть содержит хотя бы одну строку, в противном случае предикат EXISTS ложен. Предикат NOT EXISTS обратно — истинен только тогда, когда результат SubQuery пуст.

NOT EXISTS с вложенным запросом позволяет обойтись без операции разности отношений. Например, формулировка запроса со словом "все" может быть выполнена как бы с двойным отрицанием. Рассмотрим пример базы, которая моделирует поставку отдельных деталей отдельными поставщиками:

SP <номер_поставщика, номер_детали>, P <номер_детали, наименование>

3) Найти поставщиков, которые поставляют все детали:

SELECT DISTINCT номер_поставщика FROM SP SP1 WHERE NOT EXISTS (SELECT номер_детали FROM P WHERE NOT EXISTS (SELECT * FROM SP SP2 WHERE P2.номер_поставщика=SP1.номер_поставщика AND sp2.номер_детали = P.номер_детали))

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

SELECT DISTINCT номер_поставщика FROM SP GROUP BY номер_поставщика HAVING COUNT(DISTINCT номер_детали) = (SELECT COUNT(номер_детали) FROM P)

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

Например, найдем студентов, которые сдали все экзамены на оценку не ниже чем "хорошо". Работаем с БД «Сессия», но добавим к ней еще одно отношение R4, которое характеризует сдачу лабораторных работ в течение семестра:

R1 = <ФИО, Дисциплина, Оценка>; R2 = <ФИО, Группа>; R3 = <Группы, Дисциплина>;
R4 = <ФИО, Дисциплина, Номерлабраб, Оценка>; SELECT R1.ФИО FROM R1 WHERE 4 <= ALL (SELECT R11.Оценка FROM R1 AS R11 WHERE R1.ФИО = R11.ФИО)

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

SELECT R1.ФИО FROM R1 WHERE R1.Оценка >= ANY (SELECT R4.Оценка FROM R4 WHERE R1.Дисциплина = R4. Дисциплина AND R1.ФИО = R4.ФИО)

Внешние объединения

Стандарт SQL92 расширил понятие условного объединения. В стандарте SQL89 при объединении отношений использовались только условия, задаваемые в части WHERE оператора SELECT, и в этом случае в результирующее отношение попадали только сцепленные по заданным условиям строки исходных таблиц, для которых эти условия были определены и истинны. Однако в действительности часто необходимо объединять таблицы таким образом, чтобы в результат попали все строки из первой таблицы, а вместо тех строк второй таблицы, для которых не выполнено условие соединения, в результат попадали бы неопределенные значения. Или наоборот, включаются все строки из правой (второй) таблицы, а отсутствующие части строк из первой таблицы дополняются неопределенными значениями. Такие объединения были названы внешними в противоположность объединениям, определенным стандартом SQL89, которые стали называться внутренними.

В общем случае синтаксис части FROM в стандарте SQL92 выглядит следующим образом:

FROM < список исходных таблиц>< выражение естественного объединения >< выражение объединения >< выражение перекрестного объединения >< выражение запроса на объединение > <список исходных таблиц> =
<имя_таблицы_1> [имя синонима таблицы_1][ …][,<имя_таблицы_n>[ <имя синонима таблицы_n>]] <выражение естественного объединения> =<имя_таблицы_1> NATURAL { INNER | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER]} JOIN <имя_таблицы_2> <выражение перекрестного объединения> =<имя_таблицы_1> CROSS JOIN <имя_таблицы_2> <выражение запроса на объединение> = <имя_таблицы_1> UNION JOIN <имя_таблицы_2> <выражение объединения> =
<имя_таблицы_1> {INNER | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER]} JOIN {ON условие | [USING (список столбцов)]} <имя_таблицы_2>

В этих определениях INNER — означает внутреннее объединение. Ключевое слово OUTER означает внешнее, но если заданы ключевые слова FULL, LEFT, RIGHT, то объединение всегда считается внешним.

LEFT — левое объединение, то есть в результат входят все строки <имя_таблицы_1>, а части результирующих строк, для которых не было соответствующих значений в <имя_таблицы_2>, дополняются значениями NULL. Ключевое слово RIGHT означает правое внешнее объединение, и в отличие от левого объединения в этом случае в результирующее отношение включаются все строки <имя_таблицы_2>, а недостающие части из <имя_таблицы_1> дополняются неопределенными значениями. Ключевое слово FULL определяет полное внешнее объединение: и левое и правое. При полном внешнем объединении выполняются и правое и левое внешние объединения и в результирующее отношение включаются все строки из <имя_таблицы_1>, дополненные неопределенными значениями, и все строки из <имя_таблицы_2>, также дополненные неопределенными значениями.

Рассмотрим выполнение внешних объединений на примере БД «Сессия». Создадим отношение, в котором будут стоять все оценки, полученные всеми студентами по всем экзаменам, которые они должны были сдавать. Если студент не сдавал данного экзамена, то вместо оценки у него будет стоять неопределенное значение. Для этого выполним последовательно естественное внутреннее объединение таблиц R2 и R3 по атрибуту Группа, а полученное отношение соединим левым внешним объединением с таблицей R1, используя столбцы ФИО и Дисциплина.

SELECT R1.ФИО, R1.Дисциплина, R1.Оценка
FROM (R2 NATURAL INNER JOIN R3) LEFT JOIN R1 USING (ФИО, Дисциплина)

Рассмотрим еще один пример БД «Библиотека». Состав таблиц:

BOOKS <ISBN, TITLE, AUTOR, COAUTOR, YEARIZD, PAGES>READER<NUM_READER, NAME_READER, ADRESS, PHONE, WORK_PHONE, BIRTH_DAY>EXEMPLARE <INV, ISBN, YES_NO, NUM_READER, DATE_IN, DATE_OUT>

Здесь таблица BOOKS описывает все книги, присутствующие в библиотеке, атрибуты:

ISBN — уникальный шифр книги;

TITLE — название книги;

AUTOR — фамилия автора;

COAUTOR — фамилия соавтора;

YEARIZD — год издания;

PAGES — число страниц.

Таблица READER хранит сведения обо всех читателях библиотеки, атрибуты:

NUM_READER — уникальный номер читательского билета;

NAME_READER — фамилию и инициалы читателя;

ADDRESS — адрес читателя;

PHONE — номер домашнего телефона;

WORK_PHONE — номер рабочего телефона;

BIRTH_DAY — дату рождения читателя.

Таблица EXEMPLARE содержит сведения о текущем состоянии всех экземпляров всех книг:

INV — уникальный инвентарный номер экземпляра книги;

ISBN — шифр книги, который определяет, какая это книга, и ссылается на сведения из первой таблицы;

YES_NO — признак наличия или отсутствия в библиотеке данного экземпляра в текущий момент;

NUM_READER — номер читательского билета, если книга выдана читателю, и NULL в противном случае;

DATE_IN — если книга у читателя, то это дата, когда она выдана читателю;

DATE_OUT — дата, когда читатель должен вернуть книгу в библиотеку.

Определим перечень книг у каждого читателя; если у читателя нет книг, то номер экземпляра книги равен NULL:

SELECT READER.NAME_READER, EXEMPLARE.INV FROM READER LEFT JOIN EXEMPLARE ON READER.NUM_READER=EXEMPLARE.NUM_READER

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

SELECT * FROM (BOOKS LEFT JOIN EXEMPLARE) LEFT JOIN (READER NATURAL JOIN EXEMPLARE) USING (ISBN)

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

SELECT — запросUNION SELECT — запросUNIONSELECT — запрос

Например, нужно вывести список читателей, которые держат на руках книгу "Идиот" или книгу "Преступление и наказание". Вот как будет выглядеть запрос:

SELECT READER.NAME_READER FROM READER, EXEMPLARE, BOOKS WHERE EXEMPLARE.NUM_READER = READER.NUM_READER AND EXEMPLRE.ISBN = BOOKS.ISBN AND BOOKS.TITLE = ‘Идиот’UNIONSELECT READER.NAME_READER FROM READER, EXEMPLARE, BOOKS WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND EXEMPLRE.ISBN = BOOKS.ISBN AND BOOKS.TITLE = ‘Преступление и наказание’

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

SELECT — запрос UNION ALLSELECT - запрос UNION ALLSELECT - запрос

Ни один из исходных запросов в операции UNION не должен содержать предложения упорядочения результата ORDER BY, однако результат объединения может быть упорядочен, для этого предложение ORDER BY с указанием списка столбцов упорядочения записывается после текста последнего исходного SELECT-запроса.