Перехресні запити в T-SQL, Інші СУБД, Бази даних, статті

Перехресні запити (Crosstab Query) є ще однією специфічною різновидом запитів на вибірку. Призначені вони для більш глибокого аналізу інформації, що зберігається в таблицях. Ключовим словом SQL-оператора перехресного запиту, що задає його тип, є слово TRANSFORM (перетворити). Це передбачає, що значення одного зі стовпців (полів) вибірки, будуть перетворені в назви стовпців підсумкової вибірки. Результати перехресного запиту групуються за двома розділами даних, один з яких розташований у лівому стовпці (стовпцях) таблиці, а другий – у верхньому рядку. В іншому просторі таблиці відображаються результати статистичних розрахунків (Sum, Count і т.д.), виконаних над даними трансформованого поля.

Для демонстрації створимо тестову таблицю:


 
01.CREATE TABLE Sales
02.(
03.SaleID int IDENTITY PRIMARY KEY CLUSTERED,
04.ClientID int,
05.Date datetime,
06.Amount money
07.)
08.insert Sales values(1,“20010401”, 15.48)
09.insert Sales values(1,“20020302”, 134.01)
10.insert Sales values(1,“20031003”, 2346.03)
11.insert Sales values(2,“20030203”, 754.88)
12.insert Sales values(3,“20010301”, 73.07)
13.insert Sales values(3,“20030402”, 734.46)
14.insert Sales values(4,“20010301”, 1567.10)
15.insert Sales values(4,“20020404”, 6575.70)
16.insert Sales values(4,“20030307”, 6575.77)
17.insert Sales values(4,“20030309”, 6575.37)
18.insert Sales values(5,“20011201”, 1975.73)
19.insert Sales values(5,“20030306”, 178965.63)
20.insert Sales values(6,“20020103”, 16785.34)
21.insert Sales values(6,“20030304”, 1705.44)
22.GO

До версії SQL Server 2005, в якому з’явився оператор PIVOT, Перехресні запити виконувалися через оператор CASE ( BOL->Cross-Tab Reports ):


 
01.SELECT
02.  MONTH(Date) AS SaleMonth,
03.  SUM(CASE YEAR(Date)
04.        WHEN 2001 THEN Amount
05.        ELSE 0
06.      END) AS [2001],
07.  SUM(CASE YEAR(Date)
08.        WHEN 2002 THEN Amount
09.        ELSE 0
10.      END) AS [2002],
11.  SUM(CASE YEAR(Date)
12.        WHEN 2003 THEN Amount
13.        ELSE 0
14.      END) AS [2003]
15.FROM Sales
16.GROUP BY MONTH(Date)
17.ORDER BY MONTH(Date)
18.GO

Результат виконання запити:


 
01.SaleMonth   2001                  2002                  2003
02.———– ——————— ——————— ———————
03.1           .0000                 16785.3400            .0000
04.2           .0000                 .0000                 754.8800
05.3           1640.1700             134.0100              193822.2100
06.4           15.4800               6575.7000             734.4600
07.10          .0000                 .0000                 2346.0300
08.12          1975.7300             .0000                 .0000
09.  
10.(6 row(s) affected)

Тепер той же запит, але вже з використанням оператора PIVOT (версія SQL Server 2005 і вище):


 
1.SELECT * FROM
2.(SELECT YEAR(Date) y, MONTH(Date) SaleMonth, Amount FROM Sales) as s
3.PIVOT
4.(SUM(Amount) for y in ([2001], [2002], [2003])) pv

Все б добре, коли ми за раніше знаємо кількість років, але що робити, якщо ми не маємо такої інформації на вході, або потрібно, щоб код був більш універсальним і при додаванні записів з більш старшої датою код був “робітником”. Вихід-динамічний перехресний запит (Dynamic Crosstab Queries). Ускладнимо наші тестові дані додавши хоча б один запис з іншим роком:


1.insert Sales values(1,“20010401”, 15.48)

Дуже цікаве рішення цього завдання запропонував Іцик Бен-Ган (http://am.rusimport.ru/MsAccess/topic.aspx?id=285):


01.CREATE PROCEDURE sp_CrossTab
02.  @table       AS sysname,– Таблиця для побудови crosstab звіту
03.  @onrows      AS nvarchar(128),– Значення для угруповання за рядками
04.  @onrowsalias AS sysname = NULL,– Псевдонім для группіруемих колонки
05.  @oncols      AS nvarchar(128),– Значення для угруповання по колонках
06.  @sumcol      AS sysname = NULL – Значення для підсумовування
07.AS
08.SET NOCOUNT ON
09.DECLARE
10.  @sql AS varchar(8000),
11.  @NEWLINE AS char(1)
12.  
13.SET @NEWLINE = CHAR(10)
14.  
15.– Крок 1: початок рядка SQL.
16.SET @sql =
17.  “SELECT” + @NEWLINE +
18.  ”  “ + @onrows +
19.  CASE
20.    WHEN @onrowsalias IS NOT NULL THEN ” AS “ + @onrowsalias
21.    ELSE “”
22.  END
23.  
24.– Крок 2: Зберігання ключів в тимчасовій таблиці.
25.CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)
26.  
27.DECLARE @keyssql AS varchar(1000)
28.SET @keyssql =
29.  “INSERT INTO #keys “ +
30.  “SELECT DISTINCT CAST(“ + @oncols + ” AS nvarchar(100)) “ +
31.  “FROM “ + @table
32.  
33.EXEC (@keyssql)
34.  
35.– Крок 3: Середня частина рядка SQL.
36.DECLARE @key AS nvarchar(100)
37.SELECT @key = MIN(keyvalue) FROM #keys
38.  
39.WHILE @key IS NOT NULL
40.BEGIN
41.  SET @sql = @sql + “,”                   + @NEWLINE +
42.    ”  SUM(CASE CAST(“ + @oncols +
43.                     ” AS nvarchar(100))” + @NEWLINE +
44.    ”        WHEN N”“” + @key +
45.           “”” THEN “ + CASE
46.                          WHEN @sumcol IS NULL THEN “1”
47.                          ELSE @sumcol
48.                        END + @NEWLINE +
49.    ”        ELSE 0″                      + @NEWLINE +
50.    ”      END) AS [“ + @key+“]”
51.  
52.  SELECT @key = MIN(keyvalue) FROM #keys
53.  WHERE keyvalue > @key
54.END
55.  
56.– Крок 4: Кінець рядка SQL.
57.SET @sql = @sql         + @NEWLINE +
58.  “FROM “ + @table      + @NEWLINE +
59.  “GROUP BY “ + @onrows + @NEWLINE +
60.  “ORDER BY “ + @onrows
61.  
62.SET NOCOUNT OFF
63.PRINT @sql  + @NEWLINE– Для налагодження
64.EXEC (@sql)
65.GO

Виклик цієї процедури:


1.EXEC sp_CrossTab
2.  @table       = “Sales”,
3.  @onrows      = “MONTH(Date)”,
4.  @onrowsalias = “SaleMonth”,
5.  @oncols      = “YEAR(Date)”,
6.  @sumcol      = “Amount”
7.GO

Але хотілося б трохи спростити цю процедуру за допомогою оператора PIVOT і ось, що вийшло:


01.ALTER PROCEDURE sp_CrossTab_PIVOT
02.  @table       AS sysname,– Таблиця для побудови crosstab звіту
03.  @onrows      AS nvarchar(128),– Значення для угруповання за рядками
04.  @onrowsalias AS sysname = NULL,– Псевдонім для группіруемих колонки
05.  @oncols      AS nvarchar(128),– Значення для угруповання по колонках
06.  @sumcol      AS sysname = NULL – Значення для підсумовування
07.AS
08.SET NOCOUNT ON
09.DECLARE
10.  @sql AS nvarchar (max),
11.  @case AS varchar(1000)
12.SET @case=“”
13.SELECT @sql=
14.SELECT @case=@case+”“[““+CONVERT(VARCHAR, “+@oncols+“)+”“], ““”+
15.” FROM “+@table+” GROUP BY “+@oncols+” ORDER BY “+@oncols
16.  
17.EXEC sp_executesql @sql,N“@case varchar(1000) out”, @case=@case out
18.SET @case=LEFT(@case, LEN(@case)-1)
19.  
20.SELECT @sql=“SELECT * FROM (
21.SELECT “+@oncols+” y, “+@onrows+” “+@onrowsalias+“, “+@sumcol+” FROM “+@table+
22.“) as s
23.PIVOT
24.(SUM (“+@sumcol+“) for y in (“+@case+“)) as pv”
25.PRINT @sql – Для налагодження
26.EXECUTE (@sql)

Виклик моєї процедури ідентичний викликом попередньої процедури:


1.EXEC sp_CrossTab_PIVOT
2.  @table       = “Sales”,
3.  @onrows      = “MONTH(Date)”,
4.  @onrowsalias = “SaleMonth”,
5.  @oncols      = “YEAR(Date)”,
6.  @sumcol      = “Amount”
7.GO

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


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

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

Ваш отзыв

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

*

*