Agregacje z podsumowaniem ROLLUP

Suma sum w jednym zapytaniu

SQL Server to nie tylko przechowywanie danych. SQL Server to również doskonałe narzędzie do analizy tych danych. A skoro analiza, to agragacje. Sumy sprzedaży, średnie wyniki testów sprawnościowych, standardowe odchylenia pomiarów - to tylko niektóre z pytań, na które mogą nam odpowioedzieć odpowiednio skonstruowane agregacje. Każda z takich agregacji może być wykonywana dla całego zbioru danych, do podzbioru tych danych lub dla grup wyznaczonych przez sekcję GROUP BY zapytania. Każda z tych operacji wykonywana niezależnie wydaje się prosta. Dzisiaj chciałem pokazać, że niektóre agregacje mogą być równie dobrze zgrupowane w jednym zapytaniu - takie podejście, co nie powinno dziwić, pozwala uzyskać wyższą wydajność. Dzisiaj będzie o instrukcji ROLLUP, ale warto również wiedzieć o istnieniu instrukcji pochodzących z tej samej rodziny - CUBE oraz GROUPING SETS.

Tabela, czyli nasz poligon

Aby pokazać zapytanie i efekt jego działania, musi być tabela. Popatrzmy zatem na przykładową definicję takiej tabeli i instrukcje uzupełniające tę tabelę:

Po wykonaniu instrukcji można sprawdzić zawartość tabeli:

I przekonać się, że znajdują się tam natępujące dane:

Dotychczas nie było nic ciekawego, ale poczekajmy.

Proste i tradycyjne agregacje

Przypuśćmy, że chcemy wyliczyć sumę zakupów dla poszczególnych osób. Zrobilibyśmy to w sposób następujący:

Otrzymamy taki oto rezultat:

A gdybyśmy teraz zechcieli wyliczyć sumę dla każdej z osób z podziałem na poszczególne kategorie produktów? Zapytanie zmieni się w minimalny sposób:

Wynik tym razem będzie następujący:

Każdy szanujący się raport czy faktura, oprócz sum za poszczególne składniki, zawiera sumę wszystkich jego podelementów. To zapytanie będzie już bajecznie proste:

Otrzymamy prosty wynik:

Pokazałem trzy różne agregacje, które często pojawiają się wspólnie. Faktura ma wykaz poszczególnych pozycji i sumę końcową, rachunek ma sumę poszczególnych pozycji z podziałem na zamówienia i sumę końcową. Powszechność tego typu kalkulacji zmusiła osoby zarządzające bazami danych do tworzenia coraz bardziej złożónych zapytań.

Łaczenie agregacji w jeden zbiór wynikowy

Zacznijmy od przykładu prostszego. Chcemy zwrócić sumę zakupów poszczególnych osób oraz pełną sumę - wszystko w jednym zapytaniu. Wystarczy użyć zwykłej operacji łączenia zbiorów - UNION lub UNION ALL.

Warto zwrócić uwagę na dodanie do drugiego zapytania wartości NULL - aby dało się połączyć zbiory, liczba kolumn i ich typy muszą się zgadzać. NULL jest na tyle neutralny, że nadaje się do wszystkiego. Wynik powyższej instrukcji będzie następujący:

Wynik jest piękny, zapytanie już trochę mniej. To jest piękny moment, aby przedstawić tytułowego bohatera: instrukcję ROLLUP. Popatrzmy na poniższe zapytanie, które jest równoważne pokazanej przed chwilą sumie zbiorów:

Wynik będzie identyczny:

Przechodzimy w trzeci wymiar

Uzyskaliśmy przed chwilą sumę sum połączoną z pojedynczymi sumami w sposób prosty, czytelny i optymalny. Popatrzmy na nieco bardziej rozbudowany przykład, w którym uzyskamy także sumy poszczególnych towarów:

Obejrzyjmy sobie teraz zapytanie równoważne, zwracające takie same rekordy:

Pomijając różną kolejność rekordów (SQL Server nie gwarantuje kolejności, jeżeli nie użyto jawnie instrukcji ORDER BY), sam zbiór wynikowy będzie wyglądał następująco:

Prostota tego zapytania wręcz poraża. Zapytanie jest nie tyle krótsze, co wydajniejsze - o tym jednak za chwilę.

Dwie wersje równoważnego zapytania pokazałem celowo - w zapytaniach wykorzystujących sumę zbiorów UNION ALL widać pewien powtarzający się schemat. Jeżeli w jakimś zapytaniu podobny schemat namierzymy, warto zastanowić się nad wykorzystaniem instrukcji ROLLUP.

NULL zgrupowany i NULL zwykły

Grupowanie rekordów z instrukcją ROLLUP sprawia, że niektóre pola uzupełniane są wartościami NULL. Są to pola, które nie uczestniczą w grupowaniu. Jak takie pola rozpoznać i czy musimy je rozpoznawać? Popatrzmy na poniższe zapytanie i zwracany przez nie rezultat:

Teraz popatrzmy na zapytanie z instrukcją ROLLUP:

Rekordy są inaczej posortowane, bo optymalizator wybrał dla nich inny plan wykonania. Nie to jest jednak najważniejsze. W pierwszej kolumnie pojawiły się dwie wartości NULL. Która pochodzi z instrukcji ROLLUP, a która jest zwykłą pustą wartością? Co począć? Jak z tym żyć?

To właśnie do takich zadań przeznaczona jest funkcja GROUPING. Zwraca ona wartość 1, jeżeli kolumna jest zagregowana i 0, jeżeli nie jest. Można ją wykorzystać na przykład w taki sposób:

Wynik będzie naztępujący:

Podobne formatowanie można zastosować do ostatniego wiersza. Pierwsza kolumna będzie miała wartość 1 dla instrukcji GROUPING(OsobyID), druga kolumna będzie miała wartość 1 dla instrukcji GROUPING(OsobyID) oraz GROUPING(Towar).

Konsekwencje wydajnościowe grupowania ROLLUP

Mogliśmy się przekonać, że zapytania z użyciem instrukcji ROLLUP są krótsze i bardziej czytelne. To jednak nie jest wszystko. Są także znacznie szybsze, bo wszystkie grupowania i agregacje wykonywane są w jednym przebiegu. Instrukcja UNION ALL wykonuje tyle przebiegów, ile jest podzbiorów do sumowania. Przyjrzyjmy się przykładowej instrukcji sumowania zbiorów i jej planowi wykonania:

Przyjrzyjmy się teraz instrukcji grupowania z ROLLUP w tradycyjnym wydaniu:

Już na pierwszy rzut oka widać, że ROLLUP jest sprawniejsze. Jest o tyle sprawniejsze, że warto o niej pomysleć nawet wtedy, gdy nie są nam potrzebne wszystkie ze zwracanych przez tę instrukcję agregacji. Jeden przebieg będzie zawsze lepszy niż dwa przebiegi.

Instrukcja grupowania z ROLLUP to kolejne narzędzie, które, odpowiednio zastosowane, pozwoli nam się cieszyć ciekawszymi i sprawniej uzyskiwanymi rezultatami. Jest to również najprostsza instrukcja ze swojej rodziny operatorów rozszerzających działanie instrukcji GROUP BY. Dlatego właśnie poruszyłem ją w pierwszej kolejności. Zainteresowanych poszerzeniem wiedzy w tym zakresie odsyłam również do instrukcji CUBE oraz GROUPING SETS.

Kategoria:AgregacjeSQL Server