Kumulativna vsota SQL

V SQL se kumulativna vsota nanaša na metodo, ki nam omogoča izračun tekoče vsote številskega stolpca v tabeli zbirke podatkov. Kumulativna vsota pride v poštev v najrazličnejših scenarijih, kot je sledenje napredku ali izračun drsečih povprečij dane vrednosti.

V tej vadnici se bomo naučili implementirati in uporabiti kumulativno vsoto v SQL z uporabo različnih metod in tehnik.

Vzorčni podatki

Da bi bolje razumeli, kako lahko implementiramo in uporabimo kumulativno vsoto, začnimo z nastavitvijo osnovne tabele z vzorčnimi podatki. To nam omogoča, da v tej objavi hitro in učinkovito prikažemo delovanje vseh metod.

Začnite z ustvarjanjem osnovne tabele, v kateri so shranjeni podatki o izdelku.

To bi moralo ustvariti novo tabelo z imenom »izdelki«, v kateri so shranjeni ID izdelka, ime izdelka in cena vsakega izdelka.

Nato lahko nadaljujemo z dodajanjem vzorčnih podatkov, kot je prikazano v naslednjih primerih stavkov za vstavljanje:

OPOMBA: Navedeni podatki so v celoti izmišljeni. Ne predstavlja dejanske cene nobenega od navedenih artiklov.

Končna tabela je naslednja:

Kumulativna vsota SQL (samopridruževanje)

Ena od tehnik, ki jo lahko uporabimo za izvedbo kumulativne vsote v danem stolpcu, je uporaba metode samozdruževanja. Ena od prednosti te metode je, da deluje v skoraj vseh zbirkah podatkov SQL, tudi v tistih, ki ne podpirajo funkcij Window.

Vzemimo za primer prejšnjo tabelo »izdelki«. Ustvarimo lahko kumulativno vsoto stolpca cene, kot je prikazano v naslednji poizvedbi:

Ali ste opazili delovanje poizvedbe? Če ne, ostanite z nami, ko vam razložimo korak za korakom.

V danem primeru poizvedbe začnemo z ustvarjanjem dveh vzdevkov – »p1« in »p2« – za tabelo »products«, ki nam omogočata samozdruževanje.

Nato nadaljujemo z združevanjem »p1« in »p2« pod pogojem, da je »product_id« od »p1« večji ali enak »product_id« od »p2«.

V naslednjem koraku pokličemo funkcijo sum(), ki v bistvu izračuna kumulativno vsoto cen za vsako vrstico.

Nazadnje združimo rezultate z uporabo »product_id«, »product_name« in »price« ter razporedimo rezultate.

Po tej operaciji bi morali imeti kumulativno vsoto za vsak zapis, kot je prikazano v dobljeni tabeli, kot je prikazano v nadaljevanju:

Kot lahko vidite, dobimo skupno vsoto vseh prejšnjih vrstic. Zadnja vrstica naj vsebuje skupno vsoto vseh prejšnjih vrstic.

Kumulativna vsota SQL (okenske funkcije)

Bolj učinkovit in praktičen način izračuna kumulativne vsote v SQL je izkoriščanje prednosti okenskih funkcij, kadar koli so podprte.

Če imate zbirko podatkov, kot je SQL Server, PostgreSQL ali MySQL različice 8.0 ali novejšo, je to najbolj uporabna in priporočljiva metoda za določanje kumulativne vsote danega stolpca.

Oglejte si primer, kot je prikazano v nadaljevanju:

V tem primeru začnemo z izbiro stolpcev »product_id«, »product_name« in »price« v tabeli »products«.

Nato uporabimo funkcijo SUM() kot okensko funkcijo z uporabo klavzule OVER.

V klavzuli OVER določimo klavzulo ORDER BY, ki določa vrstni red izračuna kumulativne vsote.

To bi moralo vrniti podoben rezultat, kot je prikazano v naslednjem:

Opazili boste, da je uporaba okenskih funkcij bolj skladna, učinkovita in berljiva v primerjavi z uporabo samozdruževanj.

Zaključek

V tej vadnici smo izvedeli vse o kumulativnih vsotah v SQL. Pokrili smo tudi, kako uporabiti samozdruževanje in okenske funkcije za izvedbo kumulativne vsote v SQL.