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

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

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

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

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

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

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

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

Результат:

Дисциплина	COUNT(*)
Базы данных
Теория информации
Сети и телекоммуникации
Английский язык

Пример. Получить список дисциплин, по которым сдали экзамен не менее 5 человек:

SELECT R1.ДисциплинаFROM R1GROUP BY R1.ДисциплинаHAVING COUNT(*) >= 5 Результат:

Дисциплина

Базы данных

Здесь инструкция HAVING выбирает группы, удовлетворяющие заданному условию.

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

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

Примеры.

PNUM	PNAME	PSTATUS
	Иванов
	Петров
	Сидоров

Отношение P (Поставщики)

DNUM	DNAME	DSTATUS
	Болт
	Гайка
	Винт

Отношение D (Детали)

Номер поставщика PNUM	Номер детали DNUM	Поставляемое количество VOLUME

Отношение PD (Поставки)

1. Получить список поставщиков, статус которых меньше максимального статуса в таблице поставщиков (сравнение с подзапросом):

SELECT *

FROM P

WHERE P.STATYS <

(SELECT MAX(P.STATUS)

FROM P);

Замечание. Т.к. поле P.STATUS сравнивается с результатом подзапроса, то подзапрос должен быть сформулирован так, чтобы возвращать таблицу, состоящую ровно из одной строки и одной колонки.

Замечание. Результат выполнения запроса будет эквивалентен результату следующей последовательности действий:

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

2. Использование предиката IN. Получить список поставщиков, поставляющих деталь номер 2:

SELECT *

FROM P

WHERE P.PNUM IN

(SELECT DISTINCT PD.PNUM

FROM PD

WHERE PD.DNUM = 2);

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

Замечание. Результат выполнения запроса будет эквивалентен результату следующей последовательности действий:

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

3. Использование предиката EXISTS. Получить список поставщиков, поставляющих деталь номер 2:

SELECT *

FROM P

WHERE EXISTS

(SELECT *

FROM PD

WHERE

PD.PNUM = P.PNUM AND

PD.DNUM = 2);

Замечание. Результат выполнения запроса будет эквивалентен результату следующей последовательности действий:

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

Замечание. В отличие от двух предыдущих примеров, вложенный подзапрос содержит параметр (внешнюю ссылку), передаваемый из основного запроса - номер поставщика P.PNUM. Такие подзапросы называются коррелируемыми (correlated). Внешняя ссылка может принимать различные значения для каждой строки-кандидата, оцениваемого с помощью подзапроса, поэтому подзапрос должен выполняться заново для каждой строки, отбираемой в основном запросе. Такие подзапросы характерны для предиката EXISTS, но могут быть использованы и в других подзапросах.

Замечание. Может показаться, что запросы, содержащие коррелируемые подзапросы будут выполняться медленнее, чем запросы с некоррелируемыми подзапросами. На самом деле это не так, т.к. то, как пользователь, сформулировал запрос, не определяет, как этот запрос будет выполняться. Язык SQL является непроцедурным, а декларативным. Это значит, что пользователь, формулирующий запрос, просто описывает, каким должен быть результат запроса, а как этот результат будет получен - за это отвечает сама СУБД.

4. Использование предиката NOT EXISTS. Получить список поставщиков, не поставляющих деталь номер 2:

SELECT *

FROM P

WHERE NOT EXISTS

(SELECT *

FROM PD

WHERE

PD.PNUM = P.PNUM AND

PD.DNUM = 2);

Замечание. Также как и в предыдущем примере, здесь используется подзапрос. Отличие в том, что в основном запросе будут отобраны те строки из таблицы поставщиков, для которых вложенный подзапрос не выдаст ни одной строки.

5. Получить имена поставщиков, поставляющих все детали:

SELECT DISTINCT PNAME

FROM P

WHERE NOT EXISTS

(SELECT *

FROM D

WHERE NOT EXISTS

(SELECT *

FROM PD

WHERE

PD.DNUM = D.DNUM AND

PD.PNUM = P.PNUM));

Замечание. Данный запрос содержит два вложенных подзапроса и реализует реляционную операцию деления отношений.

Самый внутренний подзапрос параметризован двумя параметрами (D.DNUM, P.PNUM) и имеет следующий смысл: отобрать все строки, содержащие данные о поставках поставщика с номером PNUM детали с номером DNUM. Отрицание NOT EXISTS говорит о том, что данный поставщик не поставляет данную деталь. Внешний к нему подзапрос, сам являющийся вложенным и параметризованным параметром P.PNUM, имеет смысл: отобрать список деталей, которые не поставляются поставщиком PNUM. Отрицание NOT EXISTS говорит о том, что для поставщика с номером PNUM не должно быть деталей, которые не поставлялись бы этим поставщиком. Это в точности означает, что во внешнем запросе отбираются только поставщики, поставляющие все детали.

Внешние соединения

Часто необходимо объединять таблицы таким образом, чтобы в результат попали все строки из первой таблицы, а вместо тех строк второй таблицы, для которых не выполнено условие соединения, в результат попадали бы неопределенные значения. Или наоборот, включаются все строки из правой (второй) таблицы, а отсутствующие части строк из первой таблицы дополняются неопределенными значениями. Такие объединения были названы внешними.

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

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> { INNERFULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER]} JOIN {ON условие | [USING (список столбцов)]} <имя_таблицы_2>

В этих определениях INNER — означает внутреннее (естественное) соединение, LEFT — левое соединение, то есть в результат входят все строки таблицы 1, а части результирующих кортежей, для которых не было соответствующих значений в таблице 2, дополняются значениями NULL (неопределено). Ключевое слово RIGHT означает правое внешнее соединение, и в отличие от левого соединения в этом случае в результирующее отношение включаются все строки таблицы 2, а недостающие части из таблицы 1 дополняются неопределенными значениями. Ключевое слово FULL определяет полное внешнее соединение: и левое и правое. При полном внешнем соединении выполняются и правое и левое внешние соединения и в результирующее отношение включаются все строки из таблицы 1, дополненные неопределенными значениями, и все строки из таблицы 2, также дополненные неопределенными значениями.

Ключевое слово OUTER означает внешнее, но если заданы ключевые слова FULL, LEFT, RIGHT, то соединение всегда считается внешним.

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

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

Результат: