Wielokrotne grupowanie – GROUPING SETS, ROLLUP, CUBE

Tworząc raporty czy zestawienia bezpośrednio z bazy transakcyjnej, wchodzimy na teren analityki biznesowej BI (Business Intelligence) w zwykłej bazie OLTP (OnLine Transaction Processsing). W środowiskach komercyjnych, granica pomiędzy OLTP a bazami OLAP (OnLine Analytical Processing) jest czasem całkowicie zatarta. Zdarza się tak w sytuacji gdy po prostu nie ma wdrożonych, dedykowanych rozwiązań BI. Całość raportowania odbywa się wtedy wprost z bazy transakcyjnej.

Analityka biznesowa (raportowanie), cechuje się wieloma operacjami przekształcania danych i przekuwania ich na konkretną wiedzę. Takie zapytania bywają kosztowne jeśli chodzi o wydajność. Dlatego żeby nie obciążać systemu transakcyjnego, wykonywana są w osobnych strukturach. Jest to powód dla którego tworzone są hurtownie danych i wszelkiej maści rozwiązania BI.

W artykule tym opisuje rozszerzenia T-SQL pozwalające na wielokrotne grupowanie (wstęp do kostek analitycznych). Opisywane tu metody, bazują na zagadnieniach dotyczących grupowania danych i łączenia pionowego zbiorów, prezentowanych w tym kursie we wcześniejszych artykułach.


Wielokrotne grupowanie

W T-SQL mamy dostępnych kilka rozszerzeń składni, pozwalających na sięgnięcie do wiedzy analitycznej. Obok funkcji szeregujących i analitycznych, jednymi z ciekawszych są trzy funkcje pozwalające na wykonywanie wielokrotnych grupowań w zwykłej (pojedynczej) kwerendzie.

Wyobraźmy sobie taki scenariusz (działając na danych z bazy Northwind) :

Chcemy utworzyć raport, przedstawiający informacje o sprzedaży w latach 1997-1998 na kilku poziomach szczegółowości. Zagregowane dane na temat liczby zleceń, chcemy wyświetlić względem lat, kwartałów i na koniec całościowo (jako najwyższy poziom agregacji).

Wielokrotne_grupowanie_01

Tego typu rozbicie wielopoziomowej agregacji, można wykorzystać np. w aplikacji raportującej, w której mamy możliwość „drążenia danych”, czy po prostu w zwykłym raporcie. Powyższy przykład to nic innego jako fragment kostki analitycznej z kilkoma wymiarami czasu.

Znając już operacje na zbiorach (UNION) i możliwości grupowania (GROUP BY), mógłbyś to zadanie rozwiązać za pomocą tych elementarnych konstrukcji, np. w taki sposób :

USE Northwind
GO

-- Query1
SELECT 1 as Poziom, NULL as Rok, NULL as Kw , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998

UNION

SELECT 2, Year(Orderdate) as Rok, NULL, count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY Year(Orderdate) 

UNION

SELECT 3,Year(Orderdate)  as Rok, DatePart(q,Orderdate), count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY Year(Orderdate)  , DatePart(q,Orderdate)
ORDER BY 2,1

Zaprezentowane powyżej rozwiązanie, łączenia wyników grupowań tej samej kwerendy w różnych kombinacjach, jest mało efektywne.

Z punktu widzenia przetwarzania są to trzy osobne kwerendy, które odpytują te same dane (dublujące się odczyty) i procesowane są niezależnie. Z pomocą przychodzą nam rozszerzenia T-SQL, pozwalające zrealizować tego typu scenariusze znacznie prościej i wydajniej.


ROLLUP

ROLLUP rozszerza funkcjonalność klauzuli GROUP BY, o możliwość tworzenia tzw. kostek analitycznych połówkowych. Funkcja ta, przyjmuje jako parametry, analogicznie jak w zwykłym grupowaniu, atrybuty (nazwy kolumny) tabel wejściowych lub ich przekształcenia.

Jeśli zastosujesz ROLLUP (a,b,c), wykonane zostaną grupowania dla kolejnych kombinacji atrybutów :

  • GROUP BY (a,b,c)
  • GROUP BY (a,b)
  • GROUP BY (a)
  • oraz po całości, czyli GROUP BY () – co w praktyce zapisujemy bez jawnego grupowania (wszystkie kolumny tworzą część danych surowych)

ROLLUP to równoważnik realizacji N+1 grupowań. W przypadku podania trzech (a,b,c) atrybutów, zostaną wykonane 3 + 1 = 4 operacje. Co warto podkreślić, wydajnościowo będzie to znacznie bardziej efektywne zapytanie, niż osobne grupowania i łączenia zbiorów za pomocą UNION, pokazane w poprzednim przykładzie (Query1).

Zobaczmy teraz rozwiązanie naszego scenariusza, czyli na trzech poziomach za pomocą ROLLUP :

-- Query2
SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY ROLLUP( Year(Orderdate)  , DatePart(q,Orderdate) )

Wielokrotne_grupowanie_02

Sam zapis kwerendy, jak również jej plan wykonania (Query2) przy tym podejściu jest znacznie prostszy i co ważniejsze – bardziej efektywny. Wystarczy zerknąć na porównanie wydajności zapytań :

Wielokrotne_grupowanie_03

a także statystyki odczytów :

Wielokrotne_grupowanie_04

Odnosząc się do poprzedniego przykładu –3 krotnie została zmniejszona liczba odczytów (tu też widać, że każda kwerenda w UNION, była realizowana niezależnie).

Sam wynik może jeszcze nie być do końca satysfakcjonujący (brakuje informacji o poziomach agregacji), ale tym zajmiemy się w dalszej części tego artykułu – przy okazji omówienia funkcji GROUPING_ID().


CUBE

Drugą funkcjonalnością wielokrotnego grupowanie to CUBE czyli pełna kostka analityczna. Stosujemy ją podobnie jak ROLLUP w poleceniu GROUP BY. Jej działanie to wykonanie grupowania we wszystkich wymiarach. Czyli jeśli podamy 3 atrybuty, to wykonanych zostanie 2^3 = 8 operacji.

Chciałbym przy okazji przypomnieć, jedną z podstawowych zasad pisania zapytań. Zawsze pobieramy tylko takie dane, jakie w danym momencie potrzebujemy. CUBE wykonuje dla nas 2^N-tej grupowań – to kosztuje. Odnieśmy się do naszego przykładu i zamieńmy ROLLUP na CUBE :

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY CUBE(Year(Orderdate)  , DatePart(q,Orderdate))

Wielokrotne_grupowanie_05

Dla dwóch atrybutów w CUBE(), wykonane zostały 4 grupowania. W porównaniu do ROLLUP, pojawił się dodatkowy poziom, tylko po kolumnie Q – kwartały. Niezależnie od roku, mamy informacje o całkowitej liczbie zleceń w kwartałach.

W praktyce, CUBE stosujemy w procesach ETL, rzadko w zwykłych widokach czy zapytaniach, właśnie ze względu na ilość grupowań (pełna kostka).


GROUPING SETS

Na koniec zostawiłem wisienkę na torcie, czyli najbardziej elastyczny i chyba najczęściej wykorzystywany w praktyce sposób na wielokrotne grupowanie.

Za pomocą GROUPING SETS możemy określić konkretne poziomy grupowań. ROLLUP i CUBE opisane wcześniej miały z góry narzuconą liczebność grup. W tym przypadku, możemy jawnie określić zbiory atrybutów po których wykonamy agregacje. Jest to więc także bardziej wydajna, bo oszczędna metoda, gdy nie potrzebujemy tylu wymiarów, ile dają dwie poprzednie metody. Z tego też powodu, jest chętnie stosowana na przykład w widokach, czy zwykłych kwerendach.

Odpowiednikiem ROLLUP i rozwiązaniem naszego scenariusza za pomocą GROUPING SETS będzie poniższa kwerenda :

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY GROUPING SETS
(
	(),
	(Year(Orderdate)) ,
	(Year(Orderdate)  , DatePart(q,Orderdate))

)
ORDER BY Rok, Q

Sama struktura jest intuicyjna i wymaga tylko określenia zbiorów grupujących. Będą to atrybuty tabel wejściowych lub ich przekształcenia, analogicznie jak w zwykłym grupowaniu, po których będą wykonywane kolejne agregacje.

Załóżmy, że nie interesuje nas grupowanie podsumowujące całą sprzedaż w zadanym okresie (po całości), ale chcemy wyświetlić tylko raport na dwóch poziomach szczegółowości . Zagregowane dane na temat ilości zamówień w poszczególnych latach i kwartałach.

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY GROUPING SETS
(
	(Year(Orderdate)) ,
	(Year(Orderdate)  , DatePart(q,Orderdate))

)
ORDER BY Rok, Q

Wielokrotne_grupowanie_06

W takim scenariuszu, najlepiej właśnie użyć GROUPING SETS – zgodnie z zasadą, pobierania tylko takich danych jakie są nam potrzebne.


Funkcje GROUPING() oraz GROUPING_ID()

Z przedstawionymi powyżej trzema metodami grupowania wielokrotnego, skojarzone są dwie specjalne funkcje skalarne. W grupowaniu wielokrotnym, nie mamy jawnie podanej informacji, na jakim poziomie dana operacja się odbywa. Za pomocą tych funkcji, możemy zidentyfikować, czy dana kolumna, tworzy sekcję grupującą czy nie. Jest to szczególnie przydatne, w ostatecznym sortowaniu wyniku, aby np. otrzymać taki raport jak w pierwszym przykładzie tego artykułu.

Funkcja GROUPING(), przyjmuje jako parametr, dowolny atrybut (zazwyczaj nazwę kolumny), używany w grupowaniu. Zwraca wartość 0 lub 1, w zależności od tego, czy dana kolumna, wchodzi w skład sekcji grupującej (0) czy nie(1). Trochę to na odwrót w przyjętej powszechnie logice, ale można się przyzwyczaić.

Zobaczmy jej działanie na przykładzie :

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty , 


	GROUPING( Year(Orderdate) ) as [Grupowanie po roku],
	GROUPING( DatePart(q,Orderdate) )  as [Grupowanie po kwartale],
	GROUPING( Year(Orderdate) ) +  GROUPING( DatePart(q,Orderdate) ) as Poziom 

FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY ROLLUP(Year(Orderdate)  , DatePart(q,Orderdate))
ORDER BY Rok, Poziom DESC

Wielokrotne_grupowanie_07

Na najwyższym poziome grupowania, czyli bez wnikania w wartości lat i kwartałów, zarówno atrybut YEAR(OrderDate) jak i DATEPART(q,OrderDate) nie tworzą sekcji grupującej. Funkcja GROUPING() dla tych atrybutów zwraca wartość 1.

Analogicznie w wierszu drugim i siódmym, mamy grupowanie tylko po latach, bez wnikania w wartości kwartałów. Jest to odpowiednik GROUP BY YEAR(OrderDate). Wartość GROUPING(YEAR(OrderDate)) będzie w tym przypadku 0, ponieważ ten atrybut tworzy sekcję grupującą.

Jak widać na tym przykładzie, za pomocą GROUPING(), możemy obliczyć wartość identyfikującą poziom grupowania.

Drugą funkcją, która już bez zbędnych ceregieli wyznaczy nam od razu identyfikator, jest GROUPING_ID().

Wynik takiej funkcji jest zawsze w postaci bitowej zamienionej na dziesiętną. Poniżej przykład obliczania jej wartości dla ROLLUP ( a, b, c ).

Wielokrotne_grupowanie_08

Odnieśmy się na koniec do naszego przykładu. Będziemy mieli dwa atrybuty grupujące, zatem wartości GROUPING_ID, będzie dla ROLLUP (a , b) zwracała wartość 0, 1 oraz 3.

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty , 


	GROUPING( Year(Orderdate) ) as [Grupowanie po roku],
	GROUPING( DatePart(q,Orderdate) )  as [Grupowanie po kwartale],
	GROUPING_ID( Year(Orderdate) ,  DatePart(q,Orderdate) ) as PoziomBinarny

FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY ROLLUP(Year(Orderdate)  , DatePart(q,Orderdate))
ORDER BY Rok, PoziomBinarny DESC

Wielokrotne_grupowanie_09


Podsumowanie

Wielokrotne grupowanie za pomocą GROUPING SETS, ROLLUP I CUBE sprawdza się szczególnie w procesach ETL (Extract, Transform and Load). Rozszerza także możliwości zwykłych kwerend, skracając ich zapis i poprawiając wydajność w porównaniu do tradycyjnych metod wykorzystujących pojedyncze grupowanie i łączenie zbiorów za pomocą UNION.

3 Responses

  • Według tego wpisu, za pomocą polecenia CUBE otrzymuje się pełną kostkę analityczną. Te kostki zwykle są przedstawiane jako 3-wymiarowe, chociaż mogą zawierać więcej wymiarów. No OK. Ale w takim razie jak można sobie wyobrazić wynik zapytania z przykładu zaprezentowanego w paragrafie o nazwie „CUBE”? Ile wymiarów będzie miała tak utworzona kostka? Jakie wartości będą umieszczone na jej osiach?

    • Chyba znalazłem odpowiedź na moje pytanie. We wpisie
      https://sqlandme.com/2011/07/12/sql-server-tsql-group-by-grouping-sets/
      w jego dolnej części znajduje się akapit zaczynający się słowami „Let’s take another example”, wraz z tabelą przestawną i zapytaniem SQL wykorzystującym polecenie GROUP BY GROUPING SETS. Jeżeli dobrze zrozumiałem, są tam dwa wymiary „Product” oraz „Year”, a utworzona tabela przestawna jest dwuwymiarową kostką analityczną. A gdyby dodać trzeci wymiar do tamtego zapytania SQL, na przykład „Customer”, wtedy uzyska się trójwymiarową kostkę, taką jaką zwykle pokazuje się na ilustracjach związanych z hurtowniami danych oraz z BI. Dla takiej kostki 3D zapytanie z wyrażeniem GROUP BY CUBE albo GROUPING SETS wygeneruje odpowiednio dużo częściowych grupowań.

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.