Eine Einführung in SQL-Fensterfunktionen

Die sehr mächtige Funktion, die Sie gerne hassen (aber wissen müssen)

SQL-Fensterfunktionen bieten einige äußerst leistungsstarke und nützliche Funktionen. Aber für viele sind sie, da sie dem Standard-SQL so fremd sind, schwer zu lernen und zu verstehen, haben eine seltsame Syntax – und werden sehr oft vermieden.

Fensterfunktionen können einfach als Berechnungsfunktionen erklärt werden, die der Aggregation ähneln, aber wo die normale Aggregation über die GROUP BY -Klausel dann die einzelnen zu aggregierenden Zeilen verbirgt, haben Fensterfunktionen Zugriff auf einzelne Zeilen und können einige der Attribute aus diesen Zeilen in die hinzufügen Ergebnissatz.

In diesem Tutorial zu SQL-Fensterfunktionen werde ich Ihnen den Einstieg in Fensterfunktionen erleichtern, die Vorteile erläutern und erklären, wann Sie sie verwenden, und Ihnen echte Beispiele geben, die Ihnen bei den Konzepten helfen.

Ein Fenster in Ihre Daten

Eine der am häufigsten verwendeten und wichtigsten Funktionen in SQL ist die Möglichkeit, Datenzeilen auf bestimmte Weise zu aggregieren oder zu gruppieren. In einigen Fällen kann die Gruppierung jedoch sehr komplex werden, je nachdem, was erforderlich ist.

Wollten Sie schon immer die Ergebnisse Ihrer Abfrage durchlaufen, um ein Ranking, eine Top-X-Liste oder ähnliches zu erhalten? Hatten Sie Analytics-Projekte, bei denen Sie Ihre Daten genau richtig für ein Visualisierungstool aufbereiten wollten, es aber fast unmöglich oder so komplex fanden, dass es sich nicht gelohnt hat?

Fensterfunktionen können die Dinge erleichtern. Nachdem Sie das Ergebnis Ihrer Abfrage erhalten haben – dh nach der WHERE -Klausel und jeder standardmäßigen Aggregation – wirken Fensterfunktionen auf die verbleibenden Zeilen (das Datenfenster ) und erhalten, was Sie wollen.

Einige der Fensterfunktionen, die wir uns ansehen werden, sind:

• OVER
• COUNT()
• SUM()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LEAD()
• LAG()

Über Einfach

Die OVER Klausel gibt eine Fensterfunktion an und muss immer in der Anweisung enthalten sein. Der Standardwert in einer OVER -Klausel ist das gesamte Rowset. Sehen wir uns als Beispiel eine Mitarbeitertabelle in einer Unternehmensdatenbank an und zeigen die Gesamtzahl der Mitarbeiter in jeder Zeile zusammen mit den Informationen zu jedem Mitarbeiter, einschließlich des Zeitpunkts, zu dem er bei dem Unternehmen angefangen hat.

 SELECT COUNT(*) OVER() As NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Das Obige kann, wie viele Fensterfunktionen, auch auf eine vertrautere Weise ohne Fenster geschrieben werden – was in diesem einfachen Beispiel nicht allzu schlimm ist:

 SELECT (SELECT COUNT(*) FROM Employee) as NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Nehmen wir nun an, wir möchten die Anzahl der Mitarbeiter anzeigen, die im selben Monat wie der Mitarbeiter in der Reihe angefangen haben. Wir müssen die Anzahl für jede Zeile auf genau diesen Monat eingrenzen oder einschränken. Wie wird das gemacht? Wir verwenden die PARTITION Klausel des Fensters wie folgt:

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started),YEAR(date_started)) As NumPerMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname FROM Employee ORDER BY date_started;

Partitionen ermöglichen es Ihnen, das Fenster nach einem bestimmten Wert oder bestimmten Werten in Abschnitte zu filtern. Jeder Abschnitt wird oft als Fensterrahmen bezeichnet.

Nehmen wir weiter an, wir wollten nicht nur herausfinden, wie viele Mitarbeiter im selben Monat angefangen haben, sondern auch zeigen, in welcher Reihenfolge sie in diesem Monat angefangen haben. Dafür können wir die bekannte ORDER BY Klausel verwenden. Innerhalb einer Fensterfunktion verhält sich ORDER BY jedoch etwas anders als am Ende einer Abfrage.

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started), YEAR(date_started) ORDER BY date_started) As NumThisMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

In diesem Fall ändert ORDER BY das Fenster so, dass es vom Beginn der Partition (in diesem Fall Monat und Jahr, in dem der Mitarbeiter begonnen hat) bis zur aktuellen Zeile reicht. Somit beginnt die Zählung bei jeder Partition neu.

Ordnen Sie es

Fensterfunktionen können für Ranking-Zwecke sehr nützlich sein. Zuvor haben wir gesehen, dass die Verwendung der COUNT -Aggregationsfunktion es uns ermöglichte, zu sehen, in welcher Reihenfolge Mitarbeiter dem Unternehmen beigetreten sind. Wir hätten auch Window-Ranking-Funktionen wie ROW_NUMBER() , RANK() und DENSE_RANK() .

Die Unterschiede werden sichtbar, nachdem wir im folgenden Monat einen neuen Mitarbeiter hinzugefügt und die Partition entfernt haben:

 SELECT ROW_NUMBER() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As StartingRank, RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As EmployeeRank, DENSE_RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As DenseRank, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Sie können die Unterschiede sehen. ROW_NUMBER() gibt eine sequentielle Zählung innerhalb einer gegebenen Partition (aber ohne Partition geht es durch alle Zeilen). RANK() gibt den Rang jeder Zeile basierend auf der ORDER BY Klausel an. Es zeigt Unentschieden an und überspringt dann die nächste Rangliste. DENSE_RANK zeigt ebenfalls Unentschieden, fährt dann aber mit dem nächsten aufeinanderfolgenden Wert fort, als ob es kein Unentschieden gäbe.

Weitere Ranking-Funktionen sind:

• CUME_DIST – Berechnet den relativen Rang der aktuellen Zeile innerhalb einer Partition
• NTILE – Teilt die Zeilen für jede Fensterpartition so gleichmäßig wie möglich
• PERCENT_RANK – Prozentrang der aktuellen Zeile

Beachten Sie auch in diesem Beispiel, dass Sie mehrere Fensterfunktionen in einer einzigen Abfrage haben können – und sowohl die Partition als auch die Reihenfolge können in jeder unterschiedlich sein!

Reihen und Bereiche und Rahmen, oh mein Gott

Um Ihren Fensterrahmen innerhalb der OVER() -Klausel weiter zu definieren oder einzuschränken, können Sie ROWS und RANGE . Mit der ROWS -Klausel können Sie die in Ihrer Partition enthaltenen Zeilen als diejenigen vor oder nach der aktuellen Zeile angeben.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

In diesem Beispiel geht der Fensterrahmen von der ersten Zeile bis zur aktuellen Zeile minus 1, und die Fenstergröße nimmt für jede Zeile weiter zu.

Die Reichweite funktioniert etwas anders und wir erhalten möglicherweise ein anderes Ergebnis.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

Der Bereich umfasst die Zeilen im Fensterrahmen, die dieselben ORDER BY -Werte wie die aktuelle Zeile haben. Daher ist es möglich, dass Sie mit RANGE Duplikate erhalten, wenn ORDER BY nicht eindeutig ist.

Einige beschreiben ROWS als physikalischen Operator, während RANGE ein logischer Operator ist. In jedem Fall sind die Standardwerte für ROWS und RANGE immer UNBOUNDED PRECEDING AND CURRENT ROW .

Was sonst?

Die meisten Standard-Aggregatfunktionen arbeiten mit Windows-Funktionen. Wir haben COUNT bereits in den Beispielen gesehen. Andere sind SUM , AVG , MIN , MAX usw.

Mit Fensterfunktionen können Sie auch mit LAG und LEAD sowie FIRST_VALUE und LAST_VALUE sowohl auf vorherige als auch auf nachfolgende Datensätze zugreifen. Angenommen, Sie möchten in jeder Zeile eine Verkaufszahl für den aktuellen Monat und die Differenz zwischen der Verkaufszahl des letzten Monats anzeigen. Sie könnten so etwas tun:

 SELECT id, OrderMonth, OrderYear, product, sales, sales - LAG(sales,1) OVER (PARTITION BY product ORDER BY OrderYear, OrderMonth) As sales_change FROM sales_products WHERE sale_year = 2019;

Grundsätzlich sind SQL-Fensterfunktionen sehr leistungsfähig

Obwohl dies eine kurze Einführung in die SQL-Fensterfunktionen ist, wird es hoffentlich Ihr Interesse wecken, zu sehen, was sie alles können. Wir haben gelernt, dass Fensterfunktionen Berechnungen ähnlich wie Aggregationsfunktionen durchführen, aber mit dem zusätzlichen Vorteil, dass sie Zugriff auf Daten innerhalb der einzelnen Zeilen haben, was sie sehr leistungsfähig macht. Sie enthalten immer die OVER -Klausel und können PARTITION BY , ORDER BY und eine Vielzahl von Aggregationsfunktionen ( SUM , COUNT usw.) und andere Positionsfunktionen ( LEAD , LAG ) enthalten. Wir haben auch etwas über Fensterrahmen gelernt und wie sie Datenabschnitte einkapseln.

Beachten Sie, dass verschiedene SQL-Varianten Fensterfunktionen unterschiedlich implementieren können und einige möglicherweise nicht alle Fensterfunktionen oder -Klauseln implementiert haben. Überprüfen Sie unbedingt die Dokumentation für die Plattform, die Sie verwenden.

Wenn Sie als SQL-Entwickler daran interessiert sind, die Leistung Ihrer SQL-Datenbank zu optimieren, sehen Sie sich SQL- Datenbank-Leistungsoptimierung für Entwickler an .

Viel Spaß beim Fenstern!

Weitere Informationen zu bestimmten Implementierungen finden Sie unter:

• PostgreSQL-Dokumentation zu Windows-Funktionen für eine PostgreSQL-Implementierung.
• SELECT - OVER-Klausel (Transact-SQL) -Dokumentation von Microsoft.
• Fensterfunktionen in SQL Server für einen großartigen Überblick über SQL Server-Implementierungen und Teil 2.