Pchełki SQL: odcinek 1. Pivot – Unpivot

https://xpil.eu/qsa 📋

Zdecydowałem się, wzorem niegdysiejszych pchełek VBA, rozpocząć serię pchełek SQL.

Stopień zaawansowania pchełek będzie różny. W odróżnieniu od pchełek VBA, które pisałem trochę w postaci kursu (a więc, przynajmniej teoretycznie, można się było z nich nauczyć podstaw VBA, o ile ktoś "przerabiał" je po kolei), tutaj zakładam, że Czytelnik ma jakąś (przynajmniej podstawową) znajomość języka SQL.

Zaczynamy. Na pierwszy ogień idzie moja odwieczna bolączka, czyli PIVOT - UNPIVOT.

Kod jest kompatybilny z Oracle, ale powinien działać również w MSSQL, o ile wywali się nieszczęsne "FROM DUAL" (które, moim zdaniem, jest obrzydliwe, i nie powinno się nigdy pojawić w żadnej wersji języka SQL)

Najpierw dane wejściowe, czyli wartości sprzedaży poszczególnych działów pewnej firmy, w rozbiciu na lata:

SELECT 2009 AS yr, 11500 AS footware, 230000 AS computers, 90000 AS lightning, 10000 AS stationery FROM dual
 UNION SELECT 2010, 32000, 245000, 120000, 8800 FROM dual
 UNION SELECT 2011, 23000, 1000, 60000, 9900 FROM dual;

Wynik powyższego zapytania to:

YR   FOOTWARE COMPUTERS LIGHTNING STATIONERY
2009 11500    230000    90000     10000
2010 32000    245000    120000    8800
2011 23000    1000      60000     9900

A więc mamy jeden rekord dla każdego roku, z wartościami sprzedaży poszczególnych działów w osobnych kolumnach.

Chcielibyśmy teraz uzyskać z tego postać znormalizowaną, czyli jeden rekord dla każdej pary rok-dział, z odpowiadającą tej parze wartością sprzedaży. W tym celu użyjemy konstrukcji UNPIVOT, która pozwala na "spionowienie" danych czyli przekształcenie nazw kolumn w dane.

Zapytanie będzie wyglądało następująco:

SELECT dept, yr, amount
 FROM(
 (SELECT 2009 AS YR, 11500 AS FOOTWARE, 230000 AS COMPUTERS, 90000 AS LIGHTNING, 10000 AS STATIONERY FROM DUAL
 UNION SELECT 2010, 32000, 245000, 120000, 8800 FROM DUAL
 UNION SELECT 2011, 23000, 1000, 60000, 9900 FROM DUAL
 )
 unpivot(amount FOR dept IN(footware, computers, lightning, stationery)));

A wynik jest taki:

DEPT       YR   AMOUNT
FOOTWARE   2009 11500
COMPUTERS  2009 230000
LIGHTNING  2009 90000
STATIONERY 2009 10000
FOOTWARE   2010 32000
COMPUTERS  2010 245000
LIGHTNING  2010 120000
STATIONERY 2010 8800
FOOTWARE   2011 23000
COMPUTERS  2011 1000
LIGHTNING  2011 60000
STATIONERY 2011 9900

Jak widać, zgodnie z założeniem, mamy teraz jeden rekord dla każdej pary rok-dział, z odpowiadającą tej parze wartością sprzedaży.

Na koniec zrobimy operację odwrotną i wygenerujemy dane w postaci jednego rekordu dla każdego działu, z wartościami sprzedaży w poszczególnych latach w osobnych kolumnach. Użyjemy do tego celu konstrukcji PIVOT, która przekształca dane w nagłówki kolumn. Zapytanie wygląda tak:

SELECT DEPT, "2009", "2010", "2011"
 FROM(
 (SELECT 2009 AS YR, 11500 AS FOOTWARE, 230000 AS COMPUTERS, 90000 AS LIGHTNING, 10000 AS STATIONERY FROM DUAL
 UNION SELECT 2010, 32000, 245000, 120000, 8800 FROM DUAL
 UNION SELECT 2011, 23000, 1000, 60000, 9900 FROM DUAL
 )
 UNPIVOT(AMOUNT FOR DEPT IN(FOOTWARE, COMPUTERS, LIGHTNING, STATIONERY)))
 PIVOT(MAX(AMOUNT) FOR(YR) IN(2009 AS "2009", 2010 AS "2010", 2011 AS "2011"));

A oto wynik:

DEPT       2009   2010   2011
COMPUTERS  230000 245000 1000
LIGHTNING  90000  120000 60000
FOOTWARE   11500  32000  23000
STATIONERY 10000  8800   9900

W ten oto sposób wykonaliśmy przekształcenie danych polegające na zamianie miejscami wierszy i kolumn. Czyli coś, co w Excelu można uzyskać paroma kliknięciami, używając sekwencji "Kopiuj" => "Wklej Specjalnie" => "Transpozycja".

https://xpil.eu/qsa 📋