Co to te CTE?I jak zrobić PIVOT?

(oraz słów kilka o GROUPING SETS)

90. Spotkanie PLSSUG Warszawa, 08.10.2015

O czym będzie?• Co to CTE, a przede wszystkim jak działa rekurencja w CTE?• Jak obracać dane, czyli PIVOT i UNPIVOT na kilka sposobów• Grupowanie wielopoziomowe, czyli GROUPING SETS

O mnieBartosz Ratajczyk

programista baz danych(i aplikacji)

czasem też administratortrener

T-SQL, ETL, C#, PHP, JS, SAS 4GL

http://bartekr.net | b.ratajczyk@gmail.com

MCTS SQL Server 2008, MCSA SQL Server 2012, MCT

CTECzyli Common Table Expression

Table expressionsNazwane zapytania, które możemy traktować tak samo jak tabele

Nieutrwalone Utrwalone

Derived table View

Common Table Expression Table Valued Function

Jak wygląda CTE?-- pokaż sprzedaż pracowników w danym miesiącu;WITH Dane AS ( SELECT Pracownik = p.nazwa, Produkt = pr.nazwa, RokMiesiac = YEAR(s.data_sprzedazy) * 100 + MONTH(s.data_sprzedazy), s.ilosc, s.wartoscFROM Sprzedaz s

INNER JOIN Pracownik p ON p.id = s.pracownik_id

INNER JOIN Produkt pr ON pr.id = s.produkt_id)SELECT *FROM Dane

CTE z nazwami kolumn-- pokaż sprzedaż pracowników w danym miesiącu;WITH Dane (Pracownik, Produkt, RokMiesiąc, Ilość, Wartość) AS ( SELECT Pracownik = p.nazwa, Produkt = pr.nazwa, RokMiesiac = YEAR(s.data_sprzedazy) * 100 + MONTH(s.data_sprzedazy), s.ilosc, s.wartoscFROM Sprzedaz s

INNER JOIN Pracownik p ON p.id = s.pracownik_id

INNER JOIN Produkt pr ON pr.id = s.produkt_id)SELECT *FROM Dane

A ta rekurencja?;WITH Poziomy AS (

SELECT <kolumny>FROM Pracownik pWHERE

przelozony_id IS NULL

UNION ALL

SELECT <kolumny>FROM Pracownik p

INNER JOIN Poziomy poON po.pracownik_id = p.przelozony_id

)

SELECT *FROM Poziomy

Rekurencja• Rekurencja w programowaniu: funkcja wywołuje samą siebie• Rekurencja w SQL: zapytanie wywołuje samo siebie

Różnica między iteracją a rekurencją?Iteracja: 55 = 10 + 9 + 8 + 7 + 6 + 5 + 4 + 3 + 2 + 1Rekurencja:

55 = 10 + (Suma liczb od 1 do 9)55 = 10 + 9 + (Suma liczb od 1 do 8)55 = 10 + (9 + (8 + (7 + (6 + (5 + (4 + (3 + (2 + (1)))))))))

DEMO

Plan zapytania rekurencyjnego CTE

Ma to jakieś ograniczenia albo wady?• CTE vs funkcja (32 poziomy)• MAXRECURSION X vs MAXRECURSION 0• Widoczność tylko dla następnego zapytania• Trudniej testować niż derived tables• Każde odwołanie do CTE to jego wywołanie od zera (nie dotyczy

rekurencji)

Obracanie danychCzyli PIVOT, UNPIVOT i inne konstrukcje

Jak wygląda polecenie PIVOT (i dlaczego tak?)SELECT

Pracownik,Produkt,[201401], [201402], [201403], [201404], [201405], [201406],[201407], [201408], [201409], [201410], [201411], [201412]

FROM Dane

PIVOT (SUM(ilosc)FOR RokMiesiac IN (

[201401], [201402], [201403], [201404], [201405], [201406],[201407], [201408], [201409], [201410], [201411], [201412]

)) p

No ale dlaczego tak?• Jaich informacji potrzebujemy do obrócenia danych z wierszy na

kolumny:• Po czym grupujemy?• Z której kolumny bierzemy atrybuty?• Które wartości nas interesują?• I jak je zliczać?

A jak to przełożyć na kod – za chwilę

DEMO

To całkiem niezłe, ale ma ograniczenia• Tylko jedna funkcja agregująca w konstrukcji PIVOT• Muszę znać nazwy kolumn i podać je wprost

Zgadza się, ale można to obejść

DEMO

To teraz w drugą stronę – z kolumn na wierszeSELECT

Pracownik,Produkt,RokMiesiac,Wartosc

FROM SprzedazPivot

UNPIVOT (WartoscFOR RokMiesiac IN (

[201401], [201402], [201403], [201404], [201405], [201406],[201407], [201408], [201409], [201410], [201411], [201412]

)) u

To znowu – dlaczego tak?• Jakich informacji potrzebujemy do zmiany kolumn na wiersze:• Które kolumny mają trafić jako wiersze?• Jak się ma nazywać kolumna, która będzie trzymała nazwy kolumn?• Jak ma się nazywać kolumna z wartościami z tych kolumn?

DEMO

Ten UNPIVOT tez ma wady• Też muszę podać nazwy tych wszystkich kolumn?

Tak, ale wiesz już jak to obejść• Nie da się obrócić więcej niż jednego zestawu kolumn

Ale na to też jest sposób

DEMO

GROUPING SETSCzyli grupujemy wielopoziomowo

Co to GROUPING SET?• To po prostu zestaw wyrażeń, po których grupujemy

Czyli pisząc tak:

GROUP BY Produkt, Rok, Miesiac, Dzien

Możemy napisać tak:

GROUP BY GROUPING SETS ((Produkt, Rok, Miesiac, Dzien)

)(ale wyjdzie dłużej)

No rewelacja, a daje to coś więcej?• Oczywiście – możemy podać kilka zestawów na raz

GROUP BY GROUPING SETS ((Produkt, Rok, Miesiac, Dzien),(Produkt, Rok),(Produkt, Rok, Miesiac),()-- <-- TOTAL

)

DEMO

GROUPING() i GROUPING_ID()• GROUPING() sprawdza, czy rekord pochodzi z grupowania (1) czy to

rekord źródłowy (0)SELECT GROUPING(Produkt)

• GROUPING_ID() zwraca maskę bitową kolumn, które nie zostały użyte w grupowaniu

SELECT GROUPING_ID(Produkt, Rok, Miesiac, Dzien)

CUBE i ROLLUP• Skracają pisanie wszystkich kombinacji GROUPING SETS• CUBE tworzy zbiór wszystkich możliwych kombinacji kolumn (2^N)• ROLLUP traktuje kolejne kolumny jako hierarchię (N + 1(TOTAL))

• Obowiązująca składnia:• GROUP BY ROLLUP (<kolumny>)• GROUP BY CUBE (<kolumny>)

• Wycofywana składnia:• GROUP BY <kolumny> WITH ROLLUP• GROUP BY <kolumny> WITH CUBE

Pytania?