Utilizați SQL pentru a calcula un total de rulare

totalul de rulare în SQL poate fi calculat în mai multe moduri. Acest articol va acoperi două metode: îmbinările și funcțiile ferestrei.

vom analiza mai întâi cum să calculăm totalul de funcționare folosind îmbinarea interioară. Procedând astfel, nu numai că veți afla mai multe despre condițiile de înscriere, dar veți vedea cum să luați rezultatul și să îl rezumați, pentru a obține totalul de rulare.

după ce ați văzut cum se face stilul „old school”, vom folosi clauza OVER pentru a calcula totalurile rulante folosind o funcție window. Această metodă este mai nouă și mai concisă de utilizat.

toate exemplele pentru această lecție se bazează pe Microsoft SQL Server Management Studio și baza de date WideWorldImporters. Puteți începe să utilizați aceste instrumente gratuite utilizând ghidul meu începeți să utilizați SQL Server 2016.

ce este un total de funcționare?

scopul nostru este de a calcula totalul de funcționare care se resetează ori de câte ori modificările TransactionDate. Vom totaliza valoarea tranzacției. Pentru fiecare factură ulterioară din data tranzacției, totalul de execuție ar trebui să fie egal cu totalul de rulare al facturii anterioare plus valoarea curentă a tranzacției.

veți vedea acest lucru în acțiune în exemplul următor. RunningTotal pentru factura 3, este RunningTotal anterior de 3110.75 plus valoarea tranzacției facturii 3 de 103.50

calculați un total de rulare în SQL folosind o îmbinare interioară

mai întâi calculăm totalul de rulare folosind îmbinările interioare. Această metodă dezvăluie mai multe mecanici de calculare a unui total de rulare decât utilizarea partiției. Ca atare, vă oferă o altă oportunitate de a înțelege îmbinările interioare și de a aplica aceste concepte într-un alt caz de utilizare.

există trei pași pentru rezolvarea acestei probleme:

1. Obțineți rânduri pentru totalul de rulare
2. detalii de configurare pentru totalul de rulare folosind îmbinări interioare
3. calculați totalul de rulare rezumând datele.

să începem!

Step 1 – Obțineți rânduri pentru rularea totală

pentru a calcula totalul de rulare, vom interoga tabelul CustomerTransactions. Vom include InvoiceID, TransactionDate și TransactionAmount în rezultatul nostru. Desigur, totalul de funcționare este calculat din Tranzacțiesuma.

aici este interogarea pentru a obține datele de bază.

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

Iată datele cu care vom lucra.

într-adevăr, acest pas este menit să vă familiarizați cu informațiile de bază. Nu e nevoie să o faci cu adevărat. Cu toate acestea, uneori aș dori să recomand interogarea de bază doar pentru a vedea datele. În plus, pentru a mă asigura, de asemenea, că nu există anomalii sau situații speciale pe care trebuie să le acomodez.

Step 2 – Setup Details for Running Total using Inner Joins

pentru acest pas, vom obține detaliile configurate astfel încât să putem calcula totalul de rulare. Pentru a face acest lucru, vom primi fiecare InvoiceID Suma tranzacției și toate sumele tranzacției anterioare.

pentru a putea face acest lucru, ne vom alătura tabelului CustomerTransactions.

dacă vom face acest lucru cu nici o condiție se alăture ne-ar obține fiecare combinație de tranzacții, acest lucru nu este ceea ce ne dorim.

pentru a ne asigura că obținem combinația corectă de rânduri din fiecare tabel, vom adăuga două condiții de îmbinare. Unul pentru a obține fiecare factură și cele anterioare acesteia (Verde).

al doilea asigură că includem doar facturi la aceeași dată a tranzacției (roșu)

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

să vedem cum funcționează.

cea mai ușoară condiție de înțeles este locul în care potrivim TransactionDate. Acest lucru asigură că potrivirea facturilor are o dată comună a tranzacției. În cazul în care acest lucru a fost singurul se alăture am făcut ne-ar fi calcularea unui total sub pentru toate tranzacțiile într-o dată.

deoarece dorim să calculăm totalul de funcționare, trebuie să obținem cumva pentru fiecare InvoiceID valoarea tranzacției pentru factură și toate facturile dinaintea acesteia. Cu alte cuvinte, returnați toate rândurile potrivite în care factura este mai mare sau egală cu facturile corespunzătoare pe care încercăm să le totalizăm.

dacă te uiți la rezultatul de mai sus, veți vedea că pentru fiecare factură enumerate în prima coloană (T1.InvoiceID), este mai mare sau egal cu InvoiceID în a doua coloană (T2.InvoiceID).

acesta este rezultatul condiției de îmbinare T1.InvoiceID > = T2.InvoiceID.

rezultatul acestui join și condițiile de unire este că acum avem materiile prime pentru a calcula totalul de funcționare.

observați cum se repetă prima, a treia și a patra coloană. Putem folosi acest lucru în avantajul nostru pentru a rezuma rezultatul pentru a ajunge la totalul de rulare.

Step 3 – Calculați totalul de rulare rezumând rândurile

cu informațiile detaliate la îndemână, ultimul pas este de a rezuma rândurile. Acest lucru ne permite să calculăm totalurile de funcționare.

iată interogarea pe care o folosim pentru a efectua rezumatul:

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

observați cum grupăm după T1.InvoiceID, T1.Data tranzacției și T1.Tranzactionamount. Acestea sunt valorile care au fost repetate în datele noastre detaliate la Pasul 2.

totalul de funcționare este derivat din T2.Tranzactionamount. Reamintim că aceste valori sunt Tranzacționaresumă din toate facturile anterioare facturii afișate. Cu alte cuvinte, factura afișată este mai mare sau egală cu acestea.

acest lucru ne permite să construim un total de funcționare.

fiecare factură ulterioară din listă, calculează valoarea totală a rulării prin însumarea tuturor tranzacțiilor din factura sa și a celor anterioare acesteia.

acum, că ați văzut un mod tradițional de a ajunge la totalul de funcționare, și, probabil, a câștigat o mai mare apreciere a modului de utilizare se alătură și să se alăture Condiții pentru a rezolva, să ne uităm la una dintre cele mai noi caracteristici ale SQL, partiții, și a vedea modul în care acestea pot fi utilizate pentru a obține același rezultat.

calculați un total de rulare în SQL folosind o clauză OVER

clauza OVER dacă este o declarație foarte puternică. Acesta vă permite să definiți un set de rânduri, într-un set de rezultate care afectează o operație.

la fel ca OFFSET și FETCH ne permit să recuperăm un anumit interval de rânduri dintr-un set de rezultate, clauza OVER ne permite să facem o operație similară, în raport cu rândul curent, pentru o anumită coloană.

folosind OVER, putem defini o fereastră peste un set specificat de rânduri, la care putem aplica funcții, cum ar fi sum.

pentru a înțelege conceptul, vom împărți acest lucru în doi pași:

1. partiționarea datelor utilizând clauza OVER.
2. comandați partiții cu comandă.

să mergem.

Step 1 – Partition data using over Clause

când spunem că vrem să vrem să creăm un total de rulare pentru toate facturile dintr-o TransactionDate, vrem să partiționăm datele noastre prin TransactionDate. Pentru a partiționa datele, putem folosi clauza over.

în următoarea declarație vom rezuma TransactionAmount și după suma există o clauză de peste.

de asemenea, observați că nu există nicio clauză grup după. Acest lucru este surprinzător, de obicei funcțiile agregate, cum ar fi suma, necesită o clauză GROUP BY; de ce este cazul?

deoarece folosim clauza OVER, suma este considerată o funcție de fereastră – funcționează pe orice rânduri definite în clauza OVER.

iată funcția de fereastră pe care o vom folosi:

SUM(TransactionAmount) OVER(PARTITION BY TransactionDate) RunningTotal

ceea ce face ca aceasta să fie o funcție windows este clauza OVER. checkout partiția parte de TransactionDate. Aceasta înseamnă că suma funcționează pe toate rândurile cu aceeași Tranzacțiedata. Aceasta definește fereastra rândurilor pe care funcția SUM o afectează.

iată interogarea de până acum.

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

Step 2 – Order Partitions with Order BY

până în acest moment am partiționat datele și putem calcula un subtotal pentru toate valorile TransactionAmount într-o TransactionDate. Următorul pas este să calculați acum subtotalul.

pentru a face acest lucru, putem folosi comanda prin în clauza OVER pentru a defini „domeniul de aplicare” al funcției window. Ordinea de specificat ordinea logică funcționează funcția fereastră.

iată funcția de fereastră pe care o vom folosi:

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

diferența dintre această funcție de fereastră și cea din primul pas, este ORDER BY InvoiceID. Aceasta specifică ordinea logică de procesat în cadrul partiției.

fără ordinea prin ordinea logică este să așteptăm până când suntem la sfârșitul ferestrei pentru a calcula suma. Cu ORDER by specificat, ordinea logică este de a calcula o sumă pentru fiecare rând, inclusiv valorile anterioare TransactionAmount în cadrul ferestrei.

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

iată rezultatul rulării interogării.

când ați rulat această interogare, ați observat cât de repede a rulat decât cea care utilizează îmbinări interioare? Am fost surprins. Știu că operațiunea inner JOIN consumă multe resurse pe măsură ce combinațiile sau rândurile devin mari, dar aș fi crezut că ar avea același caz pentru soluția folosind peste.

v-aș încuraja să vă uitați la planul de interogare al fiecărei interogări. Veți începe să învețe destul de un pic despre SQL atunci când începe să facă acest lucru.