Fensterfunktionen sind eine Gruppe von Funktionen, die Berechnungen für eine Reihe von Zeilen durchführen, die sich auf Ihre aktuelle Zeile beziehen. Sie gelten als fortgeschrittene SQL-Experten und werden häufig in Interviews im Bereich Data Science gefragt. Es wird auch häufig bei der Arbeit verwendet, um viele verschiedene Arten von Problemen zu lösen. Lassen Sie uns die vier verschiedenen Arten von Fensterfunktionen zusammenfassen und erläutern, warum und wann Sie sie verwenden würden.
4 Arten von Fensterfunktionen
1. Reguläre Aggregatfunktionen
o Dies sind Aggregate wie AVG, MIN/MAX, COUNT, SUM
o Sie möchten diese verwenden, um Ihre Daten zu aggregieren und nach einer anderen Spalte wie Monat oder Jahr zu gruppieren
2. Ranking-Funktionen
o ROW_NUMBER, RANK, RANK_DENSE
o Dies sind Funktionen, die Ihnen beim Ranking Ihrer Daten helfen. Sie können entweder Ihren gesamten Datensatz einordnen oder ihn nach Gruppen, beispielsweise nach Monat oder Land, einordnen
o Äußerst nützlich, um Ranking-Indizes innerhalb von Gruppen zu erstellen
3. Statistiken erstellen
o Diese eignen sich hervorragend, wenn Sie einfache Statistiken wie NTILE (Perzentile, Quartile, Mediane) erstellen müssen.
o Sie können dies für Ihren gesamten Datensatz oder pro Gruppe verwenden
4. Umgang mit Zeitreihendaten
o Eine sehr häufige Fensterfunktion, insbesondere wenn Sie Trends wie einen gleitenden Durchschnitt von Monat zu Monat oder eine Wachstumsmetrik berechnen müssen
o LAG und LEAD sind die beiden Funktionen, die Ihnen dies ermöglichen.
1. Reguläre Aggregatfunktion
Reguläre Aggregatfunktionen sind Funktionen wie Durchschnitt, Anzahl, Summe, Min/Max, die auf Spalten angewendet werden. Das Ziel besteht darin, die Aggregatfunktion anzuwenden, wenn Sie Aggregationen auf verschiedene Gruppen im Datensatz anwenden möchten, beispielsweise auf den Monat.
Dies ähnelt der Art der Berechnung, die mit einer Aggregatfunktion durchgeführt werden kann, die Sie in der SELECT-Klausel finden, aber im Gegensatz zu regulären Aggregatfunktionen gruppieren Fensterfunktionen nicht mehrere Zeilen in einer einzigen Ausgabezeile, sondern werden gruppiert oder behalten ihre eigene Identität, je nachdem, wie Sie sie finden.
Avg() Beispiel:
Schauen wir uns ein Beispiel einer avg()-Fensterfunktion an, die zur Beantwortung einer Datenanalysefrage implementiert wurde. Sie können die Frage ansehen und Code über den folgenden Link schreiben:
platform.stratascratch.com/coding-question?id=10302&python=
Dies ist ein perfektes Beispiel für die Verwendung einer Fensterfunktion und die anschließende Anwendung von avg() auf eine Monatsgruppe. Hier versuchen wir, die durchschnittliche Entfernung pro Dollar pro Monat zu berechnen. Ohne diese Fensterfunktion ist dies in SQL kaum möglich. Hier haben wir die Fensterfunktion avg() auf die dritte Spalte angewendet, wo wir für jeden Monat und jedes Jahr im Datensatz den Durchschnittswert für Monat und Jahr ermittelt haben. Mit dieser Metrik können wir die Differenz zwischen dem Monatsdurchschnitt und dem Datumsdurchschnitt für jedes Anfragedatum in der Tabelle berechnen.
Der Code zur Implementierung der Fensterfunktion würde wie folgt aussehen:
SELECT a.request_date,
a.dist_to_cost,
AVG(a.dist_to_cost) OVER(PARTITION BY a.request_mnth) AS avg_dist_to_cost
AUS
(WÄHLEN *,
to_char(request_date::date, ‚YYYY-MM‘) AS request_mnth,
(distance_to_travel/monetary_cost) AS dist_to_cost
VON uber_request_logs) a
BESTELLEN NACH request_date
2. Ranking-Funktionen
Ranking-Funktionen sind ein wichtiges Hilfsmittel für einen Datenwissenschaftler. Sie ordnen und indizieren Ihre Daten ständig, um besser zu verstehen, welche Zeilen in Ihrem Datensatz die besten sind. SQL-Fensterfunktionen bieten Ihnen drei Ranking-Dienstprogramme – RANK(), DENSE_RANK(), ROW_NUMBER() – abhängig von Ihrem genauen Anwendungsfall. Mithilfe dieser Funktionen können Sie Ihre Daten nach Ihren Wünschen geordnet und in Gruppen auflisten.
Rank() Beispiel:
Schauen wir uns ein Beispiel für eine Ranking-Fensterfunktion an, um zu sehen, wie wir mithilfe von SQL-Fensterfunktionen Daten innerhalb von Gruppen einordnen können. Folgen Sie interaktiv diesem Link: platform.stratascratch.com/coding-question?id=9898&python=
Hier wollen wir die Top-Gehälter nach Abteilungen finden. Ohne Fensterfunktion können wir nicht einfach die Top-3-Gehälter finden, da wir dadurch nur die Top-3-Gehälter aller Abteilungen erhalten. Daher müssen wir die Gehälter individuell nach Abteilungen ordnen. Dies erfolgt durch rank() und wird nach Abteilungen aufgeteilt. Von dort aus ist es ganz einfach, abteilungsübergreifend nach den Top 3 zu filtern
Hier ist der Code zur Ausgabe dieser Tabelle. Sie können den obigen Link kopieren und in den SQL-Editor einfügen und die gleiche Ausgabe sehen.
SELECT-Abteilung,
Gehalt,
RANK() OVER (PARTITION BY a.department
ORDER BY a.salary DESC) AS rank_id
AUS
(Abteilung, Gehalt auswählen
VON twitter_employee
GRUPPE NACH Abteilung, Gehalt
Reihenfolge nach Abteilung, Gehalt) a
BESTELLEN NACH Abteilung,
Gehalt DESC
3. NTIL
NTILE ist eine sehr nützliche Funktion für diejenigen in den Bereichen Datenanalyse, Geschäftsanalyse und Datenwissenschaft. Wenn es um statistische Daten geht, müssen Sie bei Ihrer täglichen Arbeit wahrscheinlich robuste Statistiken wie Quartil, Quintil, Median, Dezil erstellen, und NTILE macht es einfach, diese Ausgaben zu generieren.
NTILE nimmt ein Argument für die Anzahl der Bins (oder im Grunde genommen für die Anzahl der Buckets, in die Sie Ihre Daten aufteilen möchten) und erstellt dann diese Anzahl von Bins, indem es Ihre Daten in so viele Bins aufteilt. Sie legen fest, wie die Daten geordnet und partitioniert werden, wenn Sie zusätzliche Gruppierungen wünschen.
NTILE(100) Beispiel
In diesem Beispiel lernen wir, wie wir NTILE verwenden, um unsere Daten in Perzentile zu kategorisieren. Über den Link hier können Sie interaktiv mitverfolgen: platform.stratascratch.com/coding-question?id=10303&python=
Was Sie hier versuchen, ist, die obersten 5 Prozent der Ansprüche anhand einer von einem Algorithmus ausgegebenen Punktzahl zu identifizieren. Aber Sie können nicht einfach die besten 5 % finden und eine Bestellung aufgeben, weil Sie die besten 5 % nach Bundesstaat suchen möchten. Eine Möglichkeit, dies zu tun, besteht also darin, eine NTILE()-Ranking-Funktion zu verwenden und dann nach dem Status zu PARTITIONIEREN. Anschließend können Sie in der WHERE-Klausel einen Filter anwenden, um die oberen 5 % zu erhalten.
Hier ist der Code zur Ausgabe der gesamten Tabelle oben. Sie können es kopieren und in den obigen Link einfügen.
SELECT Policy_num,
Zustand,
Claim_cost,
betrug_score,
Perzentil
AUS
(WÄHLEN *,
NTILE(100) OVER(PARTITION BY state
ORDER BY betrug_score DESC) AS-Perzentil
VON Fraud_Score) a
WHERE Perzentil <=5
4. Umgang mit Zeitreihendaten
LAG und LEAD sind zwei Fensterfunktionen, die für den Umgang mit Zeitreihendaten nützlich sind. Der einzige Unterschied zwischen LAG und LEAD besteht darin, ob Sie aus vorherigen oder folgenden Zeilen greifen möchten, fast wie eine Stichprobe aus früheren oder zukünftigen Daten.
Sie können LAG und LEAD verwenden, um das monatliche Wachstum oder gleitende Durchschnitte zu berechnen. Als Datenwissenschaftler und Geschäftsanalyst beschäftigen Sie sich ständig mit Zeitreihendaten und erstellen diese Zeitmetriken.
LAG() Beispiel:
In diesem Beispiel möchten wir das prozentuale Wachstum im Jahresvergleich ermitteln. Dies ist eine sehr häufige Frage, die Datenwissenschaftler und Geschäftsanalysten täglich beantworten. Wenn Sie versuchen möchten, die Lösung selbst zu programmieren, finden Sie die Problemstellung, die Daten und den SQL-Editor unter dem folgenden Link: platform.stratascratch.com/coding-question?id=9637&python=
Das Schwierige an diesem Problem ist, dass die Daten eingerichtet sind – Sie müssen den Wert der vorherigen Zeile in Ihrer Metrik verwenden. Aber dafür ist SQL nicht ausgelegt. SQL ist darauf ausgelegt, alles zu berechnen, was Sie wollen, solange sich die Werte in derselben Zeile befinden. Wir können also die Fensterfunktion „lag()“ oder „lead()“ verwenden, die die vorherigen oder nachfolgenden Zeilen übernimmt und in Ihre aktuelle Zeile einfügt, was bei dieser Frage der Fall ist.
Hier ist der Code zur Ausgabe der gesamten Tabelle oben. Sie können den Code über den obigen Link kopieren und in den SQL-Editor einfügen:
Jahr auswählen,
current_year_host,
prev_year_host,
Round(((current_year_host – prev_year_host)/(cast(prev_year_host AS numeric)))*100) geschätztes_wachstum
AUS
(Jahr auswählen,
current_year_host,
LAG(current_year_host, 1) OVER (ORDER BY year) AS prev_year_host
AUS
(SELECT extract(Jahr
FROM host_since::date) AS Jahr,
count(id) current_year_host
VON airbnb_search_details
WO host_since NICHT NULL IST
GROUP BY-Auszug (Jahr
VON host_since::date)
ORDNUNG NACH Jahr) t1) t2