Qu'est-ce qu'un total courant SQL et comment le calculer ?

Le total courant SQL est un modèle très courant, fréquemment utilisé en finance et dans l'analyse des tendances. Dans cet article, vous allez apprendre ce qu'est un total courant et comment écrire une requête SQL pour le calculer.

La meilleure façon d'apprendre le langage SQL est de le pratiquer. LearnSQL.fr propose plus de 30 cours SQL interactifs de différents niveaux de difficulté. Chaque cours est interactif : il y a un peu de lecture, suivie d'un exercice pour mettre en pratique ce que vous venez de lire. Avec chaque exercice résolu, vous gagnez en confiance dans vos compétences SQL. Inscrivez-vous dès maintenant et gratuitement !

Qu'est-ce qu'un total courant SQL ?

En SQL, un total courant est la somme cumulée des chiffres précédents dans une colonne. Regardez l'exemple ci-dessous, qui présente l'enregistrement quotidien des utilisateurs d'une boutique en ligne :

La première colonne indique la date. La deuxième colonne indique le nombre d'utilisateurs qui se sont inscrits à cette date. La troisième colonne, total_utilisateurs, résume le nombre total d'utilisateurs enregistrés ce jour-là.

Par exemple, le premier jour (2020-03-05), 32 utilisateurs se sont inscrits et la valeur totale des utilisateurs inscrits était de 32. Le jour suivant (2020-03-06), 15 utilisateurs se sont inscrits ; la valeur de total_users est devenue 47 (32+15). Le troisième jour (2020-03-07), six utilisateurs se sont inscrits et la valeur de total_users était de 53. En d'autres termes, total_users est une valeur courante qui change d'un jour à l'autre. Il s'agit du nombre total d'utilisateurs pour chaque jour.

L'exemple suivant utilise la colonne total_courant pour traiter les revenus de la société de manière similaire. Regardez le tableau ci-dessous :

Pour chaque jour, la colonne total_revenu calcule le montant des revenus générés jusqu'au jour donné. Le 04-04-2020, l'entreprise a réalisé un revenu total de 270 500 $, car il s'agit de la somme de tous les revenus du 02-04-2020 au 04-04-2020.

Les bases de données relationnelles (comme SQL Server, Oracle, PostgreSQL et MySQL) et même les moteurs non relationnels comme Hive et Presto fournissent des fonctions de fenêtre qui nous permettent de calculer un total courant. Pour en savoir plus sur les fonctions de fenêtre, je vous recommande le cours interactif Fonctions de fenêtrage cours interactif. Il contient plus de 200 exercices pour apprendre les fonctions de fenêtre en les utilisant.

Nous allons maintenant parler de la requête SQL qui permet de calculer cette somme et en apprendre davantage sur les fonctions de fenêtre.

Comment calculer une somme cumulée en SQL ?

Si vous souhaitez calculer un total cumulé en SQL, vous devez vous familiariser avec les fonctions de fenêtre fournies par votre base de données. Les fonctions de fenêtre opèrent sur un ensemble de lignes et renvoient une valeur agrégée pour chaque ligne de l'ensemble de résultats.

La syntaxe de la fonction fenêtre SQL qui calcule une somme cumulée sur plusieurs lignes est la suivante :

window_function ( column ) 
OVER ( [ PARTITION BY partition_list ] [ ORDER BY order_list] )

Il est obligatoire d'utiliser la clause OVER dans une fonction fenêtre, mais les arguments de cette clause sont facultatifs. Nous les aborderons dans les prochains paragraphes de cet article.

Exemple 1

Dans cet exemple, nous allons calculer la somme totale courante des utilisateurs enregistrés chaque jour.

Cette requête ...

SELECT registration_date,registred_users,
  SUM(registred_users) OVER (ORDER BY registration_date)
  AS total_users
FROM registration;

... sélectionne la date d'enregistrement de tous les utilisateurs. Nous avons également besoin de la somme de tous les utilisateurs pour chaque jour, à partir du premier jour donné (2020-03-05) jusqu'au jour de cette ligne.

C'est le jeu de résultats :

Pour calculer le total courant, nous utilisons la fonction d'agrégation SUM() et mettons la colonne registered_users comme argument ; nous voulons obtenir la somme cumulative des utilisateurs de cette colonne.

L'étape suivante consiste à utiliser la clause OVER. Dans notre exemple, cette clause a un seul argument : ORDER BY registration_date. Les lignes de l'ensemble de résultats sont triées en fonction de cette colonne (registration_date). Pour chaque valeur de la colonne registration_date, la somme totale des valeurs des colonnes précédentes est calculée (c'est-à-dire la somme du nombre d'utilisateurs avant la date de la ligne actuelle) et la valeur actuelle (c'est-à-dire les utilisateurs enregistrés le jour de la ligne actuelle) est ajoutée à celle-ci.

Remarquez que la somme totale est indiquée dans la nouvelle colonne, que nous avons nommée total_users.

Dans la première étape (la date d'enregistrement 2020-03-05), nous avons 57 utilisateurs enregistrés. La somme des utilisateurs enregistrés ce jour est la même 57. Dans l'étape suivante, nous ajoutons à cette valeur totale (57). Qu'ajoutons-nous ? Le nombre d'utilisateurs enregistrés à la date actuelle (2020-03-06), soit 27, ce qui nous donne un total courant de 84. Dans la dernière ligne du jeu de résultats (pour la dernière date d'enregistrement, 2020-03-07), le total courant est de 100.

Grâce aux fonctions de la fenêtre SQL, il est facile de trouver le nombre total cumulé d'utilisateurs pendant une période donnée. Par exemple, pendant la période 2020-03-05 - 2020-03-06, le nombre total d'utilisateurs enregistrés était de 84.

Exemple 2

Dans le deuxième exemple, nous allons entrer dans plus de détails sur les utilisateurs. Nous allons montrer les utilisateurs avec leurs pays. Regardez le tableau ci-dessous :

Remarquez que pour chaque jour, le nombre d'utilisateurs de chaque pays est indiqué séparément. Dans cet exemple, nous allons calculer une somme cumulative séparée des utilisateurs enregistrés pour chaque pays.

Cette requête ...

SELECT country, registration_date,registred_users,
  SUM(registred_users) 
  OVER (PARTITION BY country ORDER BY registration_date)
  AS total_users
FROM registration;

... calcule la somme des utilisateurs pour chaque jour, d'abord pour les utilisateurs d'Angleterre et ensuite pour les utilisateurs de Pologne.

Voici l'ensemble des résultats :

Pour chaque pays, chaque jour d'inscription obtient un total courant. La clause PARTITION BY de la clause OVER a pour argument la colonne country. Cela permet de diviser les lignes par pays, ce qui permet à SQL de calculer un total courant pour ce pays uniquement (au lieu des deux pays ensemble). Ainsi, en Angleterre, du 2020-03-05 au 2020-03-07, nous avons un total de 47 utilisateurs. Pour la même période en Pologne, le total des utilisateurs enregistrés était de 53.

Exemple 3

Dans le dernier exemple, nous allons analyser les données de la table competition qui contient les colonnes game_id, gamer_id, game_level, competition_date et score.

Nous devons vérifier le score total cumulé de chaque joueur pour chaque jour dans deux jeux différents. Regardez la requête ci-dessous, qui crée ce total courant :

SELECT game_id,game_level,gamer_id,competition_date,score,
  SUM(score)
  OVER (PARTITION BY game_id, gamer_id 
        ORDER BY competition_date)
  AS total_score
FROM competition;

Le résultat :

Dans ce tableau de résultats, nous pouvons lire que le joueur avec ID=4 part d'un score de 4 et termine avec un score total de 11. Le meilleur était le joueur avec ID=7, qui a terminé avec un score total de 12.

Une fois encore, dans la clause OVER, nous utilisons PARTITION BY. Cette fois, nous utilisons une liste de colonnes (game_id, gamer_id). Cela nous permet de créer deux partitions : une pour le jeu 1 et une pour le jeu 2.

Ensuite, les lignes sont divisées par gamer_id pour chaque jeu. Dans le jeu 1, nous avons les gamers 4 et 5 ; dans le jeu 2, nous avons les gamers 6, 7 et 8. Dans chaque groupe (un joueur donné joue dans un jeu donné), les lignes sont triées par date de compétition et le score de chaque jour est additionné. Dans chaque groupe, nous pouvons observer l'évolution du score de chaque joueur dans un jeu donné.

Comment utiliserez-vous les totaux courants SQL ?

L'utilisation d'une valeur de total courant dans les rapports SQL peut être très pratique, notamment pour les spécialistes de la finance. Il est donc utile de savoir ce qu'est une somme cumulée et comment utiliser les fonctions de la fenêtre SQL pour en créer une. Cet article présente quelques cas d'utilisation sélectionnés. Pour en savoir plus sur les fonctions de fenêtre, consultez notre article Exemple de fonction de fenêtre SQL avec explications ou le cours LearnSQL Fonctions de fenêtrage.