Warum sollte ich SQL-Fensterfunktionen lernen?

SQL gibt es schon seit mehr als 25 Jahren. Die grundlegende Syntax - wie SELECT, WHERE, GROUP BY, HAVING und ORDER BY - ist allgemein bekannt. Aber gibt es noch etwas anderes als traditionelles SQL? Können Sie über die Grundlagen hinausgehen und Ihre Fähigkeiten verbessern?

Die Antwort auf diese Frage lautet: Ja. Es gibt eine moderne Version von SQL. In diesem Artikel werden wir in die moderne Version eintauchen und mehr über die SQL-Fensterfunktionen erfahren. Wenn Sie ein fortgeschrittener Anfänger sind und Daten mit Hilfe der SQL-Fensterfunktionen (auch analytische Funktionen genannt) analysieren wollen, ist dieser Artikel genau das Richtige für Sie.

Eine kurze Geschichte von SQL

Geschichte der SQL-Entwicklung - Standardisierung

SQL ist eine berühmte, aber sehr alte Sprache. Sie wurde in den 1970er Jahren von IBM eingeführt. Im Jahr 1986 verabschiedeten die ANSI- und ISO-Normengruppen offiziell eine Standarddefinition für die Datenbanksprache SQL (SQL-1986).

Im Jahr 1992 wurde der ursprüngliche Standard grundlegend überarbeitet (SQL-92). Das ist der Standard-SQL, den wir heute verwenden. Das ist richtig - es ist 28 Jahre her, dass SQL-92 offiziell verabschiedet wurde. Die grundlegenden Konzepte (SELECT, WHERE usw.) sind bekannt, ganz gleich, ob Sie Oracle, DB2, MySQL, PostgreSQL oder eine andere Datenbank verwenden. In jedem DBMS werden Sie aufgrund dieser Standardisierung ähnliche SQL-Anweisungen schreiben.

Die Grundkonzepte von SQL sind zwar alt, aber sehr nützlich. Ich würde sagen, sie sind universell, denn sie funktionieren unabhängig von der Branche, in der Sie sie anwenden. Wenn Sie SQL lernen oder sich als Analyst weiterentwickeln wollen, müssen Sie diese Konzepte lernen.

Dennoch hat sich die Computerwelt seit 1992 stark verändert. Sicherlich hat sich SQL seitdem weiterentwickelt? Ja, SQL hat sich weiterentwickelt, und es wurden neue Überarbeitungen der Standards vorgenommen. In den 2000er Jahren wurde das moderne SQL eingeführt, ein Konzept, das über die grundlegenden Anweisungen hinausgeht. Im Jahr 2003 wurden die SQL-Fensterfunktionen eingeführt. Lassen Sie uns über die Vorteile der SQL-Fensterfunktionen sprechen und warum es gut ist, sie zu kennen.

Zunächst jedoch ein Überblick über etwas, das Neulinge oft mit Fensterfunktionen verwechseln: SQL-Aggregatfunktionen.

Eine Auffrischung der Aggregatfunktionen

Wenn Sie mit herkömmlichem SQL vertraut sind, haben Sie wahrscheinlich schon Aggregatfunktionen verwendet , mit denen Sie Berechnungen über mehrere Zeilen durchführen und eine einzige Ausgabezeile oder ein einziges Ergebnis erhalten können. Vielleicht haben Sie zum Beispiel Summen oder Durchschnittswerte über eine Reihe von Zeilen berechnet oder die Anzahl der Zeilen pro Kategorie gezählt. In diesem Fall haben Sie zumindest einige der Aggregatfunktionen von SQL verwendet: SUM(), AVG(), MIN(), MAX() und COUNT(). Sie werden oft mit den GROUP BY- und HAVING-Klauseln in SELECT-Anweisungen verwendet.

Sehen wir uns ein Beispiel an: wie wir den Durchschnittspreis pro Gruppe von Zeilen mit GROUP BY berechnen können.

Wir werden Forex-Daten über Wechselkurse verwenden. Hier ist unsere Eingabetabelle:

Devisenkurse - Tabelle CURRENCYTRADE

Die Tabelle enthält die Schlusskurse für die Devisenmärkte GBP-USD und EUR-USD. In diesem Beispiel verwenden wir sechs Schlusskurse für jedes Währungspaar. Aus diesen Daten werden wir den durchschnittlichen Schlusskurs für jedes Währungspaar (GBPUSD, EURUSD) separat berechnen.

Hier ist die SELECT-Anweisung, die den durchschnittlichen Schlusskurs für jedes Währungspaar berechnet:

select ticker,avg(close) as average_price from CURRENCYTRADE group by ticker;

Die Abbildung unten zeigt das Ergebnis auf der rechten Seite:

Das Ergebnis wird als einzeilige Ausgabe für jedes Währungspaar dargestellt. Die Berechnungen (der Durchschnittswert für jedes Paar) wurden in sechs Zeilen für jedes Paar durchgeführt. Dies ist ein einfaches Beispiel für eine Aggregatfunktion.

Kommen wir nun zu den Fensterfunktionen.

Was ist eine SQL-Fensterfunktion?

Sind SQL-Fensterfunktionen mit der Syntax der Aggregate GROUP BY vergleichbar? Ich denke, ja. Wie eine Aggregatfunktion, die mit einer GROUP BY-Klausel verwendet wird, führt auch eine Fensterfunktion Berechnungen über eine Reihe von Zeilen durch. Das Ergebnis einer Fensterfunktion wird jedoch nicht als einzelne Ausgabezeile für jede Gruppe dargestellt, d. h., die Zeilen werden in der Ergebnistabelle nicht zusammengefaßt. Stattdessen wird jede Zeile aus der Eingabetabelle zurückgegeben.

Der Unterschied zwischen einer Aggregatfunktion und einer Fensterfunktion in SQL ist einfach. Eine Aggregatfunktion fasst alle Zeilen zu einem einzigen Ergebnis zusammen, was bedeutet, dass Sie keinen Zugriff auf die einzelnen Zeilen haben. Eine Fensterfunktion ermöglicht den Zugriff auf jede Zeile im definierten Fenster. Dies ist in der folgenden Abbildung dargestellt:

Unterschied zwischen SQL-Aggregat- und Fensterfunktionen

Um SQL-Fensterfunktionen zu lernen, empfehle ich den interaktiven Fensterfunktionen Kurs unter LearnSQL.de.

Kehren wir zu unserem Beispiel zurück. Wenn Sie diesen Teil des Codes ausführen ...

select *,avg(close) OVER(PARTITION BY ticker) AS average_closing from CURRENCYTRADE ;

... der durchschnittliche Schlusskurs für jedes Währungspaar wird jeder Zeile der Tabelle CURRENCYTRADE Tabelle zugeordnet.

Das Ergebnis sieht in etwa so aus:

Wie Sie sehen können, hat die Fensterfunktion die Ausgabe nicht in eine einzige Ausgabezeile pro Währungspaar gruppiert. Stattdessen enthält jede Zeile jetzt zusätzliche Informationen: den durchschnittlichen Schlusskurs für das entsprechende Währungspaar. Dies kann sehr nützlich sein, da für viele Analysen zusätzliche Informationen für jede Zeile erforderlich sind, wobei alle Spalten des ursprünglichen Datensatzes erhalten bleiben.

Syntax der Fensterfunktion

In unserem letzten Beispiel einer Fensterfunktion haben wir einige spezielle Schlüsselwörter wie OVER() und PARTITION BY verwendet:

select *,avg(close) OVER(PARTITION BY ticker) AS average_closing from CURRENCYTRADE ;

Dies sind die wichtigsten Schlüsselwörter, die eine Fensterfunktion definieren. Hier ist eine kurze Erklärung:

1. OVER bedeutet, dass es sich um eine Fensterfunktion handelt. Innerhalb der OVER-Klausel können wir PARTITION, ORDER BY und andere Fensterrahmenklauseln haben, die uns sagen, wie das Fenster eingerahmt ist (d. h. die Gruppen und wie sie angeordnet sind). Obwohl wir in unserem Beispiel die Fensterrahmenklausel (ROW oder RANGE) nicht verwendet haben, sollten Sie daran denken, dass Sie beide Klauseln innerhalb der OVER-Klausel verwenden können, um Rahmen innerhalb von Partitionen anzugeben.
2. PARTITION gibt an, wie die Daten gruppiert oder eingerahmt werden. Sie wird innerhalb der OVER-Klausel platziert. In unserem Beispiel haben wir den Durchschnittspreis für jedes Währungspaar berechnet; daher haben wir unser Fenster nach der Spalte Ticker partitioniert.
3. ORDER BY (das wir in unserem Beispiel nicht verwendet haben) wird häufig verwendet, um die Reihenfolge der Zeilen innerhalb jedes Rahmens zu bestimmen.
4. ROW oder RANGE wird verwendet, wenn wir die Zeilen innerhalb der Partition weiter einschränken wollen. Dies geschieht durch die Angabe des Anfangs- und Endpunkts innerhalb der Partition. Unabhängig davon, welche dieser beiden Klauseln Sie verwenden, muss sie mit ORDER BY verwendet werden. Die Syntax sieht wie folgt aus:

[ROWS | RANGE] BETWEEN  AND 

Die <starting_row> wird durch eine der folgenden Angaben gekennzeichnet:

• UNBOUNDED PRECEDING: Das Fenster beginnt in der ersten Zeile der Partition.
• AKTUELLE ZEILE: Das Fenster beginnt in der aktuellen Zeile.
• <unsigned integer literal> PRECEDING oder FOLLOWING.

Und die <end_row> verwendet eine der folgenden:

• UNBOUNDED FOLLOWING: Das Fenster endet in der letzten Zeile der Partition.
• AKTUELLE ZEILE: Das Fenster endet in der aktuellen Zeile.
• <Ganzzahliges Literal ohne Vorzeichen> PRECEDING oder FOLLOWING.

Weitere Beispiele für Fensterrahmenklauseln finden Sie hier.

Machen Sie sich keine Sorgen, wenn Sie sich mit dieser Syntax nicht wohl fühlen. Übung macht den Meister; ich kann den Kurs von LearnSQL.deempfehlen. Fensterfunktionen empfehlen, der eine Menge guter Informationen enthält.

Nachdem Sie nun gelernt haben, wie Sie SQL-Analysefunktionen in Ihren Abfragen verwenden können, wollen wir uns im nächsten Schritt die Arten von Fensterfunktionen ansehen, die in SQL verfügbar sind. Bis jetzt haben wir nur gezeigt, wie AVG() (eine Aggregatfunktion) als Fensterfunktion verwendet werden kann. Schauen wir uns nun an, was die anderen Funktionen können.

Typen von Fensterfunktionen

Es gibt drei Haupttypen von Fensterfunktionen:

• Aggregierte Fensterfunktionen: AVG(), MIN(), MAX(), COUNT(), SUM(). Diese Funktionen dienen zur Berechnung von Durchschnitts-, Minimal- oder Maximalwerten, der Gesamtzahl der Zeilen oder der Gesamtsumme innerhalb jedes definierten Rahmens. Aggregat-Fensterfunktionen geben einen einzelnen Wert für jede Zeile der zugrunde liegenden Abfrage zurück.

• Ranking-Fensterfunktionen: RANK(), ROW_NUMBER(), und ähnliche. Ranking-Fensterfunktionen werden verwendet, um Zeilen innerhalb jedes Frames zu ordnen. Zum Beispiel ordnet RANK() einen Wert in einer Gruppe von Werten. Der ORDER BY-Ausdruck in der OVER-Klausel bestimmt den Rangwert. Jeder Wert wird innerhalb seiner Partition eingestuft. Zeilen mit gleichen Werten für die Rangfolgekriterien erhalten denselben Rang.

  Betrachten wir ein weiteres Beispiel für eine Rangfolge-Fensterfunktion. ROW_NUMBER() bestimmt die Ordnungszahl der aktuellen Zeile innerhalb ihrer Partition. Auch hier wird diese Zahl durch das ORDER BY in der OVER-Klausel bestimmt. Jeder Wert wird innerhalb seiner Partition geordnet.

• Wertfensterfunktionen: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(). Diese Funktionen sind sehr hilfreich bei der Erstellung von Berichten und der Zusammenfassung von Daten. Die Fensterfunktionen LAG() und LEAD() geben den Wert für die Zeile vor (LEAD()) oder nach (LAG()) der aktuellen Zeile in einer Partition zurück. Wenn keine Zeile existiert, wird null zurückgegeben. In ähnlicher Weise gibt die Fensterfunktion FIRST_VALUE() / LAST_VALUE() den Wert des angegebenen Ausdrucks für die erste (oder letzte) Zeile im Fensterrahmen zurück.

Wir haben bereits ein Beispiel für die Verwendung von Aggregat-Fensterfunktionen gesehen, so dass Sie verstehen können, warum sie bei der Datenanalyse nützlich sind. Tatsächlich werden alle drei Arten von SQL-Fensterfunktionen häufig in komplexen Analysen verwendet. Sie sind eine großartige Funktion in SQL.

Brauchen Sie ein Beispiel aus der Praxis? Nehmen wir an, Sie sind im Devisenhandel tätig. Beim Verkauf oder Kauf von Positionen prüfen Sie häufig den Schlusskurs der vorangegangenen Minute oder der vorangegangenen Stunde; dafür würden Sie die Funktion LAG() verwenden. Sie können Ihre Schlusskurse auch mit Hilfe von Ranking-Fensterfunktionen innerhalb eines bestimmten Zeitfensters einordnen. Oder Sie können den anfänglichen oder endgültigen Schlusskurs mit Hilfe von Wertfensterfunktionen ermitteln.

Verwendung von SQL-Fensterfunktionen: LAG()

Gehen wir nun näher auf die Verwendung von SQL-Analysefunktionen in der Praxis ein. Für jede Zeile wollen wir den Schlusskurs der vorherigen Zeile sehen. Dazu verwenden wir eine Ranking-Fensterfunktion:

select *,LAG(close) OVER(PARTITION BY ticker ORDER BY datetime) AS previous_close from CURRENCYTRADE;

Wir verwenden LAG(), die den Wert der vorherigen Zeile zurückgibt. Hier zeigt das OVER an, dass es sich um eine Fensterfunktion handelt, in der wir Zeilen pro Währungspaar gruppieren. Da wir LAG() verwenden, benötigen wir auch eine ORDER BY-Klausel, um die Daten in jedem Frame zu sortieren, bevor wir die Schlusskurse der vorherigen Zeile zuweisen. Wir ordnen die Daten nach der Datums-Spalte, was bedeutet, dass wir den Schlusskurs der letzten Minute in jeder aktuellen Zeile haben werden. Die folgende Abbildung zeigt, wie die Ausgabe aussieht:

Funktion Window Lag

Dieser Code ist einfacher und leichter zu pflegen. Das ist ein großer Vorteil der Verwendung von Fensterfunktionen. Sie haben in der Tat viele Vorteile, wie wir noch sehen werden.

Vorteile der Verwendung von SQL Fensterfunktionen

Fensterfunktionen sind nützlich, wenn Sie die Zeilen in der Ergebnismenge nicht zusammenfassen, d. h. die Ergebnisdaten in einer einzigen Ausgabezeile gruppieren müssen. Anstelle einer einzigen Ausgabezeile wird ein einzelner Wert für jede Zeile der zugrunde liegenden Abfrage zurückgegeben. Das ist der Hauptvorteil, wenn Sie mich fragen.

Zu den weiteren Vorteilen der SQL-Analysefunktionen gehören:

• Mit Fensterfunktionen können Sie sowohl aggregierte als auch nicht-aggregierte Werte auf einmal erfassen. Das liegt daran, dass für jeden zurückgegebenen Zeilenwert keine Gruppierung oder Zusammenlegung dieser Zeile erfolgt. Sie können alle Spalten jeder Zeile beibehalten und zusätzliche, von der Fensterfunktion berechnete Werte hinzufügen. Dies ist ein großer Vorteil, wenn Sie sowohl aggregierte als auch nicht aggregierte Werte in einer Tabelle benötigen.
• Die Syntax ist einfach, und der Code ist in der Produktion leichter zu pflegen. Stellen Sie sich vor, wie viel Zeit Sie benötigen würden, um das Äquivalent zur Funktion LEAD(), LAG() oder RANK() mit herkömmlichem SQL zu implementieren. Oder einfach jeder Zeile Durchschnittswerte zuzuweisen, ohne Fensterfunktionen! Sie müssten zuerst die Aggregatfunktion GROUP BY verwenden, gefolgt von einem LEFT JOIN mit der ursprünglichen Eingabedatentabelle. Ihr Code wäre dann komplizierter und schwieriger zu pflegen. Hier ist ein interessanter Artikel, der zeigt, wie ein Beispiel sowohl mit Cursors als auch mit analytischen SQL-Funktionen gelöst werden kann. Der Code der analytischen Funktionen ist sauberer und einfacher, finden Sie nicht auch?
• Sie können einer aktuellen Zeile einfach einen Wert aus einer vorherigen oder einer nachfolgenden Zeile zuweisen. In einigen Datenbanken ist dies sogar eine viel schnellere Option als die Lösung mit dem Cursor oder einer korrelierten Unterabfrage. Solche Codes sind komplexer und schwieriger zu pflegen. Hier ist ein schöner Artikel, der die Leistung (Fensterfunktionen vs. Cursor vs. Subquery) in einer MS SQL-Datenbank vergleicht. Cool, nicht wahr?

Mehr über SQL erfahren Fensterfunktionen

Die meisten SQL-Benutzer sind fortgeschrittene Anfänger (nach dem Dreyfus-Modell) und kennen sich vielleicht nicht wirklich mit modernem SQL aus. Die Verwendung von Fensterfunktionen ist eine fortgeschrittene Technik, aber es lohnt sich, sie zu lernen! Wenn Sie sich dafür entscheiden, Fensterfunktionen zu lernen, suchen Sie sich einen Kurs, der viele Beispiele für die Anwendung von SQL-Analysefunktionen in konkreten Geschäftsfällen enthält. Und suchen Sie vor allem nach einem Kurs mit vielen Übungen, in denen Sie üben können! Vergessen Sie nicht: Je mehr Sie üben, desto schneller und besser lernen Sie.

Neben Online-Kursen können Sie auch immer einen Blick auf den LearnSQL-Blog werfen. Er ist voll von interessanten Artikeln, die Fensterfunktionen erklären, wie zum Beispiel:

• Beispiel für SQL Fensterfunktionen mit erklärungen
• Gemeinsame SQL Fensterfunktionen: Positionale Funktionen
• Wie man Rangfunktionen in SQL verwendet
• Common SQL Fensterfunktionen: Verwendung von Partitionen mit Ranking-Funktionen