SQL-is viitab kumulatiivne summa meetodile, mis võimaldab meil arvutada andmebaasi tabeli numbrilise veeru jooksva kogusumma. Kumulatiivne summa tuleb mängu paljudes erinevates stsenaariumides, nagu edenemise jälgimine või antud väärtuse jooksva keskmise arvutamine.
Selles õpetuses õpime, kuidas rakendada ja kasutada kumulatiivset summat SQL-is, kasutades erinevaid meetodeid ja tehnikaid.
Näidisandmed
Et paremini mõista, kuidas saame kumulatiivset summat rakendada ja kasutada, alustame näidisandmetega põhitabeli koostamisest. See võimaldab meil selles postituses kiiresti ja tõhusalt näidata kõigi meetodite toimimist.
Alustage põhitabeli loomisega, mis salvestab tooteteabe.
CREATE TABLE tooted (
product_id INT PRIMARY KEY,
toote_nimi VARCHAR ( 255 ) ,
hind KOMMAL ( 10 , 2 )
) ;
See peaks looma uue tabeli nimega 'tooted', mis salvestab toote ID, toote nime ja iga toote hinna.
Seejärel saame jätkata näidisandmete lisamist, nagu on näidatud järgmistes näitesisestuslausetes:
INSERT INTO toodetesse ( toote_id, toote_nimi, hind )
VÄÄRTUSED
( 1 , Visual Studio Code , 10.00 ) ,
( 2 , 'Ülev tekst' , 80.00 ) ,
( 3 , 'PyCharm Professional' , 199.00 ) ,
( 4 , 'Eclipse IDE' , 30.00 ) ,
( 5 , 'IntelliJ IDEA Ultimate' , 699.00 ) ,
( 6 , 'GitHub Desktop' , 20.00 ) ,
( 7 , 'Oota tarkvara' , 10.00 ) ,
( 8 , 'Xcode' , 660.00 ) ,
( 9 , 'NetBeans' , 0.00 ) ,
( 10 , 'Aatom' , 60.00 ) ;
MÄRKUS. Antud andmed on täielikult väljamõeldud. See ei kajasta ühegi loetletud kauba tegelikku hinda.
Saadud tabel on järgmine:
SQL-i kumulatiivne summa (iseliitumine)
Üks tehnikatest, mida saame kasutada antud veeru kumulatiivse summa tegemiseks, on iseliitumise meetod. Selle meetodi üks eelis on see, et see töötab peaaegu kõigis SQL-andmebaasides, isegi nendes, mis ei toeta akna funktsioone.
Võtke näiteks eelmine “toodete” tabel. Saame luua hinna veeru kumulatiivse summa, nagu on näidatud järgmises päringus:
VALIp1.product_id,
p1.toote_nimi,
p1.hind,
SUMMA ( p2.hind ) AS kumulatiivne_summa
FROM
tooted p1
LIITU
tooted p2
PEAL
p1.toote_id > = p2.toote_id
GROUP BY
p1.product_id,
p1.product_name,
p1.hind
TELLI
p1.toote_id;
Kas märkasite päringu toimimist? Kui ei, siis jääge meie juurde, kui me seda samm-sammult selgitame.
Antud näidispäringus loome alustuseks kaks varjunime – “p1” ja “p2” – tabeli “products” jaoks, mis võimaldab meil ise liituda.
Seejärel ühendame 'p1' ja 'p2' tingimusel, et 'p1' toote_id on suurem kui 'p2' toote_id või sellega võrdne.
Järgmises etapis kutsume välja funktsiooni sum(), mis arvutab põhimõtteliselt iga rea hindade kumulatiivse summa.
Lõpuks rühmitame tulemused atribuutide „product_id”, „product_name” ja „price” abil ning järjestame tulemused.
Pärast seda toimingut peaks meil olema iga kirje kumulatiivne summa, nagu on näidatud saadud tabelis, nagu on näidatud järgmises:
Nagu näete, saame kõigi eelmiste ridade kogusumma. Viimane rida peaks sisaldama kõigi eelmiste ridade kogusummat.
SQL-i kumulatiivne summa (aknafunktsioonid)
Tõhusam ja praktilisem viis kumulatiivse summa arvutamiseks SQL-is on kasutada aknafunktsioone alati, kui seda toetatakse.
Kui teil on andmebaas, näiteks SQL Server, PostgreSQL või MySQL versioon 8.0 või uuem, on see kõige kasulikum ja soovitatavam meetod antud veeru kumulatiivse summa määramiseks.
Vaadake näidet, nagu on näidatud järgmises:
VALIproduct_id,
tootenimi,
hind,
SUMMA ( hind ) LÄBI ( TELLIMINE toote_id ) AS kumulatiivne_summa
FROM
tooted;
Sel juhul alustame sellest, et valime tabelist 'products' veerud 'product_id', 'product_name' ja 'price'.
Seejärel kasutame funktsiooni SUM() aknafunktsioonina, kasutades OVER-klauslit.
OVER-klauslis määrame klausli ORDER BY, mis määrab kumulatiivse summa arvutamise järjekorra.
See peaks tagastama sarnase väljundi, nagu on näidatud järgmises:
Märkate, et aknafunktsioonide kasutamine on sidusam, tõhusam ja loetavam võrreldes iseliitmise kasutamisega.
Järeldus
Selles õpetuses õppisime kõike SQL-i kumulatiivsete summade kohta. Samuti käsitlesime seda, kuidas kasutada SQL-is kumulatiivse summa tegemiseks iseliitumist ja aknafunktsioone.