SQL: n avulla voidaan laskea Juoksusumma

SQL: n juoksusumma voidaan laskea useilla tavoilla. Tämä artikkeli kattaa kaksi menetelmää: liittyy ja ikkunan toiminnot.

ensin katsotaan, miten juoksusumma lasketaan sisemmän liitoksen avulla. Tekemällä niin, et vain oppia lisää liittyä ehtoja, mutta miten ottaa tulos ja tiivistää sen, saada käynnissä yhteensä.

kun olet nähnyt miten se tehdään ”old school” – tyyliin, käytämme ylilauseketta laskemaan juoksevia summia ikkunafunktion avulla. Tämä menetelmä on uudempi ja tiiviimpi käyttää.

Kaikki tämän oppitunnin esimerkit perustuvat Microsoft SQL Server Management studioon ja WideWorldImporters-tietokantaan. Voit aloittaa käyttämällä näitä ilmaisia työkaluja käyttämällä My Guide Aloita käyttämällä SQL Server 2016.

mikä on juokseva yhteissumma?

tavoitteenamme on laskea juoksun loppusumma, joka nollautuu aina, kun tapahtumapäivä muuttuu. Laskemme maksut yhteen. Kunkin myöhemmän laskun juoksevan loppusumman tapahtumapäivänä olisi oltava yhtä suuri kuin aiemman laskutuksen juokseva loppusumma lisättynä tämänhetkisellä Kauppamäärällä.

näet tämän toiminnassa seuraavassa esimerkissä. Laskun 3 RunningTotal on 3110.75 prior RunningTotal + Laskun 3: n Transaktiomäärä 103.50

Calculate a Running Total in SQL using an INNER JOIN

we first calculate the running total using the INNER JOINS. Tämä menetelmä paljastaa enemmän mekaniikka laskettaessa käynnissä yhteensä kuin käyttämällä osion. Sellaisenaan se antaa sinulle uuden mahdollisuuden ymmärtää sisäisiä liittymiä ja soveltaa näitä käsitteitä toiseen käyttötapaukseen.

tämän ongelman ratkaisemiseksi on kolme vaihetta:

1. saada rivit käynnissä yhteensä
2. Setup yksityiskohtia käynnissä yhteensä käyttäen sisempiä liitoksia
3. laske käynnissä yhteensä summaamalla tiedot.

aloitetaan!

Vaihe 1-Get rivejä käynnissä yhteensä

jotta voidaan laskea käynnissä yhteensä, kysymme CustomerTransactions taulukko. Laskutustiedot, TransactionDate ja TransactionAmount sisällytetään tulokseemme. Juoksusumma lasketaan tietysti Juoksumäärästä.

tässä on kysely saada perustiedot.

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

tässä on aineisto, jonka kanssa työskentelemme.

oikeastaan tämä vaihe on tarkoitettu perehtymään perustietoihin. Ei ole tarvetta tehdä sitä. Kuitenkin, haluaisin joskus suositella perus kysely vain nähdä tiedot. Lisäksi myös varmistaa, että ei ole poikkeavuuksia tai erikoistilanteita, että minun täytyy sopeutua.

Step 2-Setup Details for Running Total using Inner Joins

For this step, we ’ ll get the details set up so that we can calculate the running total. Jotta voimme tehdä tämän, saamme jokaisen laskutuksen maksutapahtuman summan ja kaikki maksutapahtuman määrät ennen.

voidaksemme tehdä tämän, liitymme Asiakastransaktiotaulukkoon itsellemme.

jos teemme tämän ilman liittymisehtoa, saisimme jokaisen transaktioyhdistelmän, tätä emme halua.

varmistaaksemme, että saamme oikean riviyhdistelmän jokaisesta taulukosta, lisäämme kaksi liittymisehtoa. Yksi saada jokainen lasku ja ne ennen sitä (Vihreä).

toinen varmistaa, että laskumme ovat mukana vain samana tapahtumapäivänä (Punainen)

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

katsotaan, miten tämä toimii.

helpoiten ymmärrettävä ehto on se, missä kohtaamme Transaktiopäivityksen. Näin varmistetaan, että laskujen täsmäämisellä on yhteinen tapahtumapäivä. Jos tämä oli ainoa liittyminen teimme olisimme laskemassa sub yhteensä kaikille liiketoimille sisällä päivämäärä.

koska haluamme laskea juoksevan kokonaissumman, meidän on jotenkin saatava jokaisesta laskusta laskulle ja kaikille sitä edeltäville laskuille Maksutapahtumamäärä. Toisin sanoen, Palauta kaikki vastaavat rivit, joissa lasku on suurempi tai yhtä suuri kuin vastaavat laskut, joita yritämme yhteensä.

jos katsoo yllä olevaa tulosta, huomaa, että jokaisen ensimmäisessä sarakkeessa luetellun laskun kohdalla (T1.InvoiceID), se on suurempi tai yhtä suuri kuin InvoiceID: n toisessa sarakkeessa (T2.InvoiceID).

tämä johtuu liittymisehdosta T1.Laskutus >= T2.Laskutus.

tämän liitoksen ja liittymisehtojen tulos on se, että meillä on nyt raaka-aineet juoksevan loppusumman laskemiseen.

huomaa, miten ensimmäinen, kolmas ja neljäs sarake toistuvat. Voimme käyttää tätä eduksemme tiivistää tuloksen saapua käynnissä yhteensä.

Vaihe 3-Laske juoksevan kokonaissumma summaamalla rivit

käsillä olevien yksityiskohtaisten tietojen avulla, viimeinen vaihe on rivien summaaminen. Näin voimme laskea juoksevat loppusummat.

tässä on kysely, jolla suoritamme yhteenvedon:

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

Huomatkaa, miten ryhmittelemme T1: n mukaan.Laskutus, T1.TransactionDate, ja T1.Siirtomäärä. Nämä ovat arvot, jotka toistettiin yksityiskohtaisissa tiedoissamme vaiheessa 2.

juokseva loppusumma on johdettu T2: sta.Siirtomäärä. Muistuta nämä arvot ovat Transactionount kaikista laskuista ennen näytettävää laskua. Toisin sanoen näytettävä lasku on suurempi tai yhtä suuri kuin ne.

näin saadaan kasaan juokseva yhteissumma.

jokainen luettelossa oleva lasku laskee juoksevan Kokonaisarvonsa laskemalla yhteen kaikki laskunsa ja sitä edeltäneet maksut.

nyt kun olet nähnyt perinteisen tavan päästä käynnissä olevaan kokonaisuuteen, ja ehkä oppinut arvostamaan enemmän sitä, miten käyttää liitteitä ja liittymisehtoja sen ratkaisemiseksi, katsotaanpa yhtä SQL: n uudempaa ominaisuutta, osioita, ja katsotaan, miten niitä voidaan käyttää saman tuloksen saavuttamiseksi.

laske SQL: ssä juokseva kokonaisluku käyttämällä Ylilauseketta

YLILAUSEKETTA, jos kyseessä on hyvin voimakas lauseke. Sen avulla voit määritellä rivijoukon tulosjoukon sisällä, johon operaatio vaikuttaa.

aivan kuten OFFSET ja FETCH antavat meille mahdollisuuden hakea tietyn rivin tulosjoukosta, YLÄLAUSEKE sallii meidän tehdä samanlaisen operaation suhteessa nykyiseen riviin tietylle sarakkeelle.

käyttämällä Overia voimme määritellä tietyn rivijoukon päälle ikkunan, johon voimme soveltaa funktioita, kuten summaa.

jotta ymmärtäisit käsitteen, hajotamme tämän kahteen vaiheeseen:

1. osiotiedot käyttämällä over-lauseketta.
2. Järjestysosiot järjestyksellä.

Let ’ s roll.

Step 1-Partition data using OVER Clause

When we say we want to want to create a running total for all Actiontiondate, we want to partition our data by TransactionDate. Voit osio tiedot, Voimme käyttää yli lauseke.

seuraavassa lausekkeessa laskemme yhteen Transaktiomäärän ja summan jälkeen on YLILAUSEKE.

huomaa myös, että ryhmää ei ole lausekkeittain. Tämä on yllättävää, tyypillisesti yhteenlasketut funktiot, kuten summa, vaativat ryhmä lausekkeelta; miksi näin on?

koska käytämme YLILAUSEKETTA, summaa pidetään ikkunafunktiona – se toimii kaikilla YLILAUSEKKEESSA määritellyillä riveillä.

tässä on ikkunafunktio, jota käytämme:

SUM(TransactionAmount) OVER(PARTITION BY TransactionDate) RunningTotal

what make this a windows function is the OVER clause. checkout osa osio TransactionDate. Tämä tarkoittaa, että summa toimii kaikilla riveillä, joilla on sama Transaktiopäivitys. Tämä määrittelee niiden rivien ikkunan, joihin SUMMAFUNKTIO vaikuttaa.

tässä on tähänastinen kysely.

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

Up to this point we have partitioned the data and are able calculate a subtotal for all TransactionAmount values within a Transactiond date. Seuraava vaihe on nyt laskea välisumma.

tätä varten voimme käyttää järjestystä YLILAUSEKKEEN sisällä määrittelemään ikkunafunktion ”soveltamisalan”. Järjestyksessä määritelty looginen järjestys ikkunan funktio toimii.

tässä on ikkunafunktio, jota käytämme:

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

tämän ikkunafunktion ja ensimmäisen vaiheen funktion välinen erotus on järjestyksessä InvoiceID. Tämä määrittää osion sisällä käsiteltävän loogisen järjestyksen.

ilman järjestystä loogisessa järjestyksessä on odotettava, kunnes olemme ikkunan päässä laskemassa summaa. Kun järjestys on määritelty, looginen järjestys on laskea summa kullekin riville, mukaan lukien aiemmat TransactionAmount arvot ikkunan sisällä.

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

tässä on kyselyn tulos.

kun suoritit tämän kyselyn huomasitko kuinka paljon nopeammin se juoksi kuin sisempiä liittimiä käyttävä? Olin yllättynyt. Tiedän, että sisäinen liitos-operaatio kuluttaa paljon resursseja yhdistelmien tai rivien kasvaessa suuriksi, mutta olisin luullut, että sillä olisi sama asia ratkaisu käyttää yli.

Kehottaisin katsomaan jokaisen kyselyn kyselysuunnitelmaa. Alat oppia melko vähän SQL, kun alat tehdä tätä.