Базы Данных - Сибилев, 2007

192

РА. Предложение FROM построит в рабочем буфере F-таблицу – естест-

венное соединение таблиц J и SPJ:

Предложение WHERE выполнит селекцию F-таблицы по условию

Snum = ‘S1’ и создаст W-таблицу:

Наконец, предложение SELECT со спецификатором DISTINCT соз-

даст требуемую проекцию W-таблицы на столбец Jnam. Спецификатор

DISTINCT необходим, т.к. исполняющая система не удаляет дубликаты строк автоматически.

Вариант 2, возможно, представляется менее наглядным, чем первый.

Формула оператора выборки соответствует следующей фразе: «Выполнить проекцию на атрибут Jnam соединения отношений J и SPJ по условию

Snum = ‘S1’ AND J.Jnum = SPJ.Jnum». В этом случае F-таблица эквива-

лентна W-таблице предыдущего варианта и предложение WHERE в опе-

раторе выборки не нужно.

Наконец, будет выполнена SQL-проекция этой таблицы на столбец

Jnam.

Вариант 4 в словесной формулировке таков: «Выполнить проекцию на атрибут Jnam селекции расширенного прямого произведения отноше-

ний J и SPJ по условию Snum = ‘S1’ AND J.Jnum = SPJ.Jnum». Он (единст-

венный из приведённых) соответствует стандарту SQL1. Мы не будем приводить здесь F- и W-таблицы, производимые в процессе обработки этого запроса. Читателю рекомендуется проделать это самостоятельно.

В заключение заметим, что на самом деле логика исполнения экви-

валентных операторов SELECT различна только в представлении авторов формулировок. Всякая реальная СУБД обязательно имеет подсистему оп-

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

SELECT, а от того, насколько удачно сконструирован оптимизатор.

Выразительные возможности SQL в части формулирования запросов к данным мало отличаются от возможностей естественного языка. Форму-

лируя запрос, следует стремиться к ясности формулировки для человека,

нимало не заботясь о СУБД.

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

лий, для которых они поставляют детали.

Источником данных являются таблицы S, J и SPJ. Словесная форму-

лировка предполагает использование внешнего соединения. Один из воз-

можных планов выполнения запроса таков:

–получить естественное соединение отношений J и SPJ;

–получить левое внешнее соединение отношения S и результата пре-

дыдущего шага;

195

–выполнить проекцию результата предыдущего шага на атрибуты

Snam и Jnam.

Вот соответствующая этому плану формула оператора выборки:

SELECT DISTINCT Snam, Jnam

FROM S LEFT JOIN (J JOIN SPJ);

а вот результат его исполнения:

Эквивалентный результат произведёт оператор

SELECT DISTINCT Snam, Jnam

FROM (S LEFT JOIN SPJ) JOIN J;

Читатель, без сомнения, сможет предложить ещё ряд эквивалентных формул SQL2. Однако SQL1 не имеет столь компактных средств записи внешних соединений. Формула SQL1 для рассматриваемого запроса при-

ведена в п. 7.2.15

7.2.10 Предложение GROUP BY и агрегатные функции SQL

Оператор SELECT, включающий предложение GROUP BY, реали-

зует реляционную операцию подведения итогов [Ошибка! Источник ссылки не найден., п. 2.5.5]. Предложение GROUP BY определяет груп-

пы строк рабочего буфера (F- или W-таблицы), для которых должны быть вычислены значения агрегатных функций. После обработки предложения

197

Замечание. Это требование выглядит логически неоправданным.

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

Он предполагает наличие средств объявления функциональных зависимо-

стей. Стандарты же не поддерживают как обязательное даже требование целостности сущности.

Стандарты SQL дают следующее определение синтаксиса агрегат-

ных функций:

агрегатная_функция ::= { COUNT(*) } |

{

{ COUNT | SUM | AVG | MAX | MIN }

( [DISTINCT | ALL] выражение )

}

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

Если оператор выборки содержит предложение GROUP BY, то агрегатные функции могут использоваться в его целевом списке и в предикате предложения HAVING. При этом их значения вычисляются на

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

когда агрегатная функция не может встретиться в предикатах пред-

ложений WHERE или FROM иначе, как в подзапросе.

Функция COUNT(*) возвращает полное число строк в группе, вклю-

чая пустые и дубликаты.

При вычислении значений остальных пяти функций действуют сле-

дующие правила:

− перед вычислением значения функции все неопределённые значе-

198

ния аргумента исключаются;

− если определён параметр DISTINCT, то дубликаты значений аргу-

мента в подсчёте значения функции не участвуют, в противном случае ис-

пользуются все значения аргумента (параметр ALL определён по умолча-

нию);

− если аргумент пуст, т.е. содержит только NULL-значения, то функ-

ция COUNT возвращает значение 0 (ноль), а все остальные функции – значение NULL;

−функция COUNT всегда возвращает целое число; другие функции наследуют типы аргументов;

−функци SUM и AVG работают только с аргументами числовых ти-

пов;

− функции COUNT, MAX и MIN могут использоваться с аргумен-

тами любых типов.

Пример 1. Получить общее число зарегистрированных поставок.

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

SELECT COUNT(*) AS Число_поставок

FROM SPJ;

Запрос возвратит следующую таблицу:

Число_поставок

19

В нашем частном случае это истина. Однако таблицы SQL – это не отношения. Представим себе, что не определён первичный ключ таблицы

SPJ. Тогда в ней могут оказаться и частично или полностью пустые строки,

и дубликаты строк, т.е. мусор, который нередко встречается в БД, создан-

ных дилетантами. Наш оператор подсчитает все строки. Поэтому, если мы не уверены в том, что таблица не содержит мусора, нам следует сформули-

ровать запрос так: «Подсчитать число строк таблицы SPJ, в которых все столбцы имеют определённые значения и наборы значений столбцов

199

Snum, Pnum, Jnum не повторяются». Попытайтесь самостоятельно записать соответствующий оператор. Он будет значительно сложнее приведённого выше.

Пример 2. Подсчитать средний объём поставки детали P1.

SELECT AVG(Qt)

FROM SPJ

WHERE Pnum = ‘P1’;

900

Пример 3. Для каждого поставщика подсчитать количество постав-

ляемых им видов деталей.

Это двусмысленная формулировка, но, скорее всего, именно так и будет сформулировано пожелание бухгалтера, для которого нужно создать программу подготовки отчётов. Что ему нужно на самом деле? Количество видов деталей, поставляемых каждым поставщиком, зарегистрированным в БД? Или только тем, кто реально выполнял поставки? Какие сведения о поставщике должны быть включены в выходную таблицу? Только номер?

Номер и имя? И т.п.

Пусть после уточнения требования пользователя оказались следую-

щими: «Для каждого существующего поставщика получить его номер и количество поставляемых им видов деталей». В текущем состоянии БД с некоторыми поставщиками могут быть не связаны какие-либо поставки.

Поэтому в качестве источника данных следует использовать левое внеш-

нее соединение таблиц S и SPJ.

SELECT S.Snum AS Поставщик,

COUNT(DISTINCT Pnum) AS Видов

FROM S LEFT JOIN SPJ

GROUP BY S.Snum;