So nutzen Sie Window Functions in SQLScript

In diesem Artikel definieren wir zunächst den Begriff der Window Functions und beschreiben ihre Funktionsweise. Anschließend analysieren wir die Syntaxelemente dieser Funktionsgruppe und kombinieren die aufgezeigten Aspekte in einer Fallstudie.

Dabei nutzen wir einen homogenen Datensatz und arbeiten stets am Beispiel der Summierungsfunktion.

Im Appendix finden Sie eine Auswahl anderer, nützlicher Window Functions, sowie einen Download aller in diesem Artikel genutzten Tabellen, Datensätze und Coding Beispiele - so können Sie im eigenen System darauf aufbauen.

Window Functions sind ein Bestandteil aller modernen SQL Dialekte, so auch SQLScript.

Wie der Name suggeriert, handelt es sich dabei um einen Befehl, der ein virtuelles “Fenster”, sprich eine Partition, innerhalb des SELECT Befehls generiert. Dieses Fenster stellt eine Teilmenge des gesamten SELECT dar. Basierend auf der gewählten Funktion, wird innerhalb dieser Teilmenge eine entsprechende Aggregation, beispielsweise Summierung, durchgeführt.

Zur Veranschaulichung dient ein kurzes Beispiel:

Einzelbuchungen von Kosten mit zugehörigen Kostenstellen und Kostenstellengruppen.

Das Ergebnis der Window Function wird als Spalte mit dem Namen “CCGRPCOST” ausgegeben. Dafür wird die Summe aller Kosten (Gelb markiert) innerhalb einer Kostencenter Gruppe (Blau markiert) summiert.

Bei einer herkömmlichen Summenfunktion würde nur eine Zeile ausgegeben werden, wohingegen bei einer Window Function alle Zeilen ausgegeben werden, was eine größere Flexibilität bei dem Aufbau von SQL-Abfragen ermöglicht.

Aufbau der Syntax

Zur Verdeutlichung der Syntax wird das obige Beispiel weitergeführt:

Syntax Beispiele von Window Functions mit steigender Komplexität.

BASICS: AGGREGATION() OVER(PARTITION BY …)

Zunächst wird die Aggregationsfunktion definiert und, falls nötig, Parameter übergeben - hier sum(“COST”) - sprich die Summe der Spalte “COST”.

Als nächstes folgt der Befehl OVER(PARTITION BY … ) . Dadurch werden die Fenster definiert, auf welche die Aggregationsfunktion angewandt wird. So definieren wir hier die Spalte “COSTCENTREGROUPTEXT” als Fenster. Entsprechend wird die Summe für jeden einzigartigen Wert dieser Spalte separat berechnet.

Zwar lässt sich eine reguläre Summe bereits mit der obigen Syntax bilden, um jedoch eine laufende Summe zu berechnen, muss ein weiteres Syntaxelement hinzugezogen werden:

ORDER BY … [ASC | DESC]

Ähnlich wie in der WHERE Klausel einer simplen SQL Abfrage, so kann auch in einer Window Function ein solches ORDER BY Stichwort verwendet werden. Damit wird die Sortierung innerhalb des Fensters definiert, was Auswirkungen auf die Berechnung hat. Im Falle der Summenfunktion wird dadurch eine laufende Summe generiert.

ROWS BETWEEN … PRECEDING AND … FOLLOWING

Ein weiteres Element ist das ROWS Stichwort. Durch Anpassung der Argumente dieses Stichworts können noch komplexere Sachverhalte modelliert und berechnet werden. Entsprechend wird hier mittels ROWS BETWEEN 2 PRECEDING AND CURRENT ROW die Summe der zwei vorhergehenden Zeilen plus der aktuellen Zeile gebildet.

Steigern Sie die Leistung Ihres BW mit SQLScript! Hier geht's zum Whitepaper

Fallstudie: Kostenanalyse

Zuletzt beleuchten wir die aufgezeigten Konzepte anhand eines konkreten Anwendungsfalls.

Dafür betrachten wir eine initiale Fragestellung und die Entwicklung einer passenden Lösung. Wir arbeiten hierfür mit dem selben Datensatz der vorhergehenden Beispiele.

Szenario:

Im gegebenen Monat wurden mehr Kosten verursacht als das Budget eigentlich zulässt. In dem Versuch ein Sparpotenzial aufzuzeigen, möchte die Controlling Abteilung analysieren, welche die jeweils größten Kostenpunkte eines Bereichs sind. Dabei soll eine kleinere Abteilung mit weniger Budget nicht übermäßig benachteiligt werden, weshalb die Kostenpunkte in ein Verhältnis zu den Gesamtkosten der Abteilung gesetzt werden.

In dem verwendeten HANA SQL Data Warehouse gibt es drei Tabellen, die relevante Daten enthalten:

Zunächst bilden wir einen Prototypen in der Form einer SQL Abfrage und joinen die obigen Tabellen anhand ihrer gemeinsamen Fremdschlüssel und Primärschlüssel Paare. Dabei vergeben wir sinnvolle Aliase und grenzen die Auswahl im SELECT auf relevante Spalten ein.

Nun bilden wir die eigentliche Kennzahl.

Schlüsseln wir den Code etwas auf und beginnen mit den simplen Befehlen:

Die 1. und 4. Zeile dienen der Formatierung als Prozentzahl mit zwei Nachkommastellen und die 5. Zeile benennt die Spalte, um Sie bei der Ausgabe lesbarer zu gestalten.

Für den Wert selbst, verwenden wir in der zweiten Zeile die bereits gegebene Spalte cost.“COST” als Zähler und dividieren diese durch den Nenner in der 3. Zeile.

Der Nenner in der 3. Zeile wird mittels einer Window Function realisiert, welche die Summe der cost.”COST” Spalte je ccgrp.”COSTCENTREGROUPID” bzw. die Summe der Kosten je Kostencenter Gruppe berechnet.

Letztendlich erhalten wir dadurch einen prozentualen Wert, der uns den Anteil der jeweiligen Kosten an den Gesamtkosten der zugehörigen Kostencenter Gruppe zeigt.

Anschließend sortieren wir das Ergebnis mittels des ORDER BY Befehls und erhalten folgendes Ergebnis:

Durch diese Analyse konnten wir aufzeigen, dass die “COSTLINEITEMID” bzw. der Kostenpunkt mit der ID ‘49’ als Priorität bei den Sparmaßnahmen gelten sollte.

Die Entscheidungsträger sind mit der Analyse zufrieden, weshalb wir unseren SQL Code als Calculation View bereitstellen. Dadurch ist dieser als wiederverwendbares Objekt für analoge, zukünftige Analysen verfügbar und kann in einem Frontend Tool wie Tableau problemlos eingebunden, visualisiert und geteilt werden.

Appendix

Eine Vielzahl an Window Functions stehen zur Verfügung, um verschiedenartige Aggregationen zu ermöglichen. Eine Auswahl von nützlichen Varianten aus unserer Beratungspraxis finden Sie im Folgenden:

Den Download der Tabellen, Datensätze und Coding Beispiele finden Sie über folgenden Link.

Haben Sie Fragen zu SAP HANA SQLScript? Versuchen Sie das nötige Know-How in Ihrer Abteilung aufzubauen oder benötigen Sie Unterstützung bei einer konkreten Fragestellung? Wir helfen Ihnen gerne dabei. Fordern Sie noch heute ein unverbindliches Beratungsangebot an!