Használja az SQL-t a futó összeg kiszámításához

az SQL futó összege többféle módon kiszámítható. Ez a cikk két módszert ismertet: az illesztéseket és az Ablakfunkciókat.

először megvizsgáljuk, hogyan lehet kiszámítani a futó összeget a belső csatlakozás segítségével. Ezzel nem csak többet megtudhat a csatlakozási feltételekről, hanem megnézheti, hogyan kell az eredményt összefoglalni, hogy megkapja a futó összeget.

miután látta, hogyan kell csinálni “régi iskola” stílusban, az OVER záradékot használjuk a futó összegek kiszámításához egy ablakfüggvény segítségével. Ez a módszer újabb és tömörebb használni.

a lecke összes példája a Microsoft SQL Server Management Studio és a WideWorldImporters adatbázison alapul. Ezeket az ingyenes eszközöket az útmutatóm segítségével kezdheti el az SQL Server 2016 használatának megkezdése.

mi az a futó összesen?

célunk az, hogy kiszámoljuk a futó összeget, amely visszaáll, amikor a TransactionDate megváltozik. Összesítjük a tranzakció összegét. A tranzakció dátumán belüli minden további számla esetében a RunningTotal-nak meg kell egyeznie az előző InvoiceID futó összegével, plusz az aktuális TransactionAmount-tal.

ezt a következő példában láthatja működés közben. A RunningTotal a számla 3, a korábbi RunningTotal 3110.75 plusz a számla 3 tranzakciós összege 103.50

Számítsa ki a futó összesen SQL segítségével egy belső JOIN

először számítsuk ki a futó teljes segítségével a belső csatlakozik. Ez a módszer többet tár fel a futó összeg kiszámításának mechanikájából, mint a partíció használata. Mint ilyen, ez ad egy másik lehetőséget, hogy megértsék a belső csatlakozik, és alkalmazza ezeket a fogalmakat egy másik felhasználási eset.

három lépés van a probléma megoldására:

1. Get sorok a futó teljes
2. beállítási részletek a futó teljes belső csatlakozik
3. számítsuk ki a futó teljes összegző adatok.

kezdjük!

1. lépés-Get sorok futó összesen

annak érdekében, hogy kiszámítja a futó összesen, akkor lekérdezni a CustomerTransactions tábla. Az eredménybe belefoglaljuk az InvoiceID, TransactionDate és TransactionAmount azonosítót. Természetesen a futó összeget a tranzakcióból számítják Kiösszeget.

itt található az alapadatok lekérdezése.

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

itt vannak az adatok, amelyekkel dolgozni fogunk.

valójában ez a lépés az alapvető információk megismerésére szolgál. Nem kell igazán csinálni. Néha azonban csak az adatok megtekintéséhez szeretném ajánlani az alapvető lekérdezést. Ezenkívül annak biztosítása érdekében, hogy ne legyenek rendellenességek vagy különleges helyzetek, amelyeket be kell tartanom.

2. lépés-a teljes Futtatás részletei a belső illesztések használatával

ehhez a lépéshez a részleteket úgy állítjuk be, hogy kiszámítsuk a futó összeget. Ennek érdekében minden InvoiceID-t megkapunk a tranzakció összegéről és az összes tranzakció összegéről.

ahhoz, hogy ezt megtehessük, csatlakozunk a CustomerTransactions táblához.

ha ezt csatlakozási feltétel nélkül tesszük, akkor a tranzakciók minden kombinációját megkapnánk, ez nem az, amit akarunk.

annak érdekében, hogy az egyes táblázatokból a sorok megfelelő kombinációját kapjuk, két csatlakozási feltételt adunk hozzá. Az egyik, hogy minden számlát és az azt megelőző (Zöld).

a második biztosítja, hogy csak ugyanazon a tranzakción szereplő számlákat tartalmazzuk (piros)

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

lássuk, hogy működik ez.

a legegyszerűbb feltétel, hogy megértsük, hol egyezik TransactionDate. Ez biztosítja, hogy a számlák egyezésének közös tranzakciós dátuma legyen. Ha ez volt az egyetlen csatlakozás, akkor egy dátumon belüli összes tranzakció alösszegét számítjuk ki.

mivel ki akarjuk számítani a futó végösszeget, valahogy meg kell szereznünk minden InvoiceID-hez a számla tranzakciós összegét és az előtte lévő összes számlát. Más szavakkal, adja vissza az összes megfelelő sort, ahol a számla nagyobb vagy egyenlő a megfelelő számlákkal, amelyeket megpróbálunk összesíteni.

ha megnézi a fenti eredményt, látni fogja, hogy az első oszlopban felsorolt minden számla esetében (T1.InvoiceID), ez nagyobb vagy egyenlő InvoiceID ‘ s a második oszlopban (T2.InvoiceID).

ez a T1 csatlakozási feltétel eredménye.InvoiceID > = T2.InvoiceID.

ennek a csatlakozásnak és a csatlakozási feltételeknek az az eredménye, hogy most már rendelkezünk a nyersanyagokkal a futó összeg kiszámításához.

figyeljük meg, hogyan ismétlődnek az első, harmadik és negyedik oszlopok. Ezt előnyünkre használhatjuk, hogy összefoglaljuk az eredményt, hogy elérjük a futó összeget.

3. lépés-Számítsa ki a teljes futást a

sorok összegzésével a rendelkezésre álló részletes információkkal az utolsó lépés a sorok összefoglalása. Ez lehetővé teszi a futó összegek kiszámítását.

itt található az összefoglaló végrehajtásához használt lekérdezés:

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

figyeljük meg, hogyan csoportosítjuk a T1-et.InvoiceID, T1.TransactionDate és T1.Tranzakciós összeg. Ezeket az értékeket ismételtük meg részletes adatainkban a 2. lépésben.

a futó összeg a T2-ből származik.Tranzakciós összeg. Visszahívás ezek az értékek TransactionAmount az összes számla előtt a számla jelenik meg. Más szavakkal, a megjelenített számla nagyobb vagy egyenlő velük.

ez lehetővé teszi számunkra, hogy létrejöjjön egy futó összesen.

a listában szereplő minden egyes következő számla kiszámítja a RunningTotal értékét az összes tranzakciós összeg összegzésével a számlájából és az azt megelőző számlákból.

most, hogy már látta a hagyományos módon érkezik a futó összesen, és talán szerzett nagyobb elismerését, hogyan kell használni csatlakozik és csatlakozni feltételek megoldani, nézzük meg az egyik újabb funkciók SQL, partíciók, és hogyan lehet használni, hogy elérjék ugyanazt az eredményt.

számítson ki egy futó összeget az SQL-ben egy OVER záradék használatával

A OVER záradék, ha egy nagyon erős utasítás. Ez lehetővé teszi, hogy meghatározza egy sor, egy eredményhalmaz, hogy egy művelet befolyásolja.

hasonlóan az OFFSET és a FETCH lehetővé teszi számunkra, hogy egy adott sortartományt lekérjünk egy eredményhalmazból, az OVER záradék lehetővé teszi számunkra, hogy hasonló műveletet hajtsunk végre az aktuális sorhoz képest egy adott oszlophoz.

az OVER használatával megadhatunk egy ablakot egy megadott sorkészlet felett, amelyre olyan funkciókat alkalmazhatunk, mint a sum.

ahhoz, hogy megértse a koncepciót, ezt két lépésre bontjuk:

1. partíciós adatok az OVER záradék használatával.
2. rendeljen partíciókat rendeléssel.

guruljunk.

1. lépés-partíció adatok használata over záradék

amikor azt mondjuk, hogy szeretnénk, hogy szeretnénk létrehozni egy futó összesen az összes számlát a TransactionDate, azt akarjuk, hogy particionálni adatainkat TransactionDate. Az adatok felosztásához használhatjuk az over záradékot.

a következő nyilatkozatban összegezzük a TransactionAmount-ot, az összeg után pedig egy OVER záradék van.

azt is vegye figyelembe, hogy nincs GROUP BY záradék. Ez meglepő, jellemzően az összesített függvények, például az összeg, megkövetelik a GROUP BY záradék; miért van ez így?

mivel az OVER záradékot használjuk, az összeg ablakfüggvénynek számít – az OVER záradékban meghatározott sorokon működik.

itt van az ablak funkció fogjuk használni:

SUM(TransactionAmount) OVER(PARTITION by TransactionDate) RunningTotal

mi teszi ezt a windows funkció A OVER záradék. pénztár az alkatrész partíció által TransactionDate. Ez azt jelenti, hogy az összeg minden sorban ugyanazzal a TransactionDate-vel működik. Ez határozza meg a sorok ablakát, amelyet az összeg függvény befolyásol.

itt van a lekérdezés eddig.

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

2. lépés-partíció rendezése

sorrenddel eddig a pontig particionáltuk az adatokat, és képesek vagyunk kiszámítani az összes TransactionAmount érték részösszegét egy TransactionDate-en belül. A következő lépés a részösszeg kiszámítása.

ehhez használhatjuk ORDER BY az OVER záradékon belül az ablakfüggvény “hatókörének” meghatározásához. A sorrend a megadott logikai sorrendben az ablak funkció működik.

itt van az ablak funkció fogjuk használni:

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

a különbség az ablakfüggvény és az első lépés között az ORDER BY InvoiceID. Ez határozza meg a partíción belül feldolgozandó logikai sorrendet.

a logikai sorrend sorrendje nélkül meg kell várni, amíg az ablak végén vagyunk az összeg kiszámításához. A megadott sorrend szerint a logikai sorrend az egyes sorok összegének kiszámítása, beleértve az ablak korábbi TransactionAmount értékeit.

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

itt látható a lekérdezés futtatásának eredménye.

amikor futtatta ezt a lekérdezést, észrevette, hogy mennyivel gyorsabban futott, mint a belső illesztéseket használó? Meglepődtem. Tudom, hogy a belső illesztési művelet sok erőforrást fogyaszt, mivel a kombinációk vagy sorok nagyok lesznek, de azt gondoltam volna, hogy ugyanez a helyzet a megoldással.

azt javasoljuk, hogy nézd meg minden lekérdezés lekérdezési tervet. Majd elkezd tanulni egy kicsit az SQL, amikor elkezdi ezt.