În SQL, o sumă cumulativă se referă la o metodă care ne permite să calculăm totalul curent al unei coloane numerice dintr-un tabel al bazei de date. O sumă cumulativă intră în joc într-o mare varietate de scenarii, cum ar fi urmărirea progresului sau calcularea mediilor mobile ale unei anumite valori.
În acest tutorial, vom învăța cum să implementăm și să utilizăm suma cumulativă în SQL folosind diferite metode și tehnici.
Eșantion de date
Pentru a înțelege mai bine cum putem implementa și folosi suma cumulativă, să începem prin a configura un tabel de bază cu un eșantion de date. Acest lucru ne permite să demonstrăm funcționarea tuturor metodelor rapid și eficient în această postare.
Începeți prin a crea un tabel de bază care stochează informațiile despre produs.
CREATE TABLE produse (
product_id INT PRIMARY KEY,
nume_produs VARCHAR ( 255 ) ,
pret DECIMAL ( 10 , 2 )
) ;
Aceasta ar trebui să creeze un nou tabel numit „produse” care stochează ID-ul produsului, numele produsului și prețul fiecărui produs.
Apoi putem continua să adăugați un eșantion de date, așa cum se arată în următoarele exemple de instrucțiuni de inserare:
INSERT INTO produse ( product_id, product_name, price )
VALORI
( 1 , „Codul Visual Studio” , 10.00 ) ,
( 2 , „Text sublim” , 80.00 ) ,
( 3 , „PyCharm Professional” , 199.00 ) ,
( 4 , „Eclipse IDE” , 30.00 ) ,
( 5 , „IntelliJ IDEA Ultimate” , 699,00 ) ,
( 6 , „GitHub Desktop” , 20.00 ) ,
( 7 , „Așteptați software” , 10.00 ) ,
( 8 , „Xcode” , 660,00 ) ,
( 9 , „NetBeans” , 0,00 ) ,
( 10 , 'Atom' , 60.00 ) ;
NOTĂ: Datele date sunt în întregime fictive. Nu reprezintă prețul real al niciunuia dintre articolele enumerate.
Tabelul rezultat este următorul:
Sumă cumulativă SQL (Self Join)
Una dintre tehnicile pe care le putem folosi pentru a efectua o sumă cumulativă pe o anumită coloană este utilizarea metodei de auto-unire. Un avantaj al acestei metode este că funcționează în aproape toate bazele de date SQL, chiar și în cele care nu acceptă funcțiile Window.
Luați de exemplu tabelul anterior „produse”. Putem crea suma cumulativă a coloanei de preț, așa cum este demonstrat în următoarea interogare:
SELECTAȚIp1.product_id,
p1.nume_produs,
p1.preț,
SUMĂ ( p2.pret ) AS suma_cumulată
DIN
produse p1
A TE ALATURA
produse p2
PE
p1.product_id > = p2.product_id
A SE GRUPA CU
p1.product_id,
p1.nume_produs,
p1.pret
COMANDA PENTRU
p1.product_id;
Ați observat funcționarea interogării? Dacă nu, rămâneți cu noi în timp ce vă explicăm pas cu pas.
În exemplul de interogare dat, începem prin a crea două alias-uri – „p1” și „p2” – pentru tabelul „produse”, care ne permite să efectuăm o auto-unire.
Continuăm apoi să unim „p1” și „p2” cu condiția ca „product_id” al lui „p1” să fie mai mare sau egal cu „product_id” al lui „p2”.
În pasul următor, numim funcția sum() care calculează practic suma cumulativă a prețurilor pentru fiecare rând.
În cele din urmă, grupăm rezultatele folosind „product_id”, „product_name” și „price” și ordonăm rezultatele.
După această operație, ar trebui să avem o sumă cumulativă pentru fiecare înregistrare, așa cum este demonstrat în tabelul rezultat, după cum se arată în continuare:
După cum puteți vedea, obținem suma totală a tuturor rândurilor anterioare. Ultimul rând ar trebui să conțină suma totală a tuturor rândurilor anterioare.
Sumă cumulativă SQL (funcții ferestre)
O modalitate mai eficientă și mai practică de a calcula suma cumulativă în SQL este profitarea de funcțiile ferestrei ori de câte ori este acceptată.
Dacă aveți o bază de date precum SQL Server, PostgreSQL sau MySQL versiunea 8.0 și mai sus, aceasta este metoda cea mai utilă și recomandată pentru a determina suma cumulativă a unei anumite coloane.
Uitați-vă la exemplul, așa cum este demonstrat în următoarele:
SELECTAȚIproduct_id,
numele produsului,
Preț,
SUMĂ ( Preț ) PESTE ( ORDER BY product_id ) AS suma_cumulată
DIN
produse;
În acest caz, începem prin a selecta coloanele „product_id”, „product_name” și „price” din tabelul „products”.
Apoi folosim funcția SUM() ca funcție de fereastră folosind clauza OVER.
În clauza OVER, precizăm clauza ORDER BY care definește ordinea în care se calculează suma cumulativă.
Aceasta ar trebui să returneze o ieșire similară așa cum se arată în următoarele:
Veți observa că folosirea funcțiilor ferestrei este mai coerentă, mai eficientă și mai lizibilă în comparație cu utilizarea auto-uniunilor.
Concluzie
În acest tutorial, am învățat totul despre sumele cumulate în SQL. Am abordat, de asemenea, cum să folosiți funcțiile de auto-unire și fereastră pentru a efectua suma cumulativă în SQL.