Pivot (Tabele przestawne) w Oracle

Pivot (tabele przestawne)

Pivot (tabele przestawne) jest dostępny od wersji 11g Oracle. Umożliwia nam przestawianie kolumn w miejsce wierszy i odwrotnie. Jest to wspaniałe narzędzie analityczne, pozwala wygodnie przeglądać np. tendencje. Pivot jest operacją agregującą. Przykład działania pivota:

Przed:

Po:

Prawda że czytelniej?

Przejdziemy teraz całą ścieżkę krok po kroku. W pierwszej kolejności tworzymy tabelę:

create table wyniki_sprzedazy(

okres varchar2(50),

produkt varchar2(50),

suma_przychodu number

);

Następnie ładujemy do niej dane:

insert into wyniki_sprzedazy values('06-2013','Pralki',65040);

insert into wyniki_sprzedazy values('07-2013','Pralki',31650);

insert into wyniki_sprzedazy values('08-2013','Pralki',24678);

insert into wyniki_sprzedazy values('06-2013','Telewizory',40600);

insert into wyniki_sprzedazy values('07-2013','Telewizory',12300);

insert into wyniki_sprzedazy values('08-2013','Telewizory',9004);

insert into wyniki_sprzedazy values('06-2013','Lodówki',45080);

insert into wyniki_sprzedazy values('07-2013','Lodówki',21600);

insert into wyniki_sprzedazy values('08-2013','Lodówki',4670);

commit;

Zajrzyjmy teraz do tabeli:

select * from wyniki_sprzedazy;

Dane przedstawione są w tej chwili w sposób płaski i mało czytelny. Zawartość tabeli reprezentuje sprzedaż trzech produktów na przestrzeni trzech miesięcy.

Przestawimy teraz troszeczkę te dane. Chcemy wyświetlić jak zmieniała się sprzedaż produktów na przestrzeni czasu. Robimy pewnego rodzaju szachownicę gdzie jeden bok jest wyznaczany przez produkty, a drugi przez okresy. Na przecięciu kolumn i wierszy ma znaleźć się suma sprzedaży danego produktu w danym okresie.

select * from wyniki_sprzedazy pivot

(

sum(suma_przychodu) for okres in ('06-2013','07-2013','08-2013')

)

;

sum(suma_przychodu) to element agregacji, musi się tutaj pojawić.

For okres in wyznacza kolumny jakie mają powstać na podstawie danych z kolumny okres.

Pełna składnia polecenia PIVOT:

SELECT …..

FROM …..

PIVOT

(

PIVOT_CLAUSE

PIVOT_FOR_CLAUSE

PIVOT_IN_CLAUSE

) WHERE......

PIVOT_CLAUSE to funkcja wyliczeniowa (agregująca) u nas jest to sum(suma_przychodu).

PIVOT_FOR_CLAUSE określa dane na podstawie których mają powstać kolumny. U nas jest to klauzula „for okres ….” która definiuje na podstawie jakich danych powstaną kolumny.

PIVOT_IN_CLAUSE to lista wartości z danych określonych w PIVOT_FOR_CLAUSE na podstawie których powstają kolumny. Tutaj „in('06-2013','07-2013','08-2013')”. Krótko mówiąc PIVOT_FOR_CLAUSE określa z danych jakiej kolumny w źródle mają powstać kolumny w pivocie, a PIVOT_IN_CLAUSE określa zakres tychże danych -tj. ile kolumn ma być.

Powstającym kolumnom podczas pivotowania możemy nadać aliasy:

select * from wyniki_sprzedazy pivot

(

sum(suma_przychodu) for okres in ('06-2013' czerwiec,'07-2013' lipiec,'08-2013' sierpien)

)

;

Robimy to wewnątrz PIVOT_IN_CLAUSE.

Dane wynikowe możemy też sortować:

select * from wyniki_sprzedazy pivot

(

sum(suma_przychodu)

for okres

in ('06-2013' czerwiec,'07-2013' lipiec,'08-2013' sierpien)

) ORDER BY 1; 

Klauzulę dotyczącą sortowania dajemy na samym końcu całego zapytania.

Inny przykład – tym razem z użyciem schematu HR. Mamy w tabelce employees kolumny JOB_ID DEPARTMENT_ID. Job_id to indentyfikator zawodu wykonywanego przez pracownika, department_id to identyfikator departamentu w którym dany pracownik pracuje. Chcielibyśmy teraz zrobić jakieś zestawienie obrazujące ilość pracowników na różnych stanowiskach w różnych departamentach. Zaczynamy więc od wykonania grupowania:

select job_id,department_id,count(*) from employees

group by job_id,department_id;

Musieliśmy ograniczyć całą zawartość tabeli do tych dwóch kolumn (job_id i department_id) oraz jednej kolumny z agregacją (count(*)). Pozostałe kolumny nie są nam potrzebne, mając więcej niż 3 wartość ( X , Y i wartość na przecięciu X – Y) wynik byłby całkowicie niezrozumiały. Dane jako takie już mamy przygotowane, są jednak przedstawione w formie płaskiej. Teraz chcielibyśmy te dane troszeczkę przesunąć i stworzyć z nich „szachownicę” czyli tabelę przestawną.

select * from (

select job_id,department_id,count(*) liczba from employees

group by job_id,department_id

) pivot (

sum(liczba) for department_id in (10,20,30,40,50,60,70,80,90,100,110)

);

Zauważ że po FROM może znaleźć się nie tylko nazwa tabeli, ale również całe zapytanie: