En SQL, une somme cumulée fait référence à une méthode qui nous permet de calculer le total cumulé d'une colonne numérique dans une table de base de données. Une somme cumulée entre en jeu dans une grande variété de scénarios tels que le suivi de la progression ou le calcul des moyennes mobiles d'une valeur donnée.
Dans ce didacticiel, nous apprendrons comment implémenter et utiliser la somme cumulée en SQL à l'aide de diverses méthodes et techniques.
Exemples de données
Pour mieux comprendre comment nous pouvons mettre en œuvre et utiliser la somme cumulée, commençons par créer un tableau de base avec un exemple de données. Cela nous permet de démontrer le fonctionnement de toutes les méthodes rapidement et efficacement dans cet article.
Commencez par créer un tableau de base qui stocke les informations sur le produit.
Produits CRÉER UNE TABLE (
product_id INT CLÉ PRIMAIRE,
nom_produit VARCHAR ( 255 ) ,
prix DÉCIMAL ( dix , 2 )
) ;
Cela devrait créer une nouvelle table appelée « produits » qui stocke l'identifiant du produit, le nom du produit et le prix de chaque produit.
Nous pouvons ensuite procéder à l'ajout d'un exemple de données, comme indiqué dans l'exemple d'instructions d'insertion suivant :
INSÉRER DANS les produits ( product_id, product_name, prix )
VALEURS
( 1 , 'Code de studio visuel' , 10h00 ) ,
( 2 , « Texte sublime » , 80.00 ) ,
( 3 , 'PyCharm Professionnel' , 199.00 ) ,
( 4 , 'IDE Eclipse' , 30h00 ) ,
( 5 , «IntelliJ IDEA Ultime» , 699.00 ) ,
( 6 , « Bureau GitHub » , 20h00 ) ,
( 7 , « Attendre le logiciel » , 10h00 ) ,
( 8 , 'Xcode' , 660,00 ) ,
( 9 , 'NetBeans' , 0,00 ) ,
( dix , 'Atome' , 60.00 ) ;
REMARQUE : Les données fournies sont entièrement fictives. Il ne représente pas le prix réel des articles répertoriés.
Le tableau résultant est le suivant :
Somme cumulative SQL (auto-jointure)
L'une des techniques que nous pouvons utiliser pour effectuer une somme cumulée sur une colonne donnée consiste à utiliser la méthode d'auto-jointure. L'un des avantages de cette méthode est qu'elle fonctionne dans presque toutes les bases de données SQL, même celles qui ne prennent pas en charge les fonctions Windows.
Prenons par exemple le tableau « produits » précédent. Nous pouvons créer la somme cumulée de la colonne prix comme démontré dans la requête suivante :
SÉLECTIONNERp1.product_id,
p1.nom_produit,
p1.prix,
SOMME ( p2.prix ) AS somme_cumulative
DEPUIS
produits p1
REJOINDRE
produits p2
SUR
p1.product_id > = p2.product_id
PAR GROUPE
p1.product_id,
p1.nom_produit,
p1.prix
COMMANDÉ PAR
p1.product_id ;
Avez-vous remarqué le fonctionnement de la requête ? Sinon, restez avec nous pendant que nous l’expliquons étape par étape.
Dans l'exemple de requête donné, nous commençons par créer deux alias – « p1 » et « p2 » – pour la table « products » qui nous permettent d'effectuer une auto-jointure.
Nous procédons ensuite à la jointure de « p1 » et « p2 » à condition que le « product_id » de « p1 » soit supérieur ou égal au « product_id » de « p2 ».
À l’étape suivante, nous appelons la fonction sum() qui calcule essentiellement la somme cumulée des prix pour chaque ligne.
Enfin, nous regroupons les résultats en utilisant le « product_id », le « product_name » et le « price » et classons les résultats.
Après cette opération, nous devrions avoir une somme cumulée pour chaque enregistrement comme le montre le tableau résultant comme indiqué ci-dessous :
Comme vous pouvez le voir, nous obtenons la somme totale de toutes les lignes précédentes. La dernière ligne doit contenir la somme totale de toutes les lignes précédentes.
Somme cumulative SQL (fonctions de fenêtre)
Un moyen plus efficace et plus pratique de calculer la somme cumulée en SQL consiste à tirer parti des fonctions de fenêtre chaque fois qu'elles sont prises en charge.
Si vous disposez d'une base de données telle que SQL Server, PostgreSQL ou MySQL version 8.0 et supérieure, il s'agit de la méthode la plus utile et la plus recommandée pour déterminer la somme cumulée d'une colonne donnée.
Jetez un œil à l’exemple illustré ci-dessous :
SÉLECTIONNERid_produit,
nom_produit,
prix,
SOMME ( prix ) SUR ( COMMANDER PAR product_id ) AS somme_cumulative
DEPUIS
des produits;
Dans ce cas, nous commençons par sélectionner les colonnes « product_id », « product_name » et « price » dans le tableau « products ».
Nous utilisons ensuite la fonction SUM() comme fonction de fenêtre en utilisant la clause OVER.
Dans la clause OVER, on précise la clause ORDER BY qui définit l'ordre dans lequel la somme cumulée est calculée.
Cela devrait renvoyer une sortie similaire à celle indiquée ci-dessous :
Vous remarquerez que l’utilisation des fonctions de fenêtre est plus cohérente, efficace et lisible que l’utilisation des auto-jointures.
Conclusion
Dans ce tutoriel, nous avons tout appris sur les sommes cumulées en SQL. Nous avons également expliqué comment utiliser les fonctions d'auto-jointure et de fenêtre pour effectuer la somme cumulée en SQL.