Calcolo dei totali correnti utilizzando SQL Ottobre 20, 2015 / Andy Granowitz
Quanti utenti si sono iscritti negli ultimi 5 mesi? Quali sono state le vendite totali in Q2? Quante entrate è venuto dal marzo iscriviti coorte?
Sebbene queste domande possano essere risolte con un singolo numero, può essere utile vedere un totale corrente nel tempo: quanti utenti unici hanno aderito o quante entrate cumulative sono state ricevute di giorno in un certo periodo.
Di solito, i dati vengono memorizzati in modo incrementale. Ad esempio, ecco una tabella delle vendite al giorno:
| 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 questo caso, stiamo confrontando ogni data con qualsiasi data minore o uguale ad essa per calcolare il totale corrente. Concretamente, prendiamo la somma di sales nella seconda tabella su ogni riga che ha una data minore o uguale alla data proveniente dalla prima tabella. Questa è la sintassi Postgres / Redshift, ma altri dialetti SQL sono molto simili.
Questo non è un cattivo approccio; è una bella vetrina di come SQL estensibile può usare solo selectfromjoine group by istruzioni.
Ma è un sacco di codice per un compito semplice. Proviamo una funzione finestra. Sono progettati per calcolare una metrica su un insieme di righe. Nel nostro caso, vogliamo sommare ogni riga in cui la data è minore o uguale alla data nella riga corrente.
La funzione finestra può filtrare e disporre il set di righe per eseguire la funzione. Qui order by date rows unbounded preceding limita la funzione sum solo a sales prima della data della riga corrente. Le funzioni della finestra sono incredibilmente utili per le query analitiche basate sul tempo; per saperne di più, i documenti Postgres sono un ottimo punto di partenza.
La fase finale di creare un grafico e condividerlo trionfalmente con i tuoi compagni di squadra è facilmente realizzabile utilizzando Wagon. Funzioni finestra per la vittoria!
Wagon è un moderno editor SQL per analisti e ingegneri: scrivi query, visualizza dati e condividi grafici con il tuo team. Registrati gratuitamente: