Створення перехресних запитів

Хоча запити консолідації дозволяють групувати дані по декількох стовпцях, результат продовжує зберігати зовнішній вигляд списку, що робить швидкий пошук потрібного значення досить проблематичним Перехресна табуляція стовпців (або вимірів) угруповання може розгорнути рядок на 90 ° і перетворити її в стовпець (рис 114) Природно, тут існує обмеження: якщо запит з угрупованням містить безліч підсумкових функцій, перехресна таблиця буде відображена, але тільки по одному вимірюванню

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

щ

Puc 114 Поворот групи в стовпець створює перехресний запит

Перехресні запити з фіксованим стовпцем

У SQL Server 2005 існують три методи генерування перехресного запиту з відомим, фіксованим стовпцем Якщо стовпці для будь-якого перехресного запиту поки неможливо дізнатися на стадії розробки, має сенс з ними визначитися, оскільки програмування форм і звітів додатків буде значно легше при відомих стовпцях

Метод корельованих підзапитів

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

Основна ідея полягає у виконанні підзапиту для кожного екземпляра кожного вимірювання всіх стовпців угруповання Щоб пройтися по цьому запиту, уважно вдивіться в кожну рядок інструкції SELECT Перший стовпець – це поле category з таблиці RawData Стовпці South, NorthEast, Midwest і West являють собою корельовані підзапити, суммирующие значення стовпця Amount, відфільтрованого за відповідною категорії Якщо перехресний запит проходить 1000 рядків, а стовпці перехресної таблиці охоплюють щотижня року, то серверу, використовуючи цей метод, доведеться виконати 52 000 підзапитів

Останній стовпець відображає проміжний підсумок для кожної категорії:

SELECT RCategory,

(SELECT SUM(Amount)

FROM RawData

WHERE Region = South AND Category = RCategory) AS South, (SELECT SUM(Amount)

FROM RawData

WHERE Region = NorthEast

AND Category = RCategory) AS NorthEast,

(SELECT SUM(Amount)

FROM RawData

WHERE Region = MidWest AND Category = RCategory) AS MidWest, (SELECT SUM(Amount)

FROM RawData

WHERE Region = West AND Category = RCategory) AS West,

SUM(Amount) as Total FROM RawData R GROUP BY Category

Результат виконання запиту буде наступним:

Category South NorthEast MidWest West Total

X  165         NULL             24               36           225

Y&nbsp 287         181                 38               NULL 506

Z  33           55                   83               44           215

Метод виразів case

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

Розглянемо цей метод докладно Дані з таблиці RawData не обмежені пропозицією WHERE Пропозиція GROUP BY розбиває набір даних на категорії А ось і сама хитрість цього методу: функція sum () включає вираз CASE таким чином, кожен стовпець бачить дані тільки свого регіону

SELECT Category,

SUM (Case Region WHEN South THEN Amount ELSE 0 END) AS South,

SUM (Case Region WHEN NorthEast THEN Amount ELSE 0 END) AS NorthEast, SUM (Case Region WHEN MidWest THEN Amount ELSE 0 END) AS MidWest,

SUM (Case Region WHEN West THEN Amount ELSE 0 END) AS West,

SUM(Amount) as Total FROM RawData GROUP BY Category ORDER BY Category

Результат буде таким же, як і у методу корельованих підзапитів

Метод повороту

Метод повороту випливає із звичайного порядку виконання запиту У ньому обчислюються звичайні підсумкові функції, а результат створюється як джерело даних в реченні FROM

SQL Server 2005 містить нову команду pivot, призначену для полегшення створення перехресних запитів У парі з протилежною командою unpivot вони також полегшують нормалізацію і денормализация даних

Якщо уявити собі поворот як деяку табличну функцію, яка у ролі джерела даних, то вона приймає два параметра Першим параметром є підсумкова функція, яка використовується для значень перехресної таблиці другий параметр перераховує повертаються стовпці У розглянутому прикладі підсумкова функція підсумовує значення стовпця Amount, а повертаємим стовпцем є region Так як PIVOT є частиною пропозиції FROM, створюваному набору даних потрібно привласнити іменований діапазон або псевдонім

SELECT Category, SalesDate, South, NorthEast, MidWest, West FROM RawData PIVOT

(Sum (Amount)

FOR Region IN (South, NorthEast, MidWest, West)

) AS pt

Результат виконання буде наступним:

Category SalesDate                            South NorthEast MidWest West

Y2005-11-01   00:00:00000              36         NULL         NULL     NULL

Y2005-12-01   00:00:00000              15         28               NULL     NULL X   2006-01-01       00:00:00000     11        NULL   24        NULL X                 2006-02-01   00:00:00000              NULL    NULL         NULL     36

Y2006-02-01   00:00:00000              47         NULL         NULL     NULL

Y2006-03-01   00:00:00000               NULL    62              38           NULL Z    2006-04-01       00:00:00000     33        NULL   83        NULL Z                 2006-05-01   00:00:00000               NULL    55              NULL     44

X                                                      2006-06-01 00:00:00000           154 NULL        NULL NULL

Y2006-07-01 00:00:00000                  117 NULL               NULL NULL

Y2006-08-01 00:00:00000                  72         91              NULL NULL

Результат вийшов не зовсім той, який ми очікували Справа в тому, що команда PIVOT використовує всі стовпці Так як стовпці Amount і Region були використані в запиті, мається на увазі, що всі інші стовпці повинні використовуватися для угруповання Таким чином, угруповання виконувалася по полях категорії товару (Category) і дати продажу (SalesDate)

Вирішення цієї проблеми зводиться до використання підзапиту для вибору тільки тих стовпців, які мають бути надані команді PIVOT

SELECT Category, South, NorthEast, MidWest, West

FROM (Select Category, Region, Amount from RawData) sq PIVOT

(Sum (Amount)

FOR Region IN (South, NorthEast, MidWest, West)

) AS pt

Результат буде таким же, як і в попередньому прикладі, тільки без зайвого стовпця SalesDate

Останнім методом створення перехресних запитів є метод шліфування Так як команда PIVOT є логічною складовою пропозиції FROM, вона виконується раніше пропозиції WHERE Таким чином, існує ще одна причина використовувати подзапрос для підготовки даних для команди PIVOT – фільтрація даних або обєднання з іншими джерелами даних

У наступному прикладі ми відфильтруем дані по категорії Z і додамо стовпець підсумків по категорії В результаті команда PIVOT створить той же результат, що й наведений раніше метод вираження CASE

SELECT Category, South, NorthEast, MidWest, West,

IsNull(South,0) + IsNull(NorthEast,0) + IsNull(MidWest,0) +

IsNull(West,0) as Total

FROM (Select Region, Category, Amount From RawData

Where Category = Z 1 ) sq

PIVOT

(Sum (Amount)

FOR Region IN (South, NorthEast, MidWest, West)

) AS pt

Результат виконання запиту буде таким же, як у методів корельованих підзапитів і вирази CASE

Динамічні перехресні запити

Рядки перехресного запиту динамічно генеруються при консолідації, проте у всіх перерахованих вище методах стовпці (у розглянутому прикладі Region) повинні бути явно вказані в інструкції SQL Єдиним методом створення перехресного запиту з динамічними стовпцями є використання пакету SQL (можливо, збереженого як збережена процедура або призначена для користувача функція), що визначає стовпці на етапі виконання і збирає команду SQL для виконання перехресного запиту

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

Додаткова За своєю сутністю куб служби аналізу є динамічним перехресним інформація запитом на стероїди Більш докладно про створення високопродуктивних * інтерактивних кубів ми поговоримо в розділі 43

Курсор і метод повороту

Завданням курсора є послідовне проходження по унікальних регіонах та збирання розділеної запитом рядка в змінної @ RegionColumn Як тільки замикає кома видаляється з змінної @ RegionColumn, остання може використовуватися як частина команди PIVOT у динамічній інструкції SQL, яка виконується за допомогою системної збереженої процедури sp_executesql

DECLARE

©SQLStr NVARCHAR(1024),

©RegionColumn VARCHAR(50),

©Semicolon BIT SET ©Semicolon = 0 SET ©SQLStr = ‘

DECLARE ColNames CURSOR FAST_FORWARD FOR

SELECT DISTINCT Region as [Column]

FROM RawData ORDER BY Region OPEN ColNames

FETCH ColNames INTO @RegionColumn WHILE @@Fetch_Status = 0 BEGIN

SET @SQLStr = @SQLStr + @RegionColumn + 1, 1 FETCH ColNames INTO @RegionColumn — fetch next END

CLOSE ColNames DEALLOCATE ColNames

SET @SQLStr = Left(@SQLStr, Len(@SQLStr) – 1)

SET @SQLStr = SELECT Category,

+ @SQLStr

+ FROM RawData PIVOT (Sum (Amount) FOR Region IN (

+ @SQLStr + )) AS pt

PRINT @SQLStr

EXEC sp_executesql @SQLStr

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

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

i&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp —1 ням Згідно з численними тестами, метод курсора виконується більш

ніж у десять разів повільніше, ніж запропонований далі пакетний метод численного присвоєння змінної Детальну інформацію про програмування курсора ви можете отримати в главі 20

Змінна з численними присвоєннями і метод повороту

Цей метод використовує точно таку ж динамічну рядок SQL для виконання динамічного перехресного запиту, як і метод курсора, проте для створення списку регіонів він задіє пакетну інструкцію SELECT Інструкція SELECT додає кожен з регіонів до списку, розділений комами, який потім виконується так само, як і в попередньому прикладі:

DECLARE @XColumns NVARCHAR(1024)

SET @XColumns = ‘

SELECT @XColumns = @XColumns + [a][Column] + ,

FROM

(SELECT DISTINCT Region as [Column]

FROM RawData) as a SET @XColumns = Left(@XColumns, Len(@xColumns) – 1)

SET @XColumns = SELECT Category,

+ @Xcolumns

+ 1 FROM RawData PIVOT (Sum (Amount) FOR Region IN (

+ @Xcolumns + )) AS pt

PRINT @Xcolumns

EXEC sp_executesql @xColumns

Завданням програми динамічного запиту є збірка фіксованого перехресного запиту без попереднього вказівки значень стовпців Список стовпців повертає подзапрос, після чого рекурсивна змінна SELECT додає значення, а також текст, необхідний для створення динамічного перехресного запиту в змінної @ XColumns Заключна інструкція SET завершує збірку динамічної рядка SQL

Додаткова Протилежністю перехресному запитом є команда UNPIVOT, кото-інформація рая виключно корисна для нормалізації денормалізованних даних Опис цієї команди і приклади ви знайдете в главі 24

Резюме

SQL Server виділяється різноманіттям підсумкових функцій з вже став притчею во язи-цех багатим набором засобів Він здатний обчислювати суми та інші консолідації, з лишком покривають будь-які мислимі потреби Ці методи, від простої підсумкової функції count () і до складних динамічних перехресних запитів, а також нової команди PIVOT, дозволяють створювати потужні запити аналізу даних для вражаючих звітів

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

Навігація по ієрархічним даними

м

■ П і один інструмент не є унікальним для лю-

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

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

У цій главі ми розглянемо різні методи зберігання ієрархічних даних і навігації по ним: від однорівневого поворотного (рекурсивного) обєднання до громіздкого курсора і ефективних користувальницьких функцій

Стандарт ANSI SQL-99 (посилання на який з-Новінха ^ тримається на сайті цієї книги за адресою www 2005 SQLServerBiblecom) спробував впоратися

з ієрархічними даними за допомогою введення загальних табличних виразів (ETS), які можна використовувати для вибірки декількох рівнів даних з ієрархічного дерева З часом, можливо, я зміню свою думку, але цей новий синтаксис навіть не схожий на SQL, і я залишаюся прихильником користувача функцій навігації

Джерело: Нільсен, Пол 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>

*

*