Utilisez SQL pour calculer un Total en cours d’exécution

Le total en cours d’exécution en SQL peut être calculé de plusieurs manières. Cet article couvrira deux méthodes: les jointures et les fonctions de fenêtre.

Nous allons d’abord voir comment calculer le total en cours d’exécution à l’aide de la JOINTURE INTERNE. Ce faisant, vous en apprendrez non seulement plus sur les conditions de jointure, mais vous verrez comment prendre le résultat et le résumer, pour obtenir le total courant.

Une fois que vous avez vu comment le faire dans le style « old school », nous utiliserons la clause OVER pour calculer les totaux en cours d’exécution à l’aide d’une fonction de fenêtre. Cette méthode est plus récente et plus concise à utiliser.

Tous les exemples de cette leçon sont basés sur Microsoft SQL Server Management Studio et la base de données WideWorldImporters. Vous pouvez commencer à utiliser ces outils gratuits à l’aide de mon guide Commencez à utiliser SQL Server 2016.

Qu’est-ce qu’un total courant ?

Notre objectif est de calculer le total courant qui se réinitialise chaque fois que la date de transaction change. Nous totaliserons le montant de la transaction. Pour chaque facture ultérieure à la date de la transaction, le total d’exécution doit être égal au total d’exécution de l’ID de facturation antérieure plus le montant de transaction actuel.

Vous verrez cela en action dans l’exemple suivant. Le total d’exécution de la Facture 3 est le total d’exécution antérieur de 3110,75 plus le Montant de la Transaction de la Facture 3 de 103.50

Calculez un Total d’exécution en SQL à l’aide d’une JOINTURE INTERNE

Nous calculons d’abord le total d’exécution à l’aide des JOINTURES INTERNES. Cette méthode révèle davantage la mécanique du calcul d’un total de fonctionnement que l’utilisation de la PARTITION. En tant que tel, cela vous donne une autre opportunité de comprendre les JOINTURES INTERNES et d’appliquer ces concepts à un autre cas d’utilisation.

Il y a trois étapes pour résoudre ce problème:

1. Obtenez des lignes pour le total d’exécution
2. Détails de configuration pour le total d’exécution à l’aide des jointures internes
3. Calculez le total d’exécution en résumant les données.

Commençons!

Étape 1 – Obtenez des lignes pour le total d’exécution

Afin de calculer le total d’exécution, nous interrogerons la table CustomerTransactions. Nous inclurons InvoiceID, TransactionDate et TransactionAmount dans notre résultat. Bien sûr, le total en cours est calculé à partir de la TransactionAmount.

Voici la requête pour obtenir les données de base.

SELECT InvoiceID ,TransactionDate ,TransactionAmountFROM Sales.CustomerTransactionsWHERE TransactionTypeID = 1ORDER BY TransactionDate

Voici les données avec lesquelles nous allons travailler.

Vraiment, cette étape est destinée à vous familiariser avec les informations de base. Il n’y a pas besoin de le faire vraiment. Cependant, je voudrais parfois recommander la requête de base juste pour voir les données. De plus, pour m’assurer également qu’il n’y a pas d’anomalies ou de situations spéciales que je dois accommoder.

Étape 2 – Détails de configuration du total d’exécution à l’aide des jointures internes

Pour cette étape, nous allons configurer les détails afin que nous puissions calculer le total d’exécution. Pour ce faire, nous obtiendrons à chaque InvoiceID le montant de la transaction et tous les montants de la transaction au préalable.

Pour pouvoir le faire, nous joindrons la table CustomerTransactions à elle-même.

Si nous faisons cela sans condition de jointure, nous obtiendrions toutes les combinaisons de transactions, ce n’est pas ce que nous voulons.

Pour nous assurer d’obtenir la bonne combinaison de lignes de chaque table, nous ajouterons deux conditions de jointure. Un pour obtenir chaque facture et celles qui l’ont précédée (vert).

La seconde assure que nous n’incluons que les factures à la même date de transaction (rouge)

SELECT T1.InvoiceID ,T2.InvoiceID ,T1.TransactionDate ,T1.TransactionAmount ,T2.TransactionAmountFROM Sales.CustomerTransactions T1 INNER JOIN Sales.CustomerTransactions T2 ON T1.InvoiceID >= T2.InvoiceID AND T1.TransactionDate = T2.TransactionDateWHERE T1.TransactionTypeID = 1ORDER BY T1.InvoiceID, T1.TransactionAmount

Voyons comment cela fonctionne.

La condition la plus facile à comprendre est où nous correspondons TransactionDate. Cela garantit que les factures correspondent ont une date de transaction commune. Si c’était la seule jointure que nous avons faite, nous calculerions un sous-total pour toutes les transactions dans une date.

Puisque nous voulons calculer le total courant, nous devons en quelque sorte obtenir pour chaque InvoiceID le TransactionAmount pour la facture et toutes les factures avant elle. En d’autres termes, renvoyez toutes les lignes correspondantes où la facture est supérieure ou égale aux factures correspondantes que nous essayons de totaliser.

Si vous regardez le résultat ci-dessus, vous verrez que pour chaque facture répertoriée dans la première colonne (T1.InvoiceID), Il est supérieur ou égal à InvoiceID dans la deuxième colonne (T2.InvoiceID).

Ceci est le résultat de la condition de jointure T1.InvoiceID > = T2.InvoiceID.

Le résultat de cette jointure et des conditions de jointure est que nous avons maintenant les matières premières pour calculer le total courant.

Remarquez comment les première, troisième et quatrième colonnes se répètent. Nous pouvons utiliser cela à notre avantage pour résumer le résultat pour arriver au total courant.

Étape 3 – Calculez le total en cours d’exécution en résumant les lignes

Avec les informations détaillées à portée de main, la dernière étape consiste à résumer les lignes. Cela nous permet de calculer les totaux courants.

Voici la requête que nous utilisons pour effectuer le résumé:

SELECT T1.InvoiceID ,T1.TransactionDate ,T1.TransactionAmount ,Sum(T2.TransactionAmount) RunningTotalFROM Sales.CustomerTransactions T1 INNER JOIN Sales.CustomerTransactions T2 ON T1.InvoiceID >= T2.InvoiceID AND T1.TransactionDate = T2.TransactionDateWHERE T1.TransactionTypeID = 1GROUP BY T1.InvoiceID ,T1.TransactionDate ,T1.TransactionAmountORDER BY T1.InvoiceID ,T1.TransactionAmount

Remarquez comment nous groupons par T1.InvoiceID, T1.TransactionDate et T1.Montant de la transaction. Ce sont les valeurs qui ont été répétées dans nos données détaillées à l’étape 2.

Le total courant est dérivé de T2.Montant de la transaction. Rappel ces valeurs sont TransactionAmount de toutes les factures avant la facture affichée. En d’autres termes, la facture affichée est supérieure ou égale à celles-ci.

Cela nous permet de constituer un total courant.

Chaque facture suivante dans la liste calcule sa valeur RunningTotal en additionnant toutes les transactions de sa facture et celles qui l’ont précédée.

Maintenant que vous avez vu un moyen traditionnel d’arriver au total d’exécution, et que vous avez peut-être mieux compris comment utiliser les jointures et les conditions de jointure pour le résoudre, examinons l’une des nouvelles fonctionnalités de SQL, les partitions, et voyons comment elles peuvent être utilisées pour obtenir le même résultat.

Calculer un Total en cours d’exécution en SQL en utilisant une clause OVER

La clause OVER si une instruction très puissante. Il vous permet de définir un ensemble de lignes, dans un ensemble de résultats qu’une opération affecte.

Tout comme OFFSET et FETCH nous permettent de récupérer une plage spécifique de lignes à partir d’un jeu de résultats, la clause OVER nous permet de faire une opération similaire, par rapport à la ligne actuelle, pour une colonne spécifique.

En utilisant OVER, nous pouvons définir une fenêtre sur un ensemble de lignes spécifié, à laquelle nous pouvons appliquer des fonctions, telles que sum.

Pour que vous compreniez le concept, nous allons le décomposer en deux étapes:

1. Partition des données à l’aide de la clause OVER.
2. Classez les partitions avec Order.

Roulons.

Étape 1 – Partitionner les données à l’aide de la clause OVER

Lorsque nous disons que nous voulons créer un total courant pour toutes les factures dans une TransactionDate, nous voulons partitionner nos données par TransactionDate. Pour partitionner les données, nous pouvons utiliser la clause over.

Dans l’avis de déclaration suivant, nous ADDITIONNONS le TransactionAmount et après la SOMME, il y a une clause OVER.

Notez également qu’il n’y a pas de clause GROUP BY. C’est surprenant, les fonctions généralement agrégées, telles que SUM, nécessitent une clause GROUP BY; pourquoi est-ce le cas?

Puisque nous utilisons la clause OVER, la SOMME est considérée comme une fonction de fenêtre – elle opère sur toutes les lignes définies dans la clause OVER.

Voici la fonction de fenêtre que nous utiliserons:

SUM(TransactionAmount) OVER(PARTITION BY TransactionDate) RunningTotal

Ce qui en fait une fonction Windows est la clause OVER. extraire la PARTITION DE pièce PAR TransactionDate. Cela signifie que la SOMME fonctionne sur toutes les lignes avec la même TransactionDate. Cela définit la fenêtre des lignes que la fonction SOMME affecte.

Voici la requête jusqu’à présent.

SELECT InvoiceID ,TransactionDate ,TransactionAmount ,SUM(TransactionAmount) OVER(PARTITION BY TransactionDate) RunningTotalFROM Sales.CustomerTransactions T1WHERE TransactionTypeID = 1ORDER BY InvoiceID ,TransactionAmount

Étape 2 – Ordonner les partitions avec l’ordre PAR

Jusqu’à présent, nous avons partitionné les données et sommes capables de calculer un sous-total pour toutes les valeurs TransactionAmount dans une TransactionDate. L’étape suivante consiste maintenant à calculer le sous-total.

Pour ce faire, nous pouvons utiliser ORDER BY dans la clause OVER pour définir la « portée » de la fonction window. L’ORDRE PAR a spécifié l’ordre logique dans lequel fonctionne la fonction de fenêtre.

Voici la fonction de fenêtre que nous allons utiliser:

SUM(TransactionAmount) OVER (PARTITION BY TransactionDate ORDER BY InvoiceID) RunningTotal

La différence entre cette fonction de fenêtre et celle de la première étape, est ORDER BY InvoiceID. Cela spécifie l’ordre logique à traiter dans la partition.

Sans l’ORDRE PAR l’ordre logique, il faut attendre d’être à la fin de la fenêtre pour calculer la somme. Avec l’ORDRE PAR spécifié, l’ordre logique consiste à calculer une somme pour chaque ligne, y compris les valeurs TransactionAmount précédentes dans la fenêtre.

SELECT InvoiceID ,TransactionDate ,TransactionAmount ,SUM(TransactionAmount) OVER(PARTITION BY TransactionDate ORDER BY InvoiceID) RunningTotalFROM Sales.CustomerTransactions T1WHERE TransactionTypeID = 1ORDER BY InvoiceID ,TransactionAmount

Voici le résultat de l’exécution de la requête.

Lorsque vous avez exécuté cette requête, avez-vous remarqué à quel point elle s’exécutait plus rapidement que celle utilisant des JOINTURES INTERNES ? J’ai été surpris. Je sais que l’opération de JOINTURE INTERNE consomme beaucoup de ressources à mesure que les combinaisons ou les lignes deviennent grandes, mais j’aurais pensé que cela aurait le même cas pour la solution utilisant OVER.

Je vous encourage à examiner le plan de requête de chaque requête. Vous commencerez à en apprendre un peu plus sur SQL lorsque vous commencerez à le faire.