Главная » Microsoft SQL Server, Базы данных » Создание перекрестных запросов

0

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

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

щ

Puc. 11.4. Поворот группы в столбец создает перекрестный запрос

Перекрестные запросы с фиксированным столбцом

В SQL Server 2005 существуют три метода генерирования перекрестного запроса с известным, фиксированным столбцом. Если столбцы для любого перекрестного запроса пока невозможно узнать на стадии разработки, имеет смысл с ними определиться, поскольку программирование форм и отчетов приложений будет значительно легче при известных столбцах.

Метод коррелированных подзапросов

Первый метод Т-SQL является самым распространенным, однако его производительность выглядит убогой на фоне остальных методов, поэтому я не рекомендовал бы его использовать. В то же время он полезен для понимания требований к созданию результирующего набора данных.

Основная идея состоит в выполнении подзапроса для каждого экземпляра каждого измерения всех столбцов группировки. Чтобы пройтись по этому запросу, внимательно всмотритесь в каждую строку инструкции SELECT. Первый столбец — это поле category из таблицы RawData. Столбцы South, NorthEast, Midwest и West представляют собой коррелированные подзапросы, суммирующие значения столбца Amount, отфильтрованного по соответствующей категории. Если перекрестный запрос проходит 1000 строк, а столбцы перекрестной таблицы охватывают каждую неделю года, то серверу, используя этот метод, придется выполнить 52 000 подзапросов.

Последний столбец отображает промежуточный итог для каждой категории:

SELECT R.Category,

(SELECT SUM(Amount)

FROM RawData

WHERE Region = ‘South’ AND Category = R.Category) AS ‘South’, (SELECT SUM(Amount)

FROM RawData

WHERE Region = ‘NorthEast’

AND Category = R.Category) AS ‘NorthEast’,

(SELECT SUM(Amount)

FROM RawData

WHERE Region = ‘MidWest’ AND Category = R.Category) AS ‘MidWest’, (SELECT SUM(Amount)

FROM RawData

WHERE Region = ‘West’ AND Category = R.Category) 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  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:00.000              36         NULL         NULL     NULL

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

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

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

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

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

Y2006-08-01 00:00:00.000                  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                 —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           SQLServerBible.com) попытался справиться

с иерархическими данными с помощью введения общих табличных выражений (СТЕ), которые можно использовать для выборки нескольких уровней данных из иерархического дерева. Со временем, возможно, я изменю свое мнение, но этот новый синтаксис даже не похож на SQL, и я остаюсь приверженцем пользовательских функций навигации.

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

По теме:

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