V SQL sa kumulatívny súčet vzťahuje na metódu, ktorá nám umožňuje vypočítať priebežný súčet číselného stĺpca v databázovej tabuľke. Kumulatívna suma vstupuje do hry v širokej škále scenárov, ako je sledovanie pokroku alebo výpočet kĺzavých priemerov danej hodnoty.
V tomto návode sa naučíme, ako implementovať a využívať kumulatívny súčet v SQL pomocou rôznych metód a techník.
Vzorové údaje
Aby sme lepšie pochopili, ako môžeme implementovať a použiť kumulatívny súčet, začnime nastavením základnej tabuľky so vzorovými údajmi. To nám umožňuje v tomto príspevku rýchlo a efektívne demonštrovať fungovanie všetkých metód.
Začnite vytvorením základnej tabuľky, v ktorej sú uložené informácie o produkte.
CREATE TABLE produkty (
product_id INT PRIMARY KEY,
názov_produktu VARCHAR ( 255 ) ,
cena DECIMÁLNA ( 10 , 2 )
) ;
Tým by sa mala vytvoriť nová tabuľka s názvom „produkty“, v ktorej je uložené ID produktu, názov produktu a cena každého produktu.
Potom môžeme pristúpiť k pridávaniu vzorových údajov, ako je znázornené v nasledujúcom príklade príkazov na vloženie:
VLOŽIŤ DO produktov ( product_id, product_name, price )
HODNOTY
( 1 , 'Visual Studio Code' , 10:00 ) ,
( 2 , 'Vznešený text' , 80,00 ) ,
( 3 , „PyCharm Professional“ , 199,00 ) ,
( 4 , 'Eclipse IDE' , 30:00 ) ,
( 5 , „IntelliJ IDEA Ultimate“ , 699,00 ) ,
( 6 , „GitHub Desktop“ , 20:00 ) ,
( 7 , 'Wait Software' , 10:00 ) ,
( 8 , 'Xcode' , 660,00 ) ,
( 9 , 'NetBeans' , 0,00 ) ,
( 10 , 'atóm' , 60,00 ) ;
POZNÁMKA: Uvedené údaje sú úplne vymyslené. Nepredstavuje skutočnú cenu žiadnej z uvedených položiek.
Výsledná tabuľka je nasledovná:
Kumulatívny súčet SQL (vlastné spojenie)
Jednou z techník, ktoré môžeme použiť na vykonanie kumulatívneho súčtu v danom stĺpci, je použitie metódy vlastného spojenia. Jednou z výhod tejto metódy je, že funguje takmer vo všetkých SQL databázach, dokonca aj v tých, ktoré nepodporujú funkcie Window.
Vezmite si napríklad predchádzajúcu tabuľku „produktov“. Môžeme vytvoriť kumulatívny súčet stĺpca ceny, ako je to znázornené v nasledujúcom dotaze:
VYBRAŤp1.product_id,
p1.product_name,
p1.cena,
SUM ( p2.cena ) AS kumulatívny_súčet
OD
produkty p1
PRIPOJTE SA
produkty p2
ON
p1.product_id > = p2.product_id
GROUP BY
p1.product_id,
p1.product_name,
p1.cena
ZORADIŤ PODĽA
p1.product_id;
Všimli ste si fungovanie dopytu? Ak nie, zostaňte s nami, keď to krok za krokom vysvetľujeme.
V uvedenom príklade dotazu začneme vytvorením dvoch aliasov – „p1“ a „p2“ – pre tabuľku „produkty“, čo nám umožňuje vykonať vlastné spojenie.
Potom pristúpime k spojeniu „p1“ a „p2“ za podmienky, že „identifikátor_produktu“ v „p1“ je väčší alebo rovný „identifikátoru produktu“ v „p2“.
V ďalšom kroku zavoláme funkciu sum(), ktorá v podstate vypočíta kumulatívny súčet cien pre každý riadok.
Nakoniec výsledky zoskupíme pomocou „product_id“, „product_name“ a „price“ a zoradíme výsledky.
Po tejto operácii by sme mali mať kumulatívny súčet pre každý záznam, ako je to znázornené vo výslednej tabuľke, ako je uvedené nižšie:
Ako vidíte, dostaneme celkový súčet všetkých predchádzajúcich riadkov. Posledný riadok by mal obsahovať celkový súčet všetkých predchádzajúcich riadkov.
SQL Kumulatívny súčet (funkcie okna)
Efektívnejším a praktickejším spôsobom výpočtu kumulatívneho súčtu v SQL je využitie funkcií okna vždy, keď sú podporované.
Ak máte databázu, ako je SQL Server, PostgreSQL alebo MySQL verzie 8.0 a vyššej, je to najužitočnejšia a odporúčaná metóda na určenie kumulatívneho súčtu daného stĺpca.
Pozrite sa na príklad, ktorý je znázornený v nasledujúcom texte:
VYBRAŤidentifikačné číslo produktu,
Meno Produktu,
cena,
SUM ( cena ) KONIEC ( ORDER BY product_id ) AS kumulatívny_súčet
OD
Produkty;
V tomto prípade začneme výberom stĺpcov „product_id“, „product_name“ a „price“ z tabuľky „products“.
Potom použijeme funkciu SUM() ako funkciu okna pomocou klauzuly OVER.
V klauzule OVER uvádzame klauzulu ORDER BY, ktorá definuje poradie, v ktorom sa vypočítava kumulatívny súčet.
To by malo vrátiť podobný výstup, ako je znázornené v nasledujúcom texte:
Všimnete si, že používanie funkcií okna je koherentnejšie, efektívnejšie a čitateľnejšie v porovnaní s používaním vlastných spojení.
Záver
V tomto návode sme sa naučili všetko o kumulatívnych sumách v SQL. Tiež sme sa zaoberali tým, ako používať funkcie self-joins a window na vykonanie kumulatívneho súčtu v SQL.