czwartek, 25 września 2008

Dynamiczne obracanie danych

Początkowo miałem pisać dalej o optymalizacji w wykorzystaniem indeksów. Kontynuacja tematu rozpoczętego wcześniej. Dość długo się do tego zabierałem, a w między czasie pojawił się ciekawy problem na forum na stronie wss.pl.

Zanim jednak przejdę do meritum sprawy, naświetlę trochę temat, wykorzystując banalny przykład obracania danych w SQL 2005/2008 (PIVOT) oraz technikę która umożliwa osiągnięcie tego samego celu w poprzedniej wersji SQL Servera.

Na początek trochę danych.

USE TEMPDB
GO
IF OBJECT_ID('tempdb..#T1') IS NOT NULL
DROP TABLE #T1

CREATE TABLE #T1 (
CLUB NVARCHAR(40) NOT NULL,
ATTRIBUTE NVARCHAR(40) NOT NULL,
VALUE INT
)

ALTER TABLE #T1 WITH NOCHECK
ADD CONSTRAINT PK PRIMARY KEY CLUSTERED(CLUB, ATTRIBUTE)

INSERT INTO #T1
SELECT 'Diving Experience', '# members', 123 UNION ALL
SELECT 'Diving Experience', '# branches', 8 UNION ALL
SELECT 'Diving Experience', '# equipment', 100 UNION ALL
SELECT 'Global Diving', '# members', 125 UNION ALL
SELECT 'Global Diving', '# branches', 9 UNION ALL
SELECT 'My Scuba', '# members', 127 UNION ALL
SELECT 'My Scuba', '# equipment', 128 UNION ALL
SELECT 'Sharks Diving', '# members', 50 UNION ALL
SELECT 'Sharks Diving', '# equipment', 50 UNION ALL
SELECT 'Sharks Diving', '# branches', 2 UNION ALL
SELECT 'Sharks Diving', '# boats', 1


Tabela #T1 zawiera dane w następującej formie:

CLUB ATTRIBUTE VALUE
----------------- ----------- -----------
Diving Experience # branches 8
Diving Experience # equipment 100
Diving Experience # members 123
Global Diving # branches 9
Global Diving # members 125
My Scuba # equipment 128
My Scuba # members 127
Sharks Diving # boats 1
Sharks Diving # branches 2
Sharks Diving # equipment 50
Sharks Diving # members 50


Naszym celem jest przekształcenia tak danych aby każdy z atrybutów był osobą kolumną. Aby osiągnąć zamierzony cel musimy obrócić dane wokół osi, którą będzie kolumna atrybut.

Od SQL Servera w wersji 2005 mamy do dyspozycji klauzulę PIVOT.

Jako kolumny w zapytaniu, podajemy klub dla którego chcemy otrzymać nasze dane, oraz wszystkie dostępne wartości parametrów, a raczej te które nas interesują. Te same wartości parametrów muszą także występować w dalszej części zapytania.

PIVOT wykorzystuje także funkcję agregującą. W tym poniższym przykładzie będzie to MAX.

Wykonując zapytanie:

SELECT
CLUB,
ISNULL([# MEMBERS], 0) AS MEMBERS,
ISNULL([# BRANCHES], 0) AS BRANCHES,
ISNULL([# EQUIPMENT], 0) AS EQUIPMENT,
ISNULL([# BOATS], 0) AS BOATS
FROM #T1
PIVOT (MAX(VALUE) FOR ATTRIBUTE IN ([# MEMBERS], [# BRANCHES], [# EQUIPMENT], [# BOATS])) AS P

Otrzymamy następujący wynik:

CLUB MEMBERS BRANCHES EQUIPMENT BOATS
----------------- ----------- ----------- ----------- -----
Diving Experience 123 8 100 0
Global Diving 125 9 0 0
My Scuba 127 0 128 0
Sharks Diving 50 2 50 1


Każdy z atrybutów stał się teraz osobą kolumną. Teraz w razie potrzeby dane mogą być dalej przetwarzane. Wykorzystanie tabeli w postaci otwartego schematu, gdzie mamy atrybuty i ich wartości w jednym wierszu jest bardzo przydatne w przypadku gdy często zmienia się schemat danych, lub gdy też różne obiekty mają różne wartości parametrów. W SQL Server 2005/2008 ten sam cel można osiągnąć poprzez przechowywanie atrybutów obiektu w postaci XML. Jednakże to temat na inną dyskusję.

W SQL Server 2000 nie mamy do dyspozycji klauzuli PIVOT, jednakże osiągnięcie tego samego celu nie jest bardzo skomplikowane.

Poniższe zapytanie zwróci nam ten sam zbiór wyników

SELECT
CLUB,
ISNULL(MAX(CASE WHEN ATTRIBUTE = '# MEMBERS' THEN VALUE END),0) AS MEMBERS,
ISNULL(MAX(CASE WHEN ATTRIBUTE = '# BRANCHES' THEN VALUE END),0) AS BRANCHES,
ISNULL(MAX(CASE WHEN ATTRIBUTE = '# EQUIPMENT' THEN VALUE END),0) AS EQUIPMENT,
ISNULL(MAX(CASE WHEN ATTRIBUTE = '# BOATS' THEN VALUE END),0) AS BOATS
FROM #T1
GROUP BY CLUB
ORDER BY CLUB


Warto zwrócić tutaj uwagę na fakt, że w przypadku PIVOT grupowanie elementów jest ukryte. Jeżeli spojrzymy co robi optymalizator to zauważymy, że plan wykonania w obu przypadkach jest jednakowy. Co więcej zapytanie kompatybilne z SQL Server 2000 ma nieznacznie mniejszy koszt.

Może trochę nudnawe i przydługie te wprowadzenie do tematu, ale myślę, że warto.

Przejdźmy więc do meritum problemu. Jeden z użytkowników forum zapytał jak przekształcić dane z następującej postaci:

a 123
a 124
a 64
b 125
b 126
c 127
c 128
d 129
d 130
d 131

do

a 123 124 64
b 125 126
c 127 128
d 129 130 131


Na pierwszy rzut oka mogłoby to wyglądać na prosty przykład obracania danych. Jednakże na samym początku pojawią się pierwszy problem. Która kolumna będzie naszą osią. Pierwsza kolumna to unikalne elementy a druga to jakieś wartości. Brak w tym wszystkim jednej kolumny umożliwiającej nam obrócenie danych.

Dugi problem to ilość kolumn w wyniku. Z powyższego przykładu danych nie można wywnioskować ilości docelowych kolumn. Jedną z wad PIVOT jest to, że z góry musimy znać wszystkie możliwe kolumny. PIVOT nie pozwala nam tworzyć ich dynamicznie.

Na początek utwórzmy zbiór danych.

USE TEMPDB
GO

IF OBJECT_ID('tempdb..#T1') IS NOT NULL
DROP TABLE #T1

CREATE TABLE #T1 (
ID CHAR(1),
VAL INT
)

INSERT INTO #T1
SELECT 'a', 123 UNION ALL
SELECT 'a', 124 UNION ALL
SELECT 'a', 64 UNION ALL
SELECT 'b', 125 UNION ALL
SELECT 'b', 126 UNION ALL
SELECT 'c', 127 UNION ALL
SELECT 'c', 128 UNION ALL
SELECT 'd', 129 UNION ALL
SELECT 'd', 130 UNION ALL
SELECT 'd', 131


Na początek musimy rozwiązać problem kolumny, wokół której będziemy obracać nasze dane.

W tym celu z pomocą przychodzi nowa w SQL 2005 funkcja ROW_NUMBER.

IF OBJECT_ID('tempdb..#T2') IS NOT NULL
DROP TABLE #T2

;WITH CTE AS (
SELECT
ID,
VAL,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS RN,
'ATTR' + CONVERT(VARCHAR(2),ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID)) AS ATTR
FROM #T1
)


Partycjonujemy nasze dane, dzięki czemu uzyskujemy maksymalną liczbę różnych parametrów. Tworzymy też dodatkową kolumnę, ATTR, która będzie naszą osią. Unikalne parametry tworzymy korzystając ponownie z ROW_NUMBER, tym razem doklejając uzyskaną wartość do attr.

Tak zmodyfikowane dane wstawiamy do tabeli tymczasowej.

SELECT * INTO #T2 FROM CTE

Zawartość naszej tabeli przedstawia się w następujący sposób.

ID VAL RN ATTR
--- ---- --- ------
a 123 1 ATTR1
a 124 2 ATTR2
a 64 3 ATTR3
b 125 1 ATTR1
b 126 2 ATTR2
c 127 1 ATTR1
c 128 2 ATTR2
d 129 1 ATTR1
d 130 2 ATTR2
d 131 3 ATTR3


Mając teraz kolumnę ATTR możemy przejść do obracania danych. Od razu napotykamy na nasz drugi problem. Ile tak naprawdę będziemy mieli różnych rodzajów atrybutów? W powyższym przykładzie jest to 3 ale może równie dobrze być ich znacznie więcej. Tak więc zwykły PIVOT nam tutaj na niewiele się zda. Musimy wykonać go dynamicznie, a raczej skonstruować zapytanie dynamicznie.

Na początek musimy zdobyć maksymalną ilość atrybutów.

W tym celu deklarujemy zmienną i pobieramy maksymalną wartość z kolumny RN

DECLARE @MAXATTR AS INT
SELECT @MAXATTR = MAX(RN) FROM #T2


Deklarujemy także kolejnych kilka zmiennych, które będą wykorzystane przy tworzeniu dynamicznego zapytania.

DECLARE @I AS int
DECLARE @SQL AS NVARCHAR(200)
DECLARE @PIVOTSQL AS NVARCHAR(200)
DECLARE @TABLESQL AS NVARCHAR(400)
DECLARE @FINALSQL AS NVARCHAR(400)


Mając to wszystko możemy wygenerować nasz dynamiczny kod.

WHILE @I <= @MAXATTR
BEGIN

SET @SQL = @SQL + ', attr' + CONVERT(NVARCHAR(4),@I)
SET @PIVOTSQL = @PIVOTSQL + '[ATTR' + CONVERT(NVARCHAR(2),@I) + '],'
SET @TABLESQL = @TABLESQL + 'ALTER TABLE #T3 ADD ATTR' + CONVERT(NVARCHAR(4),@I) + ' INT '
SET @FINALSQL = @FINALSQL + ', MAX(ISNULL(ATTR' + CONVERT(NVARCHAR(4),@I) + ',0)) AS COLUMN' + CONVERT(NVARCHAR(4),@I)
SET @I = @I + 1

END


Kilka słów o tym co dzieję się w pętli, która wykonywana jest tyle razy ile mamy unikalnych atrybutów.

Zmienna SQL przechowuje pierwszą część naszego zapytania, tzn wszystkie kolumny, które będą występowały po SELECT.

Zmienna PIVOTSQL przechowuje wszystkie wartości atrybutów, które docelowo będą naszymi kolumnami.

Zmienna TABLESQL, tworzy kod który będzie modyfikował jedną z tabel docelowych. Kilka słów więcej o niej w dalszej części.

Zmienna FINALSQL przechowuje zapytanie, które zwróci nam ostateczny wynik. Zapytanie utworzone w tej zmiennej grupuje nasze dane.

W pętli tej jednakże tworzymy tylko fragmenty zapytań, z wyjątkiem TABLESQL, które po zakończeniu pętli jest już kompletne.

Tworzymy teraz nasze ostateczne zapytania.

SET @PIVOTSQL = STUFF(@PIVOTSQL,LEN(@PIVOTSQL),1,'')
SET @SQL = @SQL + ' FROM #T2 PIVOT(MAX(VAL) FOR ATTR IN(' + @PIVOTSQL + ')) AS P '
SET @SQL = 'INSERT INTO #T3 SELECT * FROM (' + @SQL + ') AS DSQL'
SET @FINALSQL = @FINALSQL + ' FROM #T3 GROUP BY ID ORDER BY ID'


Tworzymy także kolejną tabelę tymczasową, która będzie przechowywać wynik obracania danych.

IF OBJECT_ID('tempdb..#T3') IS NOT NULL
DROP TABLE #T3

CREATE TABLE #T3 (
ID CHAR(1)
)


Tabela ta na początek posiada tylko jedną kolumnę, która zawiera unikalne elementy. W momencie kiedy ją tworzymy nie wiemy ile będziemy mieli docelowo różnych atrybutów (kolumn). Kolumny te dodamy po uruchomianiu dynamicznie wygenerowanego kodu. W każdej iteracji naszej pętli dodawana była jedna kolumna odpowiadająca wartości naszego atrybutu.

Teraz wykonujemy nasze dynamicznie utworzone zapytania.

EXEC SP_EXECUTESQL @TABLESQL

EXEC SP_EXECUTESQL @SQL

EXEC SP_EXECUTESQL @FINALSQL


Ostatnie z nich zwraca nam nasz pożądany wynik.

ID COLUMN1 COLUMN2 COLUMN3
---- ----------- ----------- -------
a 123 124 64
b 125 126 0
c 127 128 0
d 129 130 131


Jak widać nie ograniczenie jakie niesie za sobą PIVOT idzie łatwo ominąć generując kod dynamicznie.

PS. Po napisaniu oryginalnego rozwiązania postanowiłem je jeszcze trochę zoptymalizować. Oryginalny kod wymagał użycia trzech tabel tymczasowych, a jedna z nich miała dynamicznie dodawane kolumny. Wszystko to za sprawą tego, że tabela #T2 poza danymi wynikowymi posiadała także kolumnę RN, która przechowywała maksymalne wartości atrybutów dla każdej z unikalnych grup. W wyniku przekształcania danych dużo wierszy posiadało wartości NULL. Eliminowane one były dopiero w trzeciej tabeli tymczasowej.

Poniżej cały kod, delikatnie zmodyfikowany. Wynik oczywiście taki sam.

USE TEMPDB
GO

IF OBJECT_ID('tempdb..#T1') IS NOT NULL
DROP TABLE #T1

CREATE TABLE #T1 (
ID CHAR(1),
VAL INT
)

INSERT INTO #T1
SELECT 'a', 123 UNION ALL
SELECT 'a', 124 UNION ALL
SELECT 'a', 64 UNION ALL
SELECT 'b', 125 UNION ALL
SELECT 'b', 126 UNION ALL
SELECT 'c', 127 UNION ALL
SELECT 'c', 128 UNION ALL
SELECT 'd', 129 UNION ALL
SELECT 'd', 130 UNION ALL
SELECT 'd', 131
IF OBJECT_ID('tempdb..#T2') IS NOT NULL
DROP TABLE #T2
;WITH CTE AS (
SELECT
ID,
VAL,
'ATTR' + CONVERT(VARCHAR(2),ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID)) AS ATTR
FROM #T1
)

SELECT * INTO #T2 FROM CTE

DECLARE @MAXATTR AS INT
SELECT @MAXATTR = MAX(RN) FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS RN
FROM #T1
) AS MAXATTR
DECLARE @I AS int
DECLARE @SQL AS NVARCHAR(400)
DECLARE @PIVOTSQL AS NVARCHAR(200)
SET @SQL = 'SELECT ID '
SET @PIVOTSQL = ''
SET @I = 1
WHILE @I <= @MAXATTR
BEGIN
SET @SQL = @SQL + ', ISNULL(attr' + CONVERT(NVARCHAR(4),@I) + ', 0) AS COLUMN' + CONVERT(NVARCHAR(4),@I)
SET @PIVOTSQL = @PIVOTSQL + '[ATTR' + CONVERT(NVARCHAR(2),@I) + '],'
SET @I = @I + 1
END


SET @PIVOTSQL = STUFF(@PIVOTSQL,LEN(@PIVOTSQL),1,'')
SET @SQL = @SQL + ' FROM #T2 PIVOT(MAX(VAL) FOR ATTR IN(' + @PIVOTSQL + ')) AS P '
EXEC SP_EXECUTESQL @SQL
DROP TABLE #T1
DROP TABLE #T2

Prześlij komentarz