Главная » Microsoft SQL Server, Базы данных » Группировка в результирующем наборе данных

0

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

К счастью, итоговые функции дополнены предложением GROUP BY, которое автоматически разбивает набор данных на подмножества, основываясь на значении некоторого столбца. Когда данные разделены на подгруппы, итоговая функция выполняется в каждой из них. В результате в каждой группе появляется дополнительная итоговая строка (рис. 11.3).

Самым распространенным примером группировки является подсчет объема продаж в разрезе торговых. Функция 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     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                    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 R.Category = С.RawCategorylD GROUP BY Category

Как и ожидалось, включение поля region в список столбцов привело к следующей ошибке выполнения запроса:

Msg 8120, Level 16, State 1, Line 1

Column 1RawCategory.CategoryName1 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 R.Category = С.RawCategorylD GROUP BY Category, CategoryName ORDER BY Category, CategoryName

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

Category CategoryName Sum Avg Min Max

X   Sci-Fi                           225 45 11 86

Y    Philosophy                   506 46 12 91

Z   Zoology                       215 53 33 83

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

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

SELECT sq.Category, 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 sq.Category = С.RawCategorylD ORDER BY Category, CategoryName

Подзапрос должен выполнить работу по консолидированию данных с группировкой по категориям. Чтобы вызвать имя категории, результаты подзапроса передаются во внешний запрос, который использует объединение с таблицей RawCategory для доступа к ее столбцу CategoryName.

Включение всех групп с помощью значений

Группировка в логическом порядке выполнения запроса функционально следует за предложением WHERE. Это может создать проблему, если требуется создать отчет по всем группам, даже если некоторые из них были отфильтрованы предложением WHERE и не содержат значений. Хотя такая необходимость возникает достаточно редко, существует решение данной проблемы запроса консолидации данных, не требующее внешних объединений и подзапросов. Параметр ALL предложения GROUP BY позволяет включить в результат запроса все группы, даже если их значения были отфильтрованы предложением WHERE. Следующий запрос демонстрирует использование этого приема. Он возвращает строку для группы 2 005, несмотря на то. что данные за 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              0         NULL NULL NULL NULL

2006              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              Q MaxSum

2005       4              79

2006       1              147 2006 2              215 2006 3              280

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

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

SELECT MaxQuery.Y, MaxQuery.Q, AllQuery.Category,

MaxQuery.MaxSum 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 MaxQuery.Y = AllQuery.Y

AND MaxQuery.Q = AllQuery.Q AND MaxQuery.MaxSum = AllQuery.Sum ORDER BY MaxQuery.Y, MaxQuery.Q

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

Y              Q Category MaxSum

2005  4                         Y     79

2006  1                    Y 147

2006  2                    Z          215

2006  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) >25 ORDER BY [Year], [Quarter]

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

Year Quarter Count Sum Avg

2006                      1                 6               218 36

2006                      2                 6               369 61

2006                      3                 8               280 70

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

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

По теме:

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