Krzysztof Czajkowski: O wielopoziomowej agregacji słów kilka

5 pkt
Jolanta Mieszczyk napisał/a o
5 lat temu

Tytuł: "O wielopoziomowej agregacji słów kilka"


Analizując dane często trzeba je podsumowywać - to oczywiste. Równie oczywiste je to, że zwykle życzymy sobie również podsumowań pośrednich (w ramach poszczególnych grup). Nieco mniej oczywiste jest to, jak wykonać to optymalnie.
Rozważmy poniższą tabele, zwierającą dane na temat pracowników: ich departamentów (deptno), stanowisk pracy (job) i wynagrodzeń (sal):


CREATE TABLE emp (
empno NUMBER(4),
deptno NUMBER(4),
job VARCHAR2(10),
sal NUMBER(8,2) );


Gdybyśmy zażyczyli sobie poniższy raport:

to wykorzystując tylko podstawową składnie z klauzulą GROUP BY należałoby "składać" całe zapytanie z kilku elementarnych SELECTów wykorzystując operację UNION:

SELECT to_char(deptno) deptno, job, count(*), round(avg(sal),2) FROM emp GROUP BY deptno, job
UNION
SELECT to_char(deptno), '', count(*), round(avg(sal),2) FROM emp GROUP BY deptno
UNION
SELECT '', '', count(*), round(avg(sal),2) FROM emp;

Kiepsko to wygląda - nie dość , że długie, to w przypadku konieczności modyfikacji raportu, trzeba poprawiać aż trzy miejsca. A co najważniejsze, powyższe rozwiązanie jest bardzo niewydajne. Wymaga wielokrotnego (w tym przypadku trzykrotnego) pełnego przeglądu całej tabeli. Na pewno można to zrobić prościej - a prościej często (jak i w tym przypadku) znaczy wydajniej:

SELECT deptno, job, count(*), round(avg(sal),2) FROM emp GROUP BY ROLLUP (deptno, job);

Wygląda to aż podejrzanie prosto :) Całość obliczeń kryje się w słowie kluczowym ROLLUP - to dzięki niemu uzyskujemy wielopoziomowe podsumowania. Wystarczy jednokrotne skanowanie tabeli (wydajność wzrasta więc mniej więcej trzykrotnie), bardzo łatwo również poprawiać i rozbudowywać zapytanie. Klauzula zawarta jest w standardzie ISO/ANSI SQL i funkcjonuje nie tylko na serwerach Oracle. Nie znaczy to, że wszyscy ją znają :)


Wniosek: lepsza znajomość SQL to nie tylko nowe możliwości i wyniki, których inaczej nie można uzyskać. Równie ważne jest to, że szybciej napiszemy coś, co na dodatek szybciej działa.

Pozostają do rozpatrzenia kwestie: interpretacji wartości pustych, możliwości eliminacji niektórych poziomów agregacji oraz agregacji z innych kierunkach. Te i inne zagadnienia omawiane są na szkoleniach: SQL dla analityków danych w środowisku Oracle oraz Zaawansowany SQL w środowisku Oracle.

Zapraszam na szkolenie z tego zakresu: SQL dla analityków danych w środowisku Oracle oraz Zaawansowany SQL w środowisku Oracle.

Na szkoleniu uczestnik zdobędzie wiedzę m.in. na temat funkcjonowania zaawansowanych mechanizmów dostępnych w środowisku Oracle, odpowiedzialnych za wyszukiwanie i analizowanie danych. Zakres obejmuje również: tworzenie konstrukcji wybierających dane z wielu tabel, wykorzystywanie wbudowanych funkcji jednowierszowych i grupowych, uwzględnianie formatowania danych, ich sortowania, operacji zbiorowych i podzapytań, jak również zapytań hierarchicznych oraz stosujących wielowymiarowe grupowania.

Masz pytanie?

zadaj pytanie

Masz pomysł?

zgłoś pomysł