Tabele przestawne (pivot queries) w Oracle 10g i 11g – zamiana wierszy na kolumny

Tabele przestawne to temat szeroko znany z Excela lub OpenOffice’a (Pilot danych), który sprowadza się do tego, aby dane widziane w formie ciągu wierszy zaprezentować w formie bardziej zwartej – tak aby zarówno w wierszach, jak i kolumnach znajdowały się interesujące nas wartości wymiarów, a na ich przecięciu wartość interesującej nas miary.
W najprostszym przypadku ich idea sprowadza się do zamiany

NUM KAT
1 a
1 b
1 c
2 k
3 j
3 a

Na:

NUM   a   b   c   k   j
1     1   1   1   0   0
2     0   0   0   1   1
3     1   0   0   0   1

Tutaj wartościami w sekcji wierszy są NUM, wartościami w sekcji kolumn KAT, a wartością mierzoną COUNT(*).

Spotyka się również pewne odmiany problemu – np. uzyskanie w efekcie:

NUM kol1 kol2 kol3
1   a    b    c
2   k
3   j    a

Ten przypadek omawiam na końcu.

Klasyczne tabele przestawne

Najpierw zajmiemy się przypadkiem częstszym, a więc tym pierwszym. Uzyskanie wyniku w 10g można zrealizować tak:

SELECT num, 
       COUNT(CASE WHEN KAT = 'a' THEN KAT ELSE NULL END) as "a",
       COUNT(CASE WHEN KAT = 'b' THEN KAT ELSE NULL END) as "b",
       COUNT(CASE WHEN KAT = 'c' THEN KAT ELSE NULL END) as "c",
       COUNT(CASE WHEN KAT = 'k' THEN KAT ELSE NULL END) as "k",
       COUNT(CASE WHEN KAT = 'j' THEN KAT ELSE NULL END) as "j"
FROM tabela
GROUP BY num; 

Postępujemy zatem wg. schematu:

SELECT [LISTA KOLUMN W SEKCJI WIERSZY], 
       [FUNKCJA](CASE WHEN [KOLUMNA SEKCJI KOLUMN] = [WARTOSC DLA KOLUMNY] THEN [KOLUMNA SEKCJI KOLUMN] ELSE NULL END) as [NAZWA KOLUMNY W SEKCJI KOLUMN],
       ...
FROM tabela
GROUP BY [LISTA KOLUMN W SEKCJI WIERSZY]; 

W Oracle 11g dodano składnie PIVOT:

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

pivot_clause to funkcja wyliczająca (u nas COUNT(*))
pivot_for_clause to kolumna sekcji kolumn (u nas KAT)
pivot_in_clause to lista wartości do sekcji kolumn (u nas ‚a’, ‚b’, ‚c’, ‚k’, j’)

Klauzula pivot nie informuje o tym wprost, ale do sekcji wierszy trafią wszystkie kolumny pierwotnego zapytania nie wymienione w nawiasach po słowie PIVOT Jeśli zatem w tabeli jest więcej kolumn niż te, które aktualnie chcemy analizować trzeba je ograniczyć stosując WITH (najczęstsza forma stosowania PIVOT w 11g):

WITH dane AS (SELECT kol1, kol2, kol3 FROM tabela)
SELECT ...
FROM   dane
PIVOT
   ( pivot_clause
     pivot_for_clause
     pivot_in_clause )
WHERE  ...

A więc w naszym przypadku:

WITH dane AS (SELECT NUM, KAT FROM tabela)
SELECT *
FROM   dane
PIVOT
   ( COUNT(*)
     FOR KAT
     IN ('a', 'b', 'c', 'j', 'k' )
)

Problemem w obu przypadkach jest konieczność znajomości listy wartości jakie trafią do sekcji kolumn. Da się ten problem obejść, ale nie zwyczajnym zapytaniem SQL.

Wymagania specjalne

Zamiana tego:

NUM KAT
1 a
1 b
1 c
2 k
3 j
3 a

Na:

NUM kol1 kol2 kol3
1   a    b    c
2   k
3   j    a

wymaga komentarza. Otóż tutaj mamy do czynienia z pewnym klasycznym przypadkiem w którym problem nie jest relacyjny (z punktu widzenia teorii baz danych), bowiem to, że kol1 dla wartości 1 to a a kol2 to b wynika z kolejności danych wejściowych. Unikamy jak ognia takich przypadków, bo w praktyce rodzą błędy bardzo trudne do wykrycia. Da się jednak wymusić i takie zachowanie w Oracle. Po pierwsze numerujemy sobie wiersze:

SELECT NUM, KAT, Row_Number() over (PARTITION BY NUM ORDER BY KAT) FROM tabela;

a następnie trochę modyfikujemy sposób uzyskania wyniku z 10g:

WITH dane AS (SELECT NUM, KAT, Row_Number() over (PARTITION BY NUM ORDER BY KAT) as rn FROM v)
SELECT NUM,
       MIN(CASE WHEN RN = 1 THEN KAT END),
       MIN(CASE WHEN RN = 2 THEN KAT END),
       MIN(CASE WHEN RN = 3 THEN KAT END),
       MIN(CASE WHEN RN = 4 THEN KAT END),
       MIN(CASE WHEN RN = 5 THEN KAT END),
       MIN(CASE WHEN RN = 6 THEN KAT END),
       MIN(CASE WHEN RN = 7 THEN KAT END),
       MIN(CASE WHEN RN = 8 THEN KAT END),
       MIN(CASE WHEN RN = 9 THEN KAT END)
FROM dane
GROUP BY NUM;

Niestety nadal musimy znać liczbę kolumn w wyniku – tutaj założyliśmy, że będzie ich 9. Funkcja MIN użyta w zapytaniu, może zostać zamieniona ma MAX – nie ma to znaczenia gdyż nie będzie 2 wartości o takim samym RN i NUM.

Ten wpis został opublikowany w kategorii Oracle SQL Zaawansowany, SQL i oznaczony tagami , , , , . Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz