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:

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

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:

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:

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:

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:

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

Folgen abbestellen