Laufende Summen mit SQL berechnen 20. Oktober 2015 / Andy Granowitz
Wie viele Benutzer sind in den letzten 5 Monaten beigetreten? Wie hoch war der Gesamtumsatz in Q2? Wie viel Einnahmen kamen von der März-Anmeldekohorte?Obwohl diese Fragen mit einer einzigen Zahl beantwortet werden können, kann es nützlich sein, eine laufende Summe im Laufe der Zeit zu sehen: Wie viele Unique User beigetreten sind oder wie viel kumulierter Umsatz pro Tag über einen bestimmten Zeitraum erzielt wurde.
Normalerweise werden Daten inkrementell gespeichert. Zum Beispiel, hier ist eine Tabelle der Verkäufe pro Tag:
| Date | Sales |
|---|---|
| 10/1/2015 | 5 |
| 10/2/2015 | 3 |
| 10/3/2015 | 7 |
| 10/4/2015 | 8 |
| 10/5/2015 | 2 |
| 10/6/2015 | 3 |
| 10/7/2015 | 6 |
How do we generate the following table of cumulative sales over time? In SQL, there are two typical approaches: a self join or a window function.
| Date | Running Total of Sales |
|---|---|
| 10/1/2015 | 5 |
| 10/2/2015 | 8 |
| 10/3/2015 | 15 |
| 10/4/2015 | 23 |
| 10/5/2015 | 25 |
| 10/6/2015 | 28 |
| 10/7/2015 | 34 |
A self join is a query that compares a table to itself. In diesem Fall vergleichen wir jedes Datum mit einem Datum, das kleiner oder gleich ist, um die laufende Summe zu berechnen. Konkret nehmen wir die Summe von sales in der zweiten Tabelle über jede Zeile, deren Datum kleiner oder gleich dem Datum aus der ersten Tabelle ist. Dies ist die Postgres / Redshift-Syntax, aber andere SQL-Dialekte sind sehr ähnlich.
Dies ist kein schlechter Ansatz; es ist ein schönes Beispiel dafür, wie erweiterbar SQL nur mit selectfromjoin und group by Anweisungen sein kann.
Aber es ist eine Menge Code für eine einfache Aufgabe. Versuchen wir eine Fensterfunktion. Sie dienen zur Berechnung einer Metrik über einen Satz von Zeilen. In unserem Fall möchten wir jede Zeile summieren, in der das Datum kleiner oder gleich dem Datum in der aktuellen Zeile ist.
Die Fensterfunktion kann den Satz von Zeilen filtern und anordnen, über den die Funktion ausgeführt werden soll. Hier beschränkt die order by date rows unbounded preceding die Summenfunktion auf nur sales vor dem Datum der aktuellen Zeile. Fensterfunktionen sind unglaublich nützlich für zeitbasierte analytische Abfragen; lesen Sie weiter, die Postgres-Dokumente sind ein großartiger Ort zu starten.
Der letzte Schritt, ein Diagramm zu erstellen und es triumphierend mit Ihren Teamkollegen zu teilen, ist mit Wagon einfach zu bewerkstelligen. Fensterfunktionen für den Win!Wagon ist ein moderner SQL-Editor für Analysten und Ingenieure: Schreiben Sie Abfragen, visualisieren Sie Daten und teilen Sie Diagramme mit Ihrem Team. Kostenlos anmelden: