Главная » Microsoft SQL Server, Базы данных » Реляционное деление

0

Перекрестное объединение, о котором мы говорили ранее в этой главе, представляет собой реляционное произведение — два набора данных умножаются друг на друга, чтобы создать декартово произведение. С теоретической точки зрения все объединения являются перекрестными с применением некоторых условных ограничений. Даже внутреннее объединение является результатом произведения двух таблиц с применением к результату ограничения на совпадение значений.

дополняет реляционное произведение так же, как в математике операция деления дополняет умножение. Если целью реляционного умножения является получение произведения двух множеств-множителей, то целью реляционного деления является деление одного множества (делителя) на другое (делитель), чтобы найти множество-частное (рис. 10.2). Другими словами, если известно картезианское произведение, реляционное деление поможет найти недостающий множитель.

Рис. 10.2. является операцией, обратной реляционному произведению. Оно вычисляет множество-частное путем деления множества-делимого на множество-делитель

Несмотря на то что определение реляционного деления звучит несколько академически, оно может очень пригодиться на практике. Классической пример реляционного деления дает ответ на вопрос: “Какие студенты посещали все необходимые курсы?” Запрос точного реляционного деления выявит только тех студентов, которые посетили только требуемые курсы. Запрос реляционного деления с остатком, также называемый приблизительным делением, перечислит всех студентов, которые посетили все нужные курсы, включив в список и тех, кто посещал дополнительные курсы. Естественно, последняя операция чаще применяется на практике.

— более сложное понятие, чем объединение. Объединение просто ищет соответствия между двумя наборами данных. Объединения/подзапросы и реляционное деление отвечают на два качественно разных типа вопросов. В качестве примера применим следующие вопросы к учебной базе данных и сравним два данных метода.

?               Объединения/подзапросы

• СНА2: Кто когда-либо ездил в тур?

• СНА2: Кто живет в том же регионе, где находится базовый лагерь?

• СНА2: Кто участвовал в событиях в своем родном регионе?

?               Точное реляционное деление

•                СНА2: Кто брал все туры в своем родном регионе и ни одного вне его?

•                OBXKites: Кто покупал только воздушных змеев и ничего больше?

•                Family: Какие женщины (включая вдов и разведенных) выходили замуж за одних и тех же мужчин и больше ни за каких других?

?               с остатком

•                СНА2: Кто брал все туры в своем родном регионе и, возможно, какие-либо другие?

•                OBXKites: Кто покупал всех воздушных змеев и, может быть, что-либо еще?

•                Family: Какие женщины выходили замуж за одних и тех же мужчин и, может быть, за кого-либо еще?

с остатком

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

Для демонстрации реляционного деления с остатком мы выберем упомянутый ранее вопрос, задаваемый базе данных OBXKites: “Кто покупал воздушных змеев и, может быть, что- либо еще?” Так как в запросе участвуют пять таблиц, от категории товаров до строк заказа, и так как вопрос предполагает наличие объединения таблиц OrderDetails и Product, запрос будет достаточно сложный, чтобы промоделировать реальную задачу, встречающуюся на практике и связанную с реляционными базами данных.

Категория игрушек послужит хорошим примером категории, поскольку содержит только два товара, и в учебной базе данных нет информации об их покупке. Вопрос мы сформулируем следующим образом: “Кто покупал хотя бы по одной игрушке каждого типа, продаваемой в магазине ОВХ Kites?” (Мои дети предложили стать добровольцами в решении этой задачи.)

В реализации сценария нам помогут вымышленные данные, вставляемые в базу данных OBXKites. К категории игрушек относятся только товары с кодами 1049 и 1050. База данных OBXKites использует уникальные идентификаторы для первичных ключей и соответственно для вставки использует хранимые процедуры.

В первых вызовах хранимых процедур вставки в таблицы Order и OrderDe tails будут перечислены их параметры, так что их будет несложно понять:

USE OBXKites

DECLARE ©OrderNumber INT

Первый клиент с кодом 110 приобрел только игрушки:

EXEC pOrder_AddNew

@ContactCode = ‘110’,

@EmployeeCode = ‘120’,

@LocationCode = ‘CH1,

@OrderDate= ‘6/1/2002 ‘ ,

@OrderNumber = @OrderNumber output EXEC pOrder_AddItem

@OrderNumber = @OrderNumber,

@Code = ‘1049’,

@NonStockProduct = NULL,

@Quantity = 12,

@UnitPrice = NULL,

@ShipRequestDate = ‘6/1/2002′,

@ShipComment = NULL EXEC pOrder_AddItem

@OrderNumber, ‘1050’, NULL, 3, NULL, NULL, NULL

Второй клиент с кодом 111 также приобрел только игрушки:

EXEC pOrder_AddNew

‘111’, 1119′, 1JR’, ‘6/1/2002′, ©OrderNumber output EXEC pOrder_AddItem

@OrderNumber, ‘1049’, NULL, 6, NULL, NULL, NULL EXEC pOrder_AddItem

@OrderNumber, ‘1050’, NULL, 6, NULL, NULL, NULL EXEC pOrder_AddNew

‘111’, ‘119’, ‘JR’, ‘6/1/2002′, @OrderNumber output EXEC pOrder_AddItem

©OrderNumber, ‘1050’, NULL, 6, NULL, NULL, NULL

Третий клиент с кодом 112 приобрел игрушки и еще некоторые товары:

EXEC pOrder_AddNew

‘112’, ‘119’, ‘JR’, ‘6/1/2002′, @OrderNumber output EXEC pOrder_AddItem

@OrderNumber, ‘1049’, NULL, 6, NULL, NULL, NULL EXEC pOrder_AddItem

@OrderNumber, ‘1050’, NULL, 5, NULL, NULL, NULL EXEC pOrder_AddItem

©OrderNumber, ‘1001’, NULL, 5, NULL, NULL, NULL EXEC pOrder_AddItem

@OrderNumber, ‘1002’, NULL, 5, NULL, NULL, NULL

Четвертый клиент с кодом 113 приобрел только одну игрушку:

EXEC pOrder_AddNew

‘113’, ‘119’, ‘JR’, ‘6/1/2002′, @OrderNumber output EXEC pOrder_AddItem

@OrderNumber, ‘1049’, NULL, 6, NULL, NULL, NULL

Другими словами, только клиенты 110 и 111 приобретали только игрушки; клиент 112 приобрел игрушки, равно как и воздушные змеи; клиент 113 не должен попасть в выборку, так как он приобрел всего одну игрушку.

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

В своей основе решение Селко строится на перефразировании вопроса: “У кого количество приобретенных игрушек совпадает с количеством доступных?”

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

– Является ли количество приобретенных игрушек…

SELECT Contact.ContactCode FROM dbo.Contact JOIN dbo.[Order]

ON Contact.ContactID = [Order].ContactID JOIN dbo.OrderDetail

ON [Order].OrderlD = OrderDetail.OrderlD JOIN dbo.Product

ON OrderDetail.ProductID = Product.ProductID JOIN dbo.ProductCategory

ON Product.ProductCategorylD

=ProductCategory.ProductCategorylD

WHERE ProductCategory.ProductCategoryName = ‘Toy1 GROUP BY Contact.ContactCode

HAVING COUNT(DISTINCT Product.ProductCode) =

– …равным количеству доступных игрушек?

(SELECT Count(ProductCode)

FROM dbo.Product

JOIN dbo.ProductCategory

ON Product.ProductCategorylD

= ProductCategory.ProductCategorylD WHERE ProductCategory.ProductCategoryName = ‘Toy1)

Будет получен следующий результат:

ContactCode

110

111

112

Точное реляционное деление

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

На практике это означает, что задается вопрос наподобие следующего: “Кто приобрел все игрушки, но больше ничего?”

Если адресовать этот вопрос модифицированной форме метода Селко, то он будет перефразирован следующим образом: “У кого количество приобретенных игрушек равно количеству доступных игрушек и общему количеству приобретенных товаров?” Если клиент покупал дополнительные товары, отличные от игрушек, то последняя часть вопроса отсеет его из результирующего набора данных.

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

– Точное реляционное деление

– Является ли количество всех приобретенных товаров…

SELECT Contact.ContactCode FROM dbo.Contact JOIN dbo.[Order]

ON Contact.ContactID = [Order].ContactID JOIN dbo.OrderDetail

ON [Order].OrderlD = OrderDetail.OrderlD JOIN dbo.Product

ON OrderDetail.ProductID = Product.ProductID JOIN dbo.ProductCategory PI

ON Product.ProductCategorylD = PI.ProductCategorylD JOIN

– … и количество приобретенных игрушек…

(SELECT Contact.ContactCode, Product.ProductCode FROM dbo.Contact JOIN dbo.[Order]

ON Contact.ContactID = [Order].ContactID JOIN dbo.OrderDetail

ON [Order].OrderlD = OrderDetail.OrderlD JOIN dbo.Product

ON OrderDetail.ProductID = Product.ProductID JOIN dbo.ProductCategory

ON Product.ProductCategorylD =

ProductCategory.ProductCategorylD WHERE ProductCategory.ProductCategoryName = ‘Toy’

) ToysOrdered ON Contact.ContactCode = ToysOrdered.ContactCode GROUP BY Contact.ContactCode

HAVING COUNT(DISTINCT Product.ProductCode) =

– …равным количеству доступных игрушек…

(SELECT Count(ProductCode)

FROM dbo.Product

JOIN dbo.ProductCategory

ON Product.ProductCategorylD

= ProductCategory.ProductCategorylD WHERE ProductCategory.ProductCategoryName = ‘Toy’) и равным общему количеству всех приобретенных товаров?

AND COUNT(DISTINCT ToysOrdered.ProductCode) =

(SELECT Count(ProductCode)

FROM dbo.Product

JOIN dbo.ProductCategory

ON Product.ProductCategorylD

= ProductCategory.ProductCategorylD WHERE ProductCategory.ProductCategoryName = ‘Toy’)

Результатом выполнения этого запроса станет список клиентов, содержащий количество приобретенных игрушек (2) и общее количество приобретенных товаров (2). Оба этих числа равны количеству доступных игрушек (2):

ContactCode

110

111

Резюме

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

В предыдущих главах мы заложили фундамент работы с SQL, рассмотрев инструкцию SELECT, выражения, объединения и слияния. В этой главе мы дополнили инструкцию SELECT возможностями подзапросов и общих табличных выражений. Если вы читаете эту книгу последовательно, примите поздравления: вы перешли Рубикон в изучении языка SQL. Если вы освоили реляционную алгебру и подзапросы, все остальное — сущие пустяки.

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

Консолидация данных

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

Язык SQL успешно справляется с суммированием, вычислением максимальных, минимальных и средних значений всего множества данных или некоторого его сегмента. К тому же SQL позволяет создавать перекрестные таблицы, также называемые сводными.

В то время как стандарт ANSI SQL-92 включает Новинка Х в се^я массУ стандартных итоговых функций,

2005  SQL Server 2005 добавил к этому возможность

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

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии