Kawałek Kodu

Zapytanie przestawne pozwala nam na obrócenie wyniku zapytania o 90 stopni i zamianę niektórych wierszy w kolumny (trochę jak rozkładanie parasola). Na skrzyżowaniu nowopowstałych kolumn oraz pozostałych wierszy otrzymujemy wartości z kolumn, których nie wykorzystaliśmy ani do wierszy ani do kolumn. Prostym przykładem niech będzie tabela mecz gdzie przechowujemy nazwiska graczy, numery meczów oraz ilość strzelonych goli, czyli mamy kolumny: gracz, mecz, polowa, gole. Za pomocą tabeli przestawnej chcemy uzyskać czytelniejszy widok: będziemy mieć wiersze z nazwiskami, kolumny odpowiadające poszczególnym meczom, a komórki będą zawierać sumę strzelonych goli.

Czyli z tabeli:

chcemy otrzymać:

W systemach bazodanowych Oracle czy też MSSQL mamy dostępne operatory PIVOT, ale my najczęściej zajmujemy się na tym blogu MySQL, więc fajnie by było przyjrzeć się temu operatorowi w tymże systemie.

Przyjrzałeś się? Bo ja się przyglądałem i nie znalazłem. Niestety póki co ten operator jest niedostępny.

Zrobimy pivot?! Namówiłeś!

Cały "sekret" tkwi w przekształceniu wartości wierszy na kolumny. Fajnie by było zrobić to w miarę prosto. Prosto dlatego, że będziemy musieli budować formułkę na stworzenie każdej kolumny z osobna, a ponieważ kolumn może być dużo, to dlatego... prosto. Tu się też pojawiło słowo-klucz "dużo". Musimy pamiętać, że będziemy oscylować raczej wokół policzalnej liczby kolumn, tak aby zapytanie nie rozrastało się do ogromnych rozmiarów i żeby jego tworzenie miało sens.

Jak wcześniej wspomniałem zapytanie przestawne czy też tabela przestawna (nazwa pochodzi od wyniku zapytania) powiązane jest z funkcjami grupującymi. Tak więc dla każdej kolumny, którą utworzymy możemy np. podsumować wartości, które do niej wpadają. Możemy też używać funkcji uśredniającej, zliczającej, znajdujące minimum, maksimum, itd.

Zbudujmy zapytanie dla wspomnianej tabeli mecz:

SELECT gracz,
SUM(gole*(mecz=1)) AS mecz1,
SUM(gole*(mecz=2)) AS mecz2,
SUM(gole*(mecz=3)) AS mecz3
FROM mecz
GROUP BY gracz

Co tu się odsqla?!

Mamy tu trzy formułki, dla każdego z trzech meczy. Dana formułka sumuje wartość kolumny gole, tylko jeśli spełniony jest warunek, który występuje jako mnożnik. Jeśli więc dany wiersz dotyczy meczu numer 1, to mnożnik będzie mieć wartość 1 (mecz=1), jeśli innego numeru, to będzie mieć wartość 0, tak więc do SUM wpadnie gole*0=0.

Otrzymamy wynik:

Jeśli pozbędziemy się grupowania:

SELECT 
SUM(gole*(mecz=1)) AS mecz1,
SUM(gole*(mecz=2)) AS mecz2,
SUM(gole*(mecz=3)) AS mecz3
FROM mecz

to otrzymamy:

Zajmijmy się praktyczniejszym przykładem.

Mamy tabelę przychodów na każdy dzień.

Chcemy otrzymać tabelę przestawną gdzie kolumnami będą miesiące, a w komórkach będziemy mieć sumę przychodów na miesiąc. Pozornie zapytanie wydaje się trudniejsze, skoro chcemy uzyskać sumy per miesiąc, a mamy daty. Ale to tylko pozory. Wyobraź sobie co by było gdybyśmy chcieli budować kolumny na każdy z 365 dni roku!

Aby przekształcić datę w miesiąc, użyjemy funkcji DATE_FORMAT(data, '%c'). Wartość %c formatuje datę do numeru miesiąca bez zer wiodących (1-12). Uwaga! Badamy tylko wybrany, jeden rok.

Analogicznie do powyższego zapytania dotyczącego meczy, musimy sumować przychód dla danego miesiąca. Dla stycznia formuła będzie wyglądać tak:

SUM(przychod*(DATE_FORMAT(data,'%c')=1)) AS styczen

Sumujemy przychody jeśli numer miesiąca ma wartość 1.

A dla całego roku:

SELECT
SUM(przychod*(DATE_FORMAT(data,'%c')=1)) AS st,
SUM(przychod*(DATE_FORMAT(data,'%c')=2)) AS lt,
SUM(przychod*(DATE_FORMAT(data,'%c')=3)) AS mrz,
SUM(przychod*(DATE_FORMAT(data,'%c')=4)) AS kw,
SUM(przychod*(DATE_FORMAT(data,'%c')=5)) AS mj,
SUM(przychod*(DATE_FORMAT(data,'%c')=6)) AS czrw,
SUM(przychod*(DATE_FORMAT(data,'%c')=7)) AS lp,
SUM(przychod*(DATE_FORMAT(data,'%c')=8)) AS sp,
SUM(przychod*(DATE_FORMAT(data,'%c')=9)) AS wrz,
SUM(przychod*(DATE_FORMAT(data,'%c')=10)) AS prn,
SUM(przychod*(DATE_FORMAT(data,'%c')=11)) AS lst,
SUM(przychod*(DATE_FORMAT(data,'%c')=12)) AS gr
FROM przychod

Zapytanie nie wygląda nazbyt przyjaźnie, ale być może wykorzystasz je lub wiedzę z tego wpisu gdzieś w praktyce.

A póki co, miłego obracania (nie tylko tabel)!

Przydatne linki:
Funkcja DATE_FORMAT