SQL OLAP Funktion

Aus BI-Snippets - Business Intelligence Code und Module
Wechseln zu: Navigation, Suche

OLAP Funktion

Die OLAP Funktion, oder auch als "Window Funktion" bezeichnet SQL Funktion, unterstützt Anwender bei der Umsetzung ansonsten komplexer Queries. Der folgende Artikel soll diese Funktion etwas näher bringen und zur Nutzung dieser in verschiedenen Szenarien anregen. OLAP Funktionen sind kein Allheimittel und sind nicht in jedem Szenario sinnvoll an zu wenden. Nutzt man die Möglichkeiten der Funktion jedoch an den richtigen Stellen, führt dies zu höherer Lesbarkeit und kürzeren Abfragen.

Einsatzszenario

  • Erfolgreichster Verkäufer einer Region
  • Wertvollster Kunde pro Monat
  • Durchschnittliche Kosten eines Produktes pro Kategorie XYZ
  • Umsatzdifferenz der letzten drei Jahre
  • Kumulierte Kosten seit Anfang des Jahres

... und noch viele weitere. Die Erläuterung der OLAP Funktion wird deutlich machen warum die aufgeführten Szenarien mit OLAP sehr einfach zu lösen sind und welche Möglichkeiten diese darüber hinaus bieten.

Syntax

Anhand einer exemplarischen Abfrage wird die grundsätzliche Funkion näher gebracht. Die einzelnen Komponenten der Query werde ich Stück für Stück erläutern.

SUM(salary) OVER (PARTITION BY employee ORDER BY year DESC BETWEEN 1 FOLLOWING AND 1 FOLLOWING)

sum(salary)

Das Ziel der OLAP Funktion ist einen Wert innerhalb eines bestimmten Fensters zu definieren. Welche Größe und Attribute das Fenster berücksichtigt ist erst einmal zweitrangig. Im ersten Schritt muss man nur definieren, welche Operation auf das gewählte Fenster angewandt werden soll und welches Feld diese Operation betrifft.

partition by

Im nächsten Schritt werden die Attribute des Fensters definiert, welche den Rahmen bilden. In diesem Falle ist das nur der employee, es ist aber auch eine Reihe Attribute möglich. Jedes Fenster ist nun so groß, wie es in der Relation gleiche Ausprägungen auf allen angegebenen Partitionen gibt.

order by

Nachdem das Ziel der Aggregation und die Attribute des Fensters definiert sind, wird die Reihenfolge der Betrachtung festgelegt. In dem gewählten Beispiel wird der Angestellte über die Jahre sortiert von betrachtet. In dem Fenster ist das aktuelle Jahr daher das erste und das Jahr der Einstellung wahrscheinlich das letzte. Diese Eigenschaft ist insbesondere für die nächste optionale Angabe relevant.

rows

Im Gegensatz zu den oberen Befehlen, ist dieser optional. Die unter "partition by" angegebenen Attribute grenzen das betrachtete Fenster bereits auf die gleichen Ausprägungen auf allen angegebenen Attributen ein. Dieses Fenster können wir nun noch weiter einschränken. Mögliche Angaben sind:

  • between
  • following / preceding
  • 1...XXX / unbounded

Mögliche Kombinationen sind daher

rows between 1 following and 1 following

Diese Abfrage berücksichtigt nur die kommende Zeile. In dem gewählten Szenario ist dies das Gehalt des Vorjahres, da das Jahr absteigend sortiert wurde. Ohne diese Einschränkung würde nicht das Vorjahresgehalt ausgegeben, sondern das Gesamtgehalt über alle Jahre je Mitarbeiter. Für eine kumulierte Betrachtung wäre aber auch folgende Abfrage möglich

rows unbounded following

Beispiel

Folgendes Beispiel veranschaulicht die oben beschrieben OLAP Anwendung:

Abfrage

SELECT employee, year, salary,
       SUM(salary) OVER (PARTITION BY employee 
                         ORDER BY year DESC
                         BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
FROM db.employee_salary
ORDER BY year desc;


Resultset

RowNo employee year salary salary_prev_year
1
Hans
2015
100 €
120 €
2
Hans
2014
120 €
110 €
3
Hans
2013
110 €

4
Anja
2015
160 €
200 €
5
Anja
2014
200 €
150 €
6
Anja
2013
150 €

Zusätzliche Anmerkungen

OLAP Funktionen werden stets auf das fertige Resultset angewandt. Daher sind alle anderen Operationen bereits durchgeführt und neu berechnete Attribute stehen der OLAP Funktion ebenfalls zur Verfügung.

Zum einen können OLAP Funktionen genutzt werden, um eine zusätzliche Spalte an das Resultset zu hängen, die bspw. kumulierte Werte enthält. Es ist aber in den meisten SQL Produkten möglich eine nachträgliche Eingrenzung auf das Resultset durch zu führen.

Will man beispielsweise neben dem Vorjahresgehalt auch noch das Durchschnittsgehalt eines Mitarbeiters mit dem aktuellen Vergleichen, so kann man auch dieses in derselben Abfrage tun:

SELECT employee, year, salary,
       SUM(salary) OVER (PARTITION BY employee 
                         ORDER BY year DESC
                         BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS salary_prev_year,
       AVG(salary) OVER (PARTITION BY employee) as average_salary
FROM db.employee_salary
ORDER BY year desc;

Das Ergebnis sieht dann so aus:

RowNo employee year salary salary_prev_year average_salary
1
Hans
2015
100 €
120 €
110 €
2
Hans
2014
120 €
110 €
110 €
3
Hans
2013
110 €

110 €
4
Anja
2015
160 €
200 €
170 €
5
Anja
2014
200 €
150 €
170 €
6
Anja
2013
150 €

170 €
7 Karl 2014 300 €
300 €

Anders als für das Vorjahresgehalt werden hier sämtliche Zeilen des Fensters betrachtet. Das Ergebnis steht in jeder Zeile zur Verfügung und kann auch für weitere Berechnungen genutzt werden. Man könnte also die jeweilige Abweichung vom Durchschnittsgehalt, die Differenz zum Vorjahr usw. berechnen, ohne dass man hier die Tabelle gegen sich selbst joinen müsste.

qualify

Unter dem Stichwort qualify lassen sich SQL Abfragen mit der OLAP Syntax weiter eingrenzen.

SELECT employee, year, salary, AVG(salary) OVER (PARTITION BY employee)
FROM db.salary_per_year
QUALIFY ROW_NUMBER() OVER(PARTITION BY employee ORDER BY year DESC) = 1

Das hier aufgeführte Qualify schränkt mit der jeweiligen Operation das fertige Resultset der Abfrage ein. In diesem Fall werden Fenster für jeden Employee gebildet und absteigend über das Jahr sortiert. Dann wir jede Zeile mit einer aufsteigenden Nummer versehen. Das Resultset sähe wie folgt aus, wenn man kein Qualify einsetzt, sondern das Ergebnis der OLAP Funktion ausgeben lässt (Spalte: Qualified Row Number)

Row Number employee year salary average_salary Qualified Row Number
1 Hans 2015 10.000 € 9.750 €

1

2 Hans 2014 9.500 € 9.750 € 2
3  Martina 2015 12.000 € 12.000 € 1
4 Martina 2014 13.000 € 12.000 € 2
5 Martina 2013 11.000 € 12.000 € 3
6 Karl 2014 10.000 € 10.000 € 1


Der Stand beschreibt das Resultset vor der Anwendung des Qualify. Das finale Resultset besteht nur noch aus drei Zeilen. Die jenige mit dem höchsten Jahr für jeden employee. Für Hans und Martina ist dies das Jahr 2015, für Karl 2014.

Row Number employee year salary average_salary
1 Hans 2015 10.000 € 9.750 €
3 Martina 2015 12.000 € 12.000 €
6 Karl 2014 10.000 € 10.000 €

Das Qualify hat nur die Zeilen 1 und 4 aus dem obigen Resultset ausgewählt und ausgegeben. Die Row_number funktion gibt allen Tupeln innerhalb des Fensters eine Ordnungszahl und da wir mit " = 1" nur auf die erste Ordnungszahl eingescrhänkt haben enthält auch das Resultset nur noch eine Zeile pro Fenster. Der außerdem berechnete Durchschnitt hat sich hierbei aber nicht verändert. Die Zeilen werden erst nach der Berechnung des Resultats herausgefiltert.