|
Ganz ähnlich wie Gruppenfunktionen ermitteln analytische Funktionen in ORACLE ebenfalls einen Wert basierend auf einer Gruppe von Datensätzen. Der entscheidende Unterschied ist, dass analytische Funktionen nicht nur einmal pro Gruppe, sondern mehrfach liefern! Damit eröffnen sich neue Möglichkeiten!
Bei der Benutzung von analytischen Funktionen kann man genau bestimmen, auf welcher Basis von Datensätzen ein Wert berechnet werden soll. Die Ergebniswerte werden erst nach der Ermittlung der Datensätze (also nach dem WHERE), aber vor dem Sortieren (also vor dem ORDER BY) berechnet.
Die analytischen Funktionen
Die Liste der analytischen Funktionen ist weit länger als die der Gruppenfunktionen. Insgesamt gibt es 30.
AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE, LAG, LAST, LAST_VALUE, LEAD, MAX, MIN, NTILE, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, RANK, RATIO_TO_REPORT, REGR_*, ROW_NUMBER, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE
Statistiker werden die wahre Freude daran haben, leider bin ich keiner und konzentriere mich daher auf den “Hausgebrauch”.
Der Grundaufbau
Der Grundaufbau einer solchen Funktion ist wie folgt: function (argument(s)) over (analytical clause)
Lassen wir die analytical clause erstmal weg und probieren, was ohne passiert: select ename, sal, sum(sal) over () from emp;
ENAME SAL SUM(SAL)OVER()
---------- ---------- --------------
SMITH 800 29025
ALLEN 1600 29025
WARD 1250 29025
JONES 2975 29025
MARTIN 1250 29025
BLAKE 2850 29025
CLARK 2450 29025
SCOTT 3000 29025
KING 5000 29025
TURNER 1500 29025
ADAMS 1100 29025
JAMES 950 29025
FORD 3000 29025
MILLER 1300 29025
14 rows selected.
Na, das ist doch mal was! In einem Select sowohl Detail-Information (das Gehalt eines Mitarbeiters) und eine Gruppen-Information (Summe über alle Mitarbeiter). Würde sonst nur mit Inline-Views und Join gehen. Toll!
Die ANALYTICAL CLAUSE
PARTITION BY
Wir wollen nicht die Summe des Gehalts über alle Datensätze ermitteln, sondern nur pro Abteilung (sowas würden wir ja sonst im GROUP BY Teil machen).
Dazu gibt es die Klausel PARTITION BY: select ename, sal, deptno, sum(sal) over (partition by deptno) from emp;
ENAME SAL DEPTNO SUM(SAL)OVER(PARTITIONBYDEPTNO)
---------- ---------- ---------- -------------------------------
CLARK 2450 10 8750
KING 5000 10 8750
MILLER 1300 10 8750
JONES 2975 20 10875
FORD 3000 20 10875
ADAMS 1100 20 10875
SMITH 800 20 10875
SCOTT 3000 20 10875
WARD 1250 30 9400
TURNER 1500 30 9400
ALLEN 1600 30 9400
JAMES 950 30 9400
BLAKE 2850 30 9400
MARTIN 1250 30 9400
Auch schön.
Und wenn ich die Datensätze jetzt abhängig von Ihrem Gehalt pro Abteilung sortieren will? Ein normales ORDER BY geht ja nicht, denn ich brauche es pro Abteilung.
ORDER BY
Dazu gibt es auch bei analytischen Funktionen einen ORDER BY Teil. Jetzt nutze ich mal eine andere Funktion: ROW_NUMBER select ename, sal, deptno,
row_number() over (partition by deptno order by sal desc) x
from emp;
ENAME SAL DEPTNO X
---------- ---------- ---------- ----------
KING 5000 10 1
CLARK 2450 10 2
MILLER 1300 10 3
SCOTT 3000 20 1
FORD 3000 20 2
JONES 2975 20 3
ADAMS 1100 20 4
SMITH 800 20 5
BLAKE 2850 30 1
ALLEN 1600 30 2
TURNER 1500 30 3
MARTIN 1250 30 4
WARD 1250 30 5
JAMES 950 30 6
Aaaah, ROW_NUMBER liefert mir also ähnlich wie ROWNUM eine “Datensatznummer”, nur eben pro Gruppe.
Man beachte, dass SCOTT und FORD verschiedene Satznummern haben, obwohl sie das gleiche Gehalt beziehen. Es gibt hier andere Funktionen, die in einem solchen Fall die gleiche Nummer liefern würden.
Um also die Bestverdiener pro Abteilung zu ermitteln, brauche ich jetzt nur noch eine Inline-View, denn analytische Funktionen sind im WHERE-Teil nicht erlaubt (denn sie werden ja erst nach dem WHERE-Teil berechnet). select ename, sal, deptno from
(
select ename, sal, deptno,
row_number() over (partition by deptno order by sal desc) x
from emp
) where x = 1;
ENAME SAL DEPTNO
---------- ---------- ----------
KING 5000 10
SCOTT 3000 20
BLAKE 2850 30
Kurzum, analytische Funktionen sind spitze, man vermeidet damit so manche Joins, SUBSELECTs und Inline-Views.
Die Funktionen sind sehr mächtig, wer sich damit weiter auseinandersetzen will (auch als nicht-Statistiker), dem empfehle ich als nächstes RANK, DENSE_RANK, bzw. LAG.
|
