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 za dobivanje rezultujuće sume počinju ovako:

ODABIR ZBIR (COLUMN_NAME) ...

Nakon ovog izraza slijedi FROM (TABLE_NAME), a zatim se uvjet može specificirati pomoću 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).

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):

Da biste dobili zbir svih plata, koristite sljedeći upit:

ODABERITE ZUM (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 saznati minimalnu plaću za zaposlene u odjeljenju broj 42. Da biste to učinili, napišite sljedeći zahtjev:

Upit će vratiti vrijednost 10505.90.

I opet vježba za samostalno rješavanje. U ovoj i nekim drugim vježbama trebat će vam 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.

Morate saznati maksimalnu platu zaposlenih u odjeljenju broj 42. Da biste to učinili, napišite sljedeći zahtjev:

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 odjelu 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.

Pretpostavimo da želite saznati prosječan radni staž zaposlenih u odjeljenju broj 42. Da biste to učinili, napišite sljedeći upit:

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 pokrenete upit SELECT COUNT(*) ..., rezultat će biti broj svih zapisa (redova) u tablici.

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 stupca nisu NULL će biti vraćen sljedećim upitom:

SELECT COUNT (Comm) IZ osoblja

Rezultat će biti 11.

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

Ako želite saznati ukupan broj zapisa u tablici, upotrijebite upit sa zvjezdicom kao argument funkciji COUNT:

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 .

Primjer 12. 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 naredbu, pronađite iznos novca zarađen postavljanjem oglasa u svakoj kategoriji. Pišemo sljedeći zahtjev:

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 zahtjev:

ODABERITE kategoriju, dio, MAX (jedinice) KAO maksimum IZ oglasa GRUPA 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 i SQL jezik

Kako mogu saznati broj modela računara proizvedenih od strane određenog dobavljača? Kako odrediti prosječnu cijenu računara sa istim tehničkim karakteristikama? 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 tablici 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).

Kako bi se osiguralo da se pri dobijanju statističkih pokazatelja koriste samo jedinstvene vrijednosti, kada 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 ako se koriste 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 vrednošću modela čine grupu, a izlaz SELECT izračunava broj vrednosti i prosečne vrednosti cene 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, trebate 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 korištenog tipa podataka, greška.

Dakle, ako zahtjev ne sadrži GROUP BY klauzule, To agregatne funkcije uključeno u SELECT klauzula, izvršavaju se 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.

Koraci u ovoj fazi učenja SQL upita su dizajnirani da pokažu činjenicu da SQL ne samo da može napraviti složene selekcije i sortirati podatke, već i izračunati rezultate matematičkih funkcija, izvršiti transformaciju teksta, grupne zapise itd. Tačnije, ne SQL može sve to, već oni koji to podržavaju. SQL, sa svojim standardima, samo formulira zahtjeve za te iste DBMS-ove.

Korak 15. Funkcije SUM, AVG, MIN, MAX, COUNT…

Ovaj korak će vam pokazati kako koristiti jednostavne funkcije u SQL-u, kao što su zbroj, minimalne i maksimalne vrijednosti, prosjek itd. Počnimo odmah sa primjerom izvođenja prosječnog radnog staža za sve zaposlene.

ODABERITE AVG(D_STAFF.S_EXPERIENCE) KAO [PROSJEČNO ISKUSTVO ZAPOSLENIH] IZ D_STAFF

SQL funkcija AVG.

Slično, možete izračunati minimalnu i maksimalnu vrijednost (MIN, MAX), ukupan zbroj (SUM) itd. Savjetujem vam da ovo isprobate koristeći program obuke. Vrijedi pokušati definirati dodatne kriterije za odabir zapisa uključenih u određivanje konačne vrijednosti funkcije pomoću klauzule WHERE.

Gore navedene funkcije koriste cijeli rezultat upita kako bi odredile svoju vrijednost. Takve funkcije se nazivaju agregat . Također, postoji niz funkcija čiji argumenti nisu sve vrijednosti stupca definirane u zahtjevu, već svaka pojedinačna vrijednost svakog pojedinačnog reda rezultata. Primjer takve funkcije je SQL funkcija za izračunavanje dužine tekstualnog polja LEN:

ODABIR S_NAME, LEN(D_STAFF.S_NAME) KAO [DUŽINA] OD D_STAFF-a

Može biti korišteno superpozicija SQL funkcija kao što je prikazano ispod i izračunajte maksimalnu vrijednost dužine polja S_NAME.

ODABIR MAX(LEN(D_STAFF.S_NAME)) KAO [MAXIMALNA DUŽINA] OD D_STAFF-a

SQL funkcija MAX.

Pa, u zaključku, sve zajedno.

SELECT SUM(D_STAFF.S_EXPERIENCE) KAO [SUM], AVG(D_STAFF.S_EXPERIENCE) KAO [PROSEK], MIN(D_STAFF.S_EXPERIENCE) KAO [MINIMAL], MAX(D_STAFF.S_EXPERIENCE) KAO [MAXIMUM], COUNT [BROJ SNIMAKA], MAX(LEN(D_STAFF.S_NAME)) KAO [MAXIMALNA DUŽINA] OD D_STAFF-a

Primjer korištenja agregatnih SQL funkcija.

Obratite pažnju na argument funkcije COUNT. Naveo sam (*) kao argument jer želim da dobijem ukupan broj zapisa. Ako navedete, na primjer, COUNT(S_NAME), rezultat će biti broj nepraznih vrijednosti S_NAME (S_NAME NIJE NULL). Bilo bi moguće napisati COUNT(DISTINCT S_NAME) i dobiti broj jedinstvenih vrijednosti S_NAME, ali MS Access, nažalost, ne podržava ovu opciju. U našem primjeru, COUNT(S_NAME) i COUNT(*) daju potpuno isti rezultat.

Korak 16: Pretvaranje teksta

Korisnici softvera često unose tekstualne vrijednosti drugačije: ko piše puno ime. sa velikim slovom, ko nije; Neki ljudi pišu sve velikim slovima. Mnogi obrasci izvještavanja zahtijevaju jedinstven pristup, a ne samo formulari za prijavu. Da bi riješio ovaj problem, SQL ima dvije funkcije UCASE i LCASE. Primjer zahtjeva i rezultat njegove obrade prikazani su u nastavku:

SELECT UCASE(D_STAFF.S_NAME) KAO, LCASE(D_STAFF.S_NAME) KAO IZ D_STAFF

SQL funkcije UCASE i LCASE.

Korak 17. SQL i rad sa stringovima

Tu je i divna MID funkcija koja će vam pomoći da riješite problem izdvajanja dijela stringa iz cijele vrijednosti tekstualnog polja. I ovdje bi najbolji komentar bio primjer – primjer “maltretiranja” imena korisničkih profila.

SELECT UCASE(MID(P_NAME,3,5)) IZ D_PROFILA

Superpozicija SQL funkcija UCASE i MID.

“Izrezali” smo po 5 karaktera iz vrijednosti imena profila, počevši od 3. i završili sa gomilom ponavljajućeg “smeća”. Da bismo ostavili samo jedinstvene vrijednosti, koristit ćemo ključnu riječ DISTINCT.

ODABIR DISTINCT UCASE(MID(P_NAME,3,5)) KAO IZ D_PROFILA

Odabir jedinstvenih vrijednosti agregatne funkcije.

Ponekad morate koristiti izraze s funkcijom LEN kao argumente funkciji MID. U sljedećem primjeru već prikazujemo zadnjih 5 znakova u nazivima profila.

ODABIR UCASE(MID(P_NAME,LEN(P_NAME)-4,5)) IZ D_PROFILA

Korištenje SQL funkcije LEN.

Korak 18. Korištenje SQL funkcija u kriterijima odabira zapisa. HAVING operator

Nakon razumijevanja funkcija, gotovo odmah se postavlja pitanje: kako se one mogu koristiti u kriterijima za odabir zapisa? Neke funkcije, naime one koje nisu agregatne, prilično su jednostavne za korištenje. Evo, na primjer, spiska zaposlenih čije puno ime. više od 25 karaktera.

SELECT S_NAME FROM D_STAFF WHERE LEN(D_STAFF.S_NAME) > 25

Korištenje neagregatne LEN funkcije u uvjetima SQL upita.

Pa, ako, na primjer, trebate prikazati identifikatore svih pozicija koje zauzima više od jednog zaposlenika u kompaniji, onda ovaj pristup neće funkcionirati. Ono što mislim je da sljedeći upit možda nema smisla, ali je netačan sa stanovišta strukturiranog upita. To je zbog činjenice da za ispravnu obradu takvih SQL upita koristeći agregatne funkcije, jedan linearni prolaz kroz zapise zaposlenika neće biti dovoljan.

SELECT S_POSITION FROM D_STAFF WHERE COUNT(S_POSITION)>1

Za takve slučajeve u SQL je uvedena ključna riječ HAVING koja će nam pomoći da riješimo problem sa pozicijama i zaposlenima.

ODABIR S_POZICIJA IZ GRUPE D_OSOBLJA PO S_POZICIJI KOJI IMA COUNT(S_POSITION)>1

Korištenje agregatnih funkcija u uvjetima SQL upita.

Korak 19. Grupiranje podataka u rezultatima SQL upita korištenjem operatora GROUP BY

Operator GROUP BY je potreban za grupisanje vrijednosti agregatnih funkcija prema vrijednostima njihovih povezanih polja. Potreban je kada želimo da koristimo vrijednost agregatne funkcije u kriterijima odabira zapisa (prethodni korak). Također je potrebno kada želimo uključiti vrijednost agregatne funkcije u rezultat upita. Ali u svom najjednostavnijem obliku, grupisanje je ekvivalentno naglašavanju jedinstvenih vrijednosti stupca. Pogledajmo primjer zahtjeva.

SELECT S_POSITION FROM D_STAFF

A ovo su dvije opcije koje vam omogućavaju da prikažete samo jedinstvene S_POSITION vrijednosti.

SELECT S_POSITION IZ D_STAFF GRUPE ZA S_POSITION

ODABIR DISTINCT S_POSITION OD D_STAFF

Pa, vratimo se sada grupiranju vrijednosti funkcija prema vrijednostima polja povezanih s njima. Za svaki korisnički profil prikazat ćemo broj zapisa povezanih s njim u tabeli D_STAFF_PROFILE.

SELECT PROFILE_ID AS , COUNT(PROFILE_ID) KAO [BROJ ZAPISA] IZ D_STAFF_PROFILE GRUPE PO PROFILE_ID

Korištenje SQL agregatne funkcije zajedno s grupiranjem.

Operator GROUP BY takođe vam omogućava da grupišete rezultat upita po više od jednog polja, navodeći ih odvojene zarezima. Nadam se da nakon svega navedenog nisu potrebni dodatni komentari na rezultat posljednjeg upita.

SELECT S.S_POSITION AS , S.S_NAME KAO [ZAPOSLENI], COUNT(SP.STAFF_ID) KAO [BROJ ZAPISA U TABELI D_STAFF_PROFILE] OD D_STAFF S, D_STAFF_PROFILE SP GDJE S.XD_IID=SP.STAFF_ID S.POSITION S.POSITION S. S_NAME

Grupiranje redova rezultata SQL upita po nekoliko polja.