ROLL UP to oznacza zwiń...

Grupowanie - pewno wiesz, o co chodzi JGrupowanie wykorzystuje się przy wyliczaniu wartości agregujących, na przykład: średniej, sumy dla jakiejś populacji. Oczywiście można wyodrębnić populację poprzez jej dokładne opisanie warunkami po klauzuli WHERE i wyliczyć wartość dla tej populacji, ale w ten sposób uzyskuje się jeden wynik, pojedynczą wartość dla ściśle opisanego podzbioru. 

Wskazanie w zapytaniu reguł dzielenia dużego zbioru na podzbiory pozwala za jednym pociągnięciem uzyskać wartość na przykład średnią dla wielu podzbiorów dużego zbioru, po prostu wyraźnie szybciej.  Przykład grupowania i ilustrację, o co chodzi też znajdziesz tutaj.

A co to jest to grupowanie z klauzulą ROLLUP? Powiem otwarcie Marcinie, nie lubię o tej klauzuli oraz CUBE rozmawiać, nieco pomotane to jest, w każdym razie mnie stwarza kłopoty w rozumieniu oraz opowiadaniu przyznam.

Jednak spróbuję: ROLLUP pozwala na uzyskanie wartości-wyników tak jak dla zwykłego grupowania plus dodatkowo jakby go nie było. Tekst/klauzulę ROLLUP wpisuje się po GROUP BY i podaje się w nawiasie, te pole/a dla którego ma być grupowanie normalnie plus poziom wyżej jakby go nie było (roluj w górę).

Czyli:

GROUP BY ROLLUP (PLEC) ;

Należy czytać: podziel  wiersze tabeli na podzbiory według danych występujących w polu PLEC i policz wartości funkcji agregującej (np. średnia) dla podzbiorów oraz wylicz wartość funkcji agregującej dla zbioru wyżej, bez grupowania na PLEC.

W sekcji pomiędzy SELECT a FROM można w przypadku stosowania klauzuli ROLLUP wywołać funkcje GROUPING (…)

Czyli:

SELECT GROUPING (PLEC), PLEC, AVG(OCENA) FROM OCENY GROUP BY ROLLUP (PLEC);

GROUPING(PLEC) będzie przyjmować wartości: 0 jeżeli PLEC nie będzie NULL, czyli średnia jest policzona dla określonej płci (podział zbioru ze względu na płeć był) oraz 1 gdy pleć będzie NULL, czyli wartość średnia jest wyliczona dla zbioru poziom wyżej, bez uwzględniania płci.

A teraz bardziej złożony przykład plus dodatkowo komplikacja. Mamy  dane o 146 transakcjach-kontraktach zakończonych w latach 2008-2013, realizowanych przez cztery zespoły nazwane po angielsku od stron świata. Każda transakcja ma określoną wartość.

Zadanie: policzyć wartość kontraktów i liczbę kontraktów dla każdego zespołu w danym roku i dodatkowo dla danego roku.  To wszystko jedną kwerendą.

No to rozwiązujemy, będziemy dochodzić do wyniku końcowego metodą prób i błędów (antykruchość być może dzięki temu wzrośnie J, ale o tym następnym razem). Wersja A -w kwerendzie klauzula ROLLUP obejmuje w nawiasie oba pola tj. de facto rok oraz region:

-- A

select

case

   when grouping(to_char(data_umowy,'YYYY'))=0 then 'grupuję po roku'

   else 'nie grupuję po roku' end grouping_na_rok_transakcji

, to_char(data_umowy,'YYYY') rok_transakcji

, case

   when grouping(region)=0 then 'grupuję po regonie'

   else 'nie grupuję po regionie' end grouping_na_regionie    

, region

, sum(kwota_transakcji) suma -- funkcja agregująca

, count(*) liczba -- funkcja agregująca

from transakcje

group by rollup  (to_char(data_umowy,'YYYY')  , region  )

order by 2 , 4 ;

Wykorzystuję GROUPING by widzieć czy dany wiersz to wartość z grupowania czy nie, korzystając z CASE zamieniam wartości 0 i 1 na czytelne bardziej dla człowieka teksty. Wynik na obrazku, niby dobrze, ale pojawiło się zbędne podsumowanie, wartość i liczba ogółem transakcji.

Wersja B, zamieniam miejscami pola w klauzuli ROLLUP.  Na obrazku dalej za kwerendą widać, iż zbędnych wierszy jeszcze więcej.

-- B

select

case

   when grouping(to_char(data_umowy,'YYYY'))=0 then 'grupuję po roku'

   else 'nie grupuję po roku' end grouping_rok_transakcji

, to_char(data_umowy,'YYYY') rok_transakcji

, case

   when grouping(region)=0 then 'grupuję po regionie'

   else 'nie grupuję po regionie' end grouping_region   

, region

, sum(kwota_transakcji) suma -- funkcja agregująca

, count(*) liczba -- funkcja agregująca

from transakcje

group by rollup  (  region  , to_char(data_umowy,'YYYY') )

order by 2 , 4 ;

No to wersja C, wyrzucam poza klauzulę ROLLUP rok (poza nawias dotyczący ROLLUP), kombinuję sobie: rok ma być zawsze, a grupowanie odnośnie regionu ma być robione i dodatkowo bez uwzględniania roku, czy to to co chcieliśmy. Obrazek pokazuje, że tak, mamy wartości dla regionu w danym roku i podsumowanie każdego roku - na zielono na obrazku.

-- C

select

case

  when grouping(to_char(data_umowy,'YYYY'))=0 then 'grupuję po roku'

  else'nie grupuję po roku' end grouping_rok_transakcji

, to_char(data_umowy,'YYYY') rok_transakcji

, case

   when grouping(region)=0 then 'grupuję po regionie'

   else 'nie grupuję po regionie' end grouping_region   

, region

, sum(kwota_transakcji) suma -- funkcja agregująca

, count(*) liczba -- funkcja agregująca

from transakcje

group by rollup (  region ), to_char(data_umowy,'YYYY')

order by 2 , 4 ;

A tu plik excel oraz kod do przećwiczenia samodzielnego. Patrząc na wersja A i B wyraźnie się rzuca w oczy, iż kolejność pól w klauzuli ROLLUP ma znaczenie, jeżeli więcej niż jedno jest. Jak czytać kolejność następnym razem, próby są też po to by znaleźć cel, ale czasami odkryć coś przy okazji :-)