Реляційне поділ

Перехресне обєднання, про яке ми говорили раніше в цьому розділі, являє собою реляційне твір – два набори даних множаться один на одного, щоб створити декартовій твір З теоретичної точки зору все обєднання є перехресними із застосуванням деяких умовних обмежень Навіть внутрішнє обєднання є результатом добутку двох таблиць із застосуванням до результату обмеження на збіг значень

доповнює реляционное твір так само, як у математиці операція ділення доповнює множення Якщо метою реляційного множення є отримання добутку двох множин-множників, то метою реляційного поділу є поділ одного безлічі (дільника) на інше (дільник), щоб знайти безліч-приватне (рис 102) Іншими словами, якщо відомо картезіанське твір, реляционное поділ допоможе знайти відсутній множник

Рис 102 є операцією, зворотною реляційному твору Воно обчислює безліч-приватне шляхом розподілу безлічі-діленого на безліч-дільник

Незважаючи на те що визначення реляційного ділення звучить дещо академічно, воно може дуже стати в нагоді на практиці Класичною приклад реляційного ділення дає відповідь на питання: Які студенти відвідували всі необхідні курси Запит точного реляційного ділення виявить тільки тих студентів, які відвідали лише потрібні курси Запит реляційного ділення із залишком, також званий приблизними поділом, перерахує всіх студентів, які відвідали всі потрібні курси, включивши в список і тих, хто відвідував додаткові курси Природно, остання операція частіше застосовується на практиці

Реляційне розподіл – більш складне поняття, ніж обєднання Обєднання просто шукає відповідності між двома наборами даних Обєднання / підзапити і реляційне ділення відповідають на два якісно різних типу питань Як приклад застосуємо наступні питання до навчальної бази даних і порівняємо два даних методу

■ Обєднання / підзапити

• СНА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 ContactContactCode FROM dboContact JOIN dbo[Order]

ON ContactContactID = [Order]ContactID JOIN dboOrderDetail

ON [Order]OrderlD = OrderDetailOrderlD JOIN dboProduct

ON OrderDetailProductID = ProductProductID JOIN dboProductCategory

ON ProductProductCategorylD

=ProductCategoryProductCategorylD

WHERE ProductCategoryProductCategoryName = Toy1 GROUP BY ContactContactCode

HAVING COUNT(DISTINCT ProductProductCode) =

– .. Рівною кількості доступних іграшок

(SELECT Count(ProductCode)

FROM dboProduct

JOIN dboProductCategory

ON ProductProductCategorylD

= ProductCategoryProductCategorylD WHERE ProductCategoryProductCategoryName = Toy1)

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

ContactCode

110

111

112

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

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

На практиці це означає, що задається питання на зразок такого: Хто придбав всі іграшки, але більше нічого”

Якщо адресувати це питання модифікованій формі методу Селко, то він буде перефразувавши наступним чином: У кого кількість придбаних іграшок дорівнює кількості доступних іграшок і загальною кількістю придбаних товарів Якщо клієнт купував додаткові товари, відмінні від іграшок, то остання частина питання відсіє його з результуючого набору даних

Наступний код SQL містить два головних зміни в порівнянні з попереднім Перше полягає в тому, що зовнішній запит повинен знайти і кількість придбаних іграшок, і загальна кількість придбаних товарів Це виконується за допомогою пошуку придбаних іграшок у керованій таблиці та обєднання двох наборів даних Друга зміна полягає в модифікації пропозиції HAVING для порівняння кількості доступних іграшок як з кількістю придбаних іграшок, так і з загальною кількістю придбаних товарів:

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

– Чи є кількість всіх придбаних товарів ..

SELECT ContactContactCode FROM dboContact JOIN dbo[Order]

ON ContactContactID = [Order]ContactID JOIN dboOrderDetail

ON [Order]OrderlD = OrderDetailOrderlD JOIN dboProduct

ON OrderDetailProductID = ProductProductID JOIN dboProductCategory PI

ON ProductProductCategorylD = PIProductCategorylD JOIN

– .. І кількість придбаних іграшок ..

(SELECT ContactContactCode, ProductProductCode FROM dboContact JOIN dbo[Order]

ON ContactContactID = [Order]ContactID JOIN dboOrderDetail

ON [Order]OrderlD = OrderDetailOrderlD JOIN dboProduct

ON OrderDetailProductID = ProductProductID JOIN dboProductCategory

ON ProductProductCategorylD =

ProductCategoryProductCategorylD WHERE ProductCategoryProductCategoryName = Toy

) ToysOrdered ON ContactContactCode = ToysOrderedContactCode GROUP BY ContactContactCode

HAVING COUNT(DISTINCT ProductProductCode) =

– .. Рівною кількості доступних іграшок ..

(SELECT Count(ProductCode)

FROM dboProduct

JOIN dboProductCategory

ON ProductProductCategorylD

= ProductCategoryProductCategorylD WHERE ProductCategoryProductCategoryName = Toy) і рівним загальної кількості всіх придбаних товарів

AND COUNT(DISTINCT ToysOrderedProductCode) =

(SELECT Count(ProductCode)

FROM dboProduct

JOIN dboProductCategory

ON ProductProductCategorylD

= ProductCategoryProductCategorylD WHERE ProductCategoryProductCategoryName = 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 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*