beräkning av löpande summor med SQL oktober 20, 2015 | Andy Granowitz
hur många användare gick med de senaste 5 månaderna? Vad var total försäljning i Q2? Hur mycket intäkter kom från Mars sign up kohort?
Även om dessa frågor kan besvaras med ett enda nummer, kan det vara användbart att se en löpande summa över tiden: hur många unika användare gick med, eller hur mycket kumulativa intäkter mottogs per dag under en viss period.
vanligtvis lagras data stegvis. Till exempel, här är en tabell över försäljning 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. I det här fallet jämför vi varje datum med ett datum som är mindre än eller lika med det för att beräkna den löpande summan. Konkret tar vi summan av sales I den andra tabellen över varje rad som har ett datum som är mindre än eller lika med datumet som kommer från den första tabellen. Detta är Postgres / Redshift syntax, men andra SQL-dialekter är mycket lika.
detta är inte ett dåligt tillvägagångssätt; det är en trevlig presentation av hur extensible SQL kan använda endast selectfromjoin och group by uttalanden.
men det är mycket kod för en enkel uppgift. Låt oss prova en fönsterfunktion. De är utformade för att beräkna ett mått över en uppsättning rader. I vårt fall vill vi summera varje rad där datumet är mindre än eller lika med datumet i den aktuella raden.
fönsterfunktionen kan filtrera och ordna uppsättningen rader för att köra funktionen över. Här begränsarorder by date rows unbounded preceding sum-funktionen till endastsales före datumet för den aktuella raden. Fönsterfunktioner är otroligt användbara för tidsbaserade analytiska frågor; för att lära dig mer är Postgres docs ett bra ställe att börja.
det sista steget att skapa ett diagram och dela det triumferande med dina lagkamrater uppnås enkelt med Wagon. Fönster funktioner för att vinna!
Wagon är en modern SQL editor för analytiker och ingenjörer: skriva frågor, visualisera data och dela diagram med ditt team. Registrera dig gratis: