U SQL-u kumulativni zbroj odnosi se na metodu koja nam omogućuje izračunavanje tekućeg ukupnog broja numeričkog stupca unutar tablice baze podataka. Kumulativni zbroj dolazi u obzir u raznim scenarijima kao što je praćenje napretka ili izračunavanje tekućih prosjeka dane vrijednosti.
U ovom vodiču naučit ćemo kako implementirati i koristiti kumulativni zbroj u SQL-u koristeći različite metode i tehnike.
Uzorak podataka
Da bismo bolje razumjeli kako možemo implementirati i koristiti kumulativni zbroj, počnimo s postavljanjem osnovne tablice s uzorkom podataka. To nam omogućuje da brzo i učinkovito demonstriramo djelovanje svih metoda u ovom postu.
Započnite stvaranjem osnovne tablice koja pohranjuje podatke o proizvodu.
CREATE TABLE proizvode (
product_id INT PRIMARNI KLJUČ,
naziv_proizvoda VARCHAR ( 255 ) ,
cijena DECIMALNA ( 10 , 2 )
) ;
Ovo bi trebalo stvoriti novu tablicu pod nazivom 'proizvodi' koja pohranjuje ID proizvoda, naziv proizvoda i cijenu svakog proizvoda.
Zatim možemo nastaviti s dodavanjem uzorka podataka kao što je prikazano u sljedećim primjerima izjava za umetanje:
UMETNUTI U proizvode ( product_id, product_name, cijena )
VRIJEDNOSTI
( 1 , 'Visual Studio Code' , 10.00 sati ) ,
( 2 , 'Uzvišeni tekst' , 80,00 ) ,
( 3 , 'PyCharm Professional' , 199,00 ) ,
( 4 , 'Eclipse IDE' , 30.00 sati ) ,
( 5 , 'IntelliJ IDEA Ultimate' , 699,00 ) ,
( 6 , 'GitHub stolno računalo' , 20.00 sati ) ,
( 7 , 'Softver čekanja' , 10.00 sati ) ,
( 8 , 'Xcode' , 660,00 ) ,
( 9 , 'NetBeans' , 0,00 ) ,
( 10 , 'Atom' , 60,00 ) ;
NAPOMENA: Navedeni podaci su potpuno izmišljeni. Ne predstavlja stvarnu cijenu nijednog od navedenih artikala.
Dobivena tablica je sljedeća:
SQL kumulativni zbroj (samopridruživanje)
Jedna od tehnika koju možemo koristiti za izvođenje kumulativnog zbroja na danom stupcu je korištenje metode samopridruživanja. Jedna prednost ove metode je da radi u gotovo svim SQL bazama podataka, čak i onima koje ne podržavaju Window funkcije.
Uzmimo za primjer prethodnu tablicu 'proizvodi'. Možemo stvoriti kumulativni zbroj stupca cijena kao što je prikazano u sljedećem upitu:
IZABERIp1.product_id,
p1.product_name,
p1.cijena,
IZNOS ( p2.cijena ) AS kumulativni_zbroj
IZ
proizvodi p1
PRIDRUŽITI
proizvodi p2
NA
p1.product_id > = p2.product_id
GRUPIRAJ PO
p1.product_id,
p1.product_name,
p1.cijena
NARUČI PO
p1.product_id;
Jeste li primijetili funkcioniranje upita? Ako ne, držite se nas dok vam objašnjavamo korak po korak.
U danom primjeru upita, počinjemo stvaranjem dva aliasa – “p1” i “p2” – za tablicu “proizvodi” što nam omogućuje izvođenje samostalnog spajanja.
Zatim nastavljamo spajati “p1” i “p2” pod uvjetom da je “product_id” od “p1” veći ili jednak “product_id” od “p2”.
U sljedećem koraku pozivamo funkciju sum() koja u osnovi izračunava kumulativni zbroj cijena za svaki redak.
Na kraju, grupiramo rezultate pomoću “product_id”, “product_name” i “price” i poredamo rezultate.
Nakon ove operacije, trebali bismo imati kumulativni zbroj za svaki zapis kao što je prikazano u rezultirajućoj tablici kao što je prikazano u nastavku:
Kao što vidite, dobili smo ukupni zbroj svih prethodnih redaka. Posljednji red treba sadržavati ukupan zbroj svih prethodnih redaka.
SQL kumulativni zbroj (funkcije prozora)
Učinkovitiji i praktičniji način izračuna kumulativne sume u SQL-u je iskorištavanje prednosti prozorskih funkcija kad god su podržane.
Ako imate bazu podataka kao što je SQL Server, PostgreSQL ili MySQL verzija 8.0 ili novija, ovo je najkorisnija i najpreporučljivija metoda za određivanje kumulativnog zbroja određenog stupca.
Pogledajte primjer kako je prikazano u nastavku:
IZABERIIdentifikacijski broj proizvoda,
ime proizvoda,
cijena,
IZNOS ( cijena ) NAD ( NARUDŽI PREMA product_id ) AS kumulativni_zbroj
IZ
proizvodi;
U ovom slučaju počinjemo odabirom stupaca 'product_id', 'product_name' i 'price' iz tablice 'products'.
Zatim koristimo funkciju SUM() kao prozorsku funkciju koristeći klauzulu OVER.
U klauzuli OVER specificiramo klauzulu ORDER BY koja definira redoslijed kojim se izračunava kumulativni zbroj.
Ovo bi trebalo vratiti sličan izlaz kao što je prikazano u sljedećem:
Primijetit ćete da je korištenje funkcija prozora koherentnije, učinkovitije i čitljivije u usporedbi s korištenjem samopridruživanja.
Zaključak
U ovom vodiču naučili smo sve o kumulativnim zbrojevima u SQL-u. Također smo pokrili kako koristiti funkcije samospajanja i prozora za izvođenje kumulativnog zbroja u SQL-u.