Угруповання в результуючому наборі даних

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

На щастя, підсумкові функції доповнені пропозицією GROUP BY, яке автоматично розбиває набір даних на підмножини, грунтуючись на значенні деякого шпальти Коли дані розділені на підгрупи, підсумкова функція виконується в кожній з них У результаті в кожній групі зявляється додаткова підсумкова рядок (рис 113)

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

Проста угруповання

Деякі угруповання використовують описову інформацію, тому дані, використовувані в реченні GROUP BY, достатні для розуміння угруповання Однак такий підхід великих реляційних базах даних зустрічається рідко Наприклад, наступний запит виконує угруповання за категоріями:

SELECT Category,

Count(*) as Count,

Sum(Amount) as [Sum],

Avg(Amount) a s [Avg],

Min(Amount) as [Min],

Max(Amount) as [Max]

FROM RawData GROUP BY Category

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

Category Count Sum                Avg               Min     Max

X   5                    225                45                  11          86

Y&nbsp&nbsp&nbsp&nbsp 11                  506                46                  12                  91

Z   4                    215                53                  33           83

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

SQL не обмежує вас угрупованням тільки по одному стовпцю Наведений вище запит можна розширити, доповнивши угрупованням по полю ProductCategoryName:

SELECT Year(SalesDate) as [Year], DatePart(q,SalesDate) as [Quarter], Count(*) as Count,

Sum(Amount) as [Sum],

Avg(Amount) as [Avg],

Min(Amount) as [Min],

Max(Amount) as [Max]

FROM RawData

GROUP BY Year(SalesDate), DatePart(q,SalesDate)

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

Year Quarter Count Sum Avg Min Max

2006             1                 6             218        36     11        62

2006             2                 6             369        61     33        86

2006             3                  8            280         70     54        91

2005&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 4                 4             79          19           12  28

Вирішення проблем у запитах консолідації даних

Існує кілька аспектів угруповання в запитах, які можуть створити складнощі при розробці додатків Деякі програмісти взагалі уникають запитів консолідації даних і покладають ця задачу на звіти Водночас ядро ​​бази даних може

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

Включення груп за описами

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

CREATE TABLE RawCategory (

RawCategorylD CHAR(l),

CategoryName VARCHAR(25)

)

INSERT RawCategory (RawCategorylD, CategoryName)

VALUES (X, Sci-Fi)

INSERT RawCategory (RawCategorylD, CategoryName)

VALUES (Y, Philosophy)

INSERT RawCategory (RawCategorylD, CategoryName)

VALUES (1Z, Zoology)

– Включення даних, що знаходяться за межами угруповання і підсумків SELECT Category, CategoryName,

Sum(Amount) as [Sum],

Avg(Amount) as [Avg],

Min(Amount) as [Min],

Max(Amount) as [Max]

FROM RawData R JOIN RawCategory З

ON RCategory = СRawCategorylD GROUP BY Category

Як і очікувалося, включення поля region в список стовпців призвело до наступної помилку виконання запиту:

Msg 8120, Level 16, State 1, Line 1

Column 1RawCategoryCategoryName1 is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Існують два рішення проблеми включення в запит неконсолідіруемих описових стовпців

Якщо створюється одноразовий запит, запустіть його на виконання і клацніть на кнопці Про До, щоб включити додаткові стовпці в пропозицію GROUP BY

SELECT Category, CategoryName,

Sum(Amount) as [Sum],

Avg(Amount) as [Avg],

Min(Amount) as [Min],

Max(Amount) as [Max]

FROM RawData R JOIN RawCategory З

ON RCategory = СRawCategorylD GROUP BY Category, CategoryName ORDER BY Category, CategoryName

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

Category CategoryName Sum Avg Min Max

X   Sci-Fi                           225 45 11 86

Y&nbsp&nbsp&nbsp Philosophy                   506 46 12 91

Z   Zoology                       215 53 33 83

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

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

SELECT sqCategory, CategoryName,

sq[Sum], sq[Avg], sq[Min], sq[Max]

FROM (SELECT Category,

Sum(Amount) as [Sum],

Avg(Amount) as [Avg],

Min(Amount) as [Min},

Max(Amount) as [Max]

FROM RawData GROUP BY Category) sq JOIN RawCategory З

ON sqCategory = СRawCategorylD ORDER BY Category, CategoryName

Підзапит повинен виконати роботу з консолідування даних з угрупованням за категоріями Щоб викликати імя категорії, результати підзапиту передаються в зовнішній запит, який використовує обєднання з таблицею RawCategory для доступу до її стовпцю CategoryName

Включення всіх груп за допомогою значень

Угруповання в логічному порядку виконання запиту функціонально слід за пропозицією WHERE Це може створити проблему, якщо потрібно створити звіт по всіх групах, навіть якщо деякі з них були відфільтровані пропозицією WHERE і не містять значень Хоча така необхідність виникає досить рідко, існує рішення даної проблеми запиту консолідації даних, яке не потребує зовнішніх обєднань і підзапитів Параметр ALL пропозиції GROUP BY дозволяє включити в результат запиту всі групи, навіть якщо їх значення були відфільтровані пропозицією WHERE Наступний запит демонструє використання цього прийому Він повертає рядок для групи 2005, незважаючи на те що дані за 2005 рік не беруть участь у підсумкових обчисленнях:

SELECT Year(SalesDate) as Year,

Count(*) as Count,

Sum(Amount) as [Sum],

Avg(Amount) as [Avg],

Min(Amount) as [Min],

Max(Amount) as [Max]

FROM RawData

WHERE Year(SalesDate) = 2006 GROUP BY ALL Year(SalesDate)

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

Year Count Sum Avg Min Max

2005&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 0         NULL NULL NULL NULL

2006&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 20                           867 54      11     91

Вкладені консолідації

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

(Кварталу) за допомогою підзапиту Проте може виникнути питання, в якій з категорій міститься максимальне значення Цілком очевидно, що композиція функцій max (sum ()) не спрацює, оскільки в ній недостатньо інформації для SQL Server про те, як вкладати ці угруповання Вирішення цього завдання вимагає, щоб подзапрос створив набір даних на основі першої консолідації, а потім зовнішній запит реалізував другий рівень консолідації Наприклад, наступний запит виконує підсумовування значень по року і категорії, а потім зовнішній запит за допомогою функції max () визначає, яка з сум виявилася в кожному кварталі найбільшою:

SELECT Y, Q, Max (Sum) as MaxSum FROM (- Обчислення сум

SELECT Category, Year(SalesDate) as Y,

DatePart(q,SalesDate) as Q, Sum(Amount) as Sum FROM RawData

GROUP BY Category, Year(SalesDate),

DatePart(q,SalesDate)

) sq GROUP BY Y,Q ORDER BY Y,Q

В результаті виконання запиту буде отримано наступний результат:

Y&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Q MaxSum

2005&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 4              79

2006&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 1              147 2006 2              215 2006 3              280

Включення опису деталей

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

SELECT MaxQueryY, MaxQueryQ, AllQueryCategory,

MaxQueryMaxSum as MaxSum FROM (- Пошук максимальної суми за квартал / рік SELECT Y, Q, Max (Sum) as MaxSum FROM (- Обчислення сум

SELECT Category, Year(SalesDate) as Y,

DatePart(q,SalesDate) as Q, Sum(Amount) as Sum FROM RawData

GROUP by Category, Year(SalesDate),

DatePart(q,SalesDate)

) Sq GROUP BY Y, Q) MaxQuery JOIN (- Всі дані запиту

SELECT Category, Year(SalesDate) as Y,

DatePart(q,SalesDate) as Q,

Sum(Amount) as Sum FROM RawData

GROUP BY Category, Year(SalesDate),

DatePart(q,SalesDate)

) AllQuery

ON MaxQueryY = AllQueryY

AND MaxQueryQ = AllQueryQ AND MaxQueryMaxSum = AllQuerySum ORDER BY MaxQueryY, MaxQueryQ

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

Y&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Q Category MaxSum

2005&nbsp 4                         Y     79

2006&nbsp 1                    Y 147

2006&nbsp 2                    Z          215

2006&nbsp 3                    Y          280

Хоча на перший погляд даний запит здається складним, насправді він є всього лише розширенням попереднього

Останній подзапрос (з псевдонімом AllQuery) шукає всі суми в категоріях за рік / квартал Обєднання MaxQuery з AllQuery за сумою і році / кварталу використовується для пошуку назви категорії, що дозволяє вивести в результати запиту разом з даними і їх опису

Фільтрація згрупованих результатів

Фільтрація в комбінації з угрупованням може викликати проблеми Виникає питання, чи застосовуються обмеження до угруповання чи після неї Деякі бази даних використовують вкладені запити для коректної фільтрації до або після пропозиції GROUP BY Мова SQL для фільтрації груп містить спеціальну пропозицію HAVING На початку цієї глави ви бачили спрощений порядок виконання інструкції SELECT Більш повний порядок наступний

1 Пропозиція FROM збирає дані з різних джерел

2 Пропозиція WHERE обмежує число рядків на підставі деякої умови

3 Пропозиція GROUP BY збирає підмножини даних

4 Обчислюються підсумкові функції

5 Пропозиція HAVING фільтрує підмножини даних

6 Обчислюються всі вирази

7 Пропозиція ORDER BY сортує результат

Продовжуючи працювати з навчальною таблицею RawData, ми розглянемо ще один запит, який видаляє з результатів всі групи, що мають середню значення, менше двадцяти пяти: SELECT Year (SalesDate) as [Year],

DatePart(q,SalesDate) as [Quarter],

Count(*) as Count,

Sum(Amount) as [Sum],

Avg(Amount) as [Avg]

FROM RawData

GROUP BY Year(SalesDate), DatePart(q,SalesDate)

HAVING Avg(Amount) &gt25 ORDER BY [Year], [Quarter]

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

Year Quarter Count Sum Avg

2006&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 1                 6               218 36

2006&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 2                 6               369 61

2006&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 3                 8               280 70

Без пропозиції HAVING четвертий квартал 2005 року, що має середнє значення 19, увійшов би в результуючий набір даних Однак пропозиція HAVING, включене після GROUP BY і обчислення підсумкових функцій, послужило постконсолідаціонним фільтром

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

*

*