Operacja PIVOT w języku SQL
Przeanalizujmy tabelę:
ID
|
ROK
|
KATEGORIA
|
ILOSC
|
1
|
2010
|
Pracownicy administracyjni
|
250
|
2
|
2010
|
Pracownicy techniczni
|
7
|
3
|
2010
|
Kierownicy
|
60
|
4
|
2010
|
Dyrektorzy
|
3
|
5
|
2011
|
Pracownicy administracyjni
|
238
|
6
|
2011
|
Pracownicy techniczni
|
6
|
7
|
2011
|
Kierownicy
|
65
|
8
|
2011
|
Dyrektorzy
|
2
|
9
|
2012
|
Pracownicy administracyjni
|
247
|
10
|
2012
|
Pracownicy techniczni
|
6
|
11
|
2012
|
Kierownicy
|
63
|
12
|
2012
|
Dyrektorzy
|
2
|
13
|
2013
|
Pracownicy administracyjni
|
247
|
14
|
2013
|
Pracownicy techniczni
|
6
|
15
|
2013
|
Kierownicy
|
64
|
16
|
2013
|
Dyrektorzy
|
1
|
Tabela 1
Czy jest możliwe wyświetlenie jej w inny sposób np. taki jak poniżej:
KATEGORIA
|
2010
|
2011
|
2012
|
2013
|
Pracownicy administracyjni
|
250
|
238
|
247
|
247
|
Pracownicy techniczni
|
7
|
6
|
6
|
6
|
Kierownicy
|
60
|
65
|
63
|
64
|
Dyrektorzy
|
3
|
2
|
2
|
1
|
Tabela 2
W jaki sposób możliwe jest obracanie tabel można do tego użyć funkcji DECODE
SELECT * FROM
(
SELECT
kategoria,
SUM(DECODE(rok,2010, ilosc)) as "Rok 2010",
SUM(DECODE(rok,2011, ilosc)) as "Rok 2011",
SUM(DECODE(rok,2012, ilosc)) as "Rok 2012",
SUM(DECODE(rok,2013, ilosc)) as "Rok 2013"
FROM
struktura_organizacyjna
GROUP BY kategoria
)
ORDER BY kategoria;
W powyższe zapytanie w naszym wypadku zwróci to samo co:
SELECT * FROM
(
SELECT
kategoria,
DECODE(rok,2010, ilosc) as "Rok 2010",
DECODE(rok,2011, ilosc) as "Rok 2011",
DECODE(rok,2012, ilosc) as "Rok 2012",
DECODE(rok,2013, ilosc) as "Rok 2013"
FROM
struktura_organizacyjna
GROUP BY kategoria
)
ORDER BY kategoria;
Ten sam efekt uzyskamy przy użyciu funkcji CASE.
SELECT * FROM
(
SELECT
kategoria,
SUM(CASE WHEN rok = 2010 THEN ilosc END) as "Rok 2010",
SUM(CASE WHEN rok = 2011 THEN ilosc END) as "Rok 2011",
SUM(CASE WHEN rok = 2012 THEN ilosc END) as "Rok 2012",
SUM(CASE WHEN rok = 2013 THEN ilosc END) as "Rok 2013"
FROM
struktura_organizacyjna
GROUP BY kategoria
)
ORDER BY kategoria;
Wynikiem powyższych zapytań będzie Tabela 3.
KATEGORIA
|
Rok 2010
|
Rok 2011
|
Rok 2012
|
Rok 2013
|
Pracownicy administracyjni
|
250
|
238
|
247
|
247
|
Pracownicy techniczni
|
7
|
6
|
6
|
6
|
Kierownicy
|
60
|
65
|
63
|
64
|
Dyrektorzy
|
3
|
2
|
2
|
1
|
Tabela 3
W czym może nam pomóc operacja PIVOT:
- Operacja umożliwia restrukturyzację danych do innej
postaci - do formatu, w jakim łatwiej
będzie dane przetwarzać lub analizować.
- Przestawianie kolumn na miejsce wierszy lub odwrotnie.
- Jednocześnie wykonywane są operacje agregujące (np.
sumowanie, obliczanie średniej).
- Operacja przydatna, gdy chcemy obserwować np. trendy
sprzedaży w czasie.
SELECT ...
FROM ...
PIVOT
(pivot_clause
pivot_for_clause
pivot_in_clause)
WHERE ...
- pivot_clause – definiowanie kolumn do agregacji (Pivot jest operacją agregującą)
- pivot_for_clause – definiowanie kolumn do grupowania i
obrotu
- pivote_in_clause – definiowanie filtru dla kolumny
(kolumn) z klauzuli pivote_for_clause (zakres wartości ograniczającej wyniki)
Jak wyglądało by zapytanie dające wyniki jak w Tabeli 2:
SELECT * FROM
(
SELECT kategoria, rok, ilosc FROM struktura_organizacyjna
)
PIVOT
(
SUM(ilosc)
FOR rok
IN (2010,2011,2012,2013)
);
Jeżeli chcielibyśmy zmienić nazwy kolumn tak jak w Tabeli 3.
SELECT * FROM
(
SELECT kategoria, rok, ilosc FROM struktura_organizacyjna
)
PIVOT
(
SUM(ilosc)
FOR rok
IN (2010 as "Rok
2010",2011 as "Rok 2011",2012 as "Rok
2012",2013 as "Rok 2013")
);
Widać, że zapis za pomocą PIVOT jest krótsza niż za pomocą DECODE lub CASE.
Rozważmy inny przykład:
ID
|
ROK
|
NAZWISKO
|
KATEGORIA
|
1
|
2010
|
Kowalski Marek
|
Pracownicy administracyjni
|
2
|
2010
|
Kowalska Zosia
|
Pracownicy administracyjni
|
3
|
2010
|
Kowalska Justyna
|
Pracownicy administracyjni
|
4
|
2010
|
Kowalski Mirek
|
Pracownicy administracyjni
|
5
|
2010
|
Kowalski Marcin
|
Pracownicy administracyjni
|
6
|
2010
|
Kowalski Zygmunt
|
Pracownicy administracyjni
|
7
|
2010
|
Kowalski Stanisław
|
Pracownicy administracyjni
|
8
|
2010
|
Kowalski Adam
|
Pracownicy administracyjni
|
9
|
2010
|
Kowalska Marta
|
Pracownicy administracyjni
|
10
|
2010
|
Kowalska Krystyna
|
Pracownicy administracyjni
|
11
|
2010
|
Kowalska Lidia
|
Pracownicy administracyjni
|
12
|
2010
|
Kowalski Grzegorz
|
Pracownicy administracyjni
|
13
|
2010
|
Nowak Mariusz
|
Pracownicy techniczni
|
14
|
2010
|
Nowak Krzysztof
|
Pracownicy techniczni
|
15
|
2010
|
Nowak Maria
|
Pracownicy techniczni
|
16
|
2010
|
Bach Barbara
|
Kierownicy
|
17
|
2010
|
Bach Waldemar
|
Kierownicy
|
18
|
2010
|
Bach Anna
|
Kierownicy
|
19
|
2010
|
Bach Piotr
|
Kierownicy
|
20
|
2010
|
Pawłowska Katarzyna
|
Dyrektorzy
|
21
|
2010
|
Pawłowski Mateusz
|
Dyrektorzy
|
22
|
2011
|
Kowalski Marek
|
Pracownicy administracyjni
|
23
|
2011
|
Kowalska Zosia
|
Pracownicy administracyjni
|
24
|
2011
|
Kowalska Justyna
|
Pracownicy administracyjni
|
25
|
2011
|
Kowalski Mirek
|
Pracownicy administracyjni
|
26
|
2011
|
Kowalski Marcin
|
Pracownicy administracyjni
|
27
|
2011
|
Kowalski Stanisław
|
Pracownicy administracyjni
|
28
|
2011
|
Kowalski Adam
|
Pracownicy administracyjni
|
29
|
2011
|
Kowalska Marta
|
Pracownicy administracyjni
|
30
|
2011
|
Kowalska Krystyna
|
Pracownicy administracyjni
|
31
|
2011
|
Kowalska Lidia
|
Pracownicy administracyjni
|
32
|
2011
|
Kowalski Grzegorz
|
Pracownicy administracyjni
|
33
|
2011
|
Nowak Krzysztof
|
Pracownicy techniczni
|
34
|
2011
|
Nowak Maria
|
Pracownicy techniczni
|
35
|
2011
|
Bach Barbara
|
Kierownicy
|
36
|
2011
|
Bach Waldemar
|
Kierownicy
|
37
|
2011
|
Bach Anna
|
Kierownicy
|
38
|
2011
|
Bach Piotr
|
Kierownicy
|
39
|
2011
|
Pawłowska Katarzyna
|
Dyrektorzy
|
…
|
2010
|
…
|
|
…
|
2011
|
…
|
|
…
|
2012
|
…
|
|
…
|
2013
|
…
|
Tabela 5
Dane są tutaj przedstawione inaczej i aby z tych danych uzyskać wyniki jak poprzednio z ilością pracowników w poszczególnych latach i grupach musimy skorzystać z funkcji agregujących.
Zakładając, że dane szczegółowe składają się na dane z tabeli
1 to za pomocą poniższego zapytania dostaniemy Tabelę 3.
SELECT * FROM
(
SELECT kategoria, rok, id FROM struktura_organizacyjna
)
PIVOT
(
COUNT(id)
FOR rok
IN (2010 as "Rok
2010",2011 as "Rok 2011",2012 as "Rok
2012",2013 as "Rok 2013")
)
Zakładając, że dane szczegółowe składają się na dane z Tabeli 1.
Poniższe zapytanie nie da prawidłowych wartości
SELECT * FROM struktura_organizacyjna
PIVOT
(
SUM(ilosc)
FOR rok
IN (2010 as "Rok
2010",2011 as "Rok 2011",2012 as "Rok
2012",2013 as "Rok 2013")
);
ID
|
KATEGORIA
|
Rok 2010
|
Rok 2011
|
Rok 2012
|
Rok 2013
|
1
|
Pracownicy administracyjni
|
250
|
|||
2
|
Pracownicy techniczni
|
7
|
|||
3
|
Kierownicy
|
60
|
|||
4
|
Dyrektorzy
|
3
|
|||
5
|
Pracownicy administracyjni
|
238
|
|||
6
|
Pracownicy techniczni
|
6
|
|||
7
|
Kierownicy
|
65
|
|||
8
|
Dyrektorzy
|
2
|
|||
9
|
Pracownicy administracyjni
|
247
|
|||
10
|
Pracownicy techniczni
|
6
|
|||
11
|
Kierownicy
|
63
|
|||
12
|
Dyrektorzy
|
2
|
|||
13
|
Pracownicy administracyjni
|
247
|
|||
14
|
Pracownicy techniczni
|
6
|
|||
15
|
Kierownicy
|
64
|
|||
16
|
Dyrektorzy
|
1
|
Tabela 6
Można użyć klauzuli WITH i wówczas zapytanie będzie wyglądało następująco:
WITH pivot_data AS
(
SELECT kategoria, rok, ilosc FROM struktura_organizacyjna
)
SELECT * FROM pivot_data
PIVOT
(
SUM(ilosc)
FOR rok
IN (2010 as "Rok
2010",2011 as "Rok 2011",2012 as "Rok
2012",2013 as "Rok 2013")
);
Otrzymamy wyniki takie jak w Tabeli 3.
Sortowanie danych przy użyciu PIVOT
SELECT * FROM
(
SELECT kategoria, rok, ilosc FROM struktura_organizacyjna ORDER BY kategoria
)
PIVOT
(
SUM(ilosc)
FOR rok
IN (2010 as "Rok
2010",2011 as "Rok 2011",2012 as "Rok
2012",2013 as "Rok 2013")
);
Zapytanie powyższe zwróci dane nie posortowane takie jak w
Tabeli 2. Prawidłowo posortowane dane otrzymamy w wyniku zapytania:
SELECT * FROM
(
SELECT kategoria, rok, ilosc FROM struktura_organizacyjna
)
PIVOT
(
SUM(ilosc)
FOR rok
IN (2010 as "Rok
2010",2011 as "Rok 2011",2012 as "Rok
2012",2013 as "Rok 2013")
)
ORDER BY kategoria;
Obracanie obracania danych
Operacja PIVOT jest operacją w pełni odwracalną
Przeanalizujmy widok i spróbujmy na jego podstawie dojść do
danych wzorcowych
CREATE VIEW v_struktura_organizacyjna_pivoted
AS
SELECT * FROM
(
SELECT kategoria, rok, ilosc FROM struktura_organizacyjna
)
PIVOT
(
SUM(ilosc)
FOR rok
IN (2010 as "Rok
2010",2011 as "Rok 2011",2012 as "Rok
2012",2013 as "Rok 2013")
);
SELECT * FROM v_struktura_organizacyjna_pivoted
UNPIVOT
(
ilosc
FOR rok
IN (2010 as "Rok
2010",2011 as "Rok 2011",2012 as "Rok
2012",2013 as "Rok 2013")
);
Ograniczenia PIVOT I UNPIVOT
Nie ma możliwości wybierania na liście SELECT kolumn, które znajdują się na klauzuli pivot_clause
ORA – 00904: „kolumna”: invalid identifier
Nie ma możliwości wybierania na liście SELECT kolumn, które
znajdują się na klauzuli pivot_for_clause
ORA – 00904: „kolumna”: invalid identifier
Na klauzuli pivot_clause
musi znaleźć się funkcja agregująca.
ORA-56902: expect aggregate function inside pivot operation
PIVOT działa prawidłowo dla podanej listy wartości w
klauzuli pivot_in_clause
Fakt, że lista wartości musi być podana na sztywno utrudnia
stosowanie PIVOT.
Nie ma możliwości umieszczenia w pivot_in_clause podzapytania
PODSUMOWANIE
Zasady użycia:
- Miejsce w składni SELECT
- Użycie widoków inline lub klauzuli WITH
- Konieczność podania listy wartości (lub użycia XML)
Korzyści:
- Łatwość zapisu w porównaniu do CASE lub DECODE
- Zawartość zapisu
- Możliwość generowania danych XML
- Obustronność operacji (PIVOT, UNPIVOT)
- Łatwość przestawiania danych na wielu kolumnach oraz
wieloma agregacjami
Brak komentarzy:
Prześlij komentarz