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

Byłem na grzybach i znalazłem … telefon

Marcinie, 

Ostatnio zachwalałem książkę „Pułapki myślenia” a dzisiaj ponownie do tego wrócę. Byłem na grzybach. Jak to grzybiarz, kiwałem się między brzózkami licząc na to, iż dojrzę jakiś czerwonawy kapelusz i nie będzie to oczywiście wszechobecny muchomor.

Kiwam się, się kiwam, krążę..., dzień dobry, dzień dobry, to innym spotykanym grzybiarzom… i widzę leży: telefon.

Telefon ma oczywiście określoną wartość rynkową  (tak, przyjmijmy, że sobie go przywłaszczę, ale tylko teoretycznie, na potrzeby ćwiczenia) i wiąże się z tym znaleziskiem jakaś radość (wartość psychologiczna).

i wyobraź sobie Marcinie...

jak radość moja istotnie wzrosła, pomimo nadal tej samej wartości rynkowej telefonu, gdy uzmysłowiłem sobie, że to mój. 

Po prostu uzmysłowiłem sobie, że nie tyle zyskałem, co uniknąłem straty. 

To zjawisko w skrócie można w jednym zdaniu określić:

„Jak to mawiają Anglosasi: strata boli bardziej, niż zysk cieszy” 

A na rysunku zaczerpniętym  z polecanej książki "Pułapki myślenia" Daniela Kahnemana wygląda to jak niżej. Jeżeli przeraża Ciebie Marcinie grubość książki, to na początek przeczytaj dwa artykuły z poradnika dla graczy: 

Zachowanie gracza

Błędy w ocenie ryzyka

I grzyby :-)

W.

Sortowanie – order by…

Marcinie, homo sapiens jak już widzi tabelę to automatycznie spodziewa się, iż jest ona jakoś posortowana np. alfabetycznie, od najniższej ceny do najwyższej – rzadko kto chce na początku widzieć najwyższą :-) 

A serwer bazy danych w swoich tabelach… okazuje się, iż nie przechowuje rekordów w sposób uporządkowany, dla serwera danych kolejność rekordów nie ma znaczenia i tyle.

Tu pozwolę sobie wkleić obrazek ze wskazówką zaczerpniętą z polecanej już przeze mnie książki dostępnej tutaj: http://helion.pl/ksiazki/praktyczny-kurs-sql-wydanie-iii-danuta-mendrala-marcin-szeliga,pksql3.htm 

Czyli wynik zapytania jest sortowany dla użytkownika, a sortuje się go poprzez dodanie na końcu zapytania klauzuli ORDER BY …. W miejsce kropek najlepiej podać nazwę pola/kolumny według, której należy sortować. Możesz podać więcej pół. Domyślnie następuje sortowanie rosnąco, dodanie po nazwie pola DESC spowoduj zmianę sortowania, czyli malejąco.

Zamiast nazwy pola/kolumny można podawać numer  pola z zapytania, ale tu zdarzają się niespodzianki, szczególnie kiedy pracujesz nad tym co w sekcji pomiędzy SELECT …. a FROM, po prostu pole wcale nie musi być pierwsze bo stanie się drugie, kiedy coś sobie dopiszesz, a zapomnisz poprawić numer po ORDER BY.

Czyli: 

SELECT

imie, nazwisko, adres 
from ADRES WHERE imie=’Jan’ 
ORDER BY nazwisko; 

Spowoduje wybranie  z bazy danych i posortowanie według nazwiska, można wskazać numer pola tj. 2. Sortowanie danych zapisanych fizycznie w bazie odbywa się poprzez zakładanie indeksów i jest to realizowane, bo przyspiesza dostęp do danych, ale o tym innym razem.

W.