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:

EMPNOENAMEDEPTNOHIREDATEMIN_DEPT_HIREDATE
7782CLARK1009.06.198109.06.1981
7839KING1017.11.198109.06.1981
7934MILLER1023.01.198209.06.1981
7566JONES2002.04.198117.12.1980
7902FORD2003.12.198117.12.1980
7876ADAMS2023.05.198717.12.1980
7369SMITH2017.12.198017.12.1980
7788SCOTT2019.04.198717.12.1980
7521WARD3022.02.198120.02.1981
7844TURNER3008.09.198120.02.1981
7499ALLEN3020.02.198120.02.1981
7900JAMES3003.12.198120.02.1981
7698BLAKE3001.05.198120.02.1981
7654MARTIN3028.09.198120.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):

EMPNOENAMEDEPTNOHIREDATECOUNTER_PER_DEPT
7782CLARK1009.06.19811
7839KING1017.11.19812
7934MILLER1023.01.19823
7369SMITH2017.12.19801
7566JONES2002.04.19812
7902FORD2003.12.19813
7788SCOTT2019.04.19874
7876ADAMS2023.05.19875
7499ALLEN3020.02.19811
7521WARD3022.02.19812
7698BLAKE3001.05.19813
7844TURNER3008.09.19814
7654MARTIN3028.09.19815
7900JAMES3003.12.19816

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:

EMPNOENAMEDEPTNOHIREDATECOUNTER_PER_DEPT
7782CLARK1009.06.19811
7369SMITH2017.12.19801
7499ALLEN3020.02.19811

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:

 

EMPNOENAMEHIREDATEDEPTNONEXT_DEPTNO
7782CLARK09.06.19811010
7839KING17.11.19811010
7934MILLER23.01.19821020
7369SMITH17.12.19802020
7566JONES02.04.19812020
7902FORD03.12.19812020
7788SCOTT19.04.19872020
7876ADAMS23.05.19872030
7499ALLEN20.02.19813030
7521WARD22.02.19813030
7698BLAKE01.05.19813030
7844TURNER08.09.19813030
7654MARTIN28.09.19813030
7900JAMES03.12.198130 

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:

EMPNOENAMEHIREDATEDEPTNOSALANZAHLZAEHLERSUMMESUMME_LAUF
7782CLARK09.06.19811024503187502450
7839KING17.11.19811050003287507450
7934MILLER23.01.19821013003387508750
7369SMITH17.12.1980208005110875800
7566JONES02.04.198120297552108753775
7902FORD03.12.198120300053108756775
7788SCOTT19.04.198720300054108759775
7876ADAMS23.05.1987201100551087510875
7499ALLEN20.02.19813016006194001600
7521WARD22.02.19813012506294002850
7698BLAKE01.05.19813028506394005700
7844TURNER08.09.19813015006494007200
7654MARTIN28.09.19813012506594008450
7900JAMES03.12.1981309506694009400

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:

EMPNOENAMEHIREDATEDEPTNOSALPROZ_GEHALTS_ANTEILSUMME
7782CLARK09.06.1981102450288750
7839KING17.11.198110500057,148750
7934MILLER23.01.198210130014,868750
7566JONES02.04.198120297527,3610875
7902FORD03.12.198120300027,5910875
7876ADAMS23.05.198720110010,1110875
7369SMITH17.12.1980208007,3610875
7788SCOTT19.04.198720300027,5910875
7521WARD22.02.198130125013,39400
7844TURNER08.09.198130150015,969400
7499ALLEN20.02.198130160017,029400
7900JAMES03.12.19813095010,119400
7698BLAKE01.05.198130285030,329400
7654MARTIN28.09.198130125013,39400

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 | | 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