Calcul des Totaux en cours d’exécution à l’aide de SQL 20 octobre 2015| Andy Granowitz
Combien d’utilisateurs ont rejoint au cours des 5 derniers mois ? Quelles ont été les ventes totales au 2e trimestre? Combien de revenus provenaient de la cohorte d’inscription de mars?
Bien que ces questions puissent être répondues avec un seul numéro, il peut être utile de voir un total courant au fil du temps: combien d’utilisateurs uniques ont rejoint, ou combien de revenus cumulés ont été reçus par jour sur une certaine période.
Habituellement, les données sont stockées de manière incrémentielle. Par exemple, voici un tableau des ventes par jour:
| 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. Dans ce cas, nous comparons chaque date à toute date inférieure ou égale à celle-ci afin de calculer le total courant. Concrètement, nous prenons la somme de sales dans la deuxième table sur chaque ligne qui a une date inférieure ou égale à la date provenant de la première table. Il s’agit de la syntaxe Postgres / Redshift, mais d’autres dialectes SQL sont très similaires.
Ce n’est pas une mauvaise approche ; c’est une belle démonstration de la façon dont SQL extensible peut utiliser uniquement des instructions selectfromjoin et group by.
Mais c’est beaucoup de code pour une tâche simple. Essayons une fonction de fenêtre. Ils sont conçus pour calculer une métrique sur un ensemble de lignes. Dans notre cas, nous voulons additionner chaque ligne où la date est inférieure ou égale à la date de la ligne actuelle.
La fonction fenêtre peut filtrer et organiser l’ensemble de lignes sur lesquelles exécuter la fonction. Ici, order by date rows unbounded preceding limite la fonction somme à seulement sales avant la date de la ligne en cours. Les fonctions de fenêtre sont incroyablement utiles pour les requêtes analytiques basées sur le temps; pour en savoir plus, les documents Postgres sont un excellent point de départ.
La dernière étape de la création d’un graphique et de son partage triomphal avec vos coéquipiers se fait facilement à l’aide de Wagon. Fonctions de fenêtre pour la victoire!Wagon est un éditeur SQL moderne pour les analystes et les ingénieurs : écrivez des requêtes, visualisez des données et partagez des graphiques avec votre équipe. Inscription gratuite :