Динамическое вращение с кубами и обработчиками событий в SQL Server

Tags: SQL Server, SQL

Wednesday, January 2, 2019 6:33:06 PM

Download

Вступление

В этой статье объясняется создание динамических стержней. Оно начинается с простого стержня. Второй шаг - добавить строки для расчета итогов. В этом случае вы можете видеть, что TransAct SQL имеет обработчики событий, и в сочетании с командой WITH CUBE они очень удобны. К сожалению, команда PIVOT в SQL Server (2005 и выше) работает с именами именованных столбцов. Чтобы сделать ее динамичной, нужно добавить немного программирования.

Бэкграунд

Один из наших клиентов всегда загружал дельту данных в нашу систему, пока не узнал, что можно ежемесячно добавлять все данные в нашу систему. Мы хотели увидеть, насколько большим стал рост системы. Месяц, когда он начинал загружать полные файлы вместо дельта-файлов, значительно показывал результат. Когда мы создали график данных Excel, все были поражены. Сложив итоги, был создан простой управленческий отчет, который очень просто рассказал нашему клиенту о том, на что способна наша система.

Использование кода

База данных, используемая для этой статьи - AdventureWorks, которую можно найти здесь. Запросы, прикрепленные к этому проекту, могут выполняться в SQL Server Management Studio (SSMS) для SqlServer 2008 R2. Имя базы данных Adventureworks - AdventureWorksDW2008R2.

Начало

Большинство разработчиков знают некоторый SQL, и когда им нужно создать запрос, показывающий соответствие между двумя вещами, возникает такой запрос:

SELECT var1, var2, count(var2) from table1 group by var1, var2

Если мы посмотрим на базу данных AdventureWorksDW2008R2, таблица FactSalesQuota может привести к следующему вопросу. На основе полей CalendarYear, CalendarQuarter и SalesAmountQuota можно выяснить, какая сумма была продана за квартал в год. Запрос в начале может быть таким:

SELECT [CalendarYear],[CalendarQuarter],[SalesAmountQuota] _
	FROM [AdventureWorksDW2008R2].[dbo].[FactSalesQuota]

В результате получается длинный список, который «улучшается» с помощью функции SUM () и GROUP BY. Но 3 столбца ничего не говорят. Актуальность не видна.

 

Как начать получать динамическое вращение

«Улучшенный» запрос - это начало динамического вращения. Если мы хотим увидеть эффективный результат между CalendarYear и CalendarQuarter, у нас есть четыре шага:

  1. Сохраните результат запроса во временной таблице.
  2. Найдите уникальные значения в столбцах CalendarQuarter и установите их как имена столбцов в varchar.
  3. Создайте команду pivot (на основе команды pivot с использованием определенных имен столбцов) в varchar.
  4. Выполните созданную команду pivot.
  5. Мы сказали четыре шага, но шаг 5 часто забывают: бросьте свой временную таблицу.

И теперь в коде

Начните устанавливать результат запроса во временной таблице:

SELECT [CalendarYear],[CalendarQuarter],SUM([SalesAmountQuota]) as SalesAmount
INTO	#tempPivotTable
FROM [AdventureWorksDW2008R2].[dbo].[FactSalesQuota]
GROUP BY [CalendarYear],[CalendarQuarter]

Затем создайте имена столбцов на основе уникальных значений в столбце CalendarQuarter. Каждое значение должно быть приведено к типу varchar и помещено в скобки, и ваш столбец готов! Для создания @Columns в основном используется @columns = @columns + '[' + '.....' + ']'. Опасность в этом коде заключается в том, что когда '['+' ..... '+'] 'не работает, вы не знаете, чему будет равно @columns. Поэтому лучше использовать команду STUFF. Это одна функция, и этот результат помещается в нашу переменную:

DECLARE @columns VARCHAR(8000)

SELECT @columns = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                '],[' + cast([CalendarQuarter] as varchar)
                        FROM    #tempPivotTable AS t2
                        ORDER BY '],[' + cast([CalendarQuarter] as varchar)
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'

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

DECLARE @query VARCHAR(8000)

SET @query = '
SELECT *
FROM #tempPivotTable
PIVOT
(
MAX(SalesAmount)
FOR [CalendarQuarter]
IN (' + @columns + ')
)
AS p '

Выполните созданный запрос:

EXECUTE(@query)

Сбросьте свою временную таблицу. В противном случае вы не сможете выполнить этот запрос два раза подряд.

DROP TABLE #tempPivotTable

На изображении проекта вы можете увидеть результат. Если вы видите значение NULL в поле, вы знаете, что комбинация недоступна.

Станьте профессионалом динамического вращения

Приятно иметь стержень, но следующий вопрос придет от вашего менеджера. Продаем ли мы больше в год, и продаем ли мы больше за квартал, основываясь на годах? Пришло время подсчитать строки во время выполнения запроса. Сначала нам нужна команда WITH CUBE в нашем базовом запросе.

SELECT [CalendarYear],[CalendarQuarter],SUM([SalesAmountQuota]) as SalesAmount
INTO	#tempPivotTable
FROM [AdventureWorksDW2008R2].[dbo].[FactSalesQuota]
GROUP BY [CalendarYear],[CalendarQuarter]
WITH CUBE

Если вы запустите запрос, строка будет добавлена сверху, начиная с NULL, а затем значения. Он представляет собой суммированное значение за квартал за эти годы. Но на первую часть вопроса нет ответа: продаем ли мы больше в год? На данный момент нам нужны обработчики событий в транзакционном SQL. Расслабьтесь! Мы поможем. Требуется обработчик события 'grouping'. Когда group by выполняется на сервере SQL, вы хотите, чтобы в первом столбце отображалось  'Total', в противном случае - календарный год. Таким образом, нам также нужна функция 'case when', чтобы выполнить это 'if'. Ваш основной запрос изменяется:

SELECT	CASE WHEN GROUPING(cast([CalendarYear] as varchar)) = 1
			THEN 'Total'
			ELSE cast([CalendarYear] as varchar)
			END
		 as [CalendarYear],
		[CalendarQuarter],SUM([SalesAmountQuota]) as SalesAmount
	INTO	#tempPivotTable
	FROM [AdventureWorksDW2008R2].[dbo].[FactSalesQuota]
	GROUP BY cast([CalendarYear] as varchar),[CalendarQuarter]
	WITH CUBE

В этом случае 'Total' - это varchar, а CalendarYear -  integer. Вот почему добавляется приведение к varchar (также в  group by). Вот почему нам немного повезло! Если бы calendaryear имел другое значение, например, «year of the name_an_animal» в качестве отправной точки, строка Total НАЧИНАЕТ результат. Решение дается в конце этой статьи. Если необходимо добавить столбец Total, он должен быть в коллекции CalendarQuarter. Поэтому нам также нужна группировка для CalendarQuarter. Базовый запрос снова расширяется:

SELECT	CASE WHEN GROUPING(cast([CalendarYear] as varchar)) = 1
			THEN 'Total'
			ELSE cast([CalendarYear] as varchar)
			END
		 as [CalendarYear],
		CASE WHEN GROUPING(cast([CalendarQuarter] as varchar)) = 1
		THEN 'Total'
			ELSE cast(CalendarQuarter as varchar)
			END
		 as [CalendarQuarter],
		SUM([SalesAmountQuota]) as SalesAmount
INTO	#tempPivotTable
FROM [AdventureWorksDW2008R2].[dbo].[FactSalesQuota]
GROUP BY cast([CalendarYear] as varchar), cast([CalendarQuarter] as varchar)
WITH CUBE

Если вы выполните общий запрос, вы увидите, что:

  • Квартал 3 - самый продаваемый квартал
  • Мы продаем больше каждый год, и последний год еще не закончен, но тенденции уже понятны
  • Ваш менеджер будет счастлив
  • В одно мгновение вы видите ценность ваших данных

Общий запрос:

SELECT	CASE WHEN GROUPING(cast([CalendarYear] as varchar)) = 1
			THEN 'Total'
			ELSE cast([CalendarYear] as varchar)
			END
		 as [CalendarYear],
		CASE WHEN GROUPING(cast([CalendarQuarter] as varchar)) = 1
		THEN 'Total'
			ELSE cast(CalendarQuarter as varchar)
			END
		 as [CalendarQuarter],
		SUM([SalesAmountQuota]) as SalesAmount
INTO	#tempPivotTable
FROM [AdventureWorksDW2008R2].[dbo].[FactSalesQuota]
GROUP BY cast([CalendarYear] as varchar), cast([CalendarQuarter] as varchar)
WITH CUBE

DECLARE @columns VARCHAR(8000)

SELECT @columns = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                '],[' + cast([CalendarQuarter] as varchar)
                        FROM    #tempPivotTable AS t2
                        ORDER BY '],[' + cast([CalendarQuarter] as varchar)
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'

DECLARE @query VARCHAR(8000)

SET @query = '
SELECT *
FROM #tempPivotTable
PIVOT
(
MAX(SalesAmount)
FOR [CalendarQuarter]
IN (' + @columns + ')
)
AS p '

EXECUTE(@query)

DROP TABLE #tempPivotTable

Решение для Total Row в нижней части

Дело в том, что «Total» начинается с «t», а «u, v, w, x, y, z» может привести к итоговой строке в середине вашего набора результатов. В этом случае вы должны посмотреть на используемое сопоставление в базе данных. В нашем случае это: Latin1_General_CI_AS. Если бы вы посмотрели сюда, вы могли бы видеть, что символ 161 находится после 'z'. Мы знаем, что слово «Total» теперь выглядит некрасиво, но строка и / или столбец четко различимы. Однако эффект заключается в том, что второй столбец и первая строка представляют итоговые значения. Это выглядит более уродливо, но если вы начнете «Total» с «ZZ», вы всегда можете закончить итоговые строки и столбцы.

Последние мысли

Если бы вместо квартала год использовался для имен столбцов, вы бы действительно увидели всю мощь этого запроса. Если данные за 2009 год были добавлены в таблицу, этот год сразу же появится в виде нового столбца в наборе результатов. Это делает такие запросы довольно удобными для:

  • Управленческих отчетов
  • Быстрого сканирования значений в случае проблем
  • Получение реальной ценности ваших данных видимыми



No Comments

Add a Comment