Operacja PIVOT w języku SQL w środowisku Oracle 21.11 · · 2012-11-21• pivote_for_clause –...

www.comarch.pl/szkolenia

Operacja PIVOT w języku SQL w środowisku Oracle

21.11.2012


Zakres

• Wprowadzenie • Idea przestawiania danych • Możliwe zastosowania • Przestawianie danych bez klauzuli PIVOT • Konstrukcja klauzuli • Korzyści ze stosowania PIVOT • Przykłady działania • PIVOT z klauzulą WITH • Klauzula UNPIVOT • Ograniczenia stosowania • PIVOT i opcja XML • Podsumowanie


Wprowadzenie

• W relacyjnych bazach danych dane przedstawiane są w postaci tabel.

• Sposób składowania danych bezpośrednio wpływa na sposób, w jaki można na nich operować.

• Często, aby lepiej przeanalizować dane należało zaprezentować je w innej, wygodniejszej postaci.

• W ten sposób można wydobyć informacje na temat trendów w zmiana danych (trendów sprzedaży, kosztów itp.)


Przestawianie danych


Obracanie w Oracle 10g – przykład 1

SELECT * FROM ( SELECT kategoria, SUM(DECODE(kwartal,1,ilosc)) Q1, SUM(DECODE(kwartal,2,ilosc)) Q2, SUM(DECODE(kwartal,3,ilosc)) Q3, SUM(DECODE(kwartal,4,ilosc)) Q4 FROM sprzedaz2011 GROUP BY kategoria ) ORDER BY kategoria; KATEGORIA Q1 Q2 Q3 Q4

--------------- --- --- --- ---

AGD 100 110 90 140

komputery 130 120 120 150

RTV 150 150 180 190

telefony 250 300 330 300

KWARTAL KATEGORIA ILOSC

---------- --------------- ------

1 AGD 125

1 komputery 162

1 RTV 187

1 telefony 312

2 AGD 137

2 komputery 150

2 RTV 187

2 telefony 375

3 AGD 112

3 komputery 150

3 RTV 225

3 telefony 412

4 AGD 175

4 komputery 187

4 RTV 237

4 telefony 375


Obracanie w Oracle 10g – przykład 2

SELECT * FROM ( SELECT kategoria, SUM(CASE WHEN kwartal = 1 THEN ilosc END) Q1, SUM(CASE WHEN kwartal = 2 THEN ilosc END) Q2, SUM(CASE WHEN kwartal = 3 THEN ilosc END) Q3, SUM(CASE WHEN kwartal = 4 THEN ilosc END) Q4 FROM sprzedaz2011 GROUP BY kategoria ) ORDER BY kategoria; KATEGORIA Q1 Q2 Q3 Q4

--------------- --- --- --- ---

AGD 100 110 90 140

komputery 130 120 120 150

RTV 150 150 180 190

telefony 250 300 330 300


PIVOT

• Operacja umożliwiająca 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).

• Operacje przydatne, gdy chcemy obserwować np. trendy sprzedaży w czasie.


PIVOT

SELECT ... FROM ... PIVOT ( pivot_clause pivot_for_clause pivot_in_clause ) WHERE ...

• pivot_clause – definiowanie kolumn do agregacji (Pivot jest

operacją agregującą) • pivote_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)


PIVOT – przykład 1a

SELECT * FROM (

SELECT kategoria, kwartal, ilosc FROM sprzedaz2011

)

PIVOT (SUM(ilosc)

FOR kwartal

IN (1,2,3,4));

KATEGORIA 1 2 3 4

--------------- ---------- ---------- ---------- ----------

komputery 130 120 120 150

telefony 250 300 330 300

RTV 150 150 180 190

AGD 100 110 90 140


PIVOT – przykład 1b

SELECT * FROM (


)

PIVOT (SUM(ilosc)

FOR kwartal

IN (1 AS Q1, 2 AS Q2, 3 AS Q3, 4 AS Q4));

KATEGORIA Q1 Q2 Q3 Q4

--------------- ---------- ---------- ---------- ----------

komputery 130 120 120 150

telefony 250 300 330 300

RTV 150 150 180 190

AGD 100 110 90 140


ID PRODUKT KATEGORIA DATA_SPRZ CENA R

---------- ------------------------- --------------- --------- ---------- -

1 TV Samsung LCD 50" RTV 02-JAN-11 3500 N

2 Pralka Bosch" AGD 02-JAN-11 1200 N

3 TV Sony LCD 40" RTV 03-JAN-11 2500 T

4 Lodówka Amica AGD 10-JAN-11 1500 T

5 Netbook Lenovo 12" Komputery 15-JAN-11 1700 N

6 Pralka Candy AGD 17-JAN-11 1300 N

7 Netbook Lenovo 10" Komputery 18-JAN-11 1200 N

8 Notebook Samnsung 15" Komputery 22-JAN-11 2200 N

9 TV Panasonic 32" TV 26-JAN-11 2000 N

10 Nokia Lumnia 800 Telefony 29-JAN-11 1200 N

11 TV Samsung LCD 40" RTV 01-FEB-11 3000 T

12 Zmywarka Bosch AGD 05-FEB-11 1300 T

13 TV Sony LCD 50" RTV 05-FEB-11 4500 N

14 Okap Mastercook AGD 09-FEB-11 1000 T

15 Samsung Galaxy S Telefony 11-FEB-11 1700 N

16 Sony Xperia Telefony 18-FEB-11 1100 T

17 Netbook Lenovo 10" Komputery 18-FEB-11 1200 T

18 Okap Amica AGD 23-FEB-11 1200 T

19 Samsung Galaxy S Telefony 24-FEB-11 1300 N

20 Nokia Lumnia 800 Telefony 26-FEB-11 1000 N

21 Apple IPhone 3 Telefony 27-FEB-11 1200 N

22 TV Panasonic 50" RTV 27-FEB-11 3200 N

23 Apple iPhone 3 Telefony 01-MAR-11 3000 T

24 Netbook Samsung 10" Komputery 02-MAR-11 1300 T

25 TV Samsung LCD 40" RTV 04-MAR-11 4500 N

26 Apple iPhone 3 Telefony 05-MAR-11 1000 T

27 Samsung Galaxy S Telefony 12-MAR-11 1700 N

28 Sony Xperia Telefony 16-MAR-11 1100 T


Przykład 2a – jedna agregacja

SELECT * FROM (

SELECT kategoria, TO_CHAR(data_sprzedazy,'MM') miesiac, id FROM sprzedaz2011_detal

)

PIVOT (COUNT(id)

FOR miesiac

IN ('01' AS STY,'02' AS LUT,'03' AS MAR));

KATEGORIA STY LUT MAR

--------------- ---------- ---------- ----------

RTV 3 4 5

AGD 3 3 2

Komputery 3 1 2

Telefony 1 5 7


Przykład 2a – jedna agregacja

SELECT * FROM (


)

PIVOT (COUNT(id)

FOR miesiac

IN (01 AS STY,02 AS LUT,03 AS MAR));

KATEGORIA STY LUT MAR

--------------- ---------- ---------- ----------

RTV 0 0 0

AGD 0 0 0

Komputery 0 0 0

Telefony 0 0 0


Przykład 2b – dwie agregacje

SELECT * FROM (

SELECT kategoria, TO_CHAR(data_sprzedazy,'MM') miesiac, id, cena FROM sprzedaz2011_detal

)

PIVOT (COUNT(id) ilosc, SUM(cena) zysk

FOR miesiac


KATEGORIA STY_ILOSC STY_ZYSK LUT_ILOSC LUT_ZYSK MAR_ILOSC MAR_ZYSK

--------------- ---------- ---------- ---------- ---------- ---------- ----------

RTV 3 8000 4 12900 5 17400

AGD 3 4000 3 3500 2 4400

Komputery 3 5100 1 1200 2 2500

Telefony 1 1200 5 6300 7 12200


Przykład 2b cd. – brak kolumny

SELECT * FROM (


)

PIVOT (COUNT(id) ilosc, SUM(cena) zysk

FOR miesiac


ERROR at line 4:

ORA-00904: "CENA": invalid identifier


Przykład 2c – przestawianie na wielu kolumnach

SELECT * FROM (

SELECT kategoria, TO_CHAR(data_sprzedazy,'MM') miesiac, raty, id FROM sprzedaz2011_detal

)

PIVOT (COUNT(id)

FOR (miesiac, raty)

IN (('01','N') AS STY_CAL, ('01','T') AS STY_RATY,

('02','N') AS LUT_CAL, ('02','T') AS LUT_RATY,

('03','N') AS MAR_CAL, ('03','T') AS MAR_RATY));

KATEGORIA STY_CAL STY_RATY LUT_CAL LUT_RATY MAR_CAL MAR_RATY

--------------- ---------- ---------- ---------- ---------- ---------- ----------

RTV 2 1 3 1 3 2

AGD 2 1 0 3 0 2

Komputery 3 0 0 1 0 2

Telefony 1 0 4 1 2 5


Przykład 3a – lokalizacja zapytania: podzapytanie – inline view

SELECT * FROM (


)

PIVOT (SUM(ilosc)

FOR kwartal

IN (1 AS Q1, 2 AS Q2, 3 AS Q3, 4 AS Q4));


--------------- ---------- ---------- ---------- ----------

komputery 130 120 120 150

telefony 250 300 330 300

RTV 150 150 180 190

AGD 100 110 90 140


Przykład 3b – pełny select – błędne wyniki

SELECT * FROM sprzedaz2011

PIVOT (SUM(ilosc)

FOR kwartal

IN (1 AS Q1, 2 AS Q2, 3 AS Q3, 4 AS Q4));

ID KATEGORIA 1 2 3 4

---------- --------------- ---------- ---------- ---------- ----------

2 AGD 100

4 telefony 250

11 komputery 120

14 AGD 140

15 komputery 150

6 AGD 110

9 RTV 180

16 telefony 300

5 RTV 150

1 RTV 150

8 telefony 300

12 telefony 330

3 komputery 130

7 komputery 120

10 AGD 90

13 RTV 190


Przykład 3c – wybieranie kolumn w zapytani głównym - błąd


PIVOT (SUM(ilosc)

FOR kwartal

IN (1 AS Q1, 2 AS Q2, 3 AS Q3, 4 AS Q4));

ERROR at line 1:

ORA-00904: "ILOSC": invalid identifier


Przykład 3d – subquery factoring

WITH pivot_data AS ( SELECT kategoria, kwartal, ilosc FROM sprzedaz2011 ) SELECT * FROM pivot_data PIVOT (SUM(ilosc) FOR kwartal IN (1 AS Q1, 2 AS Q2, 3 AS Q3, 4 AS Q4)); KATEGORIA Q1 Q2 Q3 Q4

--------------- ---------- ---------- ---------- ----------

komputery 130 120 120 150

telefony 250 300 330 300

RTV 150 150 180 190

AGD 100 110 90 140


Przykład 3e – lokalizacja zapytania: podzapytanie – view

CREATE OR REPLACE VIEW v_sprzedaz2011

AS SELECT kategoria, kwartal, ilosc FROM sprzedaz2011;

SELECT * FROM v_sprzedaz2011

PIVOT (SUM(ilosc)

FOR kwartal

IN (1 AS Q1, 2 AS Q2, 3 AS Q3, 4 AS Q4));


--------------- ---------- ---------- ---------- ----------

komputery 130 120 120 150

telefony 250 300 330 300

RTV 150 150 180 190

AGD 100 110 90 140


Przykład 4a – sortowanie

SELECT * FROM (


ORDER BY kategoria

)

PIVOT (SUM(ilosc)

FOR kwartal

IN (1 AS Q1, 2 AS Q2, 3 AS Q3, 4 AS Q4));


--------------- ---------- ---------- ---------- ----------

komputery 130 120 120 150

telefony 250 300 330 300

RTV 150 150 180 190

AGD 100 110 90 140


Przykład 4b – sortowanie

SELECT * FROM (


)

PIVOT (SUM(ilosc)

FOR kwartal

IN (1 AS Q1, 2 AS Q2, 3 AS Q3, 4 AS Q4))

ORDER BY kategoria;


--------------- ---------- ---------- ---------- ----------

AGD 100 110 90 140

RTV 150 150 180 190

komputery 130 120 120 150

telefony 250 300 330 300


Przykład 5a – zaokrąglanie wyników

SELECT * FROM (

SELECT kategoria, TO_CHAR(data_sprzedazy,'MM') miesiac, cena FROM sprzedaz2011_detal

)

PIVOT (AVG(cena) srednia_cena

FOR miesiac

IN ('01' AS STY,'02' AS LUT));

KATEGORIA STY_SREDNIA_CENA LUT_SREDNIA_CENA

--------------- ---------------- ----------------

RTV 2666.66667 3225

AGD 1333.33333 1166.66667

Komputery 1700 1200

Telefony 1200 1260


Przykład 5b – zaokrąglanie wyników

SELECT * FROM (


)

PIVOT (ROUND(AVG(cena),2) srednia_cena

FOR miesiac


ERROR at line 4:

ORA-56902: expect aggregate function inside pivot operation


Przykład 5c – zaokrąglanie wyników SELECT kategoria,

round(sty_srednia_cena,2) AS sty_srednia_cena, round(lut_srednia_cena,2) AS lut_srednia_cena

FROM (


)

PIVOT (AVG(cena) srednia_cena

FOR miesiac


KATEGORIA STY_SREDNIA_CENA LUT_SREDNIA_CENA

--------------- ---------------- ----------------

RTV 2666.67 3225

AGD 1333.33 1166.67

Komputery 1700 1200

Telefony 1200 1260


Przykład 6a – łączenie tabel

SELECT * FROM (

SELECT kategoria, TO_CHAR(data_sprzedazy,'MM') miesiac, odbior, s.id

FROM sprzedaz2011_detal s JOIN transakcje t ON (s.id=t.id)

)

PIVOT (COUNT(id)

FOR (miesiac, odbior)

IN (('01','OSOBISTY') AS STY_OSO, ('01','KURIER') AS STY_KUR,

('02','OSOBISTY') AS LUT_OSO, ('02','KURIER') AS LUT_KUR,

('03','OSOBISTY') AS MAR_OSO, ('03','KURIER') AS MAR_KUR));

KATEGORIA STY_OSO STY_KUR LUT_OSO LUT_KUR MAR_OSO MAR_KUR

--------------- ---------- ---------- ---------- ---------- ---------- ----------

RTV 2 1 4 0 0 5

AGD 2 1 1 2 2 0

Komputery 3 0 1 0 1 1

Telefony 0 1 2 3 3 4


Przykład 6b – łączenie tabel

SELECT * FROM (

SELECT TO_CHAR(data_sprzedazy,'MM') miesiac, odbior, s.id

FROM sprzedaz2011_detal s JOIN transakcje t ON (s.id=t.id)

)

PIVOT (COUNT(id)

FOR (miesiac, odbior)

IN (('01','OSOBISTY') AS STY_OSO, ('01','KURIER') AS STY_KUR,

('02','OSOBISTY') AS LUT_OSO, ('02','KURIER') AS LUT_KUR,

('03','OSOBISTY') AS MAR_OSO, ('03','KURIER') AS MAR_KUR));

STY_OSO STY_KUR LUT_OSO LUT_KUR MAR_OSO MAR_KUR

---------- ---------- ---------- ---------- ---------- ----------

7 3 8 5 6 10


Przykład 7a – Podsumowania

SELECT x.*, Q1+Q2+Q3+Q4 AS rocznie FROM (


)

PIVOT (SUM(ilosc)

FOR kwartal

IN (1 AS Q1, 2 AS Q2, 3 AS Q3, 4 AS Q4)) x;

KATEGORIA Q1 Q2 Q3 Q4 ROCZNIE

--------------- ---------- ---------- ---------- ---------- ----------

komputery 130 120 120 150 520

telefony 250 300 330 300 1180

RTV 150 150 180 190 670

AGD 100 110 90 140 440


Przykład 7b – Podsumowania

SELECT nvl(kategoria,'sum_kwartaly') AS kategoria, nvl(Q1,sum(Q1) OVER()) AS Q1, nvl(Q2,sum(Q2) OVER()) AS Q2, nvl(Q3,sum(Q3) OVER()) AS Q3, nvl(Q4,sum(Q4) OVER()) AS Q4, sum_kategorie FROM ( SELECT kategoria, NVL(to_char(kwartal),'rocznie') AS kw, sum(ilosc) AS il FROM sprzedaz2011 GROUP BY ROLLUP (kategoria, kwartal) ) PIVOT (SUM(il) FOR kw IN (1 AS Q1, 2 AS Q2, 3 AS Q3, 4 AS Q4, 'rocznie' AS sum_kategorie));


Przykład 7b – Podsumowania

KATEGORIA Q1 Q2 Q3 Q4 SUM_KATEGORIE

--------------- --- --- --- --- -------------

AGD 100 110 90 140 440

RTV 150 150 180 190 670

komputery 130 120 120 150 520

telefony 250 300 330 300 1180

sum_kwartaly 630 680 720 780 2810


UNPIVOT – przykład 1a

CREATE VIEW v_sprzedaz2011_pivoted AS SELECT * FROM ( SELECT kategoria, kwartal, ilosc FROM sprzedaz2011 ) PIVOT (SUM(ilosc) FOR kwartal IN (1 AS Q1, 2 AS Q2, 3 AS Q3, 4 AS Q4));


UNPIVOT – przykład 1b SELECT * FROM v_sprzedaz2011_pivoted UNPIVOT ( ilosc FOR kwartal IN (Q1, Q2, Q3, Q4) ); KATEGORIA KW ILOSC

--------------- -- ----------

komputery Q1 130

komputery Q2 120

komputery Q3 120

komputery Q4 150

telefony Q1 250

telefony Q2 300

telefony Q3 330

telefony Q4 300

RTV Q1 150

RTV Q2 150

RTV Q3 180

RTV Q4 190

AGD Q1 100

AGD Q2 110

AGD Q3 90

AGD Q4 140


--------------- ---------- ---------- ---------- ----------

AGD 100 110 90 140

RTV 150 150 180 190

komputery 130 120 120 150

telefony 250 300 330 300


PIVOT i UNPIVOT – ograniczenia

• 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 i lista wartości

• 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


PIVOT i lista wartości przykład 1a

SELECT * FROM (


)

PIVOT (SUM(ilosc)

FOR kwartal

IN (SELECT kwartal FROM sprzedaz2011));

SQL Error: ORA-00936: brak wyrażenia

00936. 00000 - "missing expression"


PIVOT i lista wartości przykład 1b

SELECT * FROM (


)

PIVOT XML (SUM(ilosc)

FOR kwartal

IN (ANY));

• Rezultat dostarczany jest w formacie XML

• Wyniki obejmują wszystkie istniejące wartości w polach danych


PIVOT i lista wartości przykład 1b cd.

KATEGORIA KWARTAL_XML

--------- -----------------------------------------------------------------

AGD <PivotSet><item><column name = "KWARTAL">1</column><column name =

"SUM(ILOSC)">100</column></item><item><column name = "KWARTAL">2</column><column name

= "SUM(ILOSC)">110</column></item><item><column name = "KWARTAL">3</column><column

name = "SUM(ILOSC)">90</column></item><item><column name = "KWARTAL">4</column><column

name = "SUM(ILOSC)">140</column></item></PivotSet>

RTV <PivotSet><item><column name = "KWARTAL">1</column><column name =



name = "SUM(ILOSC)">180</column></item><item><column name =

"KWARTAL">4</column><column name = "SUM(ILOSC)">190</column></item></PivotSet>

Komputery <PivotSet><item><column name = "KWARTAL">1</column><column name =





telefony <PivotSet><item><column name = "KWARTAL">1</column><column name =






PIVOT i lista wartości przykład 1c

Możliwy jest zapis z podzapytaniem:

SELECT * FROM (


)


FOR kwartal

IN (SELECT kwartal FROM sprzedaz2011));

• Wyniki obejmują wszystkie wartości w polach danych, nie tylko istniejące (również komórki puste)


PIVOT i lista wartości przykład 1c

• Wyniki zapytania są w formacie XMLTYPE.

• Można te dane przetwarzać za pomocą wyrażeń XPath lub XQuery

• Można również wyciągać poszczególne elementy

SELECT kategoria,

EXTRACT(kwartal_xml, '/PivotSet/item/column') AS ilosc

FROM (


)


FOR kwartal

IN (ANY))

WHERE kategoria like '%RTV%';


PIVOT i lista wartości przykład 1b cd.

KATEGORIA ILOSC

--------- -----------------------------------------------------------------

RTV <column name="KWARTAL">1</column><column name="SUM(ILOSC)">150</column>

<column name="KWARTAL">2</column><column name="SUM(ILOSC)">150</column>




Klauzula Pivot a dane z CSV

• Klauzula Pivot może pracować na plikach tekstowych.

• Plik musi zostać wcześniej podłączony do bazy, za pomocą opcji external table.

• Dalsze operacje nie różnią się od przypadku, gdy tabela jest składowana w bazie



CREATE TABLE sprzedaz2011_ext (

id NUMBER(4),

kwartal NUMBER(2),

kategoria VARCHAR2(15),

ilosc NUMBER(4)

)

ORGANIZATION EXTERNAL

(TYPE ORACLE_LOADER

DEFAULT DIRECTORY data_imp

ACCESS PARAMETERS

(RECORDS DELIMITED BY NEWLINE

BADFILE 'bad'

LOGFILE 'log'

SKIP 1

FIELDS TERMINATED BY ';'

( id,kwartal,kategoria,ilosc) )

LOCATION ('sprzedaz2011.csv')

);



SELECT * FROM (

SELECT kategoria, kwartal, ilosc FROM sprzedaz2011_ext

)

PIVOT (SUM(ilosc)

FOR kwartal

IN (1 AS Q1, 2 AS Q2, 3 AS Q3, 4 AS Q4));


--------------- ---------- ---------- ---------- ----------

komputery 130 120 120 150

telefony 250 300 330 300

RTV 150 150 180 190

AGD 100 110 90 140


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

– Zwartość zapisu

– Możliwość generowania danych XML

– Obustronność operacji (PIVOT, UNPIVOT)

– Łatwość przestawiania danych na wielu kolumnach oraz z wieloma agregacjami


Promocje

PROMOCJE – do wykorzystania dla uczestników w terminie do 31 stycznia 2013 roku*.

• SQL – język dostępu do bazy danych Oracle - w cenie specjalnej - 700 zł

• 30% rabatu na szkolenie: SQL – poziom zaawansowany.

* Przy rejestracji do 17.12.2012 dodatkowe nagrody książkowe


Dziękuję za uwagę

Operacja PIVOT w języku SQL w środowisku Oracle 21.11 · · 2012-11-21• pivote_for_clause –...

Documents

Transcript of Operacja PIVOT w języku SQL w środowisku Oracle 21.11 · · 2012-11-21• pivote_for_clause –...