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 | [email protected]
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?
Top Related