Spis treści:

Kategoria:AgregacjeSQL Server


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ę:

CREATE TABLE Zakupy
(
  OsobyID int NOT NULL,
  Towar char(8) NOT NULL,
  LiczbaSztuk int NOT NULL,
  CenaJednostkowa decimal(9,2) NOT NULL,
  Suma AS LiczbaSztuk*CenaJednostkowa
)

INSERT INTO Zakupy VALUES
(1, 'BEER', 6, 2.20),
(1, 'BOOK', 1, 69.99),
(1, 'DESK', 1, 760.00),
(1, 'MILK', 1, 2.80),
(1, 'BEER', 4, 2.30),
(1, 'BEER', 2, 2.80),
(2, 'BEER', 5, 2.20),
(2, 'MILK', 1, 3.00),
(2, 'BEER', 3, 2.50)

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

SELECTFROM Zakupy

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

OsobyIDTowarLiczbaSztukCenaJednostkowaSuma
1BEER 62.2013.20
1BOOK 169.9969.99
1DESK 1760.00760.00
1MILK 12.802.80
1BEER 42.309.20
1BEER 22.805.60
2BEER 52.2011.00
2MILK 13.003.00
2BEER 32.507.50

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:

SELECT OsobyID, SUM(Suma) Suma FROM Zakupy
GROUP BY OsobyID

Otrzymamy taki oto rezultat:

OsobyIDSuma
1860.79
221.50

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:

SELECT OsobyID, Towar, SUM(Suma) Suma FROM Zakupy
GROUP BY OsobyID, Towar

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

OsobyIDSumaOsobyID
1BEER 28.00
2BEER 18.50
1BOOK 69.99
1DESK 760.00
1MILK 2.80
2MILK 3.00

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:

SELECT SUM(Suma) Suma FROM Zakupy

Otrzymamy prosty wynik:

Suma
882.29

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.

SELECT OsobyID, SUM(Suma) Suma FROM Zakupy
GROUP BY OsobyID
UNION ALL
SELECT NULLSUM(Suma) Suma FROM Zakupy

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:

OsobyIDSuma
1860.79
221.50
NULL882.29

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:

SELECT OsobyID, SUM(Suma) Suma FROM Zakupy
GROUP BY OsobyID WITH ROLLUP

Wynik będzie identyczny:

OsobyIDSuma
1860.79
221.50
NULL882.29

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:

--Suma dla każdego towaru zakupionego przez osobę
SELECT OsobyID, Towar, SUM(Suma) Suma FROM Zakupy
GROUP BY OsobyID, Towar
UNION ALL
--Suma dla każdej z osób
SELECT OsobyID, NULLSUM(Suma) FROM Zakupy
GROUP BY OsobyID
UNION ALL
--Pełna suma
SELECT NULLNULLSum(suma) FROM Zakupy

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

SELECT OsobyID, Towar, SUM(Suma) Suma FROM Zakupy
GROUP BY OsobyID, Towar WITH ROLLUP

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:

OsobyIDTowarSuma
1BEER 28.00
1BOOK 69.99
1DESK 760.00
1MILK 2.80
1NULL860.79
2BEER 18.50
2MILK 3.00
2NULL21.50
NULLNULL882.29

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:

SELECT ID, SUM(Value) [Sum]
FROM (VALUES (1, NULL), (2, NULL), (3,1), (NULL, 4)) T(ID, Value)
GROUP BY ID
IDSum
1NULL
2NULL
31
NULL4

Teraz popatrzmy na zapytanie z instrukcją ROLLUP:

SELECT ID, SUM(Value) [Sum]
FROM (VALUES (1, NULL), (2, NULL), (3,1), (NULL, 4)) T(ID, Value)
GROUP BY ID
WITH ROLLUP
NULL4
1NULL
2NULL
31
NULL5

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:

SELECT OsobyID,
CASE WHEN GROUPING(Towar)=1 AND GROUPING(OsobyID)=0 THEN '***TOTAL' ELSE Towar END Towar,
SUM(Suma) Suma FROM Zakupy
GROUP BY OsobyID, Towar WITH ROLLUP

Wynik będzie naztępujący:

OsobyIDTowarSuma
1BEER 28.00
1BOOK 69.99
1DESK 760.00
1MILK 2.80
1***TOTAL860.79
2BEER 18.50
2MILK 3.00
2***TOTAL21.50
NULLNULL882.29

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:

SELECT OsobyID, SUM(Suma) Suma FROM Zakupy
GROUP BY OsobyID
UNION ALL
SELECT NULLSUM(Suma) Suma FROM Zakupy
Plan wykonania instrukcji GROUP BY ROLLUP zasymulowana instrukcją UNION ALL
Rys. 1 - Plan wykonania instrukcji grupowania z ROLLUP, zasymulowanej instrukcją UNION ALL.

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

SELECT OsobyID, SUM(Suma) Suma FROM Zakupy
GROUP BY OsobyID WITH ROLLUP
Plan wykonania instrukcji GROUP BY ROLLUP
Rys. 2 - Plan wykonania instrukcji grupowania z ROLLUP.

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

, 2013-12-20

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
bardo ciekawe , można dzięki takim wpisom dostrzec wędkę..
Bardzo dziękuję za jasne tłumaczenie z dobrze dobranym przykładem!
Dzieki za te informacje. były kluczowe
Dobrze wyjaśnione, dzięki !