metBD (1084482), страница 37

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

Текст из файла (страница 37)

FROM ВРЕМЕННАЯ

WHERE NOT EXISTS

(SELECT *

FROM SP SPY

WHERE SPY.НОМЕР_ПОСТАВЩИКА =

SPX.НОМЕР_ПОСТАВЩИКА

AND SPY.НОМЕР_ДЕТАЛИ =

ВРЕМЕННАЯ.НОМЕР_ДЕТАЛИ));

Результат:

(Заметим, однако, что этот запрос отличается от запроса выше необходимостью использовать, по крайней мере, один псевдоним, поскольку мы выделяем значения столбца НОМЕР_ПОСТАВЩИКА из таблицы SP, а не значения столбца ФАМИЛИЯ из таблицы S. По этой причине необходимо иметь возможность одновременно делать две различные ссылки на таб­лицу SP.)

Теперь таблица ВРЕМЕННАЯ может быть уничтожена. Идея о том, чтобы справляться со сложными запросами таким по­шаговым образом для легкости понимания, часто оказывается полезной. Однако можно также выразить рассматриваемый пол­ный запрос в виде единственного предложения SELECT, полно­стью исключая при этом необходимость в таблице ВРЕМЕННАЯ:

SELECT DISTINCT НОМЕР_ПОСТАВЩИКА

FROM SP SPX

WHERE NOT EXISTS

(SELECT *

FROM SP SPY

WHERE НОМЕР_ПОСТАВЩИКА = ’S2’

AND NOT EXISTS

(SELECT *

FROM SP SPZ

WHERE SPZ.НОМЕР_ПОСТАВЩИКА =

SPX.НОМЕР_ПОСТАВЩИКА

AND SPZ.НОМЕР_ДЕТАЛИ =

SPY.НОМЕР_ДЕТАЛИ));

Запрос, в котором используется импликация

Выдать номера поставщиков, поставляющих, по крайней мере, все те детали, которые поставляются поставщиком S2 (тот же са­мый запрос, что и в предыдущем примере).

В этом примере иллюстрируется еще одно очень полезное по­нятие — логическая импликация. Первоначальную задачу можно перефразировать следующим образом: «Выдать номера постав­щиков, скажем, Sx, таких, что ДЛЯ ВСЕХ деталей Ру, если по­ставщик S2 поставляет деталь Ру, то поставщик Sx поставляет деталь Ру».

Выражение

IF р THEN q (ЕСЛИ р ТО q),

где р и q — предикаты, является предикатом логической имплика­ции. Он определяется как эквивалент предиката:

NOT (р) OR q.

Иными словами, импликация «IF р THEN q» (читается также следующим образом: «из р СЛЕДУЕТ q») принимает значение ложь тогда и только тогда, когда q—ложь, а р—истина, как показывает приведенная ниже таблица истинности:

Многие формулировки задач на обычном языке весьма естест­венным образом выражаются в терминах логической импликации. Несколько примеров можно найти в конце данной части среди предлагаемых упражнений. Язык SQL непосредственно не поддер­живает импликацию. Но предыдущее определение показывает, каким образом любой содержащий импликацию предикат может быть трансформирован в другой предикат, который ее не содер­жит. Пусть, например, р представляет собой предикат «Постав­щик S2 поставляет деталь Ру», а q — предикат «Поставщик Sx поставляет деталь Ру». Тогда предикат

IF р THEN q

эквивалентен предикату

NOT (поставщик S2 поставляет деталь Ру)

OR (поставщик Sx поставляет деталь Ру);

или в языке SQL:

NOT EXISTS

(SELECT *

FROM SP SPY

WHERE SPY.НОМЕР_ПОСТАВЩИКА = ’S2’)

OR EXISTS

(SELECT *

FROM SP SPZ

WHERE SPZ.НОМЕР_ПОСТАВЩИКА = Sx

AND SPZ.НОМЕР_ДЕТАЛИ = SPY.НОМЕР_ДЕТАЛИ)

Следовательно, предикат

FORALL Ру (IF р THEN q),

который эквивалентен предикату

NOT EXISTS Ру (NOT (IF р THEN q)),

т. e, предикату

NOT EXISTS Ру (NOT (NOT (р) OR q)),

может быть записан, таким образом, в виде:

NOT EXISTS Ру (р AND NOT (q)),

или в языке SQL:

NOT EXISTS

(SELECT *

FROM SP SPY

WHERE SPY.НОМЕР_ПОСТАВЩИКА = ’S2’

AND NOT EXISTS

(SELECT *

FROM SP SPZ

WHERE SPZ.НОМЕР_ПОСТАВЩИКА = Sx

AND SPZ.НОМЕР_ДЕТАЛИ = SPY.НОМЕР_ДЕТАЛИ))

Поэтому полный запрос принимает вид:

SELECT DISTINCT НОМЕР_ПОСТАВЩИКА

FROM SP SPX

WHERE NOT EXISTS

(SELECT *

FROM SP SPY

WHERE SPY.НОМЕР_ПОСТАВЩИКА = ’S2’

AND NOT EXISTS

(SELECT *

FROM SP SPZ

WHERE SPZ.НОМЕР_ПОСТАВЩИКА =

SPX.НОМЕР_ПОСТАВЩИКА

AND SPZ.НОМЕР_ДЕТАЛИ = SPY.НОМЕР_ДЕТАЛИ)):

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

Стандартные функции

Хотя и весьма мощное во многих отношениях, предложение SELECT в том виде, как оно было до сих пор описано, остается все еще неадекватным для многих практических задач. Напри­мер, даже настолько простой запрос, как «Сколько имеется постав­щиков?» нельзя выразить, используя только введенные до сих пор конструкции. Для того чтобы усилить его основные возможности по выборке данных, в SQL предусматривается ряд специальных стандартных функций. В настоящее время доступны функции COUNT (число значений), SUM (сумма), AVG (среднее), МАХ (максимум) и MIN (минимум). Кроме специального случая «COUNT (*)» (см. ниже) каждая из этих функций оперирует совокупностью значений в одном столбце некоторой таблицы, воз­можно, производной, т. e. сконструированной некоторым образом из заданных базовых таблиц, и продуцирует в качестве ее резуль­тата единственное значение, определенное следующим образом:

COUNT — число значений в столбце

SUM — сумма значений по столбцу

AVG — среднее значение в столбце

МАХ — самое большое значение в столбце

MIN — самое малое значение в столбце

Для функций SUM и AVG рассматриваемый столбец должен содержать числовые значения. В общем случае аргументу функ­ции может факультативно предшествовать ключевое слово DISTINCT (различный), указывающее, что избыточные дублиру­ющие значения должны быть исключены перед тем, как будет применяться функция. Однако для функций МАХ и MIN ключе­вое слово DISTINCT не имеет отношения к делу и должно быть опущено. Для функции COUNT ключевое слово DISTINCT должно быть специфицировано. Специальная функция COUNT (*), для которой использование DISTINCT не допускается, предусмотрена для подсчета всех строк в таблице без исключения каких-либо дубликатов. Если DISTINCT специфицируется, то аргумент должен состоять только из имени столбца, например ВЕС. Если DISTINCT не специфицировано, аргумент может представлять собой ариф­метическое выражение, например ВЕС*454.

В столбце-аргументе всегда перед применением функции исклю­чаются все неопределенные значения, независимо от того, специ­фицировано ли DISTINCT, за исключением случая COUNT (*), при котором неопределенные значения обрабатываются точно так же, как и значения, не являющиеся неопределенными. Если оказывается, что аргумент — пустое множество, функция COUNT принимает значение нуль. Все другие функции принимают в этом случае неопределенное значение.

Функция во фразе select

Выдать общее количество поставщиков.

SELECT COUNT (*)

FROM S;

Результат:

5

Функция во фразе select со спецификацией distinct

Выдать общее количество поставщиков, поставляющих в на­стоящее время детали:

SELECT COUNT (DISTINCT НОМЕР_ПОСТАВЩИКА)

FROM SP;

Результат:

4

Функция во фразе select с предикатом

Выдать количество поставок для детали Р2.

SELECT COUNT (*)

FROM SP

WHERE НОМЕР_ДЕТАЛИ = ’Р2’;

Результат:

4

Функция во фразе select с предикатом

Выдать общее количество поставляемых деталей Р2.

SELECT SUM (КОЛИЧЕСТВО)

FROM SP

WHERE НОМЕР_ДЕТАЛИ = ’Р2’;

Результат:

1000

Функция в подзапросе

Выдать номера поставщиков со значением поля СОСТОЯНИЕ меньшим, чем текущее максимальное состояние в таблице S.

SELECT НОМЕР_ПОСТАВЩИКА

FROM S

WHERE СОСТОЯНИЕ <

(SELECT МАХ (СОСТОЯНИЕ)

FROM S);

Результат:

Функция в коррелированном подзапросе

Выдать номер поставщика, состояние и город для всех постав­щиков, у которых состояние больше или равно среднему для их конкретного города.

SELECT НОМЕР_ПОСТАВЩИКА, СОСТОЯНИЕ, ГОРОД

FROM S SX

WHERE СОСТОЯНИЕ >=

(SELECT AVG (СОСТОЯНИЕ)

FROM S SY

WHERE SY.ГОРОД = SX.ГОРОД);

Результат:

Включить в результат среднее состояние для каждого города невозможно.

Использование фразы group by

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

SELECT НОМЕР_ДЕТАЛИ, SUM (КОЛИЧЕСТВО)

FROM SP

GROUP BY НОМЕР_ДЕТАЛИ;

Результат:

Пояснение. С концептуальной точки зрения, оператор GROUP BY (группировать по) перекомпоновывает таблицу, пред­ставленную фразой FROM, в разделы или группы таким образом, чтобы в каждой группе все строки имели одно и то же значение поля, указанного во фразе GROUP BY. Это, конечно, не означает, что таблица физически перекомпоновывается в базе данных. В рас­сматриваемом примере строки таблицы SP группируются таким образом, что в одной группе содержатся все строки для детали Р1, в другой—все строки для детали Р2 и т. д. Далее, к каж­дой группе перекомпонованной таблицы, а не к каждой строке исходной таблицы применяется фраза SELECT. Каждое выра­жение во фразе SELECT должно принимать единственное значе­ние для группы, т. е. оно может быть либо самим полем, указан­ным во фразе GROUP BY, либо арифметическим выражением, включающим это поле, либо константой, либо такой функцией, как SUM, которая оперирует всеми значениями данного поля в группе и сводит эти значения к единственному значению.

Строки таблицы можно группировать по любой комбинации ее полей. Будет приведен пример, иллюстрирующий группи­рование более чем по одному полю. Заметим, что фраза GROUP BY не предполагает ORDER BY (упорядочить по). Чтобы гаран­тировать упорядочение результата этого примера по номерам деталей, следует специфицировать фразу ORDER BY НОМЕР_ДЕТАЛИ после фразы GROUP BY. Если поле, по значениям ко­торого осуществляется группирование, содержит какие-либо не­определенные значения, то каждое из них порождает отдельную группу.

Использование фразы where с group by

Выдать для каждой поставляемой детали ее номер и общий объем поставок, за исключением поставок поставщика S1:

SELECT НОМЕР_ДЕТАЛИ, SUM (КОЛИЧЕСТВО)

FROM SP

WHERE НОМЕР_ПОСТАВЩИКА != ’S1’

Характеристики

Список файлов книги