Naučimo da sumiramo. Ne, ovo nisu rezultati proučavanja SQL-a, već rezultati vrijednosti stupaca tablica baze podataka. SQL agregatne funkcije rade na vrijednostima stupca kako bi proizvele jednu rezultirajuću vrijednost. Najčešće korištene SQL agregatne funkcije su SUM, MIN, MAX, AVG i COUNT. Potrebno je razlikovati dva slučaja korištenja agregatnih funkcija. Prvo, agregatne funkcije se koriste same i vraćaju jednu rezultujuću vrijednost. Drugo, agregatne funkcije se koriste sa SQL GROUP BY klauzulom, odnosno grupiranjem po poljima (kolonama) kako bi se dobile rezultirajuće vrijednosti u svakoj grupi. Hajde da prvo razmotrimo slučajeve korišćenja agregatnih funkcija bez grupisanja.

SQL SUM funkcija

SQL SUM funkcija vraća zbroj vrijednosti u stupcu tablice baze podataka. Može se primijeniti samo na stupce čije su vrijednosti brojevi. SQL upiti da dobijete rezultujuću sumu počnite ovako:

ODABIR ZBIR (COLUMN_NAME) ...

Nakon ovog izraza slijedi FROM (TABLE_NAME), a zatim se uvjet može specificirati korištenjem klauzule WHERE. Uz to, nazivu stupca može prethoditi DISTINCT, što znači da će se računati samo jedinstvene vrijednosti. Prema zadanim postavkama, sve vrijednosti se uzimaju u obzir (za to možete posebno navesti ne DISTINCT, već SVE, ali riječ SVE nije potrebna).

Ako želite da pokrenete upite baze podataka iz ove lekcije na MS SQL Serveru, ali ovaj DBMS nije instaliran na vašem računaru, onda ga možete instalirati koristeći uputstva na ovom linku .

Prvo ćemo raditi sa bazom podataka kompanije - Company1. Skripta za kreiranje ove baze podataka, njenih tabela i popunjavanje tabela podacima nalazi se u datoteci na ovom linku .

Primjer 1. Postoji baza podataka kompanije sa podacima o njenim sektorima i zaposlenima. Pored svega ostalog, tabela osoblja ima kolonu sa podacima o platama zaposlenih. Izbor iz tabele izgleda ovako (za uvećanje slike kliknite na nju levim tasterom miša):

Za dobijanje sume svih plata koristimo sledeći upit (na MS SQL Serveru - sa prethodnom konstrukcijom USE company1;):

ODABERITE SUMU (Platu) IZ osoblja

Ovaj upit će vratiti vrijednost 287664.63.

I sada . U vježbama već počinjemo komplicirati zadatke, približavajući ih onima s kojima se susrećemo u praksi.

SQL MIN funkcija

SQL MIN funkcija također radi na stupcima čije su vrijednosti brojevi i vraća minimum svih vrijednosti u stupcu. Ova funkcija ima sintaksu sličnu onoj kod funkcije SUM.

Primjer 3. Baza podataka i tabela su iste kao u primjeru 1.

Moramo da saznamo minimalnu platu za zaposlene u odeljenju broj 42. Da biste to uradili, napišite sledeći upit (na MS SQL Server - sa prefiksom USE firma1;):

Upit će vratiti vrijednost 10505.90.

I opet vježba za nezavisna odluka . U ovoj i nekim drugim vježbama biće vam potrebna ne samo tabela osoblja, već i tabela organizacije koja sadrži podatke o odjelima kompanije:

Primjer 4. Tabela Org se dodaje tabeli osoblja koja sadrži podatke o odeljenjima kompanije. Odštampajte minimalni broj godina rada jednog zaposlenog u odeljenju koje se nalazi u Bostonu.

SQL MAX funkcija

SQL MAX funkcija radi slično i ima sličnu sintaksu, koja se koristi kada trebate odrediti maksimalnu vrijednost među svim vrijednostima u stupcu.

Primjer 5.

Moramo saznati maksimalnu platu zaposlenih u odjeljenju broj 42. Da biste to učinili, napišite sljedeći upit (na MS SQL Server - sa prefiksom USE company1;):

Upit će vratiti vrijednost 18352.80

Vrijeme je vježbe za samostalno rješavanje.

Primjer 6. Opet radimo sa dva stola - Staff i Org. Iskazati naziv odjela i maksimalnu vrijednost provizije koju je primio jedan zaposlenik u odjeljenju koji pripada grupi odjela (Odjeljenje) Istočni. Koristi JOIN (spajanje tablica) .

SQL AVG funkcija

Ono što je navedeno u vezi sa sintaksom za prethodno opisane funkcije važi i za SQL AVG funkciju. Ova funkcija vraća prosjek svih vrijednosti u stupcu.

Primjer 7. Baza podataka i tabela su iste kao u prethodnim primjerima.

Recimo da želite da saznate prosečni staž zaposlenih u odeljenju broj 42. Da biste to uradili, napišite sledeći upit (na MS SQL Server - sa prethodnom konstrukcijom USE company1;):

Rezultat će biti 6,33

Primjer 8. Radimo sa jednim stolom - Osoblje. Prikažite prosječnu platu zaposlenih sa 4 do 6 godina iskustva.

SQL funkcija COUNT

SQL COUNT funkcija vraća broj zapisa u tablici baze podataka. Ako navedete SELECT COUNT(COLUMN_NAME) ... u upitu, rezultat će biti broj zapisa bez uzimanja u obzir onih zapisa u kojima je vrijednost stupca NULL (nedefinirana). Ako koristite zvjezdicu kao argument i počnite SELECT upit COUNT(*) ..., tada će rezultat biti broj svih zapisa (redova) u tabeli.

Primjer 9. Baza podataka i tabela su iste kao u prethodnim primjerima.

Želite znati broj svih zaposlenih koji primaju provizije. Broj zaposlenih čije vrijednosti Comm kolone nisu NULL će biti vraćen sljedećim upitom (na MS SQL Server - sa prefiksom USE company1;):

SELECT COUNT (Comm) OD osoblja

Rezultat će biti 11.

Primjer 10. Baza podataka i tabela su iste kao u prethodnim primjerima.

Ako želite da saznate ukupan broj zapisa u tabeli, onda upotrijebite upit sa zvjezdicom kao argument funkciji COUNT (na MS SQL Serveru - sa prethodnom konstrukcijom USE company1;):

ODABIR BROJ (*) IZ osoblja

Rezultat će biti 17.

U sljedećem vježba za samostalno rješenje morat ćete koristiti potupit.

Primjer 11. Radimo sa jednim stolom - Osoblje. Prikažite broj zaposlenih u odjelu za planiranje (Plains).

Agregatne funkcije sa SQL GROUP BY

Pogledajmo sada korištenje agregatnih funkcija zajedno sa SQL GROUP BY naredbom. SQL GROUP BY izraz se koristi za grupiranje vrijednosti rezultata po stupcima u tablici baze podataka. Web stranica ima lekcija posebno posvećena ovom operateru .

Radit ćemo sa bazom podataka "Ads Portal 1". Skripta za kreiranje ove baze podataka, njene tabele i popunjavanje tabele podataka nalazi se u datoteci na ovom linku .

Primjer 12. Dakle, postoji baza podataka oglasnog portala. Ima tabelu Oglasi koja sadrži podatke o oglasima poslanim za sedmicu. Kolona Kategorija sadrži podatke o velikim kategorijama oglasa (na primjer Nekretnine), a kolona Dijelovi sadrži podatke o manjim dijelovima uključenim u kategorije (na primjer, dijelovi Stanovi i ljetnikovci su dijelovi kategorije Nekretnine). Kolona Jedinice sadrži podatke o broju dostavljenih oglasa, a kolona Novac podatke o iznosu primljenog novca za slanje oglasa.

Kategorija	Part	Jedinice	Novac
Transport	Automobili	110	17600
Nekretnina	Apartmani	89	18690
Nekretnina	Dachas	57	11970
Transport	Motocikli	131	20960
Građevinski materijali	Ploče	68	7140
Elektrotehnika	televizori	127	8255
Elektrotehnika	Frižideri	137	8905
Građevinski materijali	Regips	112	11760
Slobodno vrijeme	Knjige	96	6240
Nekretnina	Kod kuce	47	9870
Slobodno vrijeme	Muzika	117	7605
Slobodno vrijeme	Igre	41	2665

Koristeći SQL GROUP BY izraz, pronađite iznos novca zarađen postavljanjem oglasa u svakoj kategoriji. Pišemo sljedeći upit (na MS SQL Server - sa prethodnom konstrukcijom USE adportal1;):

ODABERITE kategoriju, SUM (Novac) KAO Novac IZ OGLASA GRUPA PO Kategoriji

Primjer 13. Baza podataka i tabela su iste kao u prethodnom primjeru.

Koristeći SQL GROUP BY izraz, saznajte koji dio svake kategorije ima najviše popisa. Pišemo sljedeći upit (na MS SQL Server - sa prethodnom konstrukcijom USE adportal1;):

ODABERITE kategoriju, dio, MAX (jedinice) KAO maksimum IZ OGLASA GRUPE PO kategoriji

Rezultat će biti sljedeća tabela:

Ukupne i pojedinačne vrijednosti mogu se dobiti u jednoj tabeli kombinujući rezultate upita koristeći UNION operator .

Relacijske baze podataka podataka i SQL jezika

Opisuje upotrebu aritmetičkih operatora i konstrukciju izračunatih stupaca. Uzimaju se u obzir konačne (agregatne) funkcije COUNT, SUM, AVG, MAX, MIN. Pruža primjer korištenja operatora GROUP BY za grupiranje u upitima za odabir podataka. Opisuje upotrebu klauzule HAVING.

Izgradnja izračunatih polja

Općenito, stvoriti izračunato (izvedeno) polje u SELECT listi treba navesti neki izraz SQL jezik. Ovi izrazi koriste aritmetičke operacije sabiranje, oduzimanje, množenje i dijeljenje, kao i ugrađene funkcije SQL jezika. Možete navesti ime bilo kojeg stupca (polja) tablice ili upita, ali koristite samo naziv stupca tablice ili upita koji je naveden na listi klauzule FROM odgovarajućeg izraza. Prilikom konstruisanja složenih izraza, mogu biti potrebne zagrade.

SQL standardi vam omogućuju da eksplicitno specificirate imena stupaca rezultirajuće tablice, za koje se koristi AS klauzula.

SELECT Product.Name, Product.Cijena, Deal.Quantity, Product.Cijena*Deal.Quantity KAO trošak IZ UNUTRAŠNJEG PRIDRUŽENJA proizvoda Ponuda NA Product.ProductCode=Deal.ProductCode Primjer 6.1. Obračun ukupnog troška za svaku transakciju.

Primjer 6.2. Dobijte spisak firmi sa naznakom prezimena i inicijala klijenata.

ODABERITE kompaniju, prezime+""+ lijevo(ime,1)+"."+lijevo(srednje ime,1)+"."KAO Puno ime od klijenta Primjer 6.2. Dobijanje liste firmi sa naznakom prezimena i inicijala klijenata.

Zahtjev koristi ugrađenu funkciju Lijevo, koja vam u ovom slučaju omogućava da izrežete jedan znak slijeva u tekstualnoj varijabli.

Primjer 6.3. Dobijte listu proizvoda s naznakom godine i mjeseca prodaje.

SELECT Product.Name, Year(Transaction.Date) AS Year, Month(Transaction.Date) AS Month FROM Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode Primjer 6.3. Primanje liste proizvoda sa naznakom godine i mjeseca prodaje.

Upit koristi ugrađene funkcije Godina i Mjesec za izdvajanje godine i mjeseca iz datuma.

Korištenje funkcija sažetka

Korišćenjem konačne (agregatne) funkcije unutar SQL upita možete dobiti niz općih statističkih informacija o skupu odabranih vrijednosti izlaznog skupa.

Korisnik ima pristup sljedećim osnovnim funkcije sažetka:

• Count (Expression) - određuje broj zapisa u izlaznom skupu SQL upita;
• Min/Max (Izraz) - odredi najmanju i najveću od skupa vrijednosti​​u određenom polju zahtjeva;
• Prosjek (izraz) - ova funkcija vam omogućava da izračunate prosjek skupa vrijednosti ​​pohranjenih u određenom polju zapisa odabranih upitom. To je aritmetički prosjek, tj. zbir vrijednosti podijeljen sa njihovim brojem.
• Zbir (izraz) - Izračunava zbir skupa vrijednosti ​​sadržanih u određenom polju zapisa odabranih upitom.

Najčešće se nazivi stupaca koriste kao izrazi. Izraz se također može izračunati korištenjem vrijednosti nekoliko tabela.

Sve ove funkcije rade na vrijednostima u jednoj koloni tablice ili aritmetičkom izrazu i vraćaju jednu vrijednost. Funkcije COUNT, MIN i MAX primjenjuju se i na numerička i na nenumerička polja, dok se funkcije SUM i AVG mogu koristiti samo za numerička polja, s izuzetkom COUNT(*) . Prilikom izračunavanja rezultata bilo koje funkcije, prvo se eliminiraju sve nul vrijednosti, a zatim se potrebna operacija primjenjuje samo na preostale određene vrijednosti stupaca. Opcija COUNT(*) je poseban slučaj upotrebe funkcije COUNT, njena svrha je da prebroji sve redove u rezultujućoj tabeli, bez obzira da li sadrži nulte vrednosti, duplikate ili bilo koje druge vrednosti.

Ako trebate ukloniti duple vrijednosti prije korištenja generičke funkcije, morate prethoditi imenu stupca u definiciji funkcije ključnom riječi DISTINCT. On nema značenje za funkcije MIN i MAX, ali njegova upotreba može uticati na rezultate funkcija SUM i AVG, tako da morate razmotriti da li treba da bude prisutan u svakom slučaju. Osim toga, ključna riječ DISTINCT može se specificirati samo jednom u bilo kojem upitu.

Veoma je važno to napomenuti funkcije sažetka može se koristiti samo na listi u klauzuli SELECT i kao dio klauzule HAVING. U svim ostalim slučajevima ovo je neprihvatljivo. Ako lista u SELECT klauzuli sadrži funkcije sažetka, a tekst upita ne sadrži GROUP BY klauzulu, koja omogućava kombiniranje podataka u grupe, tada nijedan od elemenata liste klauzule SELECT ne može uključivati ​​nikakve reference na polja, osim u situaciji kada polja djeluju kao argumenti konačne funkcije.

Primjer 6.4. Odredite prvi abecedni naziv proizvoda.

SELECT Min(Product.Name) AS Min_Name FROM Product Primjer 6.4. Određivanje prvog abecednog naziva proizvoda.

Primjer 6.5. Odredite broj transakcija.

SELECT Count(*) AS Number_of_deals FROM Deal Primjer 6.5. Odredite broj transakcija.

Primjer 6.6. Odredite ukupnu količinu prodate robe.

SELECT Sum(Deal.Quantity) AS Količina_stavke IZ ponude Primjer 6.6. Utvrđivanje ukupne količine prodate robe.

Primjer 6.7. Odredite prosječnu cijenu prodate robe.

ODABERITE Prosj.(Proizvod.Cijenu) KAO Prosj._Cijenu IZ UNUTRAŠNJEG PRIDRUŽENJA PROIZVODA Deal ON Product.ProductCode=Deal.ProductCode; Primjer 6.7. Određivanje prosječne cijene prodate robe.

SELECT Sumu(Proizvod.Cijena*Transakcija.Količina) KAO Trošak IZ INTERNOG PRIDRUŽENJA proizvoda Transakcija NA Product.ProductCode=Transaction.ProductCode Primjer 6.8. Izračunavanje ukupne cijene prodane robe.

GROUP BY klauzula

Upiti često zahtijevaju generiranje međuzbirova, što je obično naznačeno pojavom fraze “za svaki...” u upitu. U tu svrhu u SELECT izraz Koristi se klauzula GROUP BY. Upit koji sadrži GROUP BY naziva se upitom za grupisanje jer grupiše podatke vraćene operacijom SELECT, a zatim kreira jedan sažetak za svaku pojedinačnu grupu. SQL standard zahtijeva da klauzula SELECT i klauzula GROUP BY budu blisko povezane. Kada SELECT izraz sadrži GROUP BY klauzulu, svaki element liste u SELECT klauzuli mora imati jednu vrijednost za cijelu grupu. Štaviše, klauzula SELECT može uključivati ​​samo sljedeće tipove elemenata: imena polja, funkcije sažetka, konstante i izrazi koji uključuju kombinacije gore navedenih elemenata.

Sva imena polja navedena u klauzuli SELECT moraju se pojaviti i u klauzuli GROUP BY - osim ako se naziv stupca ne koristi u konačna funkcija. Obrnuto pravilo nije tačno - GROUP BY klauzula može sadržavati nazive stupaca koji nisu na listi klauzule SELECT.

Ako se klauzula WHERE koristi zajedno sa GROUP BY, ona se prvo obrađuje i grupišu se samo oni redovi koji zadovoljavaju uvjet pretraživanja.

SQL standard navodi da se prilikom grupisanja sve vrijednosti koje nedostaju tretiraju kao jednake. Ako dva reda tablice u istoj koloni za grupisanje sadrže NULL vrijednost i identične vrijednosti u svim drugim kolonama grupiranja koje nisu nule, oni se stavljaju u istu grupu.

Primjer 6.9. Izračunajte prosječan obim kupovina svakog kupca.

SELECT Client.LastName, Avg(Transaction.Quantity) AS Average_Quantity FROM Client INNER JOIN Trade ON Client.ClientCode=Transaction.ClientCode GRUPA PO Client.Prezime Primjer 6.9. Izračunajte prosječan obim kupovina svakog kupca.

Izraz “svaki kupac” se ogleda u SQL upitu u obliku rečenice GROUP BY Client.Prezime.

Primjer 6.10. Odredite za koliko je svaki proizvod prodan.

SELECT Product.Name, Sum(Product.Cijena*Transakcija.Quantity) KAO Trošak IZ PROIZVODA UNUTRAŠNJE PRIDRUŽENJE Deal ON Product.ProductCode=Transaction.ProductCode GRUPA PO Product.Name Primjer 6.10. Određivanje iznosa za koji je svaki proizvod prodan.

SELECT Client.Company, Count(Transaction.TransactionCode) AS Number_of_transactions FROM Client INNER JOIN Transakcija NA Client.ClientCode=Transaction.ClientCode GRUPA PO Client.Company Primjer 6.11. Prebrojavanje broja transakcija koje je izvršila svaka firma.

SELECT Customer.Company, Sum(Transaction.Quantity) AS Total_Quantity, Sum(Product.Cijena*Transaction.Quantity) KAO Trošak IZ INNER JOIN proizvoda (Customer INNER JOIN Transaction ON Customer.ClientCode=Transaction.CustomerCode=Transaction.Customer.ProductCode) .Šifra proizvoda GROUP BY Client.Company Primjer 6.12. Obračun ukupne količine kupljene robe za svaku kompaniju i njen trošak.

Primjer 6.13. Odredite ukupne troškove svakog proizvoda za svaki mjesec.

SELECT Product.Name, Month(Transaction.Date) AS Month, Sum(Product.Cijena*Transaction.Quantity) KAO Trošak IZ INNER PRIDRUŽIVANJA proizvoda Transakcija NA Product.ProductCode=Transaction.ProductCode GRUPA PREMA Nazivu proizvoda, Month(Transaction.Date ) Primjer 6.13. Određivanje ukupne cijene svakog proizvoda za svaki mjesec.

Primjer 6.14. Odredite ukupne troškove svakog prvoklasnog proizvoda za svaki mjesec.

SELECT Product.Name, Month(Transaction.Date) AS Month, Sum(Product.Cijena*Transakcija.Quantity) KAO Trošak IZ PROIZVODA UNUTRAŠNJE PRIDRUŽENJE Transakcija NA Product.ProductCode=Transaction.ProductCode GDJE Product.Grade="First" GRUPA PO proizvodu .Naziv, mjesec(Transaction.Date) Primjer 6.14. Određivanje ukupne cijene svakog prvoklasnog proizvoda za svaki mjesec.

HAVING ponudu

Koristeći HAVING, odražavaju se svi blokovi podataka prethodno grupirani koristeći GROUP BY koji zadovoljavaju uvjete navedene u HAVING. Ovo dodatna prilika"filtrirati" izlazni skup.

Uslovi u HAVING se razlikuju od uslova u WHERE:

• HAVING isključuje grupe sa agregiranim rezultatima vrijednosti iz rezultirajućeg skupa podataka;
• WHERE isključuje zapise koji ne zadovoljavaju uslov iz izračunavanja agregatnih vrijednosti grupisanjem;
• Agregatne funkcije se ne mogu specificirati u WHERE uvjetu pretraživanja.

Primjer 6.15. Identifikujte kompanije čiji je ukupan broj transakcija premašio tri.

SELECT Client.Company, Count(Trade.Quantity) AS Number_of_deals FROM Client INNER JOIN Trade ON Client.ClientCode=Transaction.ClientCode GRUPA PO Client.Kompaniji IMA Count(Transaction.Quantity)>3 Primjer 6.15. Identifikacija firmi čiji je ukupan broj transakcija veći od tri.

Primjer 6.16. Prikažite listu robe prodate za više od 10.000 rubalja.

SELECT Product.Name, Sum(Product.Cijena*Deal.Quantity) KAO TROŠAK IZ PROIZVODA UNUTRAŠNJE PRIDRUŽENJE DEAL NA Product.ProductCode=Transaction.ProductCode GRUPA PO Product.Name IMAJUĆI Zbroj (Proizvod.Cijena*Ponuda.Količina)>10000 Primjer 6.16. Prikaz liste robe prodate za više od 10.000 rubalja.

Primjer 6.17. Prikažite listu proizvoda prodanih za više od 10.000 bez navođenja količine.

IZABERITE Product.Name IZ PROIZVODA UNUTRAŠNJE PRIDRUŽENJE DEAL ON Product.ProductCode=Deal.ProductCode GRUPA PO Product.Name IMA zbir (Proizvod.Cijena*Transakcija.Količina)>10000 Primjer 6.17. Prikažite listu proizvoda prodanih za više od 10.000 bez navođenja količine.

U ovom vodiču ćete naučiti kako koristiti Funkcija SUM u SQL Serveru (Transact-SQL) sa sintaksom i primjerima.

Opis

U SQL Serveru (Transact-SQL) Funkcija SUM vraća ukupnu vrijednost izraza.

Sintaksa

Sintaksa za funkciju SUM u SQL Serveru (Transact-SQL) je:

ILI sintaksa za funkciju SUM kada grupišete rezultate po jednom ili više stupaca je:

Parametri ili argumenti

izraz1 , izraz2 , ... izraz_n su izrazi koji nisu uključeni u funkciju SUM i moraju biti uključeni u GROUP BY klauzulu na kraju SQL izraza.
agregatni_izraz je stupac ili izraz koji će biti agregiran.
tabele - tabele iz kojih želite da dobijete zapise. Mora postojati barem jedna tabela navedena u klauzuli FROM.
GDJE uslovi - opciono. Ovo su uslovi koji moraju biti ispunjeni za odabrane zapise.

Aplikacija

Funkcija SUM može se koristiti u sljedećim verzijama SQL Servera (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Primjer sa jednim poljem

Pogledajmo neke primjere funkcije SUM SQL Servera da bismo razumjeli kako koristiti funkciju SUM u SQL Serveru (Transact-SQL).

Na primjer, možete saznati ukupnu količinu svih proizvoda čija je količina veća od 10.

U ovom primjeru funkcije SUM, izraz SUM(količina) smo postavili kao pseudonim "Ukupna količina". Prilikom vraćanja skupa rezultata - "Ukupna količina" će se pojaviti kao naziv polja.

Primjer korištenja DISTINCT

Možete koristiti DISTINCT operator u funkciji SUM. Na primjer, sljedeći SQL izraz vraća ukupnu plaću sa jedinstvene vrijednosti plata, gde je plata ispod 29.000 dolara godišnje.

Ako su dvije plaće bile 24.000 dolara godišnje, samo jedna od tih vrijednosti bi se koristila u funkciji SUM.

Primjer korištenja formule

Izraz sadržan u funkciji SUM ne mora biti jedno polje. Možete koristiti i formulu. Na primjer, možete izračunati ukupnu proviziju.

Transact-SQL

ODABERITE SUM(prodaja * 0,03) KAO "Ukupna provizija" IZ naloga;

SELECT SUM (prodaja * 0,03 ) KAO "Ukupna provizija" IZ narudžbi;

Primjer korištenja GROUP BY

U nekim slučajevima, morat ćete koristiti operator GROUP BY sa funkcijom SUM.

SQL - Lekcija 11. Ukupne funkcije, izračunati stupci i pogledi

Totalne funkcije se također nazivaju statističkim, agregatnim ili zbirnim funkcijama. Ove funkcije obrađuju skup nizova za brojanje i vraćanje jedne vrijednosti. Postoji samo pet takvih funkcija:

• Funkcija AVG() vraća prosječnu vrijednost stupca.


• COUNT() Funkcija vraća broj redova u koloni.


• MAX() Funkcija vraća najveću vrijednost u stupcu.


• MIN() Funkcija vraća najmanju vrijednost u stupcu.


• SUM() Funkcija vraća zbroj vrijednosti stupca.

Već smo upoznali jednog od njih - COUNT() - u lekciji 8. A sada da upoznamo ostale. Recimo da smo htjeli znati minimalnu, maksimalnu i prosječnu cijenu knjiga u našoj radnji. Zatim iz tabele sa cijenama trebate uzeti minimalne, maksimalne i prosječne vrijednosti ​​za stupac cijena. Zahtjev je jednostavan:

SELECT MIN(cijena), MAX(cijena), AVG(cijena) IZ cijena;

Sada želimo da saznamo koliko nam je robe doneo dobavljač "Kuća štamparije" (id=2). Podnošenje takvog zahtjeva nije tako lako. Hajde da razmislimo o tome kako da ga komponujemo:

1. Prvo iz tabele Supplies (incoming) izaberite identifikatore (id_incoming) onih isporuka koje je izvršio dobavljač "Štamparije" (id=2):

2. Sada iz tabele Dnevnik nabavke (magazine_incoming) potrebno je odabrati robu (id_product) i njene količine (količina), koje su izvršene u isporukama koje se nalaze u tački 1. To jest, upit iz tačke 1 postaje ugniježđen:

3. Sada trebamo dodati u rezultujuću tabelu cijene za pronađene proizvode, koje su pohranjene u tablici Cijene. To jest, morat ćemo se pridružiti tablicama Supply Magazine (magazine_incoming) i Prices koristeći kolonu id_product:

4. Rezultirajućoj tabeli očigledno nedostaje kolona Iznos, tj izračunata kolona. Mogućnost kreiranja takvih kolona je obezbeđena u MySQL-u. Da biste to učinili, samo trebate navesti u upitu naziv izračunate kolone i šta treba izračunati. U našem primjeru, takav stupac će se zvati suma, i izračunat će proizvod kolone količine i cijene. Naziv nove kolone je odvojen riječju AS:

SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price KAO suma FROM magazine_incoming, cijene WHERE magazine_incoming.id_product= prices.id_product AND id_incoming= (SELECT id_incoming FROM incoming WHERE=2d);

5. Odlično, sve što treba da uradimo je da saberemo kolonu suma i konačno saznamo za koliko nam je dobavljač „House of Printing“ doneo robu. Sintaksa za korištenje funkcije SUM() je sljedeća:

SELECT SUM(ime_kolone) IZ ime_tablice;

Znamo ime kolone - suma, ali nemamo ime tabele, jer je rezultat upita. sta da radim? Za takve slučajeve, MySQL ima Views. Pogled je upit za odabir koji ima jedinstveno ime i može se pohraniti u bazu podataka za kasniju upotrebu.

Sintaksa za kreiranje pogleda je sljedeća:

CREATE VIEW view_name AS zahtjev;

Sačuvajmo naš zahtjev kao prikaz pod nazivom report_vendor:

KREIRAJTE PREGLED report_vendor AS SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price KAO suma FROM magazine_incoming, cijene GDJE magazin_incoming.id_product= prices.id_product AND id_incoming= FROM id_incoming_in. );

6. Sada možete koristiti konačnu funkciju SUM():

SELECT SUM(suma) FROM report_vendor;

Tako smo postigli rezultat, iako smo za to morali koristiti ugniježđene upite, spojeve, izračunate kolone i poglede. Da, ponekad morate razmišljati da biste postigli rezultate, bez ovoga ne možete nikuda. Ali dotakli smo se dvije vrlo važne teme - izračunatih kolona i pogleda. Razgovarajmo o njima detaljnije.

Izračunata polja (kolone)

Koristeći primjer, danas smo pogledali matematički izračunato polje. Ovdje bih želio dodati da možete koristiti ne samo operaciju množenja (*), već i oduzimanje (-), sabiranje (+) i dijeljenje (/). Sintaksa je sljedeća:

SELECT column_name 1, column_name 2, column_name 1 * column_name 2 AS izračunato_column_name FROM table_name;

Druga nijansa je ključna riječ AS, koristili smo je za postavljanje imena izračunate kolone. U stvari, ova ključna riječ se koristi za postavljanje aliasa za bilo koju kolonu. Zašto je to potrebno? Za smanjenje koda i čitljivost. Na primjer, naš pogled bi mogao izgledati ovako:

KREIRAJTE VIEW report_vendor AS SELECT A.id_product, A.quantity, B.price, A.quantity*B.price AS suma FROM magazin_incoming AS A, cijene KAO B GDJE A.id_product= B.id_product AND id_incoming= (IZABIR id_incoming IZ dolaznog WHERE id_vendor=2);

Slažete se da je ovo mnogo kraće i jasnije.

Zastupanje

Već smo pogledali sintaksu za kreiranje pogleda. Jednom kada se kreiraju pogledi, mogu se koristiti na isti način kao i tabele. Odnosno, pokrenite upite prema njima, filtrirajte i sortirajte podatke i kombinirajte neke poglede s drugima. S jedne strane, ovo je vrlo zgodan način skladištenja često korištenih složeni upiti(kao u našem primjeru).

Ali zapamtite da pogledi nisu tabele, odnosno ne pohranjuju podatke, već ih samo preuzimaju iz drugih tabela. Dakle, prvo, kada se podaci u tabelama promene, promeniće se i rezultati prezentacije. I drugo, kada se uputi zahtjev za prikaz, traženi podaci se traže, odnosno smanjuje se performansa DBMS-a. Stoga ih ne treba zloupotrebljavati.

Kako mogu saznati broj modela računara proizvedenih od strane određenog dobavljača? Kako odrediti prosječnu cijenu računara koji imaju istu specifikacije? Na ova i mnoga druga pitanja u vezi sa nekim statističkim informacijama može se odgovoriti pomoću konačne (agregatne) funkcije. Standard pruža sljedeće agregatne funkcije:

Sve ove funkcije vraćaju jednu vrijednost. Istovremeno, funkcije COUNT, MIN I MAX primjenjivo na bilo koji tip podataka, dok SUMA I AVG se koriste samo za numerička polja. Razlika između funkcija COUNT(*) I COUNT(<имя поля>) je da drugi ne uzima u obzir NULL vrijednosti prilikom izračunavanja.

Primjer. Pronađite minimalnu i maksimalnu cijenu za personalne računare:

Primjer. Pronađite raspoloživi broj računara proizvođača A:

Primjer. Ako nas zanima broj različitih modela koje proizvodi proizvođač A, onda se upit može formulirati na sljedeći način (koristeći činjenicu da se u tabeli proizvoda svaki model bilježi jednom):

Primjer. Pronađite broj dostupnih različitih modela proizvođača A. Upit je sličan prethodnom, u kojem se tražilo da se odredi ukupan broj modela proizvedenih od strane proizvođača A. Ovdje je potrebno pronaći i broj različitih modela u PC stol (tj. one dostupne za prodaju).

Da bi po prijemu statistički indikatori Kada su korištene samo jedinstvene vrijednosti argument agregatnih funkcija može biti korišteno DISTINCT parametar. Drugi parametar SVE je zadana vrijednost i pretpostavlja da se sve vraćene vrijednosti u koloni broje. operater,

Ako trebamo dobiti broj proizvedenih modela PC-a svima proizvođača, morat ćete koristiti GROUP BY klauzula, sintaktički nakon WHERE klauzule.

GROUP BY klauzula

GROUP BY klauzula koristi se za definiranje grupa izlaznih nizova na koje se može primijeniti agregatne funkcije (COUNT, MIN, MAX, AVG i SUM). Ako ova klauzula nedostaje i koriste se agregatne funkcije, tada se koriste svi stupci s imenima navedenim u SELECT, treba uključiti u agregatne funkcije, a ove funkcije će se primijeniti na cijeli skup redova koji zadovoljavaju predikat upita. Inače, sve kolone SELECT liste nisu uključeni u agregatnim funkcijama moraju biti specificirane u klauzuli GROUP BY. Kao rezultat toga, svi redovi izlaznog upita podijeljeni su u grupe koje karakteriziraju iste kombinacije vrijednosti u ovim stupcima. Nakon toga, agregatne funkcije će se primijeniti na svaku grupu. Imajte na umu da se za GROUP BY sve NULL vrijednosti tretiraju kao jednake, tj. kada se grupiše po polju koje sadrži NULL vrijednosti, svi takvi redovi će pasti u jednu grupu.
Ako ako postoji klauzula GROUP BY, u klauzuli SELECT nema agregatnih funkcija, onda će upit jednostavno vratiti jedan red iz svake grupe. Ova funkcija, zajedno sa ključnom riječi DISTINCT, može se koristiti za eliminaciju duplih redova u skupu rezultata.
Pogledajmo jednostavan primjer:

SELECT model, COUNT(model) AS Qty_model, AVG(cijena) AS prosječna cijena SA PC-a GROUP BY model;

U ovom zahtjevu se za svaki model PC-a utvrđuje njihov broj i prosječna cijena. Svi redovi sa istom vrijednošću modela čine grupu, a izlaz SELECT izračunava broj vrijednosti i prosječne vrijednosti cijene za svaku grupu. Rezultat upita bit će sljedeća tabela:

model	Qty_model	Prosječna_cijena
1121	3	850.0
1232	4	425.0
1233	3	843.33333333333337
1260	1	350.0

Kada bi SELECT imao stupac datuma, tada bi bilo moguće izračunati ove indikatore za svaki određeni datum. Da biste to učinili, morate dodati datum kao kolonu za grupisanje, a zatim će se agregatne funkcije izračunati za svaku kombinaciju vrijednosti (model-datum).

Postoji nekoliko specifičnih pravila za obavljanje agregatnih funkcija:

• Ako kao rezultat zahtjeva nije primljen nijedan red(ili više od jednog reda za datu grupu), tada nema izvornih podataka za izračunavanje bilo koje od agregatnih funkcija. U ovom slučaju, rezultat funkcija COUNT će biti nula, a rezultat svih ostalih funkcija će biti NULL.
• Argument agregatna funkcija ne može sama sadržavati agregatne funkcije(funkcija od funkcije). One. u jednom upitu nemoguće je, recimo, dobiti maksimum prosječnih vrijednosti.
• Rezultat izvršavanja funkcije COUNT je cijeli broj(INTEGER). Druge agregatne funkcije nasljeđuju tipove podataka vrijednosti koje obrađuju.
• Ako funkcija SUM proizvede rezultat koji je veći od maksimalne vrijednosti tipa podataka koji se koristi, greška.

Dakle, ako zahtjev ne sadrži GROUP BY klauzule, To agregatne funkcije uključeno u SELECT klauzula, se izvršavaju na svim rezultujućim redovima upita. Ako zahtjev sadrži GROUP BY klauzula, svaki skup redova koji ima iste vrijednosti stupca ili grupe kolona navedenih u GROUP BY klauzula, čini grupu i agregatne funkcije izvode se za svaku grupu posebno.

HAVING ponudu

Ako WHERE klauzula onda definira predikat za filtriranje redova HAVING ponudu primjenjuje nakon grupisanja definirati sličan predikat koji filtrira grupe prema vrijednostima agregatne funkcije. Ova klauzula je potrebna za validaciju vrijednosti koje su dobivene korištenjem agregatna funkcija ne iz pojedinačnih redova izvora zapisa definiranog u FROM klauzula, i od grupe takvih linija. Stoga takva provjera ne može biti sadržana u WHERE klauzula.