GROUP BY klauzula(Izraz SELECT) vam omogućava grupiranje podataka (redova) prema vrijednosti stupca ili više stupaca ili izraza. Rezultat će biti skup sažetih redova.
Svaki stupac na listi za odabir mora biti prisutan u klauzuli GROUP BY, jedini izuzeci su konstante i stupci - operandi agregatnih funkcija.
Tabela se može grupirati prema bilo kojoj kombinaciji svojih kolona.
Agregatne funkcije se koriste za dobivanje jedne ukupne vrijednosti iz grupe redova. Sve agregatne funkcije izvode izračune na jednom argumentu, koji može biti ili stupac ili izraz. Svaka agregatna funkcija procjenjuje se na konstantnu vrijednost koja je prikazana u zasebnom stupcu u rezultatu.
Agregatne funkcije su specificirane u listi stupaca izraza SELECT, koji također može sadržavati klauzulu GROUP BY. Ako izraz SELECT ne sadrži klauzulu GROUP BY, a lista stupaca za odabir sadrži barem jednu agregatnu funkciju, tada ne smije sadržavati jednostavne stupce. S druge strane, lista za odabir stupaca može sadržavati nazive stupaca koji nisu argumenti agregatnoj funkciji ako se ti stupci koriste kao argumenti klauzule GROUP BY.
Ako upit sadrži klauzulu WHERE, agregatne funkcije izračunavaju vrijednost za rezultate odabira.
Agregatne funkcije MIN i MAX izračunavaju se najmanja i najveća vrijednost stupca, respektivno. Argumenti mogu biti brojevi, nizovi i datumi. Sve NULL vrijednosti se uklanjaju prije izračunavanja (tj. ne uzimaju se u obzir).
Zbirna funkcija SUM izračunava ukupni zbir vrijednosti stupca. Samo brojevi mogu biti argumenti. Korištenje parametra DISTINCT uklanja sve duple vrijednosti u stupcu prije primjene funkcije SUM. Isto tako, uklonite sve NULL vrijednosti prije primjene ove agregatne funkcije.
Agregatna funkcija AVG vraća prosjek svih vrijednosti u koloni. Argumenti također mogu biti samo brojevi, a sve NULL vrijednosti se uklanjaju prije evaluacije.
Agregatna funkcija COUNT ima dva različita oblika:
- COUNT (col_name) - broji broj vrijednosti u koloni Col_name, NULL vrijednosti se ne broje
- COUNT (*) - broji broj redova u tabeli, uzimaju se u obzir i NULL vrijednosti
Ako upit koristi ključnu riječ DISTINCT, sve duplicirane vrijednosti stupaca se uklanjaju prije nego što se koristi funkcija COUNT.
COUNT_BIG funkcija ista funkcija kao COUNT. Jedina razlika između njih je u tipu rezultata koji vraćaju: COUNT_BIG uvijek vraća BIGINT vrijednosti, dok COUNT vraća INTEGER vrijednosti podataka.
V HAVING ponudu definira uvjet koji se primjenjuje na grupu redova. Ima isto značenje za grupe redova kao klauzula WHERE za sadržaj odgovarajuće tabele (WHERE se primenjuje pre grupisanja, HAVING posle).
Lekcija će pokriti temu sql preimenovanja kolone (polja) koristeći AS funkciju riječi; također je pokrio temu agregatnih funkcija u sql. Analizirat će se konkretni primjeri zahtjeva
Možete preimenovati imena kolona u upitima. Ovo čini rezultate čitljivijim.
U SQL-u, preimenovanje polja je povezano s upotrebom ključna riječ AS koji se koristi za preimenovanje imena polja u skupovima rezultata
sintaksa:
SELECT<имя поля>AS<псевдоним>OD ...
Pogledajmo primjer preimenovanja u SQL-u:
Primjer DB "Institut": Prikažite imena nastavnika i njihove plate, za one nastavnike čije su plate ispod 15000 preimenujte polje zarplata u "niska_plata"
✍ Rješenje:
Preimenovanje stupaca u SQL-u je često potrebno prilikom izračunavanja vrijednosti povezanih s više polja stolovi. Razmotrimo primjer:
Primjer DB "Institut": Iz tabele nastavnika ispišite ime polja i izračunajte iznos plate i bonusa, imenujući polje "Plata_ nagrada"
✍ Rješenje:
| 1 2 | SELECT ime, (zarplata + premia) AS zarplata_premia OD nastavnika; |
SELECT ime, (zarplata + premia) AS zarplata_premia OD nastavnika;
rezultat:
Agregatne funkcije u SQL-u
Agregatne funkcije u sql-u se koriste za dobivanje zbroja i izračunavanje izraza:
Sve agregatne funkcije vraćaju jednu vrijednost.
Funkcije COUNT, MIN i MAX primjenjuju se na bilo koji tip podataka.
Funkcije SUM i AVG koriste se samo za numerička polja.
Postoji razlika između funkcija COUNT (*) i COUNT (): druga ne uzima u obzir NULL vrijednosti prilikom brojanja.
Bitan: kada se radi sa agregatnim funkcijama u SQL-u, koristi se posebna riječ AS
Primjer DB "Institut": Dobijte vrijednost najveće plate među nastavnicima, prikažite rezultat kao "Max_zp"
✍ Rješenje:
| SELECT MAX (zarplata) AS max_zp FROM nastavnika; |
SELECT MAX (zarplata) AS max_zp FROM nastavnika;
Rezultati:
Razmotrimo složeniji primjer korištenja agregatnih funkcija u sql-u.
✍ Rješenje:
GROUP BY klauzula u SQL-u
Grupa po klauzuli u sql-u se obično koristi u sprezi sa agregatnim funkcijama.
Agregatne funkcije se izvode na svim rezultujućim redovima upita. Ako upit sadrži GROUP BY klauzulu, svaki skup redova specificiranih u GROUP BY klauzuli čini grupu i agregatne funkcije se izvršavaju za svaku grupu posebno.
Razmotrite primjer s tabelom lekcija: 
primjer:
Bitan: Dakle, kao rezultat korištenja GROUP BY, svi izlazni redovi upita su podijeljeni u grupe koje karakteriziraju iste kombinacije vrijednosti u ovim stupcima (tj. agregatne funkcije se izvode za svaku grupu posebno).
Treba imati na umu da kada se grupišu po polju koje sadrži NULL vrijednosti, svi takvi zapisi će pasti u jednu grupu.
Za različite tipove štampača odredite njihovu prosječnu cijenu i količinu (tj. zasebno za laserske, inkjet i matrične). Koristite agregatne funkcije. Rezultat bi trebao izgledati ovako:
Imati SQL izjavu
Klauzula HAVING u SQL-u je potrebna za provjeru vrijednosti, koji se dobijaju pomoću agregatne funkcije nakon grupisanja(nakon upotrebe GROUP BY). Takav ček ne može biti sadržan u klauzuli WHERE.
primjer: DB prodavnica računara... Izračunajte prosječnu cijenu računara sa istom brzinom procesora. Računajte samo za one grupe čija je prosječna cijena manja od 30.000.
- Agregatne funkcije se koriste kao imena polja u SELECT izrazu, s jednim izuzetkom: uzimaju ime polja kao argument. Sa funkcijama SUMA i AVG mogu se koristiti samo numerička polja. Sa funkcijama COUNT, MAX i MIN mogu se koristiti i numerička i karakterna polja. Kada se koristi sa znakovnim poljima MAX i MINće ih prevesti u ASCII ekvivalent i obraditi ih po abecednom redu. Neki DBMS-ovi dozvoljavaju upotrebu ugniježđenih agregata, ali to je odstupanje od ANSI standarda sa svim posljedicama koje iz toga proizlaze.
Na primjer, možete izračunati broj studenata koji su položili ispite iz svake discipline. Da biste to učinili, potrebno je izvršiti upit grupiran po polju "Disciplina" i kao rezultat prikazati naziv discipline i broj redova u grupi za ovu disciplinu. Upotreba znaka * kao argumenta funkciji COUNT znači da se broje svi redovi u grupi.
SELECT R1. Disciplina, COUNT (*)
GRUPA PO R1 Disciplina;
rezultat:
SELECT R1 Discipline, COUNT (*)
GDJE R1. NIJE NULL rezultat
GRUPA PO R1 Disciplina;
rezultat:
neće biti uključeni u skup torki prije grupisanja, pa će broj torki u grupi za disciplinu "Teorija informacija" biti 1 manji.
Sličan rezultat možete dobiti ako napišete zahtjev na sljedeći način:
SELECT R1. Disciplina, COUNT (R1. Ocjena)
GRUPA PO R1. Disciplina;
Funkcija COUNT (NAZIV ATRIBUTA) broji broj definiranih vrijednosti u grupi, za razliku od funkcije COUNT (*), koji broji broj linija u grupi. Doista, u grupi sa disciplinom "Teorija informacija" bit će 4 reda, ali samo 3 specifične vrijednosti atributa "Procjena".
Pravila za rukovanje nultim vrijednostima u agregatnim funkcijama
Ako su bilo koje vrijednosti u koloni jednake NULL oni su isključeni kada se izračuna rezultat funkcije.
Ako su sve vrijednosti u koloni jednake NULL, onda Maks. Min. zbroj pros = NULL, count = 0 (nula).
Ako je tabela prazna, broj (*) = 0 .
Agregatne funkcije se mogu koristiti i bez preliminarne operacije grupisanja, u ovom slučaju se cijela relacija smatra jednom grupom i za ovu grupu se može izračunati jedna vrijednost po grupi.
Pravila za tumačenje agregatnih funkcija
Agregatne funkcije mogu biti uključene u izlaznu listu i zatim se primjenjuju na cijelu tablicu.
SELECT MAX (Score) od R1 daće maksimalnu ocjenu na sjednici;
SELECT SUM od R1 dati će zbir svih procjena po sesiji;
SELECT AVG (Score) od R1će dati prosječnu ocjenu za cijelu sesiju.
2; Rezultat: "width =" 640 " Ponovo se osvrnuvši na bazu podataka Session (tabele R1), naći ćemo broj uspješno položenih ispita:
SELECT COUNT (*) As Iznajmljeno _ ispiti
WHERE razred 2;
rezultat:
Argument agregatnih funkcija mogu biti pojedinačni stupci tabela. Da bi se izračunao, na primjer, broj različitih vrijednosti određene kolone u grupi, potrebno je koristiti ključnu riječ DISTINCT zajedno s nazivom kolone. Izračunajmo broj različitih ocjena dobijenih za svaku disciplinu:
SELECT R1 Discipline, COUNT (DISTINCT R1 Evaluation)
GDJE R1. NIJE NULL rezultat
GRUPA PO R1 Disciplina;
rezultat:
Isti rezultat se dobija ako izuzmete eksplicitni uslov u dijelu WHERE, u kom slučaju će upit izgledati ovako:
SELECT R1. Disciplina, COUNT (DISTINCT R1. Ocjena)
GRUPA PO R1. Disciplina;
Funkcija COUNT (DISTINCT R1.Evaluacija) smatra samo izvesnim razne vrijednosti.
Da bi se u ovom slučaju dobio željeni rezultat, potrebno je izvršiti preliminarnu transformaciju tipa podataka kolone "Score", dovodeći ga do realnog tipa, tada rezultat izračunavanja prosjeka neće biti cijeli broj. U ovom slučaju, zahtjev će izgledati ovako:
2 Grupirajte po R2. Grupa, R1. Disciplina; Ovdje funkcija CAST () pretvara kolonu "Score" u važeći tip podataka. "width =" 640 " Odaberite R2.Grupa, R1.Disciplina, Count (*) kao Total, AVG (cast (Rezultat kao decimalni (3,1))) kao Average_point
Od R1, R2
gdje je R1. Puno ime = R2. Ime i R1. rezultat nije nul
i R1. Razred 2
Grupirajte po R2. Grupa, R1. Disciplina;
Evo funkcije ULOGE () pretvara kolonu Score u važeći tip podataka.
Ne možete koristiti agregatne funkcije u klauzuli WHERE jer se uvjeti u ovom odjeljku procjenjuju u smislu jednog reda, a agregatne funkcije se procjenjuju u smislu grupa redova.
Klauzula GROUP BY vam omogućava da definirate podskup vrijednosti u određenom polju u smislu drugog polja i primijenite agregatnu funkciju na podskup. Ovo omogućava kombiniranje polja i agregatnih funkcija u jednoj SELECT klauzuli. Agregatne funkcije se mogu koristiti i u izrazu za izlaz rezultata SELECT reda, iu izrazu za obradu generiranih HAVING grupa. U ovom slučaju, svaka agregatna funkcija se izračunava za svaku odabranu grupu. Vrijednosti dobivene pri izračunavanju agregatnih funkcija mogu se koristiti za prikaz odgovarajućih rezultata ili za uvjet odabira grupa.
Napravimo upit koji prikazuje grupe u kojima je na ispitima dobijeno više od jedne dvojke u jednoj disciplini:
jedan; Rezultat: "width =" 640 " SELECT R2. Grupa
OD R1, R2
GDJE R1. Puno ime = R2. Ime AND
R1 rezultat = 2
GRUPA PO R2 Grupa, R1 Disciplina
IMATI broj (*) 1;
rezultat:
Imamo DB "Banka", koja se sastoji od jedne tabele F, u kojoj se čuva relacija F, koja sadrži podatke o računima u filijalama određene banke:
Pronađite ukupno stanje računa u filijalama. Moguće je napraviti poseban upit za svaku od njih odabirom SUM iz tabele za svaku granu, ali operacija GROUP BY će ih sve staviti u jednu naredbu:
SELECT Filijala , SUMA ( Ostatak )
GROUP BY Ogranak;
GROUP BY primjenjuje agregatne funkcije nezavisno za svaku grupu identificiranu vrijednošću polja Grana. Grupa se sastoji od linija sa istom vrijednošću polja Grana i funkcije SUMA primjenjuje se posebno za svaku takvu grupu, odnosno ukupno stanje računa se obračunava posebno za svaku filijalu. Vrijednost polja na koje se odnosi GROUP BY, ima, po definiciji, samo jednu vrijednost po izlaznoj grupi, kao i rezultat agregatne funkcije.
5.000; Argumenti u klauzuli HAVING slijede ista pravila kao u klauzuli SELECT, koja koristi GROUP BY. Moraju imati jednu vrijednost po izlaznoj grupi. "width =" 640 " Pretpostavimo da odaberete samo one filijale, čija ukupna vrijednost stanja računa prelazi 5.000 $, kao i ukupna stanja za odabrane filijale. Za prikaz filijala sa ukupnim stanjem preko 5.000 USD, morate koristiti klauzulu HAVING. Klauzula HAVING definira kriterije koji se koriste za uklanjanje određenih grupa iz izlaza, baš kao što klauzula WHERE radi za pojedinačne redove.
Ispravna naredba bi bila sljedeća:
SELECT Ogranak, SUM (Stanje)
GROUP BY Filijala
IMATI SUMU ( Ostatak ) 5 000;
Argumenti u rečenici HAVING pridržavati se istih pravila kao u rečenici SELECT gdje se koristi GROUP BY... Moraju imati jednu vrijednost po izlaznoj grupi.
Sljedeća naredba će biti zabranjena:
SELECT Ogranak, SUM (Stanje)
GRUPA PO Ogranku
OTVORENI Datum = 27.12.2004 ;
Polje Datum otvaranja ne može se koristiti u rečenici HAVING jer može imati više od jedne vrijednosti po izlaznoj grupi. Da bi se izbjegla takva situacija, prijedlog HAVING treba da se odnosi samo na agregate i odabrana polja GROUP BY... Postoji ispravan način da se postavi gornji upit:
SELECT Ogranak, SUM (Stanje)
GDJE Datum otvaranja = '27 / 12/2004 '
GROUP BY Ogranak;
Značenje ovog upita je sljedeće: pronađite iznos stanja za svaku filijalu računa otvorenih 27. decembra 2004. godine.
Kao što je ranije rečeno, HAVING može koristiti samo argumente koji imaju istu vrijednost po izlaznoj grupi. U praksi, reference na agregatne funkcije su najopćenitije, ali polja odabrana pomoću GROUP BY također su važeća. Na primjer, želimo vidjeti ukupna stanja računa filijala u Sankt Peterburgu, Pskovu i Uryupinsku:
SELECT Ogranak, SUM (Stanje)
OD F, Q
GDJE F. Ogranak = Q. Ogranak
GRUPA PO Ogranku
HAVING Ogranak U („Sankt Peterburg“, „Pskov“, „Urjupinsk“);
100.000; Ako je ukupni saldo veći od 100.000 $, tada ćemo ga vidjeti u rezultirajućem omjeru, inače ćemo dobiti prazan omjer. "width =" 640 "
Stoga, u aritmetičkim izrazima predikata uključenih u uvjet odabira klauzule HAVING, možete direktno koristiti samo specifikacije stupaca specificiranih kao grupiranje stupaca u klauzuli GROUP BY. Ostatak stupaca se može specificirati samo unutar specifikacija agregatnih funkcija COUNT, SUM, AVG, MIN i MAX, koje u ovom slučaju izračunavaju neku agregatnu vrijednost za cijelu grupu redova. Rezultat izvršavanja klauzule HAVING je grupisana tabela koja sadrži samo one grupe redova za koje je rezultat procene uslova izbora u delu HAVING TRUE. Konkretno, ako je klauzula HAVING prisutna u upitu koji ne sadrži GROUP BY, tada će rezultat njegovog izvršenja biti ili prazna tabela, ili rezultat prethodnih sekcija izraza tabele, koji se smatra jednom grupom bez grupisanje kolona. Pogledajmo primjer. Recimo da želimo prikazati ukupan iznos stanja za sve filijale, ali samo ako je veći od 100 000 $. U ovom slučaju naš upit neće sadržavati operacije grupisanja, već će sadržavati odjeljak HAVING i izgledat će ovako:
ODABIR SUM ( Ostatak )
IMATI SUMU ( Ostatak ) 100 000;
Ako je ukupni saldo veći od 100.000 $, tada ćemo ga vidjeti u rezultirajućem omjeru, inače ćemo dobiti prazan omjer.
Sljedeći pododjeljci opisuju druge SELECT klauzule koje se mogu koristiti u upitima, kao i agregatne funkcije i skupove izraza. Da vas podsjetim da smo do sada pokrili upotrebu klauzule WHERE, au ovom članku ćemo pogledati klauzule GROUP BY, ORDER BY i HAVING i dati neke primjere korištenja ovih klauzula u kombinaciji sa agregatnim funkcijama koji su podržani u Transact-SQL-u.
GROUP BY klauzula
Rečenica GROUP BY grupiše odabrani skup redova da dobije skup sažetih redova na osnovu vrijednosti jedne ili više kolona ili izraza. Jednostavan slučaj upotrebe klauzule GROUP BY prikazan je u primjeru ispod:
USE SampleDb; SELECT Job FROM Works_On GROUP BY Job;
U ovom primjeru, pozicije zaposlenika su uzorkovane i grupisane.
U gornjem primjeru, klauzula GROUP BY stvara zasebnu grupu za sve moguće vrijednosti (uključujući NULL) za stupac Zadatak.
Upotreba stupaca u klauzuli GROUP BY mora ispunjavati određene uvjete. Konkretno, svaka kolona u odabranoj listi upita također se mora pojaviti u klauzuli GROUP BY. Ovaj zahtjev se ne odnosi na konstante i stupce koji su dio agregatne funkcije. (O agregatnim funkcijama se govori u sljedećem pododjeljku.) Ovo ima smisla jer samo kolone u klauzuli GROUP BY imaju zagarantovanu jednu vrijednost po grupi.
Tabela se može grupirati prema bilo kojoj kombinaciji svojih kolona. Primjer ispod pokazuje grupiranje redova u tabeli Works_on po dvije kolone:
USE SampleDb; SELECT ProjectNumber, Job FROM Works_On GROUP BY ProjectNumber, Job;
Rezultat ovog upita:
Na osnovu rezultata upita možete vidjeti da postoji devet grupa s različitim kombinacijama broja projekta i naziva. Redoslijed naziva stupaca u klauzuli GROUP BY ne mora biti isti kao u SELECT listi stupaca.
Agregatne funkcije
Agregatne funkcije se koriste za dobivanje vrijednosti sume. Sve agregatne funkcije mogu se podijeliti u sljedeće kategorije:
obične agregatne funkcije;
statističke agregatne funkcije;
korisnički definirane agregatne funkcije;
analitičke agregatne funkcije.
Ovdje ćemo pogledati prve tri vrste agregatnih funkcija.
Redovne agregatne funkcije
Transact-SQL podržava sljedećih šest agregatnih funkcija: MIN, MAX, SUMA, AVG, COUNT, COUNT_BIG.
Sve agregatne funkcije izvode izračune na jednom argumentu, koji može biti ili stupac ili izraz. (Jedini izuzetak je drugi oblik od dvije funkcije, COUNT i COUNT_BIG, odnosno COUNT (*) i COUNT_BIG (*).) Svaka agregatna funkcija procjenjuje se na konstantnu vrijednost koja se pojavljuje u zasebnoj koloni rezultata.
Agregatne funkcije su specificirane u listi stupaca izraza SELECT, koji također može sadržavati klauzulu GROUP BY. Ako izraz SELECT ne uključuje klauzulu GROUP BY, a lista stupaca za odabir sadrži barem jednu agregatnu funkciju, tada ne smije sadržavati jednostavne stupce (osim stupaca koji se koriste kao argumenti agregatnoj funkciji). Stoga je kod u primjeru ispod netačan:
USE SampleDb; SELECT Prezime, MIN (Id) FROM Employee;
Ovdje kolona Prezime u tablici Employee ne bi trebala biti na popisu stupaca odabira jer nije argument agregatnoj funkciji. S druge strane, lista za odabir stupaca može sadržavati nazive stupaca koji nisu argumenti agregatnoj funkciji ako se ti stupci koriste kao argumenti klauzule GROUP BY.
Argumentu agregatne funkcije može prethoditi jedna od dvije moguće ključne riječi:
SVEOznačava da se proračuni izvode za sve vrijednosti u koloni. Ovo je zadana postavka.
DISTINCTOdređuje da se za proračune koriste samo jedinstvene vrijednosti stupaca.
Agregatne funkcije MIN i MAX
Funkcije agregata MIN i MAX izračunavaju najmanju i najveću vrijednost u stupcu, respektivno. Ako upit sadrži klauzulu WHERE, funkcije MIN i MAX vraćaju najmanju i najveću vrijednost reda koje odgovaraju navedenim uvjetima. Primjer u nastavku pokazuje upotrebu agregatne funkcije MIN:
USE SampleDb; - Vraća 2581 SELECT MIN (Id) AS "Minimalna vrijednost ID-a" IZ Employee;
Rezultat vraćen u gornjem primjeru nije baš informativan. Na primjer, prezime zaposlenika koji posjeduje ovaj broj nije poznato. Ali ovo prezime ne možete dobiti na uobičajen način, jer, kao što je ranije spomenuto, ne možete eksplicitno navesti kolonu Prezime. Da bi se dobilo prezime ovog radnika zajedno sa najmanjim kadrovskim brojem zaposlenog, koristi se potupit. Primjer ispod pokazuje upotrebu takvog potupita, gdje potupit sadrži naredbu SELECT iz prethodnog primjera:
Rezultat izvršavanja upita:
Upotreba MAX agregatne funkcije prikazana je u primjeru ispod:
Funkcije MIN i MAX također mogu prihvatiti nizove i datume kao argumente. U slučaju argumenta string, vrijednosti se uspoređuju koristeći stvarni redoslijed sortiranja. Za sve argumente privremenih podataka tipa datuma, najniža vrijednost stupca je najraniji datum, a najviša vrijednost je najnoviji.
Ključna riječ DISTINCT može se koristiti s funkcijama MIN i MAX. Sve NULL vrijednosti se uklanjaju iz kolona argumenata prije upotrebe agregatnih funkcija MIN i MAX.
Zbirna funkcija SUM
Agregat Funkcija SUM izračunava ukupni zbir vrijednosti stupca. Argument ove agregatne funkcije uvijek mora biti numeričkog tipa podataka. Upotreba agregatne funkcije SUM prikazana je u primjeru ispod:
USE SampleDb; SELECT SUM (Budžet) "Ukupni budžet" IZ projekta;
Ovaj primjer izračunava ukupne budžete za sve projekte. Rezultat izvršavanja upita:
U ovom primjeru, agregatna funkcija grupiše sve vrijednosti budžeta projekta i određuje njihov ukupni iznos. Iz tog razloga, upit sadrži implicitnu funkciju grupiranja (kao i svi slični upiti). Implicitna funkcija grupisanja iz gornjeg primjera može se eksplicitno specificirati, kao što je prikazano u primjeru ispod:
USE SampleDb; SELECT SUM (Budžet) "Ukupni budžet" IZ Project GROUP BY ();
Korištenje parametra DISTINCT uklanja sve duple vrijednosti u stupcu prije primjene funkcije SUM. Isto tako, uklonite sve NULL vrijednosti prije primjene ove agregatne funkcije.
Agregatna funkcija AVG
Agregat AVG funkcija vraća aritmetičku sredinu svih vrijednosti u koloni. Argument ove agregatne funkcije uvijek mora biti numeričkog tipa podataka. Sve NULL vrijednosti se uklanjaju iz argumenta prije nego što se primijeni funkcija AVG.
Upotreba AVG agregatne funkcije prikazana je u primjeru ispod:
USE SampleDb; - Vraća 133833 SELECT AVG (budžet) "Prosečan budžet za projekat" IZ projekta;
Ovdje se izračunava aritmetički prosjek budžeta za sve budžete.
Agregatne funkcije COUNT i COUNT_BIG
Agregat COUNT funkcija ima dva različita oblika:
COUNT (naziv_kolca) COUNT (*)
Prvi oblik funkcije broji broj vrijednosti u stupcu col_name. Ako upit koristi ključnu riječ DISTINCT, sve duplicirane vrijednosti stupaca se uklanjaju prije nego što se koristi funkcija COUNT. Ovaj oblik funkcije COUNT ne uzima u obzir NULL vrijednosti prilikom brojanja vrijednosti stupaca.
Upotreba prvog oblika agregatne funkcije COUNT prikazana je u primjeru ispod:
USE SampleDb; SELECT ProjectNumber, COUNT (DISTINCT Job) "Poslovi u projektu" FROM Works_on GROUP BY ProjectNumber;
Ovdje se računa broj različitih pozicija za svaki projekat. Rezultat ovog upita:
Kao što možete vidjeti iz upita u primjeru, NULL vrijednosti nisu uzete u obzir od strane funkcije COUNT. (Zbroj svih vrijednosti u koloni posla je 7, a ne 11 kako bi trebao biti.)
Drugi oblik funkcije COUNT, tj. funkcija COUNT (*) broji broj redova u tabeli. A ako izraz SELECT upita s funkcijom COUNT (*) sadrži klauzulu WHERE s uvjetom, funkcija vraća broj redova koji zadovoljavaju navedeni uvjet. Za razliku od prve verzije funkcije COUNT, drugi oblik ne zanemaruje NULL vrijednosti jer ova funkcija radi na nizovima, a ne na stupcima. Primjer ispod pokazuje korištenje funkcije COUNT (*):
USE SampleDb; SELECT Job AS "Tip posla", COUNT (*) "Potrebni radnici" FROM Works_on GRUPA PO Zadatku;
Ovdje se računa broj postova u svim projektima. Rezultat izvršavanja upita:
COUNT_BIG funkcija ista funkcija kao COUNT. Jedina razlika između njih je u tipu rezultata koji vraćaju: COUNT_BIG uvijek vraća BIGINT vrijednosti, dok COUNT vraća INTEGER vrijednosti podataka.
Statističke agregatne funkcije
Sljedeće funkcije čine grupu statističkih agregatnih funkcija:
VARIzračunava statističku varijansu svih vrijednosti u stupcu ili izrazu.
VARPIzračunava statističku varijansu populacije svih vrijednosti u stupcu ili izrazu.
STDEVIzračunava standardnu devijaciju (koja se izračunava kao kvadratni korijen odgovarajuće varijanse) svih vrijednosti u stupcu ili izrazu.
STDEVPIzračunava standardnu devijaciju kolekcije svih vrijednosti u stupcu ili izrazu.
Korisnički definirane agregatne funkcije
Database Engine također podržava implementaciju korisnički definiranih funkcija. Ova mogućnost omogućava korisnicima da dopune sistemske agregatne funkcije funkcijama koje mogu sami implementirati i instalirati. Ove funkcije predstavljaju posebnu klasu korisnički definiranih funkcija i o njima će se kasnije raspravljati.
HAVING klauzula
U rečenici HAVING definira uvjet koji se primjenjuje na grupu redova. Dakle, ova klauzula ima isto značenje za grupe redova kao klauzula WHERE za sadržaj odgovarajuće tabele. Sintaksa za klauzulu HAVING je sljedeća:
HAVING stanje
Ovdje parametar uvjeta predstavlja uvjet i sadrži agregatne funkcije ili konstante.
Upotreba klauzule HAVING u sprezi sa agregatnom funkcijom COUNT (*) prikazana je u primjeru ispod:
USE SampleDb; - Vraća "p3" SELECT ProjectNumber FROM Works_on GRUPA PO ProjectNumber HAVING COUNT (*)
U ovom primjeru, sistem koristi klauzulu GROUP BY da grupiše sve redove prema vrijednostima u stupcu ProjectNumber. Zatim se broj redova u svakoj grupi i odabiru grupe koje sadrže manje od četiri reda (tri ili manje).
Klauzula HAVING se također može koristiti bez agregatnih funkcija, kao što je prikazano u primjeru ispod:
USE SampleDb; - Vratiće "Konsultant" SELECT Job FROM Works_on GRUPA PO POSAO IMA POSAO KAO "K%";
Ovaj primjer grupiše redove tabele Works_on prema naslovu posla i eliminiše poslove koji ne počinju slovom "K".
Klauzula HAVING se također može koristiti bez klauzule GROUP BY, iako to nije uobičajena praksa. U ovom slučaju, svi redovi tabele se vraćaju u istoj grupi.
ORDER BY klauzula
Rečenica POREDAK PO određuje redoslijed sortiranja redova u skupu rezultata koji vraća upit. Ova klauzula ima sljedeću sintaksu:
Redoslijed sortiranja je specificiran u parametru col_name. Col_number je alternativni indikator redoslijeda sortiranja koji identificira stupce redoslijedom kojim se pojavljuju na listi odabira SELECT izraza (1 je prva kolona, 2 druga kolona, itd.). ASC parametar definira uzlazni red, i DESC parametar- nizvodno. Podrazumevano je ASC.
Imena kolona u klauzuli ORDER BY ne moraju biti na listi odabranih kolona. Ali ovo se ne odnosi na upite poput SELECT DISTINCT, jer u takvim upitima, nazivi stupaca specificirani u ORDER BY klauzuli također se moraju pojaviti na listi odabranih stupaca. Osim toga, ova klauzula ne može sadržavati nazive stupaca iz tablica koje nisu specificirane u klauzuli FROM.
Kao što možete vidjeti iz sintakse ORDER BY klauzule, sortiranje skupa rezultata može se obaviti na više kolona. Ovo sortiranje je prikazano u primjeru ispod:
U ovom primjeru se biraju brojevi odjeljenja i prezimena i imena zaposlenih za zaposlenike čiji je broj osoblja manji od 20.000, kao i sortiranje po prezimenu i imenu. Rezultat ovog upita:
Kolone u ORDER BY klauzuli mogu se specificirati ne njihovim imenima, već redoslijedom na listi za odabir. Shodno tome, rečenica u gornjem primjeru može se prepisati na sljedeći način:
Ovaj alternativni način specificiranja stupaca po njihovoj poziciji umjesto po nazivima koristi se ako kriterij naručivanja sadrži agregatnu funkciju. (Drugi način je da koristite nazive stupaca, koji se zatim pojavljuju u ORDER BY klauzuli.) Međutim, u ORDER BY klauzuli, preporučuje se da navedete stupce njihovim imenima, a ne brojevima, kako biste olakšali ažuriranje upita ako trebate dodati ili ukloniti stupce na popisu odabira. Određivanje stupaca u ORDER BY klauzuli njihovim brojevima prikazano je u primjeru ispod:
USE SampleDb; SELECT ProjectNumber, COUNT (*) "Broj zaposlenih" IZ Works_on GROUP BY ProjectNumber ORDER BY 2 DESC;
Ovdje se za svaki projekat odabire broj projekta i broj zaposlenih koji u njemu učestvuju, slijeđujući rezultat u opadajućem redoslijedu prema broju zaposlenih.
Transact-SQL postavlja NULL vrijednosti na vrh liste kada se sortiraju rastućim redoslijedom, a NULL vrijednosti na kraju liste kada se sortiraju u opadajućem redoslijedu.
Korištenje ORDER BY klauzule za paginaciju rezultata
Prikazivanje rezultata upita na trenutnoj stranici može se ili implementirati u prilagođenu aplikaciju, ili možete naložiti poslužitelju baze podataka da to učini. U prvom slučaju, svi redovi baze podataka se šalju aplikaciji, čiji je zadatak da odabere tražene redove i prikaže ih. U drugom slučaju, samo redovi potrebni za trenutnu stranicu se preuzimaju i prikazuju sa strane servera. Kao što možete očekivati, kreiranje stranica na strani servera obično pruža najbolje performanse jer klijentu se šalju samo linije koje su potrebne za prikaz.
Kako bi podržao kreiranje stranice na strani servera, SQL Server 2012 uvodi dvije nove klauzule SELECT: OFFSET i FETCH. Primjena ove dvije rečenice prikazana je u primjeru ispod. Ovdje, iz baze podataka AdventureWorks2012 (koju možete pronaći u izvoru), izvlači poslovni ID, naziv radnog mjesta i rođendan svih zaposlenih žena, sortiran po nazivu radnog mjesta u rastućem redoslijedu. Rezultirajući skup redova se dijeli na stranice od 10 redova i prikazuje se treća stranica:
U rečenici OFFSET specificira broj redova rezultata koje treba preskočiti u prikazanom rezultatu. Ovaj iznos se izračunava nakon što se redovi sortiraju pomoću ORDER BY klauzule. U rečenici DETCH NEXT specificira broj WHERE i sortiranih redova koje treba vratiti. Parametar ove klauzule može biti konstanta, izraz ili rezultat drugog upita. FETCH NEXT klauzula je slična klauzuli PETCH FETCH.
Glavni cilj pri kreiranju stranica na strani servera je biti u mogućnosti implementirati uobičajene forme stranica koristeći varijable. Ovaj zadatak se može postići putem SQL Server paketa.
SELECT izjava i IDENTITY svojstvo
IDENTITY svojstvo omogućava vam da odredite vrijednosti za određeni stupac tablice u obliku automatski inkrementalnog brojača. Stupci numeričkog tipa podataka kao što su TINYINT, SMALLINT, INT i BIGINT mogu imati ovo svojstvo. Za takvu kolonu tabele, Database Engine automatski generiše sekvencijalne vrednosti počevši od navedene početne vrednosti. Stoga se svojstvo IDENTITY može koristiti za generiranje jednocifrenih numeričkih vrijednosti za odabranu kolonu.
Tablica može sadržavati samo jedan stupac sa svojstvom IDENTITY. Vlasnik tabele ima mogućnost da odredi početnu vrednost i inkrement, kao što je prikazano u donjem primeru:
USE SampleDb; KREIRAJ TABELU Proizvod (Id INT IDENTITETA (10000, 1) NIJE NULL, Ime NVARCHAR (30) NOT NULL, Cijena NOVAC) UMETNI U PROIZVOD (IME, Cijena) VRIJEDNOSTI ("Proizvod1", 10), ("Proizvod2", 15) , ("Proizvod3", 8), ("Proizvod4", 15), ("Proizvod5", 40); - Vratiće 10004 SELECT IDENTITYCOL FROM Product WHERE Name = "Proizvod5"; - Analogno prethodnom iskazu SELECT $ identity FROM Product WHERE Naziv = "Proizvod5";
Ovaj primjer prvo kreira tablicu proizvoda koja sadrži stupac Id sa svojstvom IDENTITY. Vrijednosti u koloni Id sistem kreira automatski, počevši od 10.000 i povećavajući se u jednom koraku za svaku narednu vrijednost: 10.000, 10.001, 10.002, itd.
Nekoliko sistemskih funkcija i varijabli pridruženo je svojstvu IDENTITY. Na primjer, primjer koda koristi sistemska varijabla $ identitet... Kao što možete vidjeti iz rezultata pokretanja ovog koda, ova varijabla se automatski poziva na svojstvo IDENTITY. Umjesto toga možete koristiti i sistemsku funkciju IDENTITYCOL.
Početna vrijednost i prirast stupca sa svojstvom IDENTITY mogu se pronaći pomoću funkcija IDENT_SEED i IDENT_INCR respektivno. Ove funkcije se primjenjuju na sljedeći način:
USE SampleDb; SELECT IDENT_SEED ("Proizvod"), IDENT_INCR ("Proizvod")
Kao što je spomenuto, IDENTITY vrijednosti postavlja sistem automatski. Ali korisnik može eksplicitno odrediti njihove vrijednosti za određene nizove dodjeljivanjem parametru IDENTITY_INSERT vrijednost ON prije umetanja eksplicitne vrijednosti:
SET IDENTITY INSERT ime tablice ON
Budući da se parametar IDENTITY_INSERT može koristiti za postavljanje bilo koje vrijednosti, uključujući duplicirane vrijednosti, na stupcu sa svojstvom IDENTITY, svojstvo IDENTITY obično ne provodi jedinstvenost vrijednosti stupca. Stoga, ograničenja UNIQUE ili PRIMARY KEY treba primijeniti da bi se nametnula jedinstvenost vrijednosti stupca.
Kada umetnete vrijednosti u tabelu nakon postavljanja IDENTITY_INSERT na uključeno, sistem kreira sljedeću vrijednost u koloni IDENTITY, povećavajući najvišu trenutnu vrijednost u toj koloni.
Izjava CREATE SEQUENCE
Upotreba svojstva IDENTITY ima nekoliko značajnih nedostataka, od kojih su najznačajniji:
svojstvo je ograničeno na navedenu tabelu;
nova vrijednost kolone se ne može dobiti na bilo koji drugi način, osim njenom primjenom;
svojstvo IDENTITY se može specificirati samo prilikom kreiranja stupca.
Iz ovih razloga, SQL Server 2012 uvodi sekvence koje imaju istu semantiku kao svojstvo IDENTITY, ali bez prethodno navedenih nedostataka. U ovom kontekstu, sekvenca se odnosi na funkcionalnost baze podataka koja vam omogućava da odredite vrijednosti brojača za različite objekte baze podataka, kao što su stupci i varijable.
Sekvence se kreiraju pomoću instrukcije CREATE SEQUEENCE... Izraz CREATE SEQUENCE definiran je u SQL standardu i podržan je od drugih sistema relacijskih baza podataka kao što su IBM DB2 i Oracle.
Primjer ispod pokazuje kako kreirati sekvencu u SQL Serveru:
USE SampleDb; CREATE SEQUENCE dbo.Sequence1 KAO INT POČINJE SA 1 POVEĆANJEM ZA 5 MIN VRIJEDNOSTI 1 MAKSIMALNA VRIJEDNOST 256 CIKLUS;
U gornjem primjeru, vrijednosti Sequence1 se generišu automatski od strane sistema, počevši od vrijednosti od 1 i u koracima od 5 za svaku uzastopnu vrijednost. Dakle, u klauzulu START naznačena je početna vrijednost i in INCREMENT ponuda- korak. (Korak može biti pozitivan ili negativan.)
U sljedeće dvije fakultativne rečenice MINVRIJEDNOST i MAXVALUE specificira minimalnu i maksimalnu vrijednost objekta sekvence. (Imajte na umu da MINVALUE mora biti manji ili jednak početnoj vrijednosti, a MAXVALUE ne može biti veći od gornje granice tipa podataka specificiranog za niz.) U rečenici CIKLUS označava da se sekvenca ponavlja od početka kada se prekorači maksimalna (ili minimalna za sekvencu sa negativnim korakom) vrijednost. Po defaultu, ova klauzula je postavljena na NO CYCLE, što znači da prekoračenje maksimalne ili minimalne vrijednosti sekvence izaziva izuzetak.
Glavna karakteristika sekvenci je njihova nezavisnost od tabela, tj. mogu se koristiti sa bilo kojim objektom baze podataka kao što su stupci tablice ili varijable. (Ovo svojstvo ima pozitivan učinak na pohranu, a samim tim i na performanse. Određeni niz ne mora biti pohranjen; pohranjuje se samo njegova posljednja vrijednost.)
Nove vrijednosti sekvence se kreiraju sa SLJEDEĆA VRIJEDNOST ZA izraze, čija je primjena prikazana u primjeru ispod:
USE SampleDb; - Vraća 1 SELECT SLJEDEĆA VRIJEDNOST ZA dbo.sequence1; - Vraća 6 (sljedeći korak) SELECT SLJEDEĆA VRIJEDNOST ZA dbo.sequence1;
Možete koristiti izraz SLJEDEĆA VRIJEDNOST FOR da dodijelite rezultat niza varijabli ili ćeliji stupca. Donji primjer ilustruje upotrebu ovog izraza za dodjelu rezultata koloni:
USE SampleDb; CREATE TABLE Proizvod (Id INT NOT NULL, Naziv NVARCHAR (30) NOT NULL, Cijena NOVAC) UMETNI U VRIJEDNOSTI proizvoda (SLJEDEĆA VRIJEDNOST ZA dbo.sequence1, "Proizvod1", 10); UMETNI U VRIJEDNOSTI proizvoda (SLJEDEĆA VRIJEDNOST ZA dbo.sequence1, "Proizvod2", 15); - ...
Gornji primjer prvo kreira tablicu pod nazivom Proizvod sa četiri stupca. Zatim, dva INSERT izraza ubacuju dva reda u ovu tabelu. Prve dvije ćelije prve kolone bit će 11 i 16.
Primjer ispod pokazuje korištenje kataloškog pogleda sys.sequences da vidite trenutnu vrijednost sekvence bez korištenja:
Tipično, izraz NEXT VALUE FOR se koristi u INSERT izrazu za instrukciju sistemu da ubaci generirane vrijednosti. Ovaj izraz se također može koristiti kao dio višelinijskog upita koji koristi klauzulu OVER.
Da promijenite svojstvo postojeće sekvence, primijenite ALTER SEQUENCE izraz... Jedna od najvažnijih upotreba ove naredbe je opcija RESTART WITH, koja resetuje navedeni niz. Primjer u nastavku pokazuje upotrebu naredbe ALTER SEQUENCE za resetiranje gotovo svih svojstava Sequence1:
USE SampleDb; ALTER SEQUENCE dbo.sequence1 RESTART SA 100 POVEĆANJA ZA 50 MIN VRIJEDNOSTI 50 MAKSIMALNE VRIJEDNOSTI 200 BEZ CIKLUSA;
Slijed se briše pomoću instrukcije DROP SEQUENCE.
Postavite operatore
Pored operatora o kojima smo ranije govorili, Transact-SQL podržava tri druga skupa operatora: UNION, INTERSECT i EXCEPT.
UNION operater
UNION operater kombinuje rezultate dva ili više upita u jedan skup rezultata koji uključuje sve redove koji pripadaju svim upitima u uniji. Posljedično, rezultat spajanja dvije tablice je nova tablica koja sadrži sve redove u jednoj ili obje originalne tablice.
Opšti oblik UNION operatora izgleda ovako:
select_1 UNION select_2 (select_3]) ...
Opcije select_1, select_2, ... su SELECT izjave koje kreiraju spajanje. Ako se koristi parametar ALL, prikazuju se svi redovi, uključujući duplikate. U naredbi UNION, parametar ALL ima isto značenje kao u SELECT, ali s jednom razlikom: ovo je zadana vrijednost za SELECT, ali mora biti eksplicitno specificirana za UNION.
U svom originalnom obliku, SampleDb nije prikladan za demonstraciju upotrebe UNION operatora. Stoga, ovaj odjeljak kreira novu tablicu EmployeeEnh koja je identična postojećoj tablici Employee, ali ima dodatni stupac Grad. Ova kolona označava mjesto prebivališta zaposlenih.
Kreiranje tabele EmployeeEnh pruža nam priliku da demonstriramo upotrebu klauzule. INTO u SELECT izrazu. Naredba SELECT INTO izvodi dvije operacije. Prvo se kreira nova tabela sa stupcima navedenim na listi SELECT. Zatim se redovi iz originalne tabele ubacuju u novu tabelu. Ime nove tablice je navedeno u INTO klauzuli, a ime izvorne tablice navedeno je u klauzuli FROM.
Primjer ispod prikazuje kreiranje tabele EmployeeEnh iz tabele Employee:
USE SampleDb; SELECT * INTO EmployeeEnh FROM Employee; ALTER TABLE EmployeeEnh DODAJ Grad NCHAR (40) NULL;
U ovom primjeru, naredba SELECT INTO kreira tablicu EmployeeEnh, u nju umeće sve redove iz izvorne tablice Employee, a zatim naredba ALTER TABLE dodaje stupac City u novu tablicu. Ali dodana kolona Grad ne sadrži nikakve vrijednosti. Vrijednosti se mogu umetnuti u ovu kolonu kroz Management Studio ili korištenjem sljedećeg koda:
USE SampleDb; UPDATE EmployeeEnh SET Grad = "Kazan" GDJE Id = 2581; UPDATE EmployeeEnh SET City = "Moskva" GDJE Id = 9031; UPDATE EmployeeEnh SET Grad = "Jekaterinburg" GDJE Id = 10102; UPDATE EmployeeEnh SET Grad = "Sankt Peterburg" GDJE Id = 18316; UPDATE EmployeeEnh SET Grad = "Krasnodar" GDJE Id = 25348; UPDATE EmployeeEnh SET Grad = "Kazan" GDJE Id = 28559; UPDATE EmployeeEnh SET Grad = "Perm" GDJE Id = 29346;
Sada smo spremni da demonstriramo upotrebu izjave UNIJE. Primjer u nastavku prikazuje upit za kreiranje spajanja između EmployeeEnh i Department tablica koristeći ovu naredbu:
USE SampleDb; ODABERITE Grad KAO "Grad" IZ UNIONA EmployeeEnh ODABIRITE lokaciju IZ Odjela;
Rezultat ovog upita:
Samo kompatibilne tablice mogu se spojiti pomoću UNION izraza. Pod kompatibilnim tabelama podrazumijevamo da obje liste kolona u selekciji moraju sadržavati isti broj kolona, a odgovarajuće kolone moraju imati kompatibilne tipove podataka. (U smislu kompatibilnosti, tipovi podataka INT i SMALLINT nisu kompatibilni.)
Rezultat konkatenacije se može poredati samo pomoću ORDER BY klauzule u posljednjoj SELECT naredbi, kao što je prikazano u primjeru ispod. Klauzule GROUP BY i HAVING mogu se koristiti sa pojedinačnim SELECT naredbama, ali ne unutar samog spoja.
Upit u ovom primjeru odabire zaposlenike koji ili rade u odjelu d1 ili koji su počeli raditi na projektu prije 1. januara 2008.
UNION operator podržava opciju SVE. Ova opcija ne uklanja duplikate iz skupa rezultata. OR operator se može koristiti umjesto UNION operatora ako se svi SELECT izrazi spojeni s jednim ili više UNION operatora odnose na istu tablicu. U ovom slučaju, skup SELECT izraza je zamijenjen jednim SELECT izrazom sa skupom OR naredbi.
INTERSECT i EXCEPT operatori
Dva druga operatera za rad sa setovima, INTERSECT i OSIM, definiraju sjecište i razliku, respektivno. Ispod presjeka, u ovom kontekstu, nalazi se skup redova koji pripadaju objema tablicama. A razlika između dvije tablice je definirana kao sve vrijednosti koje pripadaju prvoj tablici, a nisu prisutne u drugoj. Primjer ispod pokazuje upotrebu INTERSECT operatora:
Transact-SQL ne podržava upotrebu parametra ALL sa INTERSECT ili EXCEPT operatorima. Upotreba operatora EXCEPT prikazana je u primjeru ispod:
Zapamtite da ova tri operatora skupa imaju različit prioritet izvršenja: INTERSECT ima najveći prioritet, praćen EXCEPT, a UNION ima najmanji prioritet. Ne obraćanje pažnje na prioritet izvršenja kada se koristi nekoliko različitih operatora skupa može dovesti do neočekivanih rezultata.
CASE izrazi
U oblasti aplikativnog programiranja baze podataka, ponekad je potrebno modificirati prezentaciju podataka. Na primjer, ljudi se mogu podijeliti prema njihovoj društvenoj klasi, koristeći vrijednosti 1, 2 i 3, koje označavaju muškarce, žene, odnosno djecu. Ova tehnika programiranja može smanjiti vrijeme potrebno za implementaciju programa. CASE izraz Transact-SQL vam omogućava da lako implementirate ovu vrstu kodiranja.
Za razliku od većine programskih jezika, CASE nije izjava, već izraz. Stoga se izraz CASE može koristiti gotovo bilo gdje gdje Transact-SQL dozvoljava korištenje izraza. Izraz CASE ima dva oblika:
jednostavan CASE izraz;
izraz za pretragu CASE.
Sintaksa za jednostavan CASE izraz je sljedeća:
Naredba s jednostavnim CASE izrazom prvo pretražuje listu svih izraza u WHEN klauzula prvi izraz koji odgovara izrazu_1, a zatim izvršava odgovarajući THEN klauzula... Ako na listi WHEN ne postoji odgovarajući izraz, onda ELSE klauzula.
Sintaksa za CASE izraz za pretraživanje je sljedeća:
U ovom slučaju, traži prvi kvalifikujući uslov, a zatim izvršava odgovarajuću klauzulu THEN. Ako nijedan od uslova ne ispunjava uslove, ELSE klauzula se izvršava. Upotreba izraza za pretragu CASE prikazana je u primjeru ispod:
USE SampleDb; SELECT ProjectName, CASE WHEN Budget> 0 I Budžet 100000 I Budžet 150000 I Budžet
Rezultat ovog upita:
Ovaj primjer ponderira budžete svih projekata i prikazuje izračunate težine zajedno s odgovarajućim nazivima projekata.
Primjer ispod pokazuje drugi način korištenja izraza CASE gdje klauzula WHEN sadrži potupite koji su dio izraza:
USE SampleDb; SELECT ProjectName, CASE WHEN p1.Budget (IZABRAJTE AVG (p2.Budget) IZ Projekta p2) ONDA "iznad proseka" KRAJ "Kategorija budžeta" IZ Projekta p1;
Rezultat ovog upita je sljedeći:
Da bi se sumirale informacije sadržane u bazi podataka, SQL pruža agregatne funkcije. Agregatna funkcija uzima cijeli stupac podataka kao argument i vraća jednu vrijednost koja sažima taj stupac na specifičan način.
Na primjer, agregatna funkcija AVG () uzima stupac brojeva kao argument i izračunava njihov prosjek.
Za izračunavanje prosječnog dohotka po glavi stanovnika Zelenograda potreban vam je sljedeći upit:
ODABERITE 'PROSJEČNI PRIHOD =', AVG (SUMD)
SQL ima šest agregatnih funkcija koje pružaju različite vrste sažetaka (slika 1):
- SUM () izračunava zbir svih vrijednosti sadržanih u koloni;
- AVG () izračunava prosek između vrednosti sadržanih u koloni;
- MIN () pronalazi najmanju među svim vrijednostima sadržanim u koloni;
- MAX () pronalazi najveću među svim vrijednostima sadržanim u koloni;
- COUNT () broji broj vrijednosti sadržanih u koloni;
- COUNT (*) broji broj redova u tabeli rezultata upita.
Argument agregatnoj funkciji može biti jednostavno ime stupca, kao u prethodnom primjeru, ili izraz, kao u sljedećem upitu koji izračunava porez po glavi stanovnika:
ODABIR PROSJEČNOG (SUMD * 0,13)
Ovaj upit kreira privremenu kolonu koja sadrži vrijednosti (SUMD * 0,13) za svaki red u tabeli PERSON, a zatim izračunava prosjek privremene kolone.
Zbir prihoda svih stanovnika Zelenograda može se izračunati pomoću agregatne funkcije SUM:
ODABERITE ZUM (SUM) OD OSOBE
Agregatna funkcija se također može koristiti za izračunavanje ukupnih vrijednosti za tablicu rezultata dobivenih spajanjem nekoliko izvornih tablica. Na primjer, možete izračunati ukupan iznos prihoda koji stanovnici primaju iz izvora koji se zove "Stipendija":
ODABIR SUMU (NOVAC)
OD PROFITA, HAVE_D
WHERE PROFIT.ID = HAVE_D.ID
AND PROFIT.SOURCE = 'Stipendija'
Funkcije agregata MIN () i MAX () omogućuju vam da pronađete najmanju i najveću vrijednost u tablici. Međutim, kolona može sadržavati numeričke ili nizove vrijednosti, ili vrijednosti datuma ili vremena.
Na primjer, možete definirati:
(a) najniži ukupan prihod rezidenta i najveći porez koji se plaća:
ODABIR MIN (SUMD), MAX (SUMD * 0,13)
(b) datumi rođenja najstarijeg i najmlađeg stanovnika:
ODABIR MIN (RDATE), MAX (RDATE)
(c) prezimena, imena i patronimike prvih i poslednjih stanovnika na spisku, poredani po abecednom redu:
ODABIR MIN (FIO), MAX (FIO)
Kada primjenjujete ove agregatne funkcije, morate imati na umu da se numerički podaci uspoređuju prema aritmetičkim pravilima, datumi se uspoređuju uzastopno (ranije vrijednosti datuma se smatraju manjim od kasnijih), vremenski intervali se uspoređuju na osnovu njihovog trajanja.
Kada koristite MIN () i MAX () sa podacima niza, rezultat poređenja dva niza ovisi o korištenoj tablici kodiranja znakova.
Agregatna funkcija COUNT () broji broj vrijednosti u stupcu bilo koje vrste:
(a) koliko stanova ima u 1. mikrookrugu?
ODABIR BROJ (ADR) IZ STANA GDJE ADR LIKE "%, 1_ _-%"
(b) koliko stanovnika ima izvore prihoda?
ODABIR BROJ (DISTINCT NOM) OD HAVE_D
(c) koliko izvora prihoda koriste stanovnici?
SELECT COUNT (DISTINCT ID) FROM HAVE_D (Ključna riječ DISTINCT određuje da se broje neduplicirane vrijednosti u koloni).
Posebna agregatna funkcija COUNT (*) broji redove u tablici rezultata, a ne vrijednosti podataka:
(a) koliko stanova ima u 2. mikrookrugu?
ODABIR BROJ (*) IZ STANA GDJE ADR LIKE "%, 2 __-%"
(b) koliko izvora prihoda ima Ivan Ivanovič?
SELECT COUNT (*) FROM PERSON, HAVE_D WHERE FIO = "Ivanov Ivan Ivanovič" I PERSON.NOM = HAVE_D.NOM
(c) koliko stanovnika živi u stanu na datoj adresi?
ODABIR BROJ (*) OD OSOBE GDE ADR = "Zelenograd, 1001-45"
Jedan od načina da se shvati kako se agregatni upiti izvršavaju je razmišljanje o izvršenju upita u dva dijela. Prvo se utvrđuje kako bi upit funkcionirao bez agregatnih funkcija, vraćajući više redova rezultata. Agregatne funkcije se zatim primjenjuju na rezultate upita, vraćajući jedan red sa sažetkom.
Na primjer, razmotrite sljedeći složeni upit: pronađite prosječan ukupni prihod po glavi stanovnika, zbir ukupnog prihoda stanovnika i prosječan prihod izvora kao procenat ukupnog prihoda stanovnika. Odgovor daje operater
ODABERITE PROSJEČNI (SUMD), SUM (SUMD), (100 * PROSJEK (NOVAC / SUMD)) IZ OSOBE, PROFIT, IMAM_D GDJE PERSON.NOM = HAVE_D.NOM I HAVE_D.ID = PROFIT.ID
Bez agregatnih funkcija, upit bi izgledao ovako:
ODABIR SUMD, SUMD, NOVAC / SUMD OD OSOBE, PROFIT, HAVE_D GDJE PERSON.NOM = HAVE_D.NOM I HAVE_D.ID = PROFIT.ID
i vratio bi jedan red rezultata za svakog stanovnika i određeni izvor prihoda. Agregatne funkcije koriste stupce u tablici rezultata ovog upita da proizvedu tablicu jednog reda sa rezimenim rezultatima.
U redu vraćenih kolona možete navesti agregatnu funkciju umjesto imena bilo kojeg stupca. Na primjer, može se uključiti u izraz koji dodaje ili oduzima vrijednosti dvije agregatne funkcije:
ODABERITE MAX (SUMD) -MIN (SUMD) OD OSOBE
Međutim, agregatna funkcija ne može biti argument drugoj agregatnoj funkciji, tj. ugniježđene agregatne funkcije su zabranjene.
Osim toga, agregatne funkcije i regularni nazivi stupaca ne mogu se istovremeno koristiti na listi vraćenih stupaca, jer u tome nema smisla, na primjer:
ODABERITE FIO, SUM (SUM) OD OSOBE
Ovdje prvi element liste daje instrukcije DBMS-u da kreira tabelu koja će se sastojati od nekoliko redova i sadržavati jedan red za svakog korisnika. Druga stavka na listi traži od DBMS-a da dobije jednu vrijednost rezultata, koja je zbir vrijednosti u SUMD koloni. Ove dvije smjernice su u suprotnosti jedna s drugom, što rezultira greškom.
Gore navedeno ne važi za slučajeve obrade potupita i upita sa grupisanjem.
