Узагальнення даних за допомогою оператора ROLLUP

Узагальнення даних за допомогою оператора CUBE

Узагальнення даних

В Transact-SQL входять кілька елементів, що дозволяють генерувати прості зведені звіти. Для цього застосовують оператори CUBE або ROLLUP, які є частиною конструкції GROUP BY оператора SELECT. Крім того, можна використати оператори COMPUTE і COMPUTE BY, які також пов'язані з конструкцією GROUP BY. Оператори COMPUTE і COMPUTE BY підтримуються заради зворотної сумісності.

Оператор CUBE генерує результуючий набір у вигляді багатомірного куба - розширення фактичних даних або даних, що реєструють окремі події. Таке розширення засноване на стовпцях, які хоче проаналізувати користувач. Стовпці називаються вимірами. Куб являє собою результуючий набір, що містить зведені дані із всіх можливих комбінацій вимірів. У наступному операторі SELECT у конструкції GROUP BY використаний оператор CUBE:

USE Pubs

SELECT SUBSTRING(Title, 1, 65) AS Title,

SUM(qty) AS 'Quantity'

FROM Sales INNER JOIN Titles

ON Sales.Title_id = Titles.Title_id

GROUP BY Title

WITH CUBE

ORDER BY Title

Оператор SELECT відповідає відношенню один до багатьох між книгами й кількістю проданих екземплярів кожної книги. За допомогою оператора CUBE він повертає додатковий рядок. Вона містить порожнє значення в стовпці Title результуючого набору й представляє всі значення стовпця Title таблиці Titles. Результуючий набір повертає значення кількості проданих екземплярів кожної книги й сумарну кількість проданих екземплярів всіх книг. У цьому випадку оператори CUBE і ROLLUP повертають той самий результат.

Оператор ROLLUP генерує звіти з підсумками і проміжними підсумками. Створюваний його засобами результуючий набір схожий на результуючий набір оператора CUBE. Однак є й розходження:

· результуючий набір, згенерований оператором CUBE, показує підсумкові значення для всіх комбінацій значень обраних стовпців;

· результуючий набір, згенерований оператором ROLLUP, показує підсумкові значення для ієрархії значень в обраних стовпцях.

Наступний оператор SELECT містить оператор ROLLUP у конструкції GROUP BY:

USE Pubs

SELECT Pub_name, Au.lname, Title, SUM(qty) AS 'Sum'

FROM Authors a INNER JOIN TitleAuthor ta

ON a.Au_id = ta.Au_id INNER JOIN Titles t

ON t.Title_id = ta.Title_id INNER JOIN Publishers p

ON p.Pub_id = t.Pub_id INNER JOIN Sales s

ON s.Title_id - t.Title.id

GROUP BY Pub_name, Au_lname, Title

WITH ROLLUP

Оператор ROLLUP дозволяє створювати угруповання в результуючому наборі. Для подання всіх значень поля в згрупованих рядках використовується порожнє значення (за винятком поля Sum). При використанні без оператора ROLLUP оператор SELECT генерує тільки одну групу. Оператор CUBE повертає набагато більше груп. При вказівці в конструкції GROUP BY наступних стовпців: Pub_name, Aujname, і Title оператор ROLLUP повертає наступні дані:

· кількість екземплярів кожної книги кожного автора, проданих кожним видавництвом;

· сумарна кількість екземплярів книг кожного автора, проданих кожним видавництвом;

· сумарна кількість всіх книг, проданих кожним видавництвом;

· сумарна кількість всіх книг всіх авторів, проданих всіма видавництвами.

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

Дата добавления: 2015-06-29