CUBE

Nieco już Marcinie mnie męczysz. ROLLUP klauzula jest stosowana w grupowaniu gdy potrzebne są wartości dla grup o stopień wyżej dla wskazanego pola, czyli wartości z uwzględnieniem danego pola przy grupowaniu oraz bez jego uwzględnienia.

Metodą prób i błędów odkryliśmy, iż podając dwa pola w klauzuli ROLLUP i zmieniając kolejność pól istotnie wpływamy na wyświetloną kolekcję wierszy (bardzo mnie się podoba określenie kolekcja :-) ).

Jest jeszcze klauzula CUBE, CUBE przy podaniu jednego pola to ma ten sam efekt co ROLLUP, a istotna różnica jest gdy podamy, obejmiemy klauzulą dwa i więcej pól.

Użyję tego samego przykładu co w postach dotyczących klauzuli ROLLUP. I podobnie posłużę się obrazkami.

Różnica w kolejności pól dla klauzuli CUBE nie ma wpływu na wyświetlaną kolekcję, CUBE powoduje, iż jest generowanie grupowanie z danym polem i bez uwzględnienia danego pola, tak samo dla drugiego pola, z uwzględnieniem drugiego i bez oraz grupowanie dodatkowo z pominięciem obu pól, niejako wszystkie kombinacje.

Dla naszego przykładu będzie użycie klauzuli CUBE przy grupowaniu po roku i regionie wyświetliło:

- każdy wynik i liczbę transakcji w danym roku i danym regionie,

- każdy wynik i liczbę transakcji w danym roku,

- każdy wynik i liczbę transakcji w danym regionie,

- wynik i liczbę transakcji ogółem.

W przypadku CUBE kolejność pól w klauzuli nie ma znaczenia :-)

Kod jest dostępny tutaj, przy czym jeżeli ćwiczyłeś już na transakcjach klauzulę ROLLUP, to nie ma potrzeby tworzenia tabeli na nowo i jej ładowania tylko skopiuj sobie jedynie kwerendy.

ROLL UP – kolejność pól w nawiasie?

W tym poście ćwiczyłem rozwiązanie zadania metodą prób i błędów. Zadanie i dane wsadowe do zadania są w poście powyżej.

Tutaj po raz drugi wklejam kwerendy A oraz B, gdzie występuje grupowanie  oraz klauzula ROLLUP, a klauzula ROLLUP obejmuje dwa pola. Jedyna różnica pomiędzy A i B to kolejność pół w klauzuli ROLLUP, żadnych innych różnic a patrząc na wyniki widać wyraźnie, iż uzyskany zestaw wierszy jest wyraźnie inny. Różnice zilustrowałem Marcinie na obrazku i opisałem na obrazku.

Zobacz na obrazki, tak będzie łatwiej, ponoć dobry obrazek jeden lepszy niż tysiąc słów.

Pozdrawiam,

W.

-- 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 ;

-- B

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  (  region  , to_char(data_umowy,'YYYY') )

order by 2 , 4 ;

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