Az SQL-ben a kumulatív összeg olyan módszerre utal, amely lehetővé teszi számunkra, hogy kiszámítsuk egy numerikus oszlop futó összegét egy adatbázistáblán belül. A halmozott összeg a forgatókönyvek széles skálájában játszik szerepet, mint például a haladás követése vagy egy adott érték gördülő átlagának kiszámítása.
Ebben az oktatóanyagban megtanuljuk, hogyan kell megvalósítani és felhasználni a kumulatív összeget SQL-ben különféle módszerek és technikák segítségével.
Minta adat
Ahhoz, hogy jobban megértsük, hogyan tudjuk megvalósítani és használni a kumulatív összeget, kezdjük egy alaptáblázat felállításával egy mintaadatokkal. Ez lehetővé teszi, hogy ebben a bejegyzésben gyorsan és hatékonyan bemutassuk az összes módszer működését.
Kezdje egy alapvető táblázat létrehozásával, amely a termékinformációkat tárolja.
CREATE TABLE termékek (
product_id INT PRIMARY KEY,
termék_neve VARCHAR ( 255 ) ,
ár Tizedes ( 10 , 2 )
) ;
Ezzel létre kell hozni egy új „termékek” nevű táblát, amely tárolja a termékazonosítót, a termék nevét és az egyes termékek árát.
Ezután folytathatjuk a mintaadatok hozzáadását a következő példabeillesztési utasítások szerint:
INSERT INTO termékek ( product_id, product_name, price )
ÉRTÉKEK
( 1 , 'Visual Studio Code' , 10.00 ) ,
( 2 , 'Magas szöveg' , 80.00 ) ,
( 3 , 'PyCharm Professional' , 199,00 ) ,
( 4 , 'Eclipse IDE' , 30.00 ) ,
( 5 , 'IntelliJ IDEA Ultimate' , 699,00 ) ,
( 6 , 'GitHub Desktop' , 20.00 ) ,
( 7 , 'Várj szoftver' , 10.00 ) ,
( 8 , 'Xcode' , 660,00 ) ,
( 9 , 'NetBeans' , 0,00 ) ,
( 10 , 'Atom' , 60.00 ) ;
MEGJEGYZÉS: A megadott adatok teljes mértékben kitaláltak. Nem képviseli a felsorolt termékek egyikének tényleges árát sem.
Az eredményül kapott táblázat a következő:
SQL kumulatív összeg (önálló csatlakozás)
Az egyik technika, amellyel halmozott összeget tudunk megadni egy adott oszlopon, az öncsatlakozási módszer. Ennek a módszernek az egyik előnye, hogy szinte minden SQL adatbázisban működik, még azokban is, amelyek nem támogatják a Window függvényeket.
Vegyük például az előző „termékek” táblázatot. Az ár oszlop kumulatív összegét a következő lekérdezésben mutatjuk be:
KIVÁLASZTÁSp1.product_id,
p1.product_name,
p1.price,
ÖSSZEG ( p2.ár ) AS kumulatív_összeg
TÓL TŐL
termékek p1
CSATLAKOZIK
termékek p2
TOVÁBB
p1.product_id > = p2.termék_azonosító
CSOPORTOSÍT
p1.product_id,
p1.product_name,
p1.ár
RENDEZÉS
p1.product_id;
Észrevetted a lekérdezés működését? Ha nem, tartson velünk, ahogy lépésről lépésre elmagyarázzuk.
Az adott példalekérdezésben először két álnevet – „p1” és „p2” – hozunk létre a „termékek” táblához, amelyek lehetővé teszik az önillesztés végrehajtását.
Ezután folytatjuk a „p1” és a „p2” összekapcsolását azzal a feltétellel, hogy a „p1” „product_id” értéke nagyobb vagy egyenlő, mint a „p2” termék_azonosítója.
A következő lépésben meghívjuk a sum() függvényt, amely alapvetően az egyes sorok árainak összesített összegét számítja ki.
Végül az eredményeket csoportosítjuk a „product_id”, „product_name” és „price” attribútumokkal, és sorrendbe állítjuk az eredményeket.
Ezt a műveletet követően minden rekordhoz kumulatív összeget kell kapnunk, amint az az eredményül kapott táblázatban látható, az alábbiak szerint:
Amint látja, megkapjuk az összes előző sor összegét. Az utolsó sorban az összes előző sor összegét kell tartalmaznia.
SQL kumulatív összeg (ablakfüggvények)
A kumulatív összeg kiszámításának hatékonyabb és praktikusabb módja az SQL-ben az ablakfüggvények kihasználása, amikor az támogatott.
Ha olyan adatbázissal rendelkezik, mint az SQL Server, a PostgreSQL vagy a MySQL 8.0 vagy újabb verziója, akkor ez a leghasznosabb és legajánlottabb módszer egy adott oszlop kumulatív összegének meghatározására.
Vessen egy pillantást a példára, amint azt az alábbiakban bemutatjuk:
KIVÁLASZTÁStermék azonosító,
termék név,
ár,
ÖSSZEG ( ár ) FELETT ( RENDELÉS A termék_azonosítója szerint ) AS kumulatív_összeg
TÓL TŐL
Termékek;
Ebben az esetben a „product_id”, „product_name” és „price” oszlopok kiválasztásával kezdjük a „products” táblázatban.
Ezután a SUM() függvényt ablakfüggvényként használjuk az OVER záradék használatával.
Az OVER záradékban megadjuk az ORDER BY záradékot, amely meghatározza a kumulatív összeg kiszámításának sorrendjét.
Ennek a következőhöz hasonló kimenetet kell visszaadnia:
Észre fogja venni, hogy az ablakfüggvények használata koherensebb, hatékonyabb és olvashatóbb, mint az önillesztések használata.
Következtetés
Ebben az oktatóanyagban mindent megtudtunk az SQL kumulatív összegeiről. Kitértünk arra is, hogyan használhatjuk a self-joins és az ablak függvényeket a kumulatív összeg végrehajtásához SQL-ben.