7 situations réelles où vous avez besoin d'un total courant et comment le calculer en SQL

Voulez-vous apprendre ce qu'est un total courant et comment le calculer en SQL ? Dans cet article, je vais décrire différents scénarios commerciaux dans lesquels un total courant est nécessaire. Je vous apprendrai également comment calculer un total courant en SQL à l'aide des fonctions de la fenêtre. Vous êtes prêt ? C'est parti !

Définition du total courant

Un total courant est la somme cumulée d'une valeur et de toutes les valeurs précédentes dans la colonne.

Par exemple, imaginez que vous travaillez dans le secteur des ventes et que vous stockez des informations sur le nombre d'articles vendus un jour donné. Vous pourriez vouloir calculer un total courant, c'est-à-dire le nombre total d'articles vendus jusqu'à une date spécifique.

Vous trouverez ci-dessous un tableau contenant le nombre d'articles vendus un jour donné :

Nombre d'articles vendus et total courant

La troisième colonne est le total courant calculé. N'oubliez pas que la valeur de la ligne actuelle est toujours incluse dans le total courant.

Par exemple, le 5 janvier 2021, le total courant est de 66. Il s'agit du nombre total d'articles vendus du 01/01/2021 au 05/01/2021 (y compris le 05/01/2021). Plus précisément, le calcul est 10 + 12 + 15 + 9 + 20 = 66.

Calcul du total courant en SQL

En SQL, le total courant est calculé à l'aide des fonctions de la fenêtre. Cette fonction SQL spéciale permet d'effectuer des calculs complexes avec seulement quelques lignes de code SQL.

Voici la fonction fenêtre pour notre exemple ci-dessus :

SELECT *,
      SUM(no_of_items) OVER(ORDER BY date) AS running_total 
FROM sales

Et voici une explication du code :

• Il s'agit d'une fonction fenêtre, indiquée par la clause OVER.
• À l'intérieur de la clause OVER, il y a un ORDER BY. Cela nous indique comment les lignes sont ordonnées avant que le calcul soit effectué. Dans notre cas, les lignes sont ordonnées par la colonne date.
• Lorsque vous utilisez les fonctions de fenêtre, chaque enregistrement reçoit son propre calcul. Ainsi, le résultat est affiché avec une colonne supplémentaire. Il n'y a pas de lignes réduites lorsque vous travaillez avec les fonctions de fenêtre. C'est la principale différence entre la clause GROUP BY et la clause Fonctions de fenêtre en SQL.
• À l'intérieur de la clause OVER, vous pouvez également trouver le mot-clé PARTITION BY. PARTITION BY regroupe les lignes en partitions dans lesquelles chaque calcul de partition est effectué séparément. Dans notre exemple, les lignes ne sont pas organisées en partitions. Ainsi, l'ensemble des données est traité comme un seul groupe.
• Lorsque l'on travaille avec des fonctions de fenêtre, pour chaque enregistrement d'une table, on définit un cadre de fenêtre à l'intérieur duquel un calcul spécifique est effectué. Ceci est indiqué par le mot-clé ROW/RANGE à l'intérieur de l'instruction OVER. Si ce mot-clé n'est pas défini (comme dans notre exemple), une valeur par défaut est prise pour le cadre de la fenêtre. Le cadre de fenêtre par défaut de la ligne actuelle comprend la ligne actuelle et toutes les lignes précédentes.
• Dans notre exemple, la somme totale de chaque enregistrement de la table est calculée en additionnant toutes les valeurs précédentes plus la valeur actuelle. Par exemple, le total courant pour le 05 janvier 2021 est de 66. Il s'agit de la somme des quatre valeurs précédentes (10, 12, 15 et 9) plus la valeur de la ligne actuelle (20).

Exemples de totaux courants

Voyons maintenant des scénarios réels dans lesquels les totaux mobiles sont utilisés.

1) Suivi des quotas planifiés et des quotas réalisés

Dans le domaine de la vente, chaque vendeur doit atteindre des objectifs pour satisfaire aux exigences spécifiques de son poste. De même, dans les secteurs des télécommunications et de la banque, chaque division doit acquérir un nombre spécifique de nouveaux clients par trimestre et vendre un nombre spécifique de produits aux clients.

La direction suit les performances en utilisant des totaux courants. Chaque jour, le total courant est mis à jour avec de nouvelles données. Ces chiffres sont généralement évalués mensuellement, trimestriellement ou annuellement.

Voici un tableau qui suit le nombre de nouveaux prêts en 2021 sur une base mensuelle :

Nombre de nouveaux prêts, prévus et réalisés.

Pour le premier semestre de 2021, il a été décidé que 535 prêts devraient être vendus aux clients pendant cette période. Des plans sont établis pour chaque mois séparément, et 535 est l'objectif total pour ces six mois.

La direction suit les quotas planifiés par rapport aux quotas réalisés. Pour l'instant, 112 nouveaux prêts ont été vendus. Le tableau continuera à être mis à jour au fil du temps.

Vous trouverez ci-dessous le code SQL qui calcule les totaux courants :

SELECT 
      date,
      plan,
      SUM(plan) OVER(order by Date) AS plan_running_total,
      realization,
      SUM(realization) OVER(order by Date) AS realization_running_total
FROM sales;

Comme dans l'exemple précédent, une fonction fenêtre est utilisée pour calculer le total courant. La fonction fenêtre est désignée par OVER, et à l'intérieur des parenthèses, l'ordre des lignes est défini.

La fonction d'agrégation utilisée est SUM. Nous avons deux totaux courants : ?les quotas planifiés et les quotas réalisés. Nous avons donc deux fonctions de fenêtre, et deux colonnes sont ajoutées au tableau.

2. Calculs de solde

Les totaux courants sont également utilisés pour calculer un solde. Chaque fois qu'une nouvelle transaction est effectuée (un paiement ou un retrait sur un compte), la somme cumulée est rafraîchie et le solde actuel est affiché.

Vous trouverez ci-dessous un tableau de solde :

Montant de la transaction et solde actuel

Dans le tableau ci-dessus, nous voyons que la première transaction a eu lieu le 01 déc 2020-12-01, soit une entrée de 5 000 $. Le solde ce jour-là était de 5 000 $. Ensuite, le client a commencé à dépenser de l'argent.

Le 03 décembre 2020, le client a dépensé 50 $ (cette transaction est représentée par une valeur négative). Le solde est donc passé à 4 950 $. Le jour suivant, le solde a diminué de 125 dollars supplémentaires pour atteindre 4 825 dollars, et ainsi de suite.

Le solde du compte est calculé comme un total courant. Il s'agit de la somme cumulée de toutes les transactions associées à ce compte. À chaque nouvelle transaction, le solde est mis à jour, c'est-à-dire que le total courant est recalculé.

Voici la fonction de la fenêtre pour cet exemple :

SELECT date,
            transaction,
           SUM(transaction) OVER(order by date) AS balance_amount 
FROM balance;

3. Opérations de caisse

Les totaux courants sont également utilisés dans les opérations de caisse.

Par exemple, lorsqu'un client achète des articles dans un magasin, la caissière scanne les articles dans le panier. Pour chaque nouvel article scanné, le total courant mis à jour s'affiche à l'écran. Il indique le montant que le client doit payer pour les articles scannés jusqu'à présent.

Voici l'exemple d'un panier en cours de numérisation. Les couches sont l'article le plus récemment scanné. Pour l'instant, le client doit payer 19,70 EUR. Le shampooing n'a pas encore été traité, son prix n'est donc pas encore calculé dans le total courant :

Caisse enregistreuse : balayage des produits

Il s'agit pratiquement de la même fonction de fenêtre que dans l'exemple précédent (seuls les noms de table et de colonne diffèrent) :

SELECT 
       *,
      SUM(price) OVER(order by date) as running_total 
FROM cash_register;

Ainsi, chaque fois que la caissière scanne un article, la somme cumulée est mise à jour. À chaque balayage, la caissière sait combien le client doit payer jusqu'à présent.

4. Compter l'apport calorique quotidien

Si vous souhaitez perdre du poids, vous pouvez utiliser un total courant pour calculer votre apport calorique quotidien. Chaque jour, vous commencez à compter les calories à zéro, puis vous mettez à jour le total courant en fonction de ce que vous mangez et de la quantité que vous consommez.

Vous trouverez ci-dessous un décompte de calories pour une journée :

Le total courant est mis à jour chaque fois que vous mangez quelque chose. Ainsi, vous savez toujours combien vous avez déjà mangé et combien vous pouvez encore manger ce jour-là. Cette approche est appelée le régime de comptage des calories, qui est une option populaire.

Voici le code qui calcule le total courant :

SELECT calorie_intake.*,
      SUM(calories) OVER(ORDER BY hours) AS running_total
FROM calorie_intake;

Passons en revue quelques autres scénarios dans lesquels les totaux courants sont utilisés. Gardez à l'esprit que les fonctions de la fenêtre qui calculent les totaux courants restent pratiquement les mêmes. Donc, à partir de maintenant, je vais expliquer brièvement les exemples sans inclure le code SQL.

5. cas confirmés de COVID-19

Chaque pays collecte chaque jour des informations sur le nombre total de cas confirmés de COVID-19, de décès et de cas guéris. Avec ces nouvelles informations, les totaux courants sont mis à jour afin que chaque pays sache combien de cas confirmés, de décès et de cas guéris il a eu jusqu'à présent, depuis le début de la pandémie jusqu'à aujourd'hui.

Pour plus de détails, voir comment le total courant est calculé à l'aide des données John Hopkins : comment analyser les données COVID-19.

6. utilisateurs enregistrés d'une application mobile

Les propriétaires d'applications mobiles souhaitent généralement connaître la somme cumulée des nouveaux utilisateurs enregistrés et des installations/désinstallations effectuées au cours du dernier mois, trimestre et/ou année. Là encore, vous pouvez utiliser les totaux courants pour obtenir de meilleures informations. Ces statistiques indiquent aux propriétaires comment l'application ou une fonction spécifique a été reçue et comment développer le produit à l'avenir.

De même, si vous gérez un site Web, l'une des mesures les plus importantes à suivre est le nombre total de pages vues ou de visites. Cette information est généralement recueillie quotidiennement. En calculant le total courant, vous pouvez voir comment la somme cumulée se comporte dans le temps et comment elle se compare aux totaux courants antérieurs.

7. programmes de fidélisation des compagnies aériennes

Ce concept est probablement bien connu de ceux qui prennent fréquemment l'avion. À chaque vol, vous accumulez des points. Une fois que vous avez accumulé un certain nombre de points, vous bénéficiez de remises et/ou de récompenses.

Un total courant est utilisé pour calculer le nombre total de points que vous avez sur votre compte. Chaque fois que vous achetez un billet, le total courant est actualisé et vos points augmentent.

Résumé

Dans cet article, j'ai montré comment les totaux courants sont utilisés dans différents scénarios. Comme vous l'avez appris, les totaux courants ont de nombreuses applications.

Les fonctions de fenêtre SQL vous permettent de calculer les totaux courants relativement facilement avec seulement quelques lignes de code. Les fonctions de fenêtre sont utilisées lorsqu'il s'agit de calculs complexes. Une fois que vous aurez appris la syntaxe, vous écrirez un code plus propre et plus compréhensible.

Bien que je n'aie expliqué comment utiliser les fonctions de fenêtre que pour le calcul des totaux courants, les fonctions de fenêtre sont largement utilisées pour toute une série de calculs. Dans cet article avec des exemples de fonctions de fenêtre, vous pouvez découvrir d'autres façons d'utiliser les fonctions de fenêtre.

Nous proposons également un excellent cours interactif à l'adresseFonctions de fenêtrage , avec de nombreux exemples. Si vous souhaitez vous plonger dans les fonctions de fenêtre, je vous recommande vivement de suivre ce cours. Il propose de nombreux exercices pratiques, ce qui est important lorsque l'on acquiert de nouvelles compétences SQL.