Überblick
- Lernen Sie die Funktionen des SQL-Fensters kennen
- Verstehen Sie, was Aggregatfunktionen fehlen und warum wir Fensterfunktionen in SQL benötigen
Einführung
Daten vermehren sich mit erstaunlicher Geschwindigkeit, Aufwachsen 44 zettabytes de 2020! Und natürlich wandelt sich auch die Technik zur Verarbeitung dieser gigantischen Datenmengen proportional..
Heute verfügen wir über eine Vielzahl von Tools wie Hive und Spark, um mit Big Data umzugehen. Aber, auch wenn sie sich in einigen Punkten unterscheiden, setzen immer noch die Grundlagen von SQL ein, macht es Menschen aus allen Gesellschaftsschichten super einfach, Big Data im Handumdrehen zu manipulieren. Obwohl wir in bestimmten Aspekten von SQL immer noch scheitern. Dann, In diesem Artikel, Ich werde speziell über einen dieser Aspekte sprechen: Fensterfunktionen.
Korrekt! Es gibt Fensterfunktionen in SQL, es ist kein scherz! Und angesichts des erstaunten Gesichtsausdrucks, Dieser Artikel scheint das Gebot der Stunde zu sein. Selbst mir waren diese Funktionen bis vor kurzem noch nicht so bewusst, was zeigt, wie unterschätzt diese Funktionen sind.
Aber warten Sie bis zum Ende dieses Artikels, denn Window Functions werden Sie mit der Einfachheit, mit der sie so komplexe Probleme lösen, wirklich umhauen.. Und wenn, Dateningenieure, Datenwissenschaftler, Datenanalysten und alle anderen, die mit Daten flirten, sollten diesen Rollen ihre gebührende Anerkennung zollen.
Bevor es weitergeht, Ich schlage vor, dass Sie sich mit den grundlegenden SQL-Funktionen vertraut machen, indem Sie diesen Artikel lesen: 24 häufig verwendete SQL-Funktionen. Und wenn Sie daran interessiert sind, SQL in einem Kursformat zu lernen, Überprüfen Sie unseren Kurs: Strukturierte Abfragesprache (SQL) für Datenwissenschaft.
Inhaltsverzeichnis
- Wir präsentieren den Datensatz
- Wo hinken Aggregatfunktionen hinterher??
- Was sind Fensterfunktionen in SQL??
- Verstehen der SQL-Fensterfunktionen – Überklausel
- Windows mit PARTITION BY
- Organisieren von Zeilen innerhalb von Partitionen
- Fensterfunktionen
- Zeilennummer
- Rango gegen Dense_Rank
- Nth_Value
- Nil
- Vorsprung und Rückstand
- SQL-Code-Datei
Wir präsentieren den Datensatz
vor dem Fortfahren, Lassen Sie mich Ihnen den fiktiven Datensatz vorstellen, an dem wir in diesem Artikel arbeiten werden. Angenommen, es gibt ein Unternehmen, das Namensaufzeichnungen führt, Job und Gehalt des Mitarbeiters wie folgt:
Wir verwenden dieses Beispiel-Dataset, um die Konzepte in diesem Artikel zu verstehen.. Sehr gut, Lasst uns beginnen!
Wo hinken Aggregatfunktionen hinterher??
Angenommen, Sie möchten das Gesamtgehalt aller Mitarbeiter im Unternehmen ermitteln. Wie würdest du es machen? Sie können einfach die Summenfunktion SUM verwenden () in der Spalte GEHALT.
Einfach.
Wie wäre es mit der Ermittlung des Gesamtgehalts der Mitarbeiter nach Jobkategorie? Verwenden Sie die letzte Abfrage und fügen Sie eine GROUP BY-Klausel in der JOB-Spalte hinzu.
Exzellent!
Jetzt lass mich dir noch zwei Fragen stellen:
- Gesamtgehalt und Gesamtgehalt nach Jobkategorie zusammen mit dem Wert jeder Zeile anzeigen.
- Organisieren Sie das Gehalt in absteigender Reihenfolge innerhalb jeder Jobkategorie.
Hast du es verstanden? Nein? Wahrscheinlich?
Diese waren definitiv nicht so einfach wie die ersten, die man sofort bekommen konnte. Aber warum?
Gut, Wenn du darüber nachdenkst, vorherige Abfragen erforderten einfache Aggregatfunktionen, um das Problem zu lösen. SQL-Aggregatfunktionen geben uns nur einen einzigen Wert für die Aggregatzeilengruppe (Denken Sie an die erste Anfrage, die wir geschrieben haben).
Aber mit solchen Funktionen lassen sich die neuesten Fragestellungen nicht einfach lösen. Diese Abfragen möchten, dass wir die ursprüngliche Identität der einzelnen Zeilen beibehalten, etwas, das Aggregatfunktionen nicht ansprechen können. Deswegen, um diese Art von Anfragen zu lösen, Wir brauchen verschiedene Arten von Funktionen: Fensterfunktionen.
Was sind Fensterfunktionen in SQL??
Fensterfunktionen Berechnungen für eine Reihe von Zeilen durchführen, die miteinander in Beziehung stehen. Aber, im Gegensatz zu Aggregatfunktionen, Fensterfunktionen reduzieren das Ergebnis der Zeilen nicht auf einen einzigen Wert. jedoch, alle Zeilen behalten ihre ursprüngliche Identität und das berechnete Ergebnis wird für jede Zeile zurückgegeben.
Verstehen der SQL-Fensterfunktionen – Überklausel
Zum Beispiel, wenn ich das Gesamtgehalt der Mitarbeiter zusammen mit jedem Zeilenwert anzeigen würde, so würde es aussehen:
das AUF -Klausel bedeutet ein Fenster von Zeilen, auf das eine Fensterfunktion angewendet wird. Kann mit Aggregatfunktionen verwendet werden, wie wir es hier mit der SUM-Funktion verwendet haben, damit in eine Fensterfunktion umgewandelt. Oder es kann auch mit nicht aggregierten Funktionen verwendet werden, die nur als Fensterfunktionen verwendet werden (wir werden in späteren Abschnitten mehr darüber erfahren).
Dann, Die Syntax zum Definieren einer einfachen Fensterfunktion, die für alle Zeilen denselben Wert ausgibt, lautet wie folgt:
window_function_name () ÜBER ()
Aber, Wie wäre es, wenn wir die Fensterfunktion auf bestimmte Zeilen statt auf die gesamte Tabelle anwenden??
Windows mit PARTITION BY
das TEILUNG VON Die Klausel wird in Verbindung mit der OVER-Klausel verwendet. Teilen Sie die Reihen in verschiedene Partitionen auf. Dann, die Fensterfunktion wirkt auf diese Partitionen.
Zum Beispiel, um das Gesamtgehalt nach Jobkategorie für alle Zeilen anzuzeigen, wir müssten unsere ursprüngliche SQL-Abfrage wie folgt ändern:
Wie du siehst, das total_work_lohn Die Spalte stellt die Summe der Verkäufe für diese bestimmte Jobkategorie dar und nicht für die gesamte Tabelle.
Dann, Die Syntax zum Definieren der Fensterfunktion für die Zeilenpartition lautet wie folgt:
window_function_name () ÜBER ()
Jetzt, Wie wäre es mit der Anordnung der Zeilen innerhalb jeder Partition??
Organisieren von Zeilen innerhalb von Partitionen
Wir wissen das, um Zeilen in einer Tabelle zu organisieren, wir können die ORDER BY-Klausel verwenden. Dann, um Zeilen innerhalb jeder Partition zu organisieren, wir müssen die OVER-Klausel mit der ORDER BY-Klausel ändern.
Hier, die Ränge wurden nach ihrer Berufskategorie eingeteilt, wie in der Spalte JOB angegeben. Wie es nach unten scrollt, Sie werden feststellen, dass die Spalte GEHALT absteigend sortiert ist und die geordneter_arbeitslohn Die Spalte stellt die laufende Summe für die Jobkategorie dar (nach jeder Partition von vorne beginnen).
Dann, Die Syntax zum Definieren der Fensterfunktion zum Partitionieren von Zeilen und zum Anordnen der Zeilen lautet wie folgt:
window_function_name () ÜBER ( )
Fensterfunktionen
Jetzt wissen wir, wie man Fensterfunktionen mit der OVER-Klausel und einigen ihrer modifizierten Versionen definiert, Endlich können wir mit Fensterfunktionen arbeiten!
1. Zeilennummer
Manchmal, Ihr Dataset enthält möglicherweise keine Spalte, die die sequenzielle Reihenfolge der Zeilen beschreibt, wie bei unserem Datensatz. Dann, wir können das gebrauchen ZEILENNUMMER() Fensterfunktion. Weisen Sie jeder Zeile in der Tabelle eine eindeutige fortlaufende Nummer zu.
Beachten Sie, dass die Nummerierung bei beginnt 1. Was ist mehr, um Konflikte mit dem MySQL-Schlüsselwort für die Funktion zu vermeiden, Ich habe den Spaltennamen in Anführungszeichen gesetzt.
Aber, da es sich um eine Fensterfunktion handelt, wir können es auch auf Partitionen beschränken und diese Partitionen dann bestellen.
Hier, wir haben die Zeilen in der Spalte ARBEIT geteilt und nach dem GEHALT des Mitarbeiters geordnet. Beachten Sie, wie die Nummerierung jedes Mal neu startet, wenn eine neue Partition gestartet wird.
Aber nehmen wir an, wir möchten Mitarbeiter nach ihrem Gehalt klassifizieren.
2. Ranking vs. Dense_Rank
das RANG() Die Fensterfunktion, wie der Name schon sagt, sortiert die Zeilen innerhalb Ihrer Partition basierend auf der angegebenen Bedingung.
Beachten Sie den hervorgehobenen Teil. Im Fall von ROW_NUMBER (), wir haben eine fortlaufende nummer. Zweitens, im Fall von RANK (), wir haben den gleichen Bereich für Zeilen mit dem gleichen Wert.
Aber hier ist ein Problem. Obwohl Zeilen mit dem gleichen Wert der gleiche Rang zugewiesen wird, nachfolgender Rang überspringt fehlender Rang. Dies würde uns nicht die gewünschten Ergebnisse bringen, wenn wir "top N different" Werte aus einer Tabelle zurückgeben müssten. Deswegen, Wir haben eine andere Funktion, um dieses Problem zu lösen.
das DICHTE_RANK () Die Funktion ist ähnlich wie RANK () bis auf einen Unterschied, überspringt keine Bereiche beim Sortieren von Zeilen.
Hier, alle Bereiche sind unterschiedlich und erhöhen sich sequentiell innerhalb jeder Partition. Im Vergleich zur RANK-Funktion (), innerhalb einer Partition wurde kein Bereich übersprungen.
3. Nth_Value
Wenn Sie den n-ten Wert eines Fensterrahmens für einen Ausdruck abrufen möchten, Sie können die Fensterfunktion NTH_VALUE verwenden (Ausdruck, n).
Zum Beispiel, das dritthöchste Gehalt in jeder JOB-Kategorie zurückzufordern, wir können die Zeilen nach der Spalte WORK aufteilen, dann sortieren Sie die Zeilen innerhalb der Partitionen nach abnehmendem Gehalt und, Schließlich, Verwenden Sie die Funktion NTH_VALUE, um den Wert abzurufen. Der Befehl lautet wie folgt:
Sie müssen nach der Order By-Klausel etwas anderes bemerkt haben. Das ist der Rahmenklausel. Bestimme die Teilmenge der Partition (der Meilenstein) die von der Fensterfunktion verwendet wird, um den Wert der aktuellen Zeile zu berechnen.
Hier, Ich habe erwähnt, dass alle vorherigen und nächsten Zeilen einer aktuellen Zeile innerhalb des Rahmens berücksichtigt werden, wenn die Fensterfunktion angewendet wird. Aber, Warum habe ich hier die Rahmenklausel verwendet und nicht mit anderen Funktionen?? Dies liegt daran, dass die anderen Fensterfunktionen auf der gesamten Partition arbeiten, auch wenn eine Rahmenklausel vorgesehen ist. Pero-Solo NTH_VALUE () kann an Frames innerhalb einer Partition arbeiten.
Angenommen, Sie möchten den ersten Wert jeder Partition generieren. Obwohl es ein FIRST_VALUE () Funktion auch, Ich werde den NTH_VALUE dafür verwenden.
Auf die gleiche Weise, wir haben auch ein LAST_VALUE () Funktion. Aber ich werde den letzten Wert innerhalb jeder Partition wie oben bestimmen, obwohl in absteigender Reihenfolge der Zeilen.
4. Nil
Manchmal, Vielleicht möchten Sie die Zeilen innerhalb der Partition in eine bestimmte Anzahl von Gruppen sortieren. Dies ist nützlich, wenn Sie das Perzentil bestimmen möchten, Quartil, etc. in der sich eine bestimmte Zeile befindet. das NTILE () Die Funktion wird für solche Zwecke verwendet. Gibt die Gruppennummer für jede Zeile in der Partition zurück.
Zum Beispiel, Lassen Sie uns das Quartil jeder Zeile anhand des GEHALTS des Mitarbeiters ermitteln:
Ähnlich, Sie können die Zeilen in verschiedene Gruppen aufteilen und den NTILE für verschiedene Partitionen berechnen.
5. Vorsprung und Rückstand
Häufig, Vielleicht möchten Sie den Wert der aktuellen Zeile mit dem der vorherigen oder nächsten Zeile vergleichen. Hilft bei der einfachen Datenanalyse. das DAS BLEI() Ja VERZÖGERN() Die Fensterfunktionen sind nur dafür da.
Hier, Wir erstellen eine neue Spalte mit SALARY aus der nächsten Zeile innerhalb jeder Partition, die nach Gehalt geordnet ist, mit der LEAD-Funktion. Beachten Sie, dass die letzte Zeile jeder Partition einen Nullwert enthält, da es keine nachfolgende Zeile zum Extrahieren von Daten gibt.
Jetzt, Machen wir dasselbe mit der LAG-Funktion.
Hier, wir erstellen zwei neue Spalten. Die erste Spalte enthält SALARY aus der vorherigen Zeile innerhalb jeder Partition, geordnet nach Gehalt. Während die zweite Spalte die Differenz zwischen GEHALT der vorherigen Zeile und der aktuellen Zeile enthält. Wie du siehst, Dies ist sehr nützlich für eine schnelle Analyse des Unterschieds zwischen den Löhnen innerhalb derselben Partition.
SQL-Code-Datei
Den gesamten SQL-Code zu Fensterfunktionen für diesen Artikel finden Sie in diesem Link.
Abschließende Anmerkungen
Wir haben bereits einige Fensterfunktionen gesehen und ich hoffe, Sie können jetzt die Schönheit der Fensterfunktionen in SQL erkennen. Aber das Lernen hört hier nicht auf. Nach dem Beherrschen der Grundlagen, Es ist an der Zeit, die Tools für den Umgang mit Big Data zu beherrschen.
Wenn Sie in die Data Engineering-Domäne wechseln möchten, Ich schlage die folgenden Artikel für einen einfachen Übergang vor:
