SQL-Fensterfunktionen: Alles, was Sie über ihre Verwendung wissen müssen

Die Vielseitigkeit von SQL als DBMS-Abfragesprache hat im Laufe der Jahre zugenommen. Sein umfassender Nutzen und seine Vielseitigkeit machen es zu einem absoluten Favoriten für jeden Datenanalysten.

Neben den regulären SQL-Funktionen gibt es noch einige fortgeschrittene Funktionen. Diese Funktionen sind allgemein als Fensterfunktionen bekannt. Wenn Sie mit komplexen Daten arbeiten und erweiterte Berechnungen durchführen möchten, können Sie sie verwenden, um Ihre Daten optimal zu nutzen.

Die Bedeutung von Fensterfunktionen

In SQL sind mehrere Fensterfunktionen verfügbar, die Ihnen bei der Durchführung einer Reihe von Berechnungen helfen. Von der Erstellung von Partitionen über die Rangfolge von Zeilen bis hin zur Zuweisung von Zeilennummern, diese Fensterfunktionen erledigen ein bisschen von allem.

Fensterfunktionen sind nützlich, wenn Sie Aggregatfunktionen auf ein bestimmtes Dataset oder eine Sammlung von Zeilen anwenden. Diese Funktionen gehen über die von GROUP BY bereitgestellten Aggregationsfunktionen hinaus. Der Hauptunterschied besteht jedoch darin, dass Ihre Daten im Gegensatz zur Gruppierungsfunktion nicht zu einer einzigen Zeile zusammengefasst werden.

Innerhalb der Anweisungen WHERE , FROM und GROUP BY können Sie keine Fensterfunktionen verwenden .

Syntax einer Fensterfunktion

Wenn Sie auf eine beliebige Fensterfunktion verweisen, müssen Sie der Standard-Syntaxstruktur folgen, damit sie korrekt funktioniert. Wenn Sie den Befehl falsch strukturieren, erhalten Sie eine Fehlermeldung und Ihr Code kann nicht ausgeführt werden.

Hier ist die Standard-Syntax:

SELECT columnname1,
{window_function}(columnname2)
OVER([PARTITION BY columnname1] [ORDER BY columnname3]) AS new_column
FROM table_name;

Woher:

• coulmnname1 ist der erste Spaltenname, den Sie auswählen möchten.
• {window_function} ist der Name einer Aggregatfunktion wie sum, avg, count, row_number, rank oder dense_rank.
• Spaltenname2 ist der Name der Spalte, auf die Sie die Fensterfunktion anwenden.
• Spaltenname3 ist der dritte Spaltenname, der die Basis für die Partition bildet.
• new_column ist eine Bezeichnung für die neue Spalte, die Sie mit dem Schlüsselwort AS anwenden können .
• Tabellenname ist der Name der Quelltabelle .

Fensterfunktionen unterscheiden sich von einigen der grundlegendsten SQL-Befehle. Im Gegensatz zu Aggregatfunktionen in SQL können Sie diese Fensterfunktionen verwenden, um erweiterte Funktionen auszuführen.

Vorbereiten des Datensatzes

Sie können die CREATE TABLE -Anweisung verwenden, um eine neue Tabelle in SQL zu erstellen. Hier ist ein Beispieldatensatz, den dieser Leitfaden verwendet, um einige Fensterfunktionen zu definieren:

Die Summenfunktion erklärt

Angenommen, Sie möchten den Gesamtumsatz für jeden Wert in der Kategoriespalte berechnen. So können Sie es tun:

SELECT category, color,
sum(sale_price)
OVER (order by category) AS total_sales
FROM sahil.sample;

Im obigen Code ruft die SQL-Anweisung die Kategorie und Farbe aus dem ursprünglichen Dataset ab. Die Summenfunktion addiert die Spalte sale_price. Dies geschieht nach Kategorie, da die OVER-Klausel die Sortierung nach Kategoriespalte angibt. Das Endergebnis sieht wie folgt aus:

So verwenden Sie die Fensterfunktion Avg()

Wie bei der Summenfunktion können Sie mit der avg – Funktion den Durchschnitt pro Datenzeile berechnen. Anstelle der Summe erhalten Sie eine Spalte mit den durchschnittlichen Umsätzen.

SELECT category, color,
avg(sale_price)
OVER (order by category) AS avg_sales
FROM sahil.sample;

Erfahren Sie, wie Sie die Fensterfunktion Count() verwenden

Ähnlich wie die Sum- und Avg-Funktionen ist die Count-Window-Funktion in SQL ziemlich einfach und funktioniert nach dem gleichen Prinzip wie die beiden anderen Funktionen. Wenn Sie die Zählfunktion übergeben, erhalten Sie die Gesamtzahl jedes Werts in der neuen Spalte.

So können Sie die Anzahl berechnen:

SELECT category, color,
count(category)
OVER (order by category) AS item_count
FROM sahil.sample;

Die Fensterfunktion Row_Number()

Anders als einige der anderen oben aufgeführten Fensterfunktionen funktioniert row_number() etwas anders. Die Funktion row_number() weist jeder Zeile abhängig von der order by-Klausel eine Zeilennummer zu. Die Startreihennummer ist 1; die row_number weist jeder Zeile bis zum Ende einen entsprechenden Wert zu.

Hier ist die Grundstruktur einer row_number()-Funktion:

SELECT category, color,
row_number()
OVER (order by category) AS item_number
FROM sahil.sample;

Aber was passiert, wenn Sie jedem Kategorieelement separate Zeilennummern zuweisen möchten? Die obige Syntax legt eine fortlaufende Seriennummer fest, unabhängig von den in der Kategorie gespeicherten Artikeln. Beispielsweise sollte die Kategorie der Geräte ihre exklusive Nummerierung haben, gefolgt von Ordnern und so weiter.

Sie können die Partitionsfunktion verwenden, um diese einfache, aber praktische Aufgabe auszuführen. Das Partitionsschlüsselwort weist jedem Kategorieelement bestimmte Zeilennummern zu.

SELECT category, color,
row_number()
OVER (partition by category order by category) AS item_number
FROM sahil.sample;

Die Funktionen Rank() und Dense_Rank()

Die Funktion rank() funktioniert anders als die Funktion row_number() . Sie müssen den Spaltennamen innerhalb der order by-Funktion angeben, um ihn als Grundlage für die Definition der Rangwerte zu verwenden. Im folgenden Codebeispiel können Sie beispielsweise die Farbspalte innerhalb der Order-by-Funktion verwenden. Die Abfrage verwendet dann diese Reihenfolge, um jeder Zeile einen Rangwert zuzuweisen.

Sie können die folgende Codesyntax verwenden, um eine Rangfunktion in SQL zu übergeben:

SELECT category, color,
rank()
OVER (order by color) AS item_rank
FROM sahil.sample;

Sehen Sie sich die Ausgabe an, um zu verstehen, wie diese Funktion funktioniert.

Die Sortieren-nach-Funktion sortiert die Farbkategorie, während die Rangfunktion jeder Farbe einen Rang zuweist. Alle gleichen Farbwerte haben jedoch denselben Rang, während die verschiedenen Farben getrennte Ränge haben. Die Farbe Schwarz kommt innerhalb des Datensatzes dreimal vor; Anstatt einen Rangwert von 1, 2 und 3 zuzuweisen, erhalten die schwarzen Elemente den Rang 1.

Allerdings erhält die nächste Farbe Braun den Rang 4 statt Rang 2. Die Rangfunktion überspringt Werte und weist den unterschiedlichen Einträgen den nächsten chronologischen Wert zu. Wenn Sie einen aussagekräftigeren Rangwert zuweisen möchten, können Sie die Funktion dense_rank() verwenden.

Die dense_rank-Funktion überspringt keine Rangwerte während der order by-Funktion. Beispielsweise haben die ersten drei Farbelemente (Schwarz) Rang 1. Die folgende Farbe (Braun) hat jedoch nicht Rang 4, sondern Rang 2, was die folgende chronologische Nummer in der Nummerierungsliste ist. Die Funktion dense_rank ist eine praktischere Fensterfunktion, da sie der Liste der Elemente einen aussagekräftigen Wert zuweist.

So können Sie die Funktion dense_rank in SQL verwenden:

SELECT category, color,
dense_rank()
OVER (order by color) AS item_rank
FROM sahil.sample;

Und hier ist ein Beispiel dafür, wie die Ausgabe dieser Funktion aussehen wird:

SQL-Funktionen zur Rettung

Die Fensterfunktionen von SQL sind ideal für die Durchführung fortgeschrittener analytischer Operationen. Sie können jedoch viele andere SQL-Befehle verwenden, um sicherzustellen, dass Ihre Rechenfähigkeiten erstklassig sind. Wenn Sie mehrere Ergebnisse auf einmal kombinieren und berechnen, gibt es nichts Besseres, als die Unterabfragen von SQL zu verwenden.

Unterabfragen sind ein hervorragendes Werkzeug, um erweiterte Funktionen auszuführen und die Qualität Ihrer Ergebnisse zu verbessern. Je nach Bedarf der Stunde können Sie Ihre Abfragen anpassen und effektiver an Ihre Anforderungen anpassen.