metBD (1084482), страница 38
Текст из файла (страница 38)
GROUP BY НОМЕР_ДЕТАЛИ;
Результат:
НОМЕР_ДЕТАЛИ | |
P1 | 300 |
P2 | 800 |
P4 | 300 |
P5 | 400 |
Строки, не удовлетворяющие фразе WHERE, исключаются до того, как будет осуществляться какое-либо группирование.
Использование having
Выдать номера деталей для всех деталей, поставляемых более чем одним поставщиком.
SELECT НОМЕР_ДЕТАЛИ
FROM SP
GROUP BY НОМЕР_ДЕТАЛИ
HAVING COUNT (*) > 1;
Результат:
НОМЕР_ДЕТАЛИ |
P1 |
P2 |
P4 |
P5 |
Фраза HAVING играет такую же роль для групп, что и фраза WHERE для строк. (Конечно, если специфицирована фраза HAVING, то должна быть специфицирована и фраза GROUP BY.) Иными словами, HAVING используется для того, чтобы исключать группы, точно так же, как WHERE используется для исключения строк. Выражение во фразе HAVING должно принимать единственное значение для группы.
Было показано, что этот запрос может быть сформулирован без GROUP BY (и без HAVING) с использованием коррелированного подзапроса. Однако этот пример в действительности основан на несколько ином восприятии логики, связанной с определением ответа на этот вопрос. Можно также сформулировать запрос, используя по существу ту же логику, что и в варианте GROUP BY/HAVING, но без явного использования фраз GROUP BY и HAVING вообще:
SELECT DISTINCT НОМЕР_ДЕТАЛИ
FROM SP SPX
WHERE 1 <
(SELECT COUNT (*)
FROM SP SPY
WHERE SPY.НОМЕР_ДЕТАЛИ = SPX.HOMEP_ДЕТАЛИ);
Следующий вариант, в котором вместо SPX используется таблица Р, является, вероятно, более ясным:
SELECT НОМЕР_ДЕТАЛИ
FROM Р WHERE 1 <
(SELECT COUNT (НОМЕР_ПОСТАВЩИКА)
FROM SP
WHERE НОМЕР_ДЕТАЛИ = P.НОМЕР_ДЕТАЛИ);
Еще одна формулировка связана с использованием EXISTS:
SELECT НОМЕР_ДЕТАЛИ
FROM Р
WHERE EXISTS
(SELECT *
FROM SP SPX
WHERE SPX.НОМЕР_ДЕТАЛИ = P.НОМЕР_ДЕТАЛИ
AND EXISTS
(SELECT *
FROM SP SPY
WHERE SPY.НОМЕР_ДЕТАЛИ = P.НОМЕР_ДЕТАЛИ
AND SPY.НОМЕР_ПОСТАВЩИКА !=
SPX.НОМЕР_ПОСТАВЩИКА);
Все эти альтернативные варианты являются в некотором отношении более предпочтительными по сравнению с вариантом GROUP BY/HAVING в связи с тем, что они, по крайней мере, логически более понятны и, в частности, не требуют этих дополнительных языковых конструкций. Из первоначальной формулировки задачи на естественном языке — «Выдать номера деталей для всех деталей, поставляемых более чем одним поставщиком» — без сомнения, не ясно, что группирование само по себе — это то, что необходимо для ответа на данный вопрос, и в нем, действительно, нет необходимости. Не является также непосредственно очевидным, что необходимо условие HAVING, а не условие WHERE. Вариант GROUP BY/HAVING более похож на процедурное предписание для решения задачи, чем просто на ясную логическую формулировку ее существа. С другой стороны, нельзя опровергнуть тот факт, что вариант GROUP BY/HAVING наиболее лаконичен. Далее, в свою очередь имеются некоторые задачи такого же общего характера, для которых GROUP BY и HAVING просто неадекватны, в силу чего следует использовать один из альтернативных подходов.
Наконец, конструкции GROUP BY свойственно серьезное ограничение — она работает только на одном уровне. Невозможно разбить каждую из этих групп на группы более низкого уровня и т.д., а затем применить некоторую стандартную функцию, например SUM или AVG на каждом уровне группирования.
Объединение
Объединением, двух множеств называется множество всех элементов, принадлежащих какому-либо одному или обоим исходным множествам. Поскольку отношение—это множество (множество строк), можно построить объединение двух отношений. Результатом будет множество, состоящее из всех строк, входящих в какое-либо одно или в оба первоначальных отношения. Если, однако, этот результат сам по себе должен быть другим отношением, а не просто разнородной смесью строк, то два, исходных отношения должны быть совместимыми по объединению. Нестрого говоря, строки в обоих отношениях должны быть одной и той же «формы». Что касается SQL, то две таблицы совместимы по объединению (и к ним может быть применен оператор UNION) тогда и только тогда, когда:
-
они, имеют одинаковое число столбцов, например, m;
-
для всех i (i=1, 2, ..., m) i-й столбец первой таблицы и i-й столбец второй таблицы имеют в точности одинаковый тип данных:
-
если тип данных—DECIMAL (p, q), то p должно быть одинаковым для обоих столбцов и q должно быть одинаковым для обоих столбцов;
-
если тип данных—CHAR (n), то должно быть одинаковым для обоих столбцов;
-
если тип данных—VARCHAR (n), то n должно быть одинаковым для обоих столбцов;
-
если NOT NULL специфицировано для какого-либо из этих столбцов, то такая же спецификация должна быть для другого столбца.
Запрос, требующий использования union
Выдать номера деталей, которые имеют вес более 16 фунтов либо поставляются поставщиком S2 (либо то и другое).
SELECT НОМЕР_ДЕТАЛИ
FROM Р
WHERE ВЕС > 16
UNION
SELECT НОМЕР_ДЕТАЛИ
FROM SP
WHERE НОМЕР_ПОСТАВЩИКА = ’S2’;
Результат:
P1
P2
P3
P6
Из этого простого примера следует несколько соображений:
-
Избыточные дубликаты всегда исключаются из результата UNION. Поэтому, хотя в рассматриваемом примере деталь P2 выбирается обеими из двух составляющих предложений SELECT, в окончательном результате она появляется только один раз.
-
Любое число предложений SELECT может быть соединено операторами UNION. Можно расширить данный пример с тем, чтобы включить номера красных деталей, дополнив приведенный выше запрос следующей конструкцией:
UNION
SELECT НОМЕР_ДЕТАЛИ
FROM Р
WHERE ЦВЕТ = ’Красный’
перед заключительной точкой с запятой. Заметим, что такого же результата можно было достигнуть, добавляя к первому из первоначальных предложений SELECT фразу OR ЦВЕТ = ’Красный’.
-
Любая фраза ORDER BY в запросе должна входить как часть только в последнее предложение SELECT и должна указывать столбцы, по которым осуществляется упорядочение, путем указания их порядковых позиций, т. е. их номеров.
-
В связи с оператором UNION часто оказывается полезной возможность включения констант во фразу SELECT. Например, можно указать, какому из двух условий WHERE удовлетворяет каждая из отдельных деталей:
SELECT НОМЕР_ДЕТАЛИ, ’ее вес> 16 фунтов’
FROM Р
WHERE ВЕС > 16
UNION
SELECT НОМЕР_ДЕТАЛИ, ’деталь поставляется S2’
FROM SP
WHERE НОМЕР_ПОСТАВЩИКА = ’S2' ORDER BY 2, 1;
Результат:
P1 | деталь поставляется S2 |
P2 | деталь поставляется S2 |
P2 | ее вес> 16 фунтов |
P3 | ее вес> 16 фунтов |
P6 | ее вес> 16 фунтов |
Когда строковая константа выступает в качестве элемента, подлежащего выборке, считается, что она имеет тип VARCHAR и длину, равную числу литер в константе, и допускаются неопределенные значения.
-
Может возникнуть желание узнать, поддерживаются ли в языке SQL какие-либо аналоги операторов INTERSECTION (пересечение) и DIFFERENCE (разность), поскольку объединение, пересечение и разность в теоретико-множественных рассмотрениях обычно выступают совместно. Пересечение двух множеств представляет собой множество всех элементов, принадлежащих обоим исходным множествам.
Разность двух множеств — это множество элементов, принадлежащих первому исходному множеству, но не принадлежащих второму. В языке SQL эти два оператора непосредственно не поддерживаются, но каждый из них может быть смоделирован с помощью функции EXISTS. Пусть, например, А и В — таблицы, состоящие из единственного столбца, а именно, столбца номеров поставщиков. Пусть А представляет «поставщиков из Лондона», а В — «поставщиков, которые поставляют деталь Р1».
Тогда
SELECT НОМЕР_ПОСТАВЩИКА
FROM А
WHERE EXISTS
(SELECT НОМЕР_ПОСТАВЩИКА
FROM В
WHERE В.НОМЕР_ПОСТАВЩИКА = А.НОМЕР_ПОСТАВЩИКА);
представляет пересечение А и В, т.е. поставщиков из Лондона, которые поставляют деталь Р1, а
SELECT НОМЕР_ПОСТАВЩИКА
FROM А
WHERE NOT EXISTS
(SELECT НОМЕР_ПОСТАВЩИКА
FROM В
WHERE В.НОМЕР_ПОСТАВЩИКА =
А.НОМЕР_ПОСТАВЩИКА);
представляет разность между А и В (в указанном порядке), т. е. поставщиков из Лондона, которые не поставляют деталь Р1. Упражнение. Что представляет собой разность между В и А (именно в этом порядке)?
Заключение
Теперь мы рассмотрели все возможности предложения SELECT языка SQL. Чтобы завершить эту часть, приведем весьма изощренный пример, который показывает, каким образом многие (но отнюдь не все) эти средства могут быть использованы вместе в едином запросе. Рассмотрим также концептуальный алгоритм обработки SQL — запросов общего вида.
Многоаспектный пример
Выдать номер детали, вес в граммах, цвет и максимальный объем поставки для всех красных и голубых деталей, таких, что общий объем их поставки больше, чем 350, исключая при этом из общего объема все такие поставки, для которых количество меньше или равно 200 деталей. Результат упорядочить по убыванию номеров деталей в рамках возрастающих значений этого максимального объема поставки.
SELECT P.НОМЕР_ДЕТАЛИ, ’вес в граммах = ’, Р.ВЕС*454, Р.ЦВЕТ
’максимальный объем поставки =’,
МАХ (SP.КОЛИЧЕСТВО)
FROM Р, SP
WHERE Р.НОМЕР_ДЕТАЛИ = SP.НОМЕР_ДЕТАЛИ
AND Р.ЦВЕТ IN (’Красный’, ’Голубой’)
AND SP.КОЛИЧЕСТВО > 200
GROUP BY Р.НОМЕР_ДЕТАЛИ, Р.ВЕС, Р.ЦВЕТ
HAVING SUM (КОЛИЧЕСТВО) > 350
ORDER BY 6, P.НОМЕР_ДЕТАЛИ DESC;
Результат:
НОМЕР_ ДЕТАЛИ | ЦВЕТ | ||||
P1 | вес в | 5448 | Красный | максимальный объем поставки = | 300 |
P5 | вес в | 5448 | Голубой | максимальный объем поставки = | 400 |
P3 | вес в | 7718 | Голубой | максимальный объем поставки = | 400 |
Пояснение. Фразы предложения SELECT применяются в таком порядке, в котором они записаны, за исключением самой фразы SELECT, которая применяется между фразами HAVING и ORDER BY, если они имеются. В данном примере, следовательно, можно представить себе, что результат строится следующим образом.
-
FROM. В результате обработки фразы FROM создается новая таблица, которая является декартовым произведением таблиц Р и SP.
-
WHERE. Из результата шага 1 исключаются все строки, не удовлетворяющие фразе WHERE. В данном примере исключаются строки, не удовлетворяющие предикату: P.HOMEP_ДЕТАЛИ = SP.HOMEP_ДЕТАЛИ AND Р.ЦВЕТ IN (’Красный’, ’Голубой’) AND SР.КОЛИЧЕСТВО > 200.
-
GROUP BY. Результат шага 2 группируется по значениям поля (полей), указанного во фразе GROUP BY. В нашем примере это поля Р.НОМЕР_ДЕТАЛИ, Р.ВЕС и Р.ЦВЕТ.
-
HAVING. Группы, не удовлетворяющие условию
SUM (КОЛИЧЕСТВО) > 350,
исключаются из результата, полученного на шаге 3.
-
SELECT. Каждая группа, полученная на шаге 4, следующим образом генерирует единственную строку для результата. Во-первых, из группы выделяются номер детали, вес, цвет и максимальный объем поставки. Во-вторых, вес преобразуется в граммы. В-третьих, в соответствующие места полученной строки вставляются две строковые константы ’вес в граммах=’ и ’максимальный объем поставки=’.
-
ORDER BY. Результат шага 5 упорядочивается в соответствии со спецификацией фразы ORDER BY для получения окончательного результата.
Конечно, приведенный выше запрос весьма сложен, но представим себе, какую он выполняет работу. Обычная программа, в другом языке, которая выполняет ту же самую работу, вполне могла бы составить девять страниц по сравнению только с девятью строками, приведенными выше. При этом работа, необходимая для того, чтобы эта программа стала действующей, значительно больше, чем это необходимо для формулировки приведенного варианта запроса на языке SQL. Большинство запросов на практике будет, конечно, во всяком случае, значительно проще по сравнению с ним.