Ab hier? Diese Lektion ist Teil eines ausführlichen Lernprogramms zur Verwendung von SQL für die Datenanalyse. Schauen Sie sich den Anfang an.
In dieser Lektion werden wir Folgendes behandeln:
- Einführung in Fensterfunktionen
- Grundlegende Fenstersyntax
- Die üblichen Verdächtigen: SUM, COUNT und AVG
- ROW_NUMBER ()
- RANK () und DENSE_RANK ()
- NTILE
- LAG und LEAD
- Definieren eines Fensteralias
- Erweiterte Fenstertechniken
In dieser Lektion werden Daten aus dem veröffentlichten Capital Bikeshare-Programm von Washington DC verwendet detaillierte historische Daten auf Reiseebene auf ihrer Website. Die Daten wurden im Februar 2014 heruntergeladen, sind jedoch auf Daten beschränkt, die im ersten Quartal 2012 gesammelt wurden. Jede Zeile repräsentiert eine Fahrt. Die meisten Felder sind selbsterklärend, außer rider_type: „Registriert“ bedeutet eine monatliche Mitgliedschaft im Mitfahrgelegenheitsprogramm. „Casual“ bedeutet, dass der Fahrer einen 3-Tage-Pass gekauft hat. Die Felder start_time und end_time wurden aus ihren ursprünglichen Formularen entfernt, um der SQL-Datumsformatierung zu entsprechen. Sie werden in dieser Tabelle als Zeitstempel gespeichert.
Einführung in Fensterfunktionen
In der Dokumentation von PostgreSQL wird das Konzept der Fensterfunktionen hervorragend eingeführt:
Eine Fensterfunktion führt eine Berechnung für eine Reihe von Tabellenzeilen durch, die in irgendeiner Weise mit der aktuellen Zeile zusammenhängen. Dies ist vergleichbar mit der Art der Berechnung, die mit einer Aggregatfunktion durchgeführt werden kann. Im Gegensatz zu regulären Aggregatfunktionen wird jedoch ein Fenster verwendet Die Funktion bewirkt nicht, dass Zeilen in einer einzelnen Ausgabezeile gruppiert werden. Die Zeilen behalten ihre getrennten Identitäten. Hinter den Kulissen kann die Fensterfunktion auf mehr als nur die aktuelle Zeile des Abfrageergebnisses zugreifen.
Das praktischste Beispiel hierfür ist eine laufende Summe:
Sie können sehen, dass die obige Abfrage eine Aggregation erstellt (running_total) ohne Verwendung von GROUP BY. Lassen Sie uns die Syntax aufschlüsseln und sehen, wie sie funktioniert.
Grundlegende Fenstersyntax
Der erste Teil der obigen Aggregation, SUM(duration_seconds) , sieht einer anderen Aggregation sehr ähnlich. Durch Hinzufügen von OVER wird sie als Fensterfunktion bezeichnet. Sie können die obige Aggregation als „Summe von duration_seconds über die gesamte Ergebnismenge in der Reihenfolge start_time. „
Wenn Sie das Fenster vom gesamten Datensatz auf einzelne Gruppen innerhalb eingrenzen möchten Im Datensatz können Sie PARTITION BY verwenden, um dies zu tun:
Die obigen Abfragegruppen gruppieren und ordnen die Abfrage nach start_terminal . Innerhalb jedes Wertes von start_terminal wird er nach start_time geordnet und die laufenden Gesamtsummen über die aktuelle Zeile und alle vorherigen Zeilen von duration_seconds. Scrollen Sie nach unten, bis sich der Wert start_terminal ändert und Sie feststellen, dass running_total von vorne beginnt. Dies passiert, wenn Sie mit PARTITION BY gruppieren. Falls Sie immer noch von ORDER BY überrascht sind, wird einfach nach der angegebenen Spalte sortiert (s) auf die gleiche Weise wie die Klausel ORDER BY, außer dass jede Partition als separat behandelt wird. Außerdem wird die laufende Summe erstellt. Ohne ORDER BY ist jeder Wert einfach eine Summe aller duration_seconds -Werte in seiner jeweiligen start_terminal. Führen Sie die obige Abfrage ohne ORDER BY aus, um eine Vorstellung zu erhalten:
Die ORDER und PARTITION definieren, was als“ Fenster „bezeichnet wird – die geordnete Teilmenge von Daten, über die Berechnungen durchgeführt werden.
Hinweis: Sie können Fensterfunktionen und Standardaggregationen nicht gleichzeitig verwenden Abfrage. Insbesondere können Sie „Fensterfunktionen nicht in eine GROUP BY -Klausel aufnehmen.
Übungsproblem
Schreiben Sie eine Abfragemodifikation der obigen Beispielabfrage, in der die Dauer jeder Fahrt als Prozentsatz der Gesamtzeit angezeigt wird, die die Fahrer von jedem Startterminal erhalten haben / div>
Die üblichen Verdächtigen: SUM, COUNT und AVG
Bei Verwendung von Fensterfunktionen können Sie dieselben Aggregate anwenden, die Sie unter normalen Umständen verwenden würden – SUM, COUNT und AVG. Der einfachste Weg, diese zu verstehen, besteht darin, das vorherige Beispiel mit einigen zusätzlichen Funktionen erneut auszuführen. Machen Sie
Alternativ dieselben Funktionen mit ORDER BY:
Stellen Sie sicher, dass Sie die beiden vorherigen Abfragen in den Modus einfügen und ausführen. Dieses nächste Übungsproblem ist den Beispielen sehr ähnlich. Versuchen Sie also, den obigen Code zu ändern, anstatt von vorne zu beginnen.
Übungsproblem
Schreiben Sie eine Abfrage, die die laufende Summe der Dauer von Radtouren anzeigt (ähnlich der letztes Beispiel), jedoch gruppiert nach end_terminal und mit absteigender Fahrdauer.
Probieren Sie es aus Siehe Antwort
ROW_NUMBER ()
ROW_NUMBER() macht genau das, wonach es sich anhört – zeigt die Nummer einer bestimmten Zeile an. Es beginnt mit 1 und nummeriert die Zeilen gemäß dem ORDER BY Teil der window-Anweisung. ROW_NUMBER() erfordert nicht, dass Sie eine Variable in Klammern angeben:
Mit der Klausel PARTITION BY können Sie dies tun Beginnen Sie erneut, in jeder Partition 1 zu zählen. Die folgende Abfrage startet die Zählung für jedes Terminal erneut:
RANK () und DENSE_RANK ()
RANK() unterscheidet sich geringfügig von ROW_NUMBER(). Wenn Sie beispielsweise nach start_time bestellen, kann es vorkommen, dass einige Terminals Fahrten mit zwei identischen Startzeiten haben. In diesem Fall erhalten sie den gleichen Rang, während ROW_NUMBER() ihnen unterschiedliche Nummern gibt. In der folgenden Abfrage bemerken Sie die 4. und 5. Beobachtung für start_terminal 31000 – beide erhalten den Rang 4, und das folgende Ergebnis erhält den Rang 6:
Abhängig von Ihrer Anwendung können Sie auch DENSE_RANK() anstelle von RANK() verwenden. Stellen Sie sich eine Situation vor, in der drei Einträge den gleichen Wert haben. Mit beiden Befehlen erhalten alle den gleichen Rang. Für dieses Beispiel sagen wir „s“ 2. “ So würden die beiden Befehle die nächsten Ergebnisse unterschiedlich bewerten:
-
RANK()würde den identischen Zeilen den Rang 2 geben und dann die Ränge 3 überspringen und 4, also wäre das nächste Ergebnis 5 -
DENSE_RANK()würde immer noch allen identischen Zeilen einen Rang von 2 geben, aber die folgende Zeile wäre 3 – nein Ränge würden übersprungen.
Übungsproblem
Schreiben Sie eine Abfrage, die die 5 längsten Fahrten anzeigt von jedem Startterminal, geordnet nach Terminal, und längste bis kürzeste Fahrten innerhalb jedes Terminals. Beschränken Sie sich auf Fahrten, die vor dem 8. Januar 2012 stattgefunden haben.
Probieren Sie es aus Siehe Antwort
NTILE
Mithilfe von Fensterfunktionen können Sie ermitteln, in welches Perzentil (oder Quartil oder eine andere Unterteilung) eine bestimmte Zeile fällt. Die Syntax lautet NTILE(*# of buckets*). In diesem Fall bestimmt ORDER BY, welche Spalte zum Bestimmen der Quartile verwendet werden soll (oder wie viele „Kacheln Sie verwenden“ angeben). Beispiel:
Wenn Sie sich die Ergebnisse der obigen Abfrage ansehen, sehen Sie, dass die Spalte percentile nicht genau so berechnet, wie Sie es erwarten. Wenn Sie nur Wenn Sie zwei Datensätze hatten und Perzentile gemessen haben, würden Sie erwarten, dass ein Datensatz das 1. Perzentil und der andere Datensatz das 100. Perzentil definiert. Mit der Funktion NTILE sehen Sie tatsächlich einen Datensatz im 1. Perzentil und einen im 2. Perzentil. Dies können Sie in den Ergebnissen für 31000 – Die Spalte percentile sieht nur wie eine numerische Rangfolge aus. Wenn Sie nach unten zu start_terminal 31007 scrollen, werden Sie kann sehen, dass Perzentile richtig berechnet werden, da es mehr als 100 Datensätze für dieses start_terminal gibt. Wenn Sie mit sehr kleinen Fenstern arbeiten, denken Sie daran und erwägen Sie die Verwendung von Quartilen oder ähnlich kleinen Bänder.
Übungsproblem
Schreiben Sie eine Abfrage, die nur die Dauer der Reise und das Perzentil anzeigt, in das Diese Dauer sinkt (über den gesamten Datensatz – nicht nach Terminal aufgeteilt).
Probieren Sie es aus Siehe Antwort
LAG und LEAD
Es kann oft nützlich sein, Zeilen mit vorhergehenden oder folgenden Zeilen zu vergleichen, insbesondere Wenn Sie die Daten in einer sinnvollen Reihenfolge erhalten haben, können Sie LAG oder LEAD verwenden, um Spalten zu erstellen, die Werte von anderen abrufen Zeilen – Sie müssen lediglich eingeben, aus welcher Spalte Sie ziehen möchten und wie viele Zeilen Sie entfernen möchten. LAG zieht aus vorherigen Zeilen und LEAD zieht aus folgenden Zeilen:
Dies ist besonders nützlich, wenn Sie berechnen möchten Unterschiede zwischen Zeilen:
Die erste Zeile der Spalte difference ist null, da keine vorherige Zeile zum Abrufen vorhanden ist. In ähnlicher Weise werden bei Verwendung von LEAD am Ende des Datasets Nullen erstellt. Wenn Sie die Ergebnisse etwas sauberer gestalten möchten, können Sie sie in eine äußere Abfrage einschließen, um Nullen zu entfernen:
Definieren eines Fensteralias
Wenn Sie planen zu schreiben Mehrere Fensterfunktionen in derselben Abfrage. Mit demselben Fenster können Sie einen Alias erstellen.Nehmen Sie das obige Beispiel NTILE:
Dies kann wie folgt umgeschrieben werden:
Die WINDOW Die Klausel sollte, falls enthalten, immer nach der WHERE -Klausel stehen.
Erweiterte Fenstertechniken
Sie können eine vollständige Liste der Fenster auschecken Funktionen in Postgres (der Syntaxmodus verwendet) in der Postgres-Dokumentation. Wenn Sie Fensterfunktionen in einer verbundenen Datenbank verwenden, sollten Sie die entsprechende Syntaxanleitung für Ihr System lesen.