Bruk SQL Til Å Beregne En Løpende Total

den løpende totalen I SQL kan beregnes på flere måter. Denne artikkelen vil dekke to metoder: Sammenføyningene og Vinduets funksjoner.

vi vil først se på hvordan du beregner løpende total ved HJELP AV INNER JOIN. Ved å gjøre det, vil du ikke bare lære mer om join betingelser, men se hvordan du tar resultatet og oppsummere det, for å få løpende totalt.

Når du har sett hvordan du gjør det» old school » – stil, bruker VI over-klausulen til å beregne løpende totaler ved hjelp av en vindusfunksjon. Denne metoden er nyere og mer konsis å bruke.

alle eksemplene for denne leksjonen er Basert På Microsoft SQL Server Management Studio og wideworldimporters database. Du kan komme i gang med Å bruke DISSE gratis verktøyene ved hjelp AV Min Guide Kom I GANG MED SQL Server 2016.

Hva Er En Løpende Total?

vårt mål Er å beregne den løpende totalen som tilbakestilles når TransactionDate endres. Vi summerer Transaksjonsbeløpet. For hver påfølgende faktura innen transaksjonsdatoen skal RunningTotal være lik Den tidligere Fakturaidens løpende sum pluss gjeldende Transaksjonsbeløp.

du vil se dette i aksjon i følgende eksempel. Den RunningTotal For Faktura 3, er den tidligere RunningTotal av 3110.75 pluss Fakturaen 3 Transaksjonsbeløpet 103.50

Beregn En Løpende Total I SQL ved HJELP AV EN INDRE SAMMENFØYNING

vi beregner først løpende total ved HJELP AV DE INDRE SAMMENFØYNINGENE. Denne metoden avslører mer av mekanikken til å beregne en løpende total enn Å bruke PARTISJONEN. Som sådan gir det deg en annen mulighet til å forstå INDRE SAMMENFØYNINGER og bruke disse konseptene til en annen brukstilfelle.

det er tre trinn for å løse dette problemet:

1. Få rader for den løpende totalen
2. Oppsettdetaljer for den løpende totalen ved hjelp av indre sammenføyninger
3. Beregn den løpende totalen ved å oppsummere data.

La oss komme i gang!

Trinn 1-Få Rader For Å Kjøre Totalt

for å beregne løpende total, spør Vi Kundetransaksjonstabellen. Vi inkluderer InvoiceID, TransactionDate og TransactionAmount i resultatet vårt. Selvfølgelig beregnes løpende sum fra Transaksjonenbeløp.

her er spørringen for å få grunnleggende data.

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

her er dataene vi skal jobbe med.

Virkelig, dette trinnet er ment å gjøre deg kjent med grunnleggende informasjon. Det er ikke nødvendig å virkelig gjøre det. Imidlertid vil jeg noen ganger anbefale den grunnleggende spørringen bare for å se dataene. I tillegg, for å også sikre at det ikke er noen anomalier eller spesielle situasjoner som jeg trenger å imøtekomme.

Trinn 2-Setup Details For Running Total ved Hjelp Av Indre Joins

for dette trinnet får vi detaljene satt opp slik at vi kan beregne running total. For å gjøre dette, vil vi få hver InvoiceID transaksjonsbeløpet og alle transaksjonsbeløp før.

for å kunne gjøre dette, blir Vi Med I CustomerTransactions-tabellen til seg selv.

hvis vi gjør dette uten sammenføyning, vil vi få alle kombinasjoner av transaksjoner, dette er ikke det vi vil ha.

for å sikre at vi får den riktige kombinasjonen av rader fra hvert bord, legger vi til to sammenføyningsforhold. En for å få hver faktura og de før Den (Grønn).

den andre sikrer at vi bare inkluderer fakturaer på samme transaksjonsdato (Rød)

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

La oss se hvordan dette fungerer.

den enkleste betingelsen å forstå er hvor Vi samsvarer TransactionDate. Dette sikrer at fakturaene samsvarer med en felles transaksjonsdato. Hvis dette var den eneste delta vi gjorde vi ville være å beregne en sub total for alle transaksjoner innen en dato.

Siden vi ønsker å beregne den løpende summen, må vi på en eller annen måte få For Hver InvoiceID TransactionAmount for fakturaen og alle fakturaer før den. Med andre ord, returner alle matchende rader der fakturaen er større enn eller lik de tilsvarende fakturaene vi prøver å totalt.

hvis du ser på resultatet ovenfor, vil du se at for hver faktura oppført i den første kolonnen (T1.InvoiceID), er det større enn Eller lik InvoiceID i den andre kolonnen (T2.InvoiceID).

Dette er et resultat Av sammenføyningsbetingelsen T1.InvoiceID > = T2.InvoiceID.

resultatet av denne sammenføyningen og sammenføyningsbetingelsene er at vi nå har råvarene til å beregne løpende sum.

Legg merke til hvordan den første, tredje og fjerde kolonnen gjentas. Vi kan bruke dette til vår fordel for å oppsummere resultatet for å komme fram til løpende total.

Trinn 3-Beregn Løpende Total Ved Å Oppsummere Rader

med detaljert informasjon for hånden, er det siste trinnet å oppsummere radene. Å gjøre det tillater oss å beregne løpende totaler.

her er spørringen vi bruker til å utføre sammendraget:

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

Legg merke til hvordan vi grupperer Ved T1.InvoiceID, T1.TransactionDate Og T1.Transaksjonsbeløp. Dette er verdiene som ble gjentatt i våre detaljerte data i trinn 2.

Den Løpende Totalen er avledet Fra T2.Transaksjonsbeløp. Recall disse verdiene Er TransactionAmount fra Alle Fakturaer før fakturaen vises. Med andre ord, fakturaen som vises er større enn eller lik dem.

Dette tillater oss å bygge opp En Løpende total.

hver påfølgende faktura i listen, beregner Den RunningTotal verdi ved å summere opp Alle TransactionAmount Fra Sin Faktura og de før Den.

Nå som du har sett en tradisjonell måte å komme frem til løpende total, og kanskje fått en større forståelse for hvordan du bruker joins og join betingelser for å løse det, la oss se på en av de nyere funksjonene I SQL, partisjoner, og se hvordan de kan brukes til å oppnå samme resultat.

Beregn En Løpende Total I SQL ved HJELP Av EN Over-Setning

over-setningen hvis en veldig kraftig setning. Den lar deg definere et sett med rader, innenfor et resultatsett som en operasjon påvirker.

Mye SOM OFFSET OG HENTE tillate oss å hente en bestemt rekke rader fra et resultatsett, OVER klausulen tillater oss å gjøre en lignende operasjon, i forhold til gjeldende rad, for en bestemt kolonne.

Ved HJELP AV OVER kan VI definere et vindu over et angitt sett med rader, som vi kan bruke funksjoner, for eksempel sum.

for at du skal forstå konseptet, bryter vi dette ned i to trinn:

1. Partisjonsdata ved HJELP AV over-klausulen.
2. Bestill Partisjoner Med Ordre.

La oss rulle.

Trinn 1-Partisjonsdata ved HJELP AV Over-Klausul

Når vi sier at vi vil, vil vi opprette en løpende sum for Alle Fakturaer i En TransactionDate, vi vil partisjonere dataene våre Etter TransactionDate. For å partisjonere dataene, kan vi bruke over-klausulen.

I følgende uttalelse varsel SUMMERER Vi Transaksjonsbeløpet og etter SUMMEN er DET EN overklausul.

legg også merke til at DET Ikke ER NOEN GRUPPE etter klausul. Dette er overraskende, typisk aggregerte funksjoner, som SUM, krever EN GRUPPE etter klausul; hvorfor er dette tilfelle?

SIDEN VI bruker overklausulen, BETRAKTES SUMMEN som en vindusfunksjon – den opererer på alle rader definert i overklausulen.

her er vindu-funksjonen vi bruker:

SUM(TransactionAmount) OVER(PARTISJON Av TransactionDate) RunningTotal

hva gjør dette til en windows-funksjon ER over-klausulen. kassa delen PARTISJON Av TransactionDate. DETTE betyr AT SUMMEN opererer på alle rader med samme TransactionDate. Dette definerer vinduet for rader SUM-funksjonen påvirker.

her er spørringen så langt.

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

Trinn 2-Bestill Partisjoner Med Order BY

opp til dette punktet har vi partisjonert dataene og kan beregne en delsum for Alle TransactionAmount-verdier i En TransactionDate. Det neste trinnet er å nå beregne delsummen.

for å gjøre dette kan VI bruke ORDER BY i over-klausulen for å definere» omfanget » av vindu-funksjonen. REKKEFØLGEN ved angitt den logiske rekkefølgen vinduet funksjonen opererer.

her er vindusfunksjonen vi bruker:

SUM(TransactionAmount) OVER(PARTISJON Av TransactionDate ORDRE Av InvoiceID) RunningTotal

forskjellen mellom dette vinduet funksjon og som fra første trinn, ER ORDRE Av InvoiceID. Dette angir den logiske rekkefølgen for å behandle i partisjonen.

UTEN REKKEFØLGEN av den logiske rekkefølgen er å vente til vi er på slutten av vinduet for å beregne summen. MED DEN angitte REKKEFØLGEN, er den logiske rekkefølgen å beregne en sum for hver rad, inkludert tidligere Transaksjonsbeløpverdier i vinduet.

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

her er resultatet av å kjøre spørringen.

da du kjørte denne spørringen la du merke til hvor mye raskere den kjørte enn DEN SOM brukte INDRE KOBLINGER? Jeg ble overrasket. JEG vet AT INNER JOIN-operasjonen bruker mye ressurser da kombinasjonene eller radene blir store, men jeg ville trodd det ville ha samme sak for løsningen ved Å bruke OVER.

jeg vil oppfordre deg til å se på hver spørringsplan. Du begynner å lære mye om SQL når du begynner å gjøre dette.