В статье прошлого месяца, я рассказал о нескольких новых возможностях XML, которые разработчики могут использовать в SQL Server 2005. В этом месяце я начинаю серию из трех статей о новых возможностях языка Transact-SQL в SQL Server 2005. Microsoft добавила много нового в SQL Server 2005 в ответ на пожелания разработчиков, а также в дальнейшем следовании стандарту SQL-99.
В этом месяце мы рассмотрим три новые языковые конструкции:
Новая конструкция PIVOT, позволяющая преобразовать строки данных в столбцы
Новый оператор OUTPUT, немедленно возвращающий данные после выполнения оператора INSERT/UPDATE
Расширение функциональности конструкции TOP N, которое позволяет разработчику задавать выражение в качестве числового параметра
1 - PIVOT
Одной из самых общих задач, с которой сталкиваются разработчики приложений, - это преобразование строк "сырых" строк базы данных к некоторому типу аналитического представления. Пользователи часто хотят видеть данные, просуммированные по кварталам, месяцам или еще по какой-нибудь другой бизнес-категории. Для этого требуется преобразовать строки данных в столбцы. В SQL Server 2000 разработчики в основном имели два варианта:
- Извлечь запросом сырые SQL данные в слой приложения и написать код на C#, VB или на некотором другом языке, и далее агрегировать данные в необходимый формат.
- Использовать выражение CASE T-SQL, чтобы привести сырые данные к необходимому формату столбца на основании соответствующего условия.
Например, рассмотрим таблицу OpenBalances со столбцами DueDate и BalanceOwed; и получим временной отчет, распределяющий открытые балансы по стандартным интервалам времени (1-30 дней, 31-60, 61-90, и т.д.) на основании срока переменной даты.
SELECT SUM(CASE WHEN DueDate BETWEEN @dAgingDate-30 AND @dAgingDate
THEN BalanceOwed ELSE 0 END) AS Age30 ,
SUM(CASE WHEN DueDate BETWEEN @dAgingDate-60 AND @dAgingDate-31
THEN BalanceOwed ELSE 0 END) AS Age60 ,
SUM(CASE WHEN DueDate BETWEEN @dAgingDate-90 AND @dAgingDate-61
THEN BalanceOwed ELSE 0 END) AS Age90
FROM OpenBalances
При том, что оба подхода работают, SQL Server 2005 предоставляет новое ключевое слово PIVOT, чтобы более ясно и просто решить эту задачу. Просто формулируемый, PIVOT позволяет разработчикам превращать строки данных в столбцы. Давайте взглянем на два примера кода, использующих PIVOT. Для первого примера возьмем заказы из базы данных Northwind Orders и получим результирующий набор, который суммирует для каждого клиента величины заказов по кварталам 1997 года.
sp_dbcmptlevel Northwind, 90 - необходимо выполнить для баз данных ранних версий
USE northwind
- Создаем табличную переменную для хранения результатов
DECLARE @tQtrPivotedTable TABLE (CustomerID char(25), M_Q1 Money, M_Q2 Money, M_Q3 Money, M_Q4 Money)
INSERT INTO @tQtrPivotedTable
SELECT * FROM (SELECT CustomerID, DATEPART (q,OrderDate) as OrderQtr, - определяем квартал месяца, используя DatePart
(UnitPrice * Quantity) as Amount
FROM orders OH
JOIN [dbo].[Order Details] OD on OH.Orderid = OD.orderid WHERE Year(OrderDate)=1997) AS TempOrders
PIVOT ( SUM(Amount) FOR OrderQtr In ( [1],[2],[3],[4])) As X
-Распределяем суммы по значениям каждого из четырех кварталов
SELECT * FROM @tQtrPivotedTable - Выборка результатов
Вышеприведенный код определяет соответствующий квартал для каждой даты заказа (OrderDate ), используя функцию SQL DatePart. Затем код поворачивает (то есть преобразует строки в столбцы) сумму объемов заказов (Amount) в зависимости от квартала, представленного одним из четырех значений. Если Вы захотите просуммировать заказы клиента по месяцам, то следует сделать три вещи: изменить табличную переменную, чтобы она имела по одному столбцу на каждый месяц, использовать DartPart для месяца (m) вместо квартала (q) и изменить ссылки с OrderQtr на OrderMonth, а в PIVOT на OrderMonth IN ([1], [2], [3]...... [12]).
Важно отметить, что список значений предиката IN ДОЛЖЕН быть предопределенным, жестко закодированным значением. Значение, для которого применяется PIVOT, (в нашем случае, OrderQtr) должен равняться одному из значений списка. Если список рассматриваемых значений является переменным, Вы должны строить строку запроса вручную и использовать динамический SQL.
Вернемся к нашему примеру о сроках заказов, поскольку это один из наиболее общих результирующих наборов, который должны выдавать разработчики бизнес-приложений. Это займет немного больше времени, но продемонстрирует, как мы можем использовать PIVOT в более конкретной ситуации.
Сначала предположим, что мы имеем таблицу счетов клиентов, содержащую дату счета, сумму счета, и величину, полученную на дату...
DECLARE @tInvoices TABLE (CustomerID char(15), InvoiceNo Char(20), InvoiceDate DateTime,InvoiceAmount decimal(14,2), ReceivedAmount decimal(14,2))
INSERT INTO @tInvoices VALUES ('Customer 1', 'ABC', '09-01-2005', 1000, 0)
INSERT INTO @tInvoices VALUES ('Customer 1', 'DEF', '10-01-2005', 2000, 100)
INSERT INTO @tInvoices VALUES ('Customer 1', 'GHI', '11-01-2005', 3000, 3000)
INSERT INTO @tInvoices VALUES ('Customer 1', 'JKL', '12-01-2005', 4000, 175)
INSERT INTO @tInvoices VALUES ('Customer 1', 'MNO', '12-18-2005', 4000, 175)
INSERT INTO @tInvoices VALUES ('Customer 2', 'PQR', '05-01-2005', 500, 250)
INSERT INTO @tInvoices VALUES ('Customer 2', 'STU', '08-01-2005', 12000, 0)
INSERT INTO @tInvoices VALUES ('Customer 2', 'WYX', '10-01-2005', 7000, 70)
INSERT INTO @tInvoices VALUES ('Customer 2', 'YYZ', '12-01-2005', 3200, 1750)
Затем каждый отчет о сроках должен иметь дату 'НА' (например, отчет на начало месяца ...)
DECLARE @dAgingDate DATETIME
SET @dAgingDate = CAST('12-1-2005' AS DATETIME)
Кроме того, не каждый отчет имеет один и тот же диапазон дат. Поскольку чаще всего определяют такие диапазоны сроков, как 1-30 дней, 31-60, и т.д, нам следует сделать это посредством таблицы. Поэтому мы создадим табличную переменную, которая будет хранить определения сроков на основании диапазонов дней.
- создадим интервалы посредством границ..., которые можно изменять [1-45 дней, и т.д.]
DECLARE @tAgingBrackets TABLE ( StartDay int, EndDay int, BracketNumber int, BracketLabel char(20))
INSERT INTO @tAgingBrackets VALUES (0, 30, 1, '< 30 Days')
INSERT INTO @tAgingBrackets VALUES (31, 60, 2, '31-60 Days')
INSERT INTO @tAgingBrackets VALUES (61, 90, 3, '61-90 Days')
INSERT INTO @tAgingBrackets VALUES (91, 120, 4, '91-120 Days' )
INSERT INTO @tAgingBrackets VALUES (121, 999999, 5, '> 120 Days')
Теперь создадим табличную переменную для хранения результирующего набора - каждый клиент, дата/номер счета и пять столбцов для пяти временных интервалов:
- создаем наш результирующий набор
DECLARE @tAgingDetails TABLE (CustomerID char(15), InvoiceNo char(20), InvoiceDate DateTime,
Bracket1 decimal(14,2), Bracket2 decimal(14,2), Bracket3 decimal(14,2), Bracket4 decimal(14,2),
Bracket5 decimal(14,2))
Наконец, мы используем оператор PIVOT, чтобы преобразовать Amount Owed (величину счета, полученную к настоящему времени) к одному из пяти сроков, сопоставляя BracketNumber из определения @tAgingBrackets, основанного на числе дней, прошедших от даты выставления счета, с номером соответствующего интервального срока.
INSERT INTO @tAgingDetails
SELECT * FROM (select CustomerID,InvoiceNo,invoicedate, InvoiceAmount-ReceivedAmountAS AmountOwed, TBR.BracketNumber
FROM @tInvoices TI, @tAgingBrackets TBR
WHERE InvoiceAmount-ReceivedAmount <> 0 and
DATEDIFF(dd,invoicedate,@dagingdate) BETWEEN TBR.StartDay and TBR.EndDay ) as Temp
PIVOT ( SUM(AmountOwed) FOR BracketNumber In ( [1], [2],[3],[4],[5])) As X
SELECT * FROM @tAgingBrackets
SELECT * FROM @tAgingDetails ORDER BY CustomerID, InvoiceNo
Если описанный выше пример кажется вам слишком пространным, пробуем так: мы используем функцию DateDiff, чтобы определить, что второй счет (датированный 10/1/05) имеет срок 61 день на дату 12-1-2005. Запрос выполняет поиск в таблице @tAgingBrackets, где разность (61 день) находится в диапазоне между заданными начальной и конечной датами. В нашем случае он падает в третий интервал, и мы определяем в PIVOT для Amount Owed номер интервала (Bracket Number) со значением 3.
Одно заключительное примечание относительно PIVOT: я использовал табличные переменные для временного хранения результирующих наборов, отчасти, для того, чтобы самодокументировать эту статью. В последующей статье я расскажу о новой возможности SQL Server 2005 - Общем Табличном Выражении (CTE), - и о том, как мы можем использовать CTE вместо табличных переменных.
2 - OUTPUT
Как поступить в SQL Server 2000, если вы захотите сразу запросить таблицу после оператора INSERT или UPDATE, чтобы вернуть значение столбца, подвергнувшегося изменению? Это может быть результат вычисляемого столбца или значение столбца identity, или некоторое другое значение по умолчанию. Обычно вы либо следом пишите оператор SELECT в хранимой процедуре, либо из своего приложения посылаете вызов обратно на сервер, чтобы получить этот результат. А как в SQL Server 2000 сразу вернуть и старое, и новое значения, после обновления данных (UPDATE)? Зачастую вам приходится обращаться к системным таблицам INSERTED и DELETED, которые видны только в триггере.
Например, в SQL Server 2000, если мы хотим определить значение столбца IDENTITY после выполнения оператора INSERT, нам часто приходится использовать функцию SQL Server SCOPE_IDENTITY:
DECLARE @tTestTable TABLE ( MainPK [int] IDENTITY(1,1) NOT NULL, Name Char(50))
INSERT INTO @tTestTable VALUES ('steve Goff')
SELECT SCOPE_IDENTITY()
SQL Server 2005 предоставляет новый оператор OUTPUT, который позволяет разработчикам решать эти задачи более удобно и эффективно.
Используя OUTPUT в сочетании с оператором INSERT/UPDATE, мы можем легко прочитать добавленную/измененную информацию. Вместо использования SCOPE_IDENTITY, мы можем сразу ВЫВЕСТИ значение столбца IDENTITY:
DECLARE @tTestTable TABLE ( MainPK [int] IDENTITY(1,1) NOT NULL, Name Char(50))
INSERT @tTestTable OUTPUT Inserted.MainPK VALUES ('steve Goff')
Если Вы выполняете несколько операторов INSERT, и вам необходим после этого список вставленных строк, Вы можете перенаправить ВЫВОД в таблицу:
DECLARE @tTestTable TABLE ( MainPK [int] IDENTITY(1,1) NOT NULL, Name Char(50))
DECLARE @tTemp table (mainpk int)
INSERT @tTestTable OUTPUT Inserted.MainPK into @tTemp VALUES ('Kevin Goff')
INSERT @tTestTable OUTPUT Inserted.MainPK into @tTemp VALUES ('steve Goff')
SELECT * FROM @tTemp
Далее, если Вы выполняете оператор UPDATE и хотите сразу увидеть новое/старое значения из системных таблиц INSERTED и DELETED, Вы можете сделать следующее:
DECLARE @tTest TABLE ( MainPK [int] IDENTITY(1,1) NOT NULL ,Amount decimal(10,2))
INSERT INTO @tTest VALUES (100)
INSERT INTO @tTest VALUES (200)
INSERT INTO @tTest VALUES (300)
UPDATE @tTest SET Amount = Amount * 10
OUTPUT DELETED.MainPK, DELETED.Amount AS OldValue, INSERTED.Amount AS NewValue
И снова, если Вы хотите перенаправить результаты OUTPUT в табличную переменную, то можно сделать следующее:
DECLARE @tTemp TABLE (MainPK int, OldValue Decimal(10,2), NewValue Decimal(10,2))
UPDATE @tTest SET Amount = Amount * 10
OUTPUT DELETED.MainPK, DELETED.Amount AS OldValue, INSERTED.Amount AS NewValue INTO @tTemp
Одно замечание: не думайте о новой возможности OUTPUT как о замене аудиторской журнализации с помощью триггеров базы данных. Думайте об этом как об удобном средстве получить мгновенную обратную связь без необходимости обращаться к дополнительным операторам и/или лишним обращениям к серверу.
Триггеры базы данных предлагают лучшие средства реализации функциональных возможностей аудита.
3 - TOP N
SQL Server 2000 трактует N в операторах SELECT TOP N как литерал. Разработчики, которым требовалось выполнить оператор SELECT TOP N, чтобы вернуть первые N строк, вынуждены были использовать динамический SQL (или использовать оператор ROWCOUNT), если величина N являлась переменной. Примером может служить приложение, позволяющее конечному пользователю задавать значение N в процессе его выполнения (например, показать 10-ку лучших по продажам или 5-ку по доходам и т.д.).
Теперь SQL Server 2005 трактует N как числовую переменную, позволяя разработчикам указывать ее в круглых скобках. Так, если мы хотим вернуть 5 (10 и т.д) первых заказов при сортировке по убыванию для величины заказа (Order Amount), то можем поступить следующим образом:
use northwind
DECLARE @nTop int
SET @nTop = 5
Select TOP (@nTop) customerid, oh.orderid, (unitprice * quantity) as amount
from orders OH
join [dbo].[Order Details] OD on oh.orderid = od.orderid
order by Amount Desc
Вы можете задать TOP (N) PERCENT, а также использовать необязательное предложение WITH TIES, чтобы вернуть дополнительные строки с тем же самым значением в выражении ORDER BY.
TOP (N) может даже являться результатом вычисления некоторой функции или выражением, которое возвращает целое число. Например, следующий запрос вернет первые 3 строки из таблицы Orders при сортировке по столбцу Freight в порядке убывания, поскольку в таблице Shippers имеется только три строки:
SELECT TOP( SELECT COUNT(*) FROM SHIPPERS) *
FROM ORDERS ORDER BY Freight DESC
Наконец, Вы можете применить функциональность TOP (N) к операторам INSERT и UPDATE. Это особенно полезно при выполнении специальной обработки промежуточных результирующих наборов. Следующий код создает табличную переменную с тремя строками, и применяет оператор UPDATE для обновления первых двух строк. Затем код создает вторую табличную переменную и вставляет туда первые две строки из первой табличной переменной.
DECLARE @nTop int
SET @nTop = 2
DECLARE @tTest1 TABLE ( Amount decimal(10,2))
INSERT INTO @ttest1 VALUES ( 100)
INSERT INTO @ttest1 VALUES ( 200)
INSERT INTO @ttest1 VALUES ( 300)
UPDATE TOP(@nTop) @tTest1 SET Amount = Amount * 10
DECLARE @tTest2 TABLE ( Amount decimal(10,2))
INSERT TOP(2) @tTest2 SELECT * FROM @tTest1 order by amount
SELECT * FROM @ttest2
Kevin S. Goff, Microsoft MVP (оригинал: Take Five with SQL Server 2005: New T-SQL Capabilities in SQL Server 2005 - Part 1 of 3)
Перевод: Моисеенко С.И.
Оригинал перевода