berekening van lopende totalen met behulp van SQL 20 oktober 2015 | Andy Granowitz
hoeveel gebruikers zijn in de afgelopen 5 maanden toegetreden? Wat waren de totale verkopen in Q2? Hoeveel inkomsten kwam uit de Maart sign up cohort?
hoewel deze vragen kunnen worden beantwoord met een enkel nummer, kan het nuttig zijn om een lopend totaal in de loop van de tijd te zien: hoeveel unieke gebruikers zijn toegetreden, of hoeveel cumulatieve inkomsten per dag werd ontvangen over een bepaalde periode.
gewoonlijk worden gegevens stapsgewijs opgeslagen. Bijvoorbeeld, hier is een tabel van de verkoop per dag:
| 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 dit geval vergelijken we elke datum met een datum kleiner dan of gelijk aan het om het lopende totaal te berekenen. Concreet nemen we de som van sales in de tweede tabel over elke rij die een datum heeft die kleiner is dan of gelijk is aan de datum die uit de eerste tabel komt. Dit is Postgres / Redshift syntaxis, maar andere SQL dialecten zijn zeer vergelijkbaar.
Dit is geen slechte benadering; het is een mooi voorbeeld van hoe uitbreidbaar SQL alleen selectfromjoin, en group by statements kan gebruiken.
maar het is veel code voor een eenvoudige taak. Laten we een raamfunctie proberen. Ze zijn ontworpen om een metriek over een reeks rijen te berekenen. In ons geval willen we elke rij optellen waarbij de datum kleiner is dan of gelijk is aan de datum in de huidige rij.
De vensterfunctie kan de reeks rijen filteren en rangschikken om de functie uit te voeren. Hier beperkt de order by date rows unbounded preceding de somfunctie tot alleen sales vóór de datum van de huidige rij. Vensterfuncties zijn ongelooflijk nuttig voor op tijd gebaseerde analytische queries; voor meer informatie, de Postgres docs zijn een geweldige plek om te beginnen.
de laatste stap van het maken van een grafiek en het triomfantelijk delen met je teamgenoten is eenvoudig te bereiken met Wagon. Window functies voor de win!
Wagon is een moderne SQL-editor voor analisten en ingenieurs: schrijf queries, Visualiseer gegevens en deel grafieken met uw team. Registreer gratis: