single.php
< Beitrag von Frank Winter

Analytische Funktion in Oracle-SQL

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.

 

 

Folgen
X

Folgen

E-mail : *
Kategorie: Oracle-Development | | Kommentare: 1

Beitrag kommentieren

CAPTCHA * Time limit is exhausted. Please reload CAPTCHA.

Kommentare

  • 08.07.2016 von Michael Dick

    Ein wirklich gelungener Artikel mit sehr anschaulichen Beispielen. Auch ich habe um die Analytischen Funktionen immer eine Bogen gemacht. Bis heute, wo ich ohne ROW_NUMBER an meine Grenzen gekommen bin. Dank Ihnen habe ich nun den ersten Schritt gemacht. Vielen Dank und Viele Grüße aus Berlin