Die HTML DB wird recht häufig genutzt, um einfache Berichte möglichst schnell im Web bereitzustellen. Fachanwender wünschen sich jedoch recht häufig statistische Berechnungen - die einfache Darstellung der Daten reicht meistens nicht aus. Gängige Anforderungen sind ...

• Gleitende Durchschnitte
• Rankings 
• laufende Aufsummierung von Werten 
• Statistische Analysen (Regression)

Diese Funktionen kann man natürlich mit PL/SQL oder mit anderen Mitteln programmieren. Der heutige Tipp zeigt Ihnen, wie Sie diese Anforderungen ohne Umwege nur mit einer SQL-Abgrage erfüllen können. Dazu werden die seit Oracle9i verfügbaren Analytischen Funktionen verwendet. In diesem Tipp erfahren Sie, wie Sie mit den analytischen Funktionen ein Query Window definieren und gleitende Durchschnitte oder Rankings berechnen können. Im nächsten Tipp erfahren Sie, wie Sie anspruchsvolle Analysen (bspw. lineare Regression) durchführen können.

Die Ausgangdaten sind Umsätze, Ausgaben für Werbung sowie die Anzahl der Verkäufer einer virtuellen Firma. Sie sind in einer Tabelle (VERKAUF) enthalten. Die folgenden Beispiele lassen sich leicht auf andere Daten anwenden. Wenn Sie keine anderen Daten haben, können Sie das SQL-Skript herunterladen.

    JAHR    MONAT UMSATZ_EUR WERBUNG_TV_EUR WERBUNG_ZEITG_EUR ANZAHL_VERKAEUFER
-------- -------- ---------- -------------- ----------------- -----------------
    2004       11     130000          14000               400                10
    2004       12     140000          13200               700                10
    2005        1     100000           9000              1000                10
    2005        2     103000           9500               900                10
    2005        3     112000          10000              1500                10
    2005        4      90000          12000              1000                 8
    2005        5      98000          13000              1000                 8
    2005        6      70000           4000              5000                 9
    2005        7     109010          12000              2000                15
    2005        8     120000          10000               500                15
    2005        9     130000           9000              1000                15
    2005       10     150000          15000              1000                15
    2005       11     170000          18000              1000                15
    2005       12     200000          18000              1000                15
    2006        1     160000          10000              1000                10
    2006        2     140000          10200              1500                10

Abb. 1: Ausgangssituation

Dem Bericht liegt die einfache SQL-Abfrage zugrunde ...

select MONAT, UMSATZ_EUR, WERBUNG_TV_EUR, WERBUNG_ZEITG_EUR, ANZAHL_VERKAEUFER
from VERKAUF

Zunächst sollen dem Bericht und der Abfrage ein gleitender Durchschnitt über die Umsätze mit einer Periode von einem Quartal hinzugefügt werden. Diese Aufgabe wird mit analytischen Funktionen gelöst. Man könnte die Aufgabe auch mit einfachem SQL, der AVG()-Funktion und GROUP BY lösen, das SQL wäre jedoch sehr komplex mit vielen Sub-Selects - schwer verständlich und kaum noch wartbar. Analytische Funktionen haben daneben jedoch einen weiteren, gewichtigen Vorteil: Als SQL-Kommandos werden Sie vom Query Optimizer der Datenbank berücksichtigt. Der Optimizer kann eine für die gewünschte Funktion optimale Ausführungsstrategie bestimmen und alle Möglichkeiten der Datenbank (parallele Ausführung, Nutzung von Indizes, ...) nutzen. Wird die Funktionalität in der Mittelschicht oder mit eigenem PL/SQL-Code nachprogrammiert, so gibt man diese Möglichkeiten auf - Die Abfragen dürften tendenziell langamer laufen.

Als erstes wird nun das Konzept des Query Window vorgestellt. Beachten Sie bitte, das dies das logische Konzept ist - zur tatsächlichen Ausführung einer analytischen Funktion bedient sich die Datenbank anderer Strategien. Wenn man sich vorstellt, dass eine SQL-Abfrage eine Ergebnismenge zurückliefert und ein Cursor sich Zeile für Zeile durch diese Ergebnismenge bewegt, dann ist das Query Window ein Teilbereich dieser Ergebnismenge - immer abhängig von der Zeile, auf der sich der Cursor gerade befindet. Um den gleitenden Durchschnitt zu realisieren, definieren wir also ein Query Window, welches stets eine Zeile "vor dem Cursor" beginnt und eine Zeile "danach" endet. Formuliert als SQL-Abfrage sieht das wie folgt aus:

select MONAT, UMSATZ_EUR, WERBUNG_TV_EUR, WERBUNG_ZEITG_EUR, ANZAHL_VERKAEUFER, 
 AVG(UMSATZ_EUR) over (order by MONAT rows between 1 preceding and 1 following) GL_DS_UMSATZ
from VERKAUF

Für die Berechnung eines Durchschnitts wird die AVG()-Funktion verwendet. Das Query Window wird anhand der sortierten Spalte Monat definiert; es reicht wie gefordert immer von der ersten Zeile vor dem Cursor bis zur ersten Zeile nach dem Cursor. In der HTML DB-Anwendung (Bericht und Diagramm) sieht das Ergebnis dann wie folgt aus: 

Wenn Sie die analytischen Funktionen direkt im SQL des HTML DB-Berichts verwenden, müssen Sie die "automatische Sortierung bei Klick auf die Spaltenüberschriften" abschalten, da die HTML DB in diesem Fall kein ORDER BY in der SQL-Anweisung zulässt. Wenn Sie die automatische Sortierung dennoch benötigen, definieren Sie die SQL-Abfrage mit der analytischen Funktion im SQL Workshop als View und fragen im HTML DB-Bericht einfach nur diese View ab.

Als nächstes sollen die Umsätze in jedem Jahr mit jedem Monat aufsummiert (kumuliert) werden. Die Definition des Query Window wird nun etwas anspruchsvoller. Denn es muss für jedes Jahr im Januar beginnen und bei der jeweils "aktuellen" Zeile enden. Um dies abzubilden, wird ein neues Syntaxelement der analytischen Funktion genutzt: die Partition. 

select MONAT, UMSATZ_EUR, WERBUNG_TV_EUR, WERBUNG_ZEITG_EUR, ANZAHL_VERKAEUFER, 
 SUM(UMSATZ_EUR) over (
 partition by JAHR order by MONAT
 rows between unbounded preceding and current row
 ) as KUM_UMSATZ
from VERKAUF

Mit PARTITION BY JAHR wird die Ergebnismenge logisch unterteilt.  Die Definition des Query Window wird dann auf jede (logische) Partition angewendet. In jeder Partition beginnt das Query Window dann in der ersten auftretenden Zeile (UNBOUNDED PRECEDING) und es endet bei der "aktuellen" Zeile (CURRENT ROW). Auf Mehrprozessormaschinen kann die analytische Funktion über die Partitionen parallel ausgeführt werden. Übrigens: Die PARTITIONS der analytischen Funktionen sind völlig unabhängig von der (speichertechnischen) Partitionierung einer Tabelle (Oracle Partitioning Option). Das Ergebnis stellt sich in der HTML DB wie folgt dar.

Abb. 3: Umsatzdaten mit kumulierten Umsätzen

Der Übersichtlichkeit halber wurde der gleitende Durchschnitt wieder aus dem Bericht entfernt. Für die kumulierten Umsätze wurde ein neues Diagramm hinzugefügt. 
Als letztes Beispiel sollen die Umsätze als Prozentwerte vom Jahresumsatz dargestellt werden. Die dazu verwendete analytische SQL-Funktion ist RATIO_TO_REPORT(). Wiederum wird nach JAHR "partitioniert". Das jeweilige Query Window beinhaltet dabei das gesamte Jahr; wird also nicht spezifiziert. Die SQL-Abfrage sieht demnach wie folgt aus:

select MONAT, UMSATZ_EUR, WERBUNG_TV_EUR, WERBUNG_ZEITG_EUR, ANZAHL_VERKAEUFER, 
 sum(UMSATZ_EUR) over (
 partition by jahr order by monat
 rows between unbounded preceding and current row
 ) as KUM_UMSATZ,
 ratio_to_report(UMSATZ_EUR) over (
 partition by jahr
 ) as UMSATZ_PERCENT
from VERKAUF

In der nächsten Ausgabe erfahren Sie, wie Sie mit analytischen Funktionen anspruchsvolle Analysen wie lineare Regression durchführen können. Weitere Informationen über analytische Funktionen finden Sie in der SQL Reference oder dem Data Warehousing Guide der Oracle-Dokumentation.

Zurück zur Community-Seite