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

Файл №1084482 metBD (Учебное пособие) 38 страницаmetBD (1084482) страница 382018-01-12СтудИзба
Просмтор этого файла доступен только зарегистрированным пользователям. Но у нас супер быстрая регистрация: достаточно только электронной почты!

Текст из файла (страница 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 для строк. (Конечно, если специфицирована фраза HA­VING, то должна быть специфицирована и фраза 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) тогда и только тогда, когда:

  1. они, имеют одинаковое число столбцов, например, m;

  2. для всех 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 выбирается обеими из двух составляющих предложений SE­LECT, в окончательном результате она появляется только один раз.

  • Любое число предложений 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. Упражнение. Что представляет собой разность между В и А (именно в этом порядке)?

Заключение

Теперь мы рассмотрели все возможности предложения SE­LECT языка 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, если они имеются. В данном примере, следова­тельно, можно представить себе, что результат строится следую­щим образом.

  1. FROM. В результате обработки фразы FROM создается новая таблица, которая является декартовым произведением таб­лиц Р и SP.

  2. WHERE. Из результата шага 1 исключаются все строки, не удовлетворяющие фразе WHERE. В данном примере исклю­чаются строки, не удовлетворяющие предикату: P.HOMEP_ДЕТАЛИ = SP.HOMEP_ДЕТАЛИ AND Р.ЦВЕТ IN (’Красный’, ’Голубой’) AND SР.КОЛИЧЕСТВО > 200.

  3. GROUP BY. Результат шага 2 группируется по значениям поля (полей), указанного во фразе GROUP BY. В нашем примере это поля Р.НОМЕР_ДЕТАЛИ, Р.ВЕС и Р.ЦВЕТ.

  4. HAVING. Группы, не удовлетворяющие условию

SUM (КОЛИЧЕСТВО) > 350,

исключаются из результата, полученного на шаге 3.

  1. SELECT. Каждая группа, полученная на шаге 4, следую­щим образом генерирует единственную строку для результата. Во-первых, из группы выделяются номер детали, вес, цвет и мак­симальный объем поставки. Во-вторых, вес преобразуется в грам­мы. В-третьих, в соответствующие места полученной строки вставляются две строковые константы ’вес в граммах=’ и ’макси­мальный объем поставки=’.

  2. ORDER BY. Результат шага 5 упорядочивается в соответ­ствии со спецификацией фразы ORDER BY для получения окон­чательного результата.

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

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

Тип файла
Документ
Размер
1,9 Mb
Тип материала
Предмет
Высшее учебное заведение

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

Свежие статьи
Популярно сейчас
А знаете ли Вы, что из года в год задания практически не меняются? Математика, преподаваемая в учебных заведениях, никак не менялась минимум 30 лет. Найдите нужный учебный материал на СтудИзбе!
Ответы на популярные вопросы
Да! Наши авторы собирают и выкладывают те работы, которые сдаются в Вашем учебном заведении ежегодно и уже проверены преподавателями.
Да! У нас любой человек может выложить любую учебную работу и зарабатывать на её продажах! Но каждый учебный материал публикуется только после тщательной проверки администрацией.
Вернём деньги! А если быть более точными, то автору даётся немного времени на исправление, а если не исправит или выйдет время, то вернём деньги в полном объёме!
Да! На равне с готовыми студенческими работами у нас продаются услуги. Цены на услуги видны сразу, то есть Вам нужно только указать параметры и сразу можно оплачивать.
Отзывы студентов
Ставлю 10/10
Все нравится, очень удобный сайт, помогает в учебе. Кроме этого, можно заработать самому, выставляя готовые учебные материалы на продажу здесь. Рейтинги и отзывы на преподавателей очень помогают сориентироваться в начале нового семестра. Спасибо за такую функцию. Ставлю максимальную оценку.
Лучшая платформа для успешной сдачи сессии
Познакомился со СтудИзбой благодаря своему другу, очень нравится интерфейс, количество доступных файлов, цена, в общем, все прекрасно. Даже сам продаю какие-то свои работы.
Студизба ван лав ❤
Очень офигенный сайт для студентов. Много полезных учебных материалов. Пользуюсь студизбой с октября 2021 года. Серьёзных нареканий нет. Хотелось бы, что бы ввели подписочную модель и сделали материалы дешевле 300 рублей в рамках подписки бесплатными.
Отличный сайт
Лично меня всё устраивает - и покупка, и продажа; и цены, и возможность предпросмотра куска файла, и обилие бесплатных файлов (в подборках по авторам, читай, ВУЗам и факультетам). Есть определённые баги, но всё решаемо, да и администраторы реагируют в течение суток.
Маленький отзыв о большом помощнике!
Студизба спасает в те моменты, когда сроки горят, а работ накопилось достаточно. Довольно удобный сайт с простой навигацией и огромным количеством материалов.
Студ. Изба как крупнейший сборник работ для студентов
Тут дофига бывает всего полезного. Печально, что бывают предметы по которым даже одного бесплатного решения нет, но это скорее вопрос к студентам. В остальном всё здорово.
Спасательный островок
Если уже не успеваешь разобраться или застрял на каком-то задание поможет тебе быстро и недорого решить твою проблему.
Всё и так отлично
Всё очень удобно. Особенно круто, что есть система бонусов и можно выводить остатки денег. Очень много качественных бесплатных файлов.
Отзыв о системе "Студизба"
Отличная платформа для распространения работ, востребованных студентами. Хорошо налаженная и качественная работа сайта, огромная база заданий и аудитория.
Отличный помощник
Отличный сайт с кучей полезных файлов, позволяющий найти много методичек / учебников / отзывов о вузах и преподователях.
Отлично помогает студентам в любой момент для решения трудных и незамедлительных задач
Хотелось бы больше конкретной информации о преподавателях. А так в принципе хороший сайт, всегда им пользуюсь и ни разу не было желания прекратить. Хороший сайт для помощи студентам, удобный и приятный интерфейс. Из недостатков можно выделить только отсутствия небольшого количества файлов.
Спасибо за шикарный сайт
Великолепный сайт на котором студент за не большие деньги может найти помощь с дз, проектами курсовыми, лабораторными, а также узнать отзывы на преподавателей и бесплатно скачать пособия.
Популярные преподаватели
Добавляйте материалы
и зарабатывайте!
Продажи идут автоматически
6511
Авторов
на СтудИзбе
302
Средний доход
с одного платного файла
Обучение Подробнее