In den letzten Jahren habe ich immer wieder festgestellt, dass viele Software-Entwickler zwar in den Grundzügen SQL beherrschen, aber bei etwas fortgeschrittenen SQL-Operationen wie z.B. dem Einsatz Analytischer Funktionen gerne die Segel streichen. Dabei beherrscht heutzutage fast jedes relationales Datenbanksystem, das etwas auf sich hält, Analytische Funktionen zumindest in den Grundzügen. Und sie sind, wie Sie gleich sehen werden, extrem praktisch.

Grund genug, an dieser Stelle einen schnellen Einstieg in dieses Thema und ein paar praktische Oracle-Anwendungsbeispiele zu geben.

Analytische Funktion – was ist das?

Die meisten Entwickler kennen SQL-Aggregatsfunktionen wie SUM, COUNT, AVG, MIN oder MAX. Hierbei wird in einem Select-Statement eine Ergebnismenge nach ausgesuchten Attributen gruppiert. Ein einfaches Beispiel wäre z.B. folgendes Statement:

Je Abteilungsnummer (DEPTNO) wird hierbei das jeweils älteste Anstellungsdatum (HIREDATE) ausgegeben. Was aber, wenn Sie in der gleichen Abfrage erkennen möchten, wer denn nun dieser dienstälteste Mitarbeiter ist? Wenn Sie also – technisch gesprochen – Aggregate bilden und dennoch alle Zeilen einer Tabelle, View oder Unterabfrage ausgeben möchten? An dieser Stelle spielen die sog. Analytischen Funktion ihre Stärke aus. Hier ein einfaches Beispiel:

EMPNO	ENAME	DEPTNO	HIREDATE	MIN_DEPT_HIREDATE
7782	CLARK	10	09.06.1981	09.06.1981
7839	KING	10	17.11.1981	09.06.1981
7934	MILLER	10	23.01.1982	09.06.1981
7566	JONES	20	02.04.1981	17.12.1980
7902	FORD	20	03.12.1981	17.12.1980
7876	ADAMS	20	23.05.1987	17.12.1980
7369	SMITH	20	17.12.1980	17.12.1980
7788	SCOTT	20	19.04.1987	17.12.1980
7521	WARD	30	22.02.1981	20.02.1981
7844	TURNER	30	08.09.1981	20.02.1981
7499	ALLEN	30	20.02.1981	20.02.1981
7900	JAMES	30	03.12.1981	20.02.1981
7698	BLAKE	30	01.05.1981	20.02.1981
7654	MARTIN	30	28.09.1981	20.02.1981

In dieser Abfrage mit der bemerkenswerten Syntax hinter der MIN-Funktion werden die Mitarbeiterdaten aus Tabelle EMP und das älteste Anstellungsdatum je Abteilung gemeinsam ausgegeben. Und das ganze, ohne dass gruppiert werden muss(!).

Vor der Einführung der Analytischen Funktionen (die es schon seit Oracle 8i gibt) hätte man hierfür zwei gejointe Abfragen benötigt – eine ungruppierte auf allen Zeilen der Tabelle EMP und eine gruppierte Abfrage ebenfalls auf allen Zeilen der Tabelle EMP, die dann über die DEPTNO hätten verknüpft werden müssen. Umständlich und bei größeren Tabellen natürlich deutlich ressourcenfressender.

Die Syntax – man gewöhnt sich dran

Schauen wir uns nochmal das obige Beispiel an. Auffällig ist hier natürlich die Syntax mit „over (partition by deptno)“. Wie ist das zu verstehen?

Der Begriff „over“ hinter bestimmten SQL-Funktion (nicht allen) leitet zu der sog. analytischen Klausel über. In dem Beispiel wäre die analytische Klausel somit der in Klammern stehende Bereich „(partition by deptno)“.

Die analytische Klausel kann aus folgenden Bestandteilen bestehen:

• PARTITION BY: „PARTITION BY“ dient dazu, die gesamte Ergebnismenge in Bereiche zu unterteilen, innerhalb derer bestimmte Berechnungen gemacht werden, wie z.B. das älteste Anstellungsdatum je Abteilung oder auch die Anzahl der Mitarbeiter je Abteilung. Ohne „PARTITION BY“ wird die gesamte Ergebnismenge als eine Partition betrachtet.
• ORDER BY: „ORDER BY“ dient – wie man sich unschwer denken kann – der Sortierung innerhalb der Ergebnismengen. Und das jeweils innerhalb der durch „PARTITION BY“ vorgegebenen Teilergebnismengen. Ein Beispiel weiter unten soll das gleich verdeutlichen.
• Windowing Clause: Dient der Definition von Betrachtungsfenstern innerhalb der mit „PARTITION BY“ definierten und „ORDER BY“ sortierten Teilmengen. Ein typisches Anwendungsbeispiel ist Bildung sog. gewichteter Mittelwerte. Die Windowing-Clause wird in diesem einführenden Artikel nicht weiter behandelt.

Analytische Funktionen sind nicht nur mit den üblichen Aggregatsfunktionen, sondern auch in zahlreichen weiteren SQL-Funktionen verwendbar. Siehe in der Oracle-Dokumentation hier, hier oder auch hier.

Schauen wir uns nun ein Beispiel an, in dem die analytische Klausel ein „ORDER BY“ enthält (und in diesem Fall sogar enthalten muss):

EMPNO	ENAME	DEPTNO	HIREDATE	COUNTER_PER_DEPT
7782	CLARK	10	09.06.1981	1
7839	KING	10	17.11.1981	2
7934	MILLER	10	23.01.1982	3
7369	SMITH	20	17.12.1980	1
7566	JONES	20	02.04.1981	2
7902	FORD	20	03.12.1981	3
7788	SCOTT	20	19.04.1987	4
7876	ADAMS	20	23.05.1987	5
7499	ALLEN	30	20.02.1981	1
7521	WARD	30	22.02.1981	2
7698	BLAKE	30	01.05.1981	3
7844	TURNER	30	08.09.1981	4
7654	MARTIN	30	28.09.1981	5
7900	JAMES	30	03.12.1981	6

ROW_NUMBER ist eine analytische Funktion, die ich sehr häufig verwende. Sie zählt alle Datensätze innerhalb einer Teilergebnismenge in der durch „ORDER BY“ vorgegeben Reihenfolge durch. Leider lassen sich Analytische Funktionen nicht in Where-Klauseln verwenden. Dies lässt sich aber einfach durch folgendes Konstrukt (Inline-View) umgehen:

EMPNO	ENAME	DEPTNO	HIREDATE	COUNTER_PER_DEPT
7782	CLARK	10	09.06.1981	1
7369	SMITH	20	17.12.1980	1
7499	ALLEN	30	20.02.1981	1

Wenn also die Frage z.B. lautet: Gib mir eine Liste der jeweils dienstältesten Mitarbeiter je Abteilung, so ist das mithilfe von ROW_NUMBER (ggf. auch RANK, DENSE_RANK oder COUNT) schnell gemacht.

Diese Art der Abfragen kommen oft vor: Zeige mir alle Kunden mit ihren jeweils ersten oder neuesten Bestellungen? Gib mir zu jedem Mitarbeiter die jeweils erste oder letzte Gehaltszahlung aus? Derartige Fragen haben Sie bestimmt auch schon gehabt und ohne Analytische Funktionen nur deutlich komplizierter und inperformanter lösen können.

Das „ORDER BY“ gibt in diesem Beispiel übrigens nicht unbedingt die Ausgabereihenfolge vor, sondern nur die Reihenfolge, in der gezählt wird. Das Statement könnte parallel ein abschließendes „order by“ enthalten, das die gesamte Ausgabe sortiert, ohne dass dies die Zählreihenfolge beeinflusst.

Ein paar weitere praktische Beispiele

Wenn Sie erst mal die ungewohnte Syntax verinnerlicht haben, werden Sie feststellen, welche enorme Vielfalt an Anwendungsmöglichkeiten Analytische Funktionen bieten. Nachfolgend ein paar typische Einsatzszenarien.

LEAD und LAG

Kennen Sie folgendes Problem? Sie arbeiten (mit welcher Programmiersprache auch immer) eine sortierte Menge an Records durch und möchten vor jedem Gruppenwechsel eine bestimmte Aktion auslösen. Leider bekommen Sie den Gruppenwechsel immer erst bei dem ersten Satz der nachfolgenden Gruppe mit. Hier kann folgendes helfen:

 

EMPNO	ENAME	HIREDATE	DEPTNO	NEXT_DEPTNO
7782	CLARK	09.06.1981	10	10
7839	KING	17.11.1981	10	10
7934	MILLER	23.01.1982	10	20
7369	SMITH	17.12.1980	20	20
7566	JONES	02.04.1981	20	20
7902	FORD	03.12.1981	20	20
7788	SCOTT	19.04.1987	20	20
7876	ADAMS	23.05.1987	20	30
7499	ALLEN	20.02.1981	30	30
7521	WARD	22.02.1981	30	30
7698	BLAKE	01.05.1981	30	30
7844	TURNER	08.09.1981	30	30
7654	MARTIN	28.09.1981	30	30
7900	JAMES	03.12.1981	30

Die Funktion LEAD holt sich (ohne explizite Angabe der Sprungweite) einen bestimmten Wert aus der jeweils nächsten Zeile. In dem Beispiel steht also immer ein Gruppenwechsel an, wenn DEPTNO und NEXT_DEPTNO (also die DEPTNO aus der nächsten Zeile) nicht gleich sind – und Sie erkennen somit einen Gruppenwechsel schon in der letzten Zeile einer Gruppe und nicht erst danach. Sehr praktisch.

Die Funktion LAG macht übrigens etwas ähnliches wie LEAD, nur dass sie sich einen Wert nicht aus dem nächsten Datensatz, sondern aus einem vorangegangen Datensatz holt.

Sie können bei LEAD und LAG sogar die Sprungweite angeben, sodass Sie nicht einen Wert aus dem nächsten oder vorangegangenen Datensatz holen, sondern auch weiter springen können. Probieren Sie einfach mal folgendes Beispiel:

SUM, COUNT, AVG, MIN und MAX

Die klassischen SQL-Aggregatfunktionen gibt es in Oracle alle auch in einer Variante als Analytische Funktion. Hier ein Beispiel, das die Funktionen SUM und COUNT verwendet:

EMPNO	ENAME	HIREDATE	DEPTNO	SAL	ANZAHL	ZAEHLER	SUMME	SUMME_LAUF
7782	CLARK	09.06.1981	10	2450	3	1	8750	2450
7839	KING	17.11.1981	10	5000	3	2	8750	7450
7934	MILLER	23.01.1982	10	1300	3	3	8750	8750
7369	SMITH	17.12.1980	20	800	5	1	10875	800
7566	JONES	02.04.1981	20	2975	5	2	10875	3775
7902	FORD	03.12.1981	20	3000	5	3	10875	6775
7788	SCOTT	19.04.1987	20	3000	5	4	10875	9775
7876	ADAMS	23.05.1987	20	1100	5	5	10875	10875
7499	ALLEN	20.02.1981	30	1600	6	1	9400	1600
7521	WARD	22.02.1981	30	1250	6	2	9400	2850
7698	BLAKE	01.05.1981	30	2850	6	3	9400	5700
7844	TURNER	08.09.1981	30	1500	6	4	9400	7200
7654	MARTIN	28.09.1981	30	1250	6	5	9400	8450
7900	JAMES	03.12.1981	30	950	6	6	9400	9400

Das SQL-Statement verwendet die Funktionen SUM und COUNT mal mit und mal ohne „ORDER BY“ und das macht offensichtlich jeweils einen großen Unterschied.

• COUNT(*) ohne  „ORDER BY“: berechnet die Anzahl der Zeilen je Teilmenge (Abteilung).
• COUNT(*) mit  „ORDER BY“: bewirkt das gleiche wie die oben beschriebene ROW_NUMBER-Funktion.
• SUM(SAL) ohne „ORDER BY“: berechnet die Summe des Gehalts je Teilmenge (Abteilung).
• SUM(SAL) mit „ORDER BY“: berechnet die laufende Summe des Gehalts je Teilmenge (Abteilung).

Spielen Sie einfach mal mit weiteren Aggregatfunktion bzw. ihren Varianten als Analytische Funktion. Ihnen werden mit Sicherheit schnell konkrete Anwendungsbeispiele einfallen.

RATIO_TO_REPORT

Die Analytische Funktion RATIO_TO_REPORT wird ebenfalls oft verwendet. Folgendes Beispiel verdeutlicht die Funktionalität:

EMPNO	ENAME	HIREDATE	DEPTNO	SAL	PROZ_GEHALTS_ANTEIL	SUMME
7782	CLARK	09.06.1981	10	2450	28	8750
7839	KING	17.11.1981	10	5000	57,14	8750
7934	MILLER	23.01.1982	10	1300	14,86	8750
7566	JONES	02.04.1981	20	2975	27,36	10875
7902	FORD	03.12.1981	20	3000	27,59	10875
7876	ADAMS	23.05.1987	20	1100	10,11	10875
7369	SMITH	17.12.1980	20	800	7,36	10875
7788	SCOTT	19.04.1987	20	3000	27,59	10875
7521	WARD	22.02.1981	30	1250	13,3	9400
7844	TURNER	08.09.1981	30	1500	15,96	9400
7499	ALLEN	20.02.1981	30	1600	17,02	9400
7900	JAMES	03.12.1981	30	950	10,11	9400
7698	BLAKE	01.05.1981	30	2850	30,32	9400
7654	MARTIN	28.09.1981	30	1250	13,3	9400

Mit RATIO_TO_REPORT wird der Anteil eines Wertes an der Gesamtsumme je Teilmenge berechnet. In dem Beispiel also der Gehaltsanteil je Mitarbeiter an dem Gesamtgehalt der jeweiligen Abteilung. Wenn Sie einfach nur „RATIO_TO_REPORT(sal) over ()“ verwenden, die Analytische Klausel also leer lassen, würde der Gehaltsanteil je Mitarbeiter an dem Gesamtgehalt der Firma berechnet werden.

Fazit

Die Liste der Anwendungsbeispiele für die verschiedenen Analytischen Funktionen ließe sich beliebig fortsetzen. Insbesondere im Data Warehouse-Bereich und für statistische Auswertungen haben Sie enorme Möglichkeiten. Sie werden sich – so wie ich vor ein paar Jahren – schnell fragen, wie Sie SQL bislang ohne Analytischen Funktionen verwenden konnten.

Ich kann nur empfehlen, sich mal ein paar Stunden Zeit zu nehmen, die obigen Anwendungsbeispielen auszuprobieren, zu variieren und natürlich die Oracle-Dokumentation zu diesem Thema anzuschauen. Sie werden feststellen, dass dieser Blog-Beitrag nur die Oberfläche des Themas ankratzt. Die dargestellten Möglichkeiten, eine Vielzahl statistischer Funktionen und die hier noch nicht behandelte Windowing Clause machen Analytischen Funktionen zu einem der mächtigsten Werkzeuge innerhalb von Oracle-SQL.