SQL:ssä kumulatiivinen summa viittaa menetelmään, jonka avulla voimme laskea tietokantataulukon numeerisen sarakkeen juoksevan summan. Kumulatiivinen summa tulee käyttöön useissa erilaisissa skenaarioissa, kuten edistymisen seurannassa tai tietyn arvon liukuvan keskiarvon laskemisessa.
Tässä opetusohjelmassa opimme toteuttamaan ja hyödyntämään kumulatiivista summaa SQL:ssä erilaisilla menetelmillä ja tekniikoilla.
Esimerkkitiedot
Ymmärtääksemme paremmin, kuinka voimme toteuttaa ja käyttää kumulatiivista summaa, aloitetaan perustamalla perustaulukko esimerkkitiedoilla. Tämän avulla voimme osoittaa kaikkien menetelmien toiminnan nopeasti ja tehokkaasti tässä viestissä.
Aloita luomalla perustaulukko, joka tallentaa tuotetiedot.
CREATE TABLE -tuotteet (
product_id INT PRIMARY KEY,
tuotteen_nimi VARCHAR ( 255 ) ,
hinta DECIMAL ( 10 , 2 )
) ;
Tämän pitäisi luoda uusi taulukko nimeltä 'tuotteet', joka tallentaa tuotetunnuksen, tuotteen nimen ja kunkin tuotteen hinnan.
Sitten voimme jatkaa esimerkkitietojen lisäämistä seuraavien esimerkkilisäyslausekkeiden mukaisesti:
INSERT INTO tuotteisiin ( tuotteen_tunnus, tuotteen_nimi, hinta )
ARVOT
( 1 , 'Visual Studio Code' , 10.00 ) ,
( 2 , 'Ylevä teksti' , 80.00 ) ,
( 3 , 'PyCharm Professional' , 199,00 ) ,
( 4 , 'Eclipse IDE' , 30.00 ) ,
( 5 , 'IntelliJ IDEA Ultimate' , 699,00 ) ,
( 6 , 'GitHub Desktop' , 20.00 ) ,
( 7 , 'Odota ohjelmisto' , 10.00 ) ,
( 8 , 'Xcode' , 660,00 ) ,
( 9 , 'NetBeans' , 0,00 ) ,
( 10 , 'Atomi' , 60.00 ) ;
HUOMAA: Annetut tiedot ovat täysin kuvitteellisia. Se ei edusta minkään listatun tuotteen todellista hintaa.
Tuloksena oleva taulukko on seuraava:
SQL:n kumulatiivinen summa (itseliittyminen)
Yksi tekniikoista, joita voimme käyttää kumulatiivisen summan suorittamiseen tietylle sarakkeelle, on itseliitosmenetelmän käyttäminen. Yksi tämän menetelmän etu on, että se toimii lähes kaikissa SQL-tietokannassa, myös niissä, jotka eivät tue Window-toimintoja.
Otetaan esimerkiksi edellinen 'tuotteet' -taulukko. Voimme luoda hintasarakkeen kumulatiivisen summan seuraavan kyselyn osoittamalla tavalla:
VALITSEp1.product_id,
p1.product_name,
p1.hinta,
SUMMA ( p2.hinta ) AS kumulatiivinen_summa
FROM
tuotteet p1
LIITTYÄ SEURAAN
tuotteet p2
PÄÄLLÄ
p1.product_id > = p2.tuotetunnus
GROUP BY
p1.product_id,
p1.product_name,
p1.hinta
TILAA
p1.tuotetunnus;
Huomasitko kyselyn toiminnan? Jos ei, pysy kanssamme, kun selitämme sen vaihe vaiheelta.
Esitetyssä esimerkkikyselyssä aloitamme luomalla kaksi aliasta – “p1” ja “p2” – “products”-taulukkoon, jonka avulla voimme suorittaa itseliitoksen.
Jatkamme sitten liittämistä 'p1' ja 'p2' sillä ehdolla, että 'p1':n 'product_id' on suurempi tai yhtä suuri kuin 'p2':n 'product_id'.
Seuraavassa vaiheessa kutsumme sum()-funktiota, joka periaatteessa laskee kunkin rivin hintojen kumulatiivisen summan.
Lopuksi ryhmittelemme tulokset käyttämällä 'product_id', 'product_name' ja 'price' ja järjestämme tulokset.
Tämän toiminnon jälkeen meillä pitäisi olla kumulatiivinen summa jokaiselle tietueelle, kuten tuloksena olevassa taulukossa on esitetty seuraavassa kuvassa:
Kuten näet, saamme kaikkien edellisten rivien kokonaissumman. Viimeisen rivin tulee sisältää kaikkien edellisten rivien yhteissumma.
SQL:n kumulatiivinen summa (ikkunafunktiot)
Tehokkaampi ja käytännöllisempi tapa laskea kumulatiivinen summa SQL:ssä on hyödyntää ikkunatoimintoja aina, kun sitä tuetaan.
Jos sinulla on tietokanta, kuten SQL Server, PostgreSQL tai MySQL-versio 8.0 tai uudempi, tämä on hyödyllisin ja suositeltavin menetelmä tietyn sarakkeen kumulatiivisen summan määrittämiseen.
Katso esimerkkiä, kuten seuraavassa esitetään:
VALITSETuotetunnus,
tuotteen nimi,
hinta,
SUMMA ( hinta ) YLI ( TILAA product_id ) AS kumulatiivinen_summa
FROM
Tuotteet;
Tässä tapauksessa aloitamme valitsemalla sarakkeet 'product_id', 'product_name' ja 'price' 'products' -taulukosta.
Käytämme sitten SUM()-funktiota ikkunafunktiona käyttämällä OVER-lausetta.
OVER-lauseessa määritetään ORDER BY -lause, joka määrittää järjestyksen, jossa kumulatiivinen summa lasketaan.
Tämän pitäisi palauttaa samanlainen tulos, joka näkyy seuraavassa:
Huomaat, että ikkunatoimintojen käyttö on johdonmukaisempaa, tehokkaampaa ja luettavampaa kuin itseliitosten käyttäminen.
Johtopäätös
Tässä opetusohjelmassa opimme kaiken kumulatiivisista summista SQL:ssä. Käsittelimme myös itseliitos- ja ikkunatoimintojen käyttämistä kumulatiivisen summan suorittamiseen SQL:ssä.