Нові можливості T-SQL у SQL Server 2005 – Частина 1 / 3

У статті минулого місяця, я розповів про декілька нових можливостях 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)
Переклад: Моісеєнко С.І.
Оригінал перекладу
 

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*