RAČUNARSTVO

Završne funkcije

Izrazi SQL upita često zahtijevaju prethodnu obradu podataka. U tu svrhu koriste se posebne funkcije i izrazi.

Često želite da znate koliko zapisa odgovara određenom upitu,koliki je zbir vrijednosti neke numeričke kolone, njene maksimalne, minimalne i prosječne vrijednosti. Za to se koriste takozvane konačne (statističke, agregatne) funkcije. Funkcije sažetka obrađuju skupove zapisa specificiranih, na primjer, klauzulom WHERE. Ako su uključeni u listu stupaca nakon naredbe SELECT, rezultirajuća tablica će sadržavati ne samo stupce tablice baze podataka, već i vrijednosti izračunate pomoću ovih funkcija. Sledeće jelista funkcija sažetka.

• COUNT (parametar ) vraća broj zapisa navedenih u parametru. Ako želite da dobijete broj svih zapisa, tada morate navesti znak zvjezdice (*) kao parametar. Ako navedete ime stupca kao parametar, funkcija će vratiti broj zapisa u kojima ovaj stupac ima vrijednosti koje nisu NULL. Da biste saznali koliko različitih vrijednosti kolona sadrži, prethodite njenom imenu ključnom riječi DISTINCT. Na primjer:

SELECT COUNT(*) FROM Clients;

SELECT COUNT(Order_Amount) FROM kupaca;

SELECT COUNT(DISTINCT Order_Sum) FROM kupaca;

Pokušaj izvršenja sljedećeg upita rezultirat će porukom o grešci:

SELECT Region , COUNT(*) FROM Clients ;

• SUM (parametar ) vraća zbir vrijednosti kolone navedene u parametru. Parametar također može biti izraz koji sadrži ime stupca. Na primjer:

SELECT SUM (Iznos_narudžbe) FROM kupaca;

Ovaj SQL izraz vraća tablicu s jednim stupcem i jednim zapisom koja sadrži zbroj svih definiranih vrijednosti u stupcu Order_Amount iz tablice Kupci.

Recimo da su u izvornoj tabeli vrijednosti stupca Order_Amount izražene u rubljama, a ukupan iznos trebamo izračunati u dolarima. Ako je trenutni kurs, na primjer, 27,8, onda možete dobiti željeni rezultat koristeći izraz:

SELECT SUM (Iznos_narudžbe*27,8) FROM kupaca;

• AVG (parametar ) vraća aritmetičku sredinu svih vrijednosti kolone navedene u parametru. Parametar može biti izraz koji sadrži ime stupca. Na primjer:

SELECT AVG (Order_Amount) FROM kupaca;

ODABERITE AVG (iznos_porudžbine*27,8) IZ kupaca

WHERE Region<>"Sjeverozapad";

• MAX (parametar ) vraća maksimalnu vrijednost u koloni navedenoj u parametru. Parametar također može biti izraz koji sadrži ime stupca. Na primjer:

SELECT MAX(Order_Amount) FROM kupaca;

SELECT MAX(Order_Sum*27.8) OD kupaca

W OVDJE Region<>"Sjeverozapad";

• MIN (parametar ) vraća minimalnu vrijednost u koloni navedenoj u parametru. Parametar može biti izraz koji sadrži ime stupca. Na primjer:

SELECT MIN(Order_Amount) FROM kupaca;

SELECT MIN (Order_Amount*27. 8) OD kupaca

W OVDJE Region<>"Sjeverozapad";

U praksi je često potrebno dobiti zbirnu tabelu koja sadrži ukupne, prosečne, maksimalne i minimalne vrednosti numeričkih kolona. Da biste to učinili, koristite funkcije grupiranja (GROUP BY) i sažetka.

SELECT Region, SUM (Iznos_narudžbe) OD kupaca

GROUP BY Region;

Tabela rezultata za ovaj upit sadrži nazive regiona i ukupne (ukupne) količine narudžbi svih kupaca iz odgovarajućih regiona (slika 5).

Sada razmotrite upit da dobijete sve zbirne podatke po regijama:

SELECT Region, SUM (Iznos_porudžbine), AVG (iznos_narudžbe), MAX(iznos_narudžbe), MIN (iznos_narudžbe)

OD klijenata

GROUP BY Region;

Početna i rezultat tabele su prikazane na sl. 8. U primjeru, samo je sjeverozapadna regija predstavljena u izvornoj tabeli sa više od jednog zapisa. Stoga, u tablici rezultata za to, različite konačne funkcije daju različite vrijednosti.

Rice. 8. Zbirna tabela iznosa naloga po regionima

Kada se funkcije sažetka koriste u listi stupaca u SELECT izrazu, odgovarajući naslovi stupaca u tablici rezultata su Expr1001, Expr1002, itd. (ili nešto slično, ovisno o SQL implementaciji). Međutim, možete postaviti zaglavlja za vrijednosti funkcija sažetka i drugih stupaca prema vlastitom nahođenju. Da biste to učinili, dovoljno je navesti izraz forme iza stupca u SELECT izrazu:

AS column_header

Ključna riječ AS (as) znači da u tablici rezultata odgovarajuća kolona treba imati naslov naveden nakon AS. Dodijeljeno zaglavlje se također naziva alias. Sljedeći primjer (slika 9) postavlja pseudonime za sve izračunate stupce:

SELECT Region,

SUMA (Iznos_narudžbe) AS [Ukupni iznos narudžbe],

AVG (Iznos_narudžbe) AS [Prosječni iznos narudžbe],

MAX(Iznos_narudžbe) AS Maximum,

MIN (Iznos_narudžbe) AS minimalno,

OD klijenata

GROUP BY Region;

Rice. 9. Zbirna tabela iznosa narudžbi po regijama koristeći pseudonime stupaca

Aliasi koji se sastoje od više riječi razdvojenih razmacima nalaze se u uglastim zagradama.

Funkcije sažetka mogu se koristiti u klauzulama SELECT i HAVING, ali se ne mogu koristiti u klauzuli WHERE. Onerator HAVING sličan je klauzuli WHERE, ali za razliku od WHERE, odabire zapise u grupama.

Recimo da želite da odredite koje regije imaju više od jednog korisnika. U tu svrhu možete koristiti sljedeći upit:

SELECT Region , Count(*)

OD klijenata

GRUPA PO REGIONU KOJI IMA BROJ(*) > 1;

Funkcije upravljanja vrijednostima

Kada radite s podacima, često ih morate obraditi (transformirati ih u željeni oblik): odabrati određeni podniz u nizu, ukloniti početne i zadnje razmake, zaokružiti broj, izračunati kvadratni korijen, odrediti trenutno vrijeme itd. SQL ima sljedeće tri vrste funkcija:

• string funkcije;
• numeričke funkcije;
• funkcije datuma i vremena.

Funkcije niza

Funkcije stringa uzimaju string kao parametar i vraćaju string ili NULL nakon obrade.

• SUBSTRING (red OD početka)vraća podniz koji je rezultat niza koji je dat kao parametar linija . Podniz počinje sa znakom čiji je redni broj naveden u početnom parametru i ima dužinu navedenu u parametru dužine. Znakovi niza su numerisani s lijeva na desno, počevši od 1. Uglaste zagrade ovdje samo označavaju da je izraz koji je u njima opcionalan. Ako izraz ZA dužinu se ne koristi, onda podniz od Počni i do kraja originalne linije. Vrijednosti parametara početak i dužina mora biti odabran tako da je traženi podniz zapravo unutar originalnog niza. U suprotnom, funkcija SUBSTRING će vratiti NULL.

Na primjer:

PODNIZ ("Draga Maša!" OD 9 ZA 4) vraća "Maša";

SUBSTRING ("Draga Maša!" OD 9) vraća "Maša!";

SUBSTRING("Draga Maša!" OD 15) vraća NULL.

Ovu funkciju možete koristiti u SQL naredbi, na primjer, ovako:

SELECT * FROM Clients

WHERE PODNIZ(Regija OD 1 ZA 5) = "Sjever";

• UPPER(string ) pretvara sve znakove niza specificiranih u parametru u velika slova.
• LOWER(niz ) pretvara sve znakove niza specificiranih u parametru u mala slova.
• TRIM (VODEĆA | ZADNJA | OBA ["znak"] IZ niza ) uklanja vodeći (LEADING), zadnji (TRAILING) ili oba (BOTH) znaka iz niza. Prema zadanim postavkama, znak koji treba ukloniti je razmak (""), tako da se može izostaviti. Najčešće se ova funkcija koristi za uklanjanje razmaka.

Na primjer:

TRIM (VODEĆI "" IZ "grada Sankt Peterburga") rotira "grad St. Petersburg";

TRIM(TRALING " " IZ "grada Sankt Peterburga") vraća "grad St. Petersburg";

TRIM (OBA " " IZ " grada Sankt Peterburga ") vraća "grad St. Petersburg";

TRIM(OBA IZ "grad St. Petersburg") vraća "grad St. Petersburg";

TRIM(OBA "g" IZ "grad St. Petersburg") vraća "grad St. Petersburg".

Među ovim funkcijama najčešće se koriste SUBSTRING() i TRIM().

Numeričke funkcije

Numeričke funkcije mogu uzeti podatke ne samo numeričkog tipa kao parametar, već uvijek vraćaju broj ili NULL (nedefinirana vrijednost).

• POZICIJA( targetString IN niz) traži pojavljivanje ciljnog niza u navedenom nizu. Ako je pretraga uspješna, vraća se broj pozicije prvog znaka, inače 0. Ako ciljni niz ima nultu dužinu (na primjer, niz " "), tada funkcija vraća 1. Ako je barem jedan od parametara NULL, zatim se vraća NULL. Znakovi niza su numerisani s lijeva na desno, počevši od 1.

Na primjer:

POSITION ("e" U "Zdravo svima") vraća 5;

POSITION ("svi" U "zdravo svima") vraća 8;

POSITION ("" Pozdrav svima") vraća 1;

POSITION("Zdravo!" U "Zdravo svima") vraća 0.

U tabeli Klijenti (vidi sliku 1), kolona Adresa sadrži, pored naziva grada, poštanski broj, naziv ulice i druge podatke. Možda ćete morati da odaberete evidenciju kupaca baziranu na određenom gradu. Dakle, ako želite da odaberete zapise koji se odnose na klijente koji žive u Sankt Peterburgu, možete koristiti sljedeći izraz SQL upita:

SELECT * FROM Clients

GDJE POZICIJA ("Sankt Peterburg" na adresi) > 0;

Imajte na umu da se ovaj jednostavan upit za dohvaćanje podataka može preformulisati kao:

SELECT * FROM Clients

WHERE Adresa LIKE "%Petersburg%";

• EKSTRAKT (opcija ) izdvaja element iz vrijednosti datum-vrijeme ili iz intervala. Na primjer:

IZVOD (MJESEC OD DATUMA "2005-10-25") vraća 10.

• CHARACTER_LENGTH (niz ) vraća broj znakova u nizu.

Na primjer:

CHARACTER_LENGTH("Zdravo svima") vraća 11.

• OCTET_LENGTH (niz ) vraća broj okteta (bajtova) u nizu. Svaki znak latinice ili ćirilice predstavljen je jednim bajtom, a znak kineske abecede je predstavljen sa dva bajta.
• KARDINALNOST (parametar ) uzima kolekciju elemenata kao parametar i vraća broj elemenata u kolekciji (kardinalni broj). Kolekcija može biti, na primjer, niz ili višestruki skup koji sadrži elemente različitih tipova.
• ABS (broj ) vraća apsolutnu vrijednost broja. Na primjer:

ABS (-123) vraća 123;

ABS (2 - 5) vraća 3.

• MO D(broj 1, broj 2 ) vraća ostatak cjelobrojnog dijeljenja prvog broja sa drugim. Na primjer:

MOD (5, h) vraća 2;

MOD (2, h) vraća 0.

• LN (broj ) vraća prirodni logaritam broja.
• EXP(broj) vraća e broj (baza prirodnog logaritma na stepen broja).
• SNAGA (broj 1, broj 2 ) vraća broj1 broj2 (broj 1 na stepen broja 2).
• SQRT (broj ) vraća kvadratni korijen broja.
• SPRAT (br ) vraća najveći cijeli broj koji ne prelazi dati parametar (zaokruživanje prema dolje). Na primjer:

FLOOR (5.123) vraća 5.0.

• CEIL (broj) ili CEILING (broj ) vraća najmanji cijeli broj koji nije manji od navedenog parametra zaokruživanja). Na primjer:

CEIL (5.123) vraća 6.0.

• WIDTH_BUCKET (broj1, broj2, broj3, broj4) vraća cijeli broj u rasponu između 0 i number4 + 1. Parametri broj2 i broj3 definiraju numerički segment podijeljen na jednake intervale, čiji je broj specificiran parametrom broj 4. Funkcija određuje broj intervala u koji vrijednost pada broj1. Ako je broj1 izvan navedenog raspona, funkcija vraća 0 ili broj 4 + 1. Na primjer:

WIDTH_BUCKET(3.14, 0, 9, 5) vraća 2.

Funkcije datuma i vremena

AT SQL jezik Postoje tri funkcije koje vraćaju trenutni datum i vrijeme.

• TRENUTNI DATUM vraća trenutni datum (tip DATE).

Na primjer: 2005-06-18.

• CURRENT_TIME (broj ) vraća trenutno vrijeme (tipa TIME). Cjelobrojni argument specificira preciznost druge reprezentacije. Na primjer, s vrijednošću od 2 sekunde bit će predstavljen s točnošću od stotinke (dvije znamenke u razlomku):

12:39:45.27.

• CURRENT_TIMESTAMP (broj ) vraća datum i vrijeme (tip TIMESTAMP). Na primjer, 2005-06-18 12:39:45.27. Cjelobrojni argument specificira preciznost druge reprezentacije.

Imajte na umu da su datum i vrijeme koje vraćaju ove funkcije tipa bez znakova. Ako želite da ih predstavite kao nizove znakova, trebali biste koristiti funkciju konverzije tipa CAST ().

Datetime funkcije se obično koriste u upitima za umetanje, ažuriranje i brisanje podataka. Na primjer, kada bilježite informacije o prodaji, stupac posebno predviđen za ovu svrhu sadrži trenutni datum i vrijeme. Nakon sumiranja rezultata za mjesec ili kvartal, podaci o prodaji za izvještajni period mogu se obrisati.

Izračunati izrazi

Računarski izrazi se grade od konstanti (numeričkih, string, logičkih), funkcija, imena polja i podataka drugih tipova povezujući ih aritmetičkim, stringovim, logičkim i drugim operatorima. Zauzvrat, izrazi se mogu kombinovati pomoću operatora u složenije (složene) izraze. Zagrade se koriste za kontrolu redoslijeda u kojem se izrazi evaluiraju.

Logički operatori I, ILI i NE i funkcije su ranije pregledani.

Aritmetički operatori:

• + dodatak;
• - oduzimanje;
• * množenje;
• / divizija.

String operatorsamo jedan operator konkatenacije ili konkatenacije (| |). Neke implementacije SQL-a (kao što je Microsoft Access) koriste znak (+) umjesto (| |). Operator konkatenacije dodaje drugi niz na kraj prvog primjera, izraz:

"Saša" | | "voli" | | "maša"

kao rezultat će vratiti string "Sasha voli Mašu".

Kada sastavljate izraze, morate osigurati da operandi operatora imaju važeće tipove. Na primjer, izraz: 123 + "Sasha" je nevažeći jer se aritmetički operator sabiranja primjenjuje na string operand.

Izračunati izrazi se mogu pojaviti nakon SELECT izraz, kao iu izrazima uvjeta klauzula WHERE i HAVI NG.

Pogledajmo nekoliko primjera.

Recimo da tabela Prodaja sadrži kolone Vrsta_stavke, Količina i Cijena, i želimo znati prihod za svaki tip artikla. Da biste to učinili, dovoljno je uključiti izraz Količina*Cijena u listu stupaca nakon naredbe SELECT:

SELECT Art_Type, Količina, Cijena, Količina*Cijena AS

Ukupno od prodaje;

Ovdje se ključna riječ AS (as) koristi za zamjenu kolone izračunatih podataka.

Na sl. 10 prikazuje originalnu tabelu prodaje i rezultujuću tabelu upita.

Rice. 10. Rezultat upita s izračunom prihoda za svaku vrstu proizvoda

Ako želite saznati ukupan prihod od prodaje svih proizvoda, onda samo primijenite sljedeći upit:

SELECT SUM (Količina*Cijena) IZ prodaje;

Sljedeći upit sadrži izračunate izraze iu listi kolona iu uvjetu klauzule WHERE. Iz prodajne tabele bira one proizvode čiji je prihod od prodaje veći od 1000:

SELECT Product_Type, Quantity*Cijena AS Total

IZ prodaje

GDJE Količina*Cijena > 1000;

Pretpostavimo da želite da dobijete tabelu sa dve kolone:

Proizvod koji sadrži vrstu i cijenu proizvoda;

Ukupni prihod.

Budući da se pretpostavlja da je u izvornoj tablici prodaje stupac Product_Type tip karaktera (CHAR tip), a stupac Cijena numerički, onda kada kombinujete (lijepite) podatke iz ovih kolona, ​​morate pretvoriti numerički tip u znak koristeći CAST () funkcija. Upit koji izvršava ovaj zadatak izgleda ovako (slika 11):

SELECT Vrsta_proizvoda | | " (Cijena: " | | CAST(Cijena KAO CHAR(5)) | | ")" AS Roba, Količina*Cijena AS Ukupno

IZ prodaje;

Rice. 11. Rezultat upita sa kombinovanjem heterogenih podataka u jednoj koloni

Bilješka. U Microsoft Accessu sličan upit bi izgledao ovako:

SELECT Product_Type + " (Cijena: " + C str (Cijena) + ")" AS Stavka,

Količina*AS Cijena Ukupno

IZ prodaje;

Uvjetni izrazi s naredbom CASE

U konvencionalnim programskim jezicima postoje operatori uslovnog skoka koji vam omogućavaju da kontrolišete proces računanja u zavisnosti od toga da li je određeni uslov ispunjen ili ne. U SQL-u, ovaj operator je CASE (slučaj, okolnost, slučaj). U SQL:2003, ovaj operator vraća vrijednost i stoga se može koristiti u izrazima. Ima dva glavna oblika o kojima ćemo govoriti u ovom odeljku.

CASE izraz s vrijednostima

Naredba CASE sa vrijednostima ima sljedeću sintaksu:

CASE vrijednost_testirano

WHEN vrijednost1 THEN rezultat1

KADA vrijednost2 THEN rezultat2

. . .

KADA vrijednost N THEN rezultat N

ELSE rezultatX

U slučaju kada value_tested jednako vrijednosti1 , naredba CASE vraća vrijednost rezultat1 navedeno nakon ključna riječ ONDA (to). Inače, test_value se uspoređuje sa vrijednost2 , a ako su jednaki, onda se vraća rezultat2. U suprotnom, vrijednost koja se provjerava uspoređuje se sa sljedećom vrijednošću specificiranom nakon ključne riječi WHEN itd. Ako vrijednost koja se provjerava nije jednaka nijednoj od ovih vrijednosti, tada će vrijednost rezultat X Navedeno iza ključne riječi ELSE (inače).

Ključna riječ ELSE nije obavezna. Ako je odsutan i nijedna od vrijednosti za usporedbu nije jednaka vrijednosti koja se testira, onda naredba CASE vraća NULL.

Pretpostavimo da na osnovu tabele Kupci (vidi sliku 1) želite da dobijete tabelu u kojoj su imena regiona zamenjena njihovim kodnim brojevima. Ako nema previše različitih regija u izvornoj tablici, onda je zgodno koristiti upit s operatorom CASE za rješavanje ovog problema:

SELECT Ime , Adresa ,

CASE Region

KAD "Moskva" ONDA "77"

KADA "Tver region" ONDA "69"

. . .

ELSE Region

AS Regionalni kod

OD klijenata;

CASE izraz s pojmovima za pretraživanje

Drugi oblik naredbe CASE uključuje njegovo korištenje za pretraživanje tablice za zapise koji zadovoljavaju određeni uvjet:

CASE

KADA uslov1 ONDA rezultat1

KADA uslov2 ONDA rezultat2

. . .

KADA uslov N ONDA rezultat N

ELSE rezultatX

Naredba CASE testira da li je uvjet1 istinit za prvi zapis u skupu definiranom klauzulom WHERE ili u cijeloj tablici ako ne postoji WHERE. Ako da, onda CASE vraća rezultat1. Inače, uslov2 se provjerava za ovaj unos. Ako je istina, onda se vraća rezultat2, itd. Ako nijedan od uvjeta nije istinit, onda se vraća rezultat. X Navedeno iza ključne riječi ELSE.

Ključna riječ ELSE nije obavezna. Ako je odsutan i nijedan od uvjeta nije ispunjen, naredba CASE rotira NULL. Nakon što se naredba koja sadrži CASE izvrši za prvi zapis, prelazi na sljedeći zapis. To se nastavlja sve dok se cijeli skup zapisa ne obradi.

Pretpostavimo da je u tabeli knjiga (naslov, cijena) stupac NULL ako odgovarajuća knjiga nije na zalihama. Sljedeći upit vraća tablicu koja prikazuje "Nema na zalihama" umjesto NULL:

SELECT Ime,

CASE

KADA JE CENA NULL ONDA "Nema na zalihama"

ELSE CAST(Cijena KAO CHAR(8))

AS Price

FROM Books;

Sve vrijednosti iste kolone moraju imati iste tipove. Stoga, u dati zahtjev koristi funkciju konverzije tipa CAST za pretvaranje numeričkih vrijednosti stupca Cijena u tip znakova.

Imajte na umu da uvijek možete koristiti drugi oblik naredbe CASE umjesto prvog obrasca:

CASE

KADA vrijednost_testirana = vrijednost1 ONDA rezultat1

KADA vrijednost_testirana = vrijednost2 ONDA rezultat2

. . .

KADA vrijednost_testirana = vrijednost N ONDA rezultat

ELSE rezultat

NULLIF i COALESCE funkcije

U nekim slučajevima, posebno u upitima za ažuriranje podataka (naredba UPDATE), zgodno je koristiti kompaktnije funkcije NULLIF () (NULL if) i COALESCE () (kombiniraj) umjesto glomaznog izraza CASE.

NULLIF funkcija( vrijednost1, vrijednost2) vraća NULL ako vrijednost prvog parametra odgovara vrijednosti drugog parametra, u suprotnom vrijednost prvog parametra se vraća nepromijenjena. To jest, ako je jednakost value1 = value2 istinita, tada funkcija vraća NULL, u suprotnom vrijednost vrijednost1.

Ova funkcija je ekvivalentna naredbi CASE u sljedeća dva oblika:

• CASE vrijednost1

WHEN value2 THEN NULL

ELSE vrijednost1

• CASE

KADA vrijednost1 = vrijednost2 THEN NULL

ELSE vrijednost1

funkcija COALESCE( vrijednost1, vrijednost2, ... , N vrijednost) prihvaća listu vrijednosti koja može biti definirana ili null (NULL). Funkcija vraća određenu vrijednost sa liste, ili NULL ako su sve vrijednosti nedefinirane.

Ova funkcija je ekvivalentna sljedećoj izjavi CASE:

CASE

KADA vrijednost 1 NIJE NULL ONDA vrijednost 1

KADA vrijednost 2 NIJE NULL ONDA vrijednost 2

. . .

KADA vrijednost N NIJE NULL ONDA vrijednost N

ELSE NULL

Pretpostavimo da je u tabeli Knjige (naslov, cijena) kolona Cijena NULL ako odgovarajuća knjiga nije na zalihama. Sljedeći upit vraća tablicu gdje umjesto NULL prikazuje se tekst "Nema na zalihama":

ODABIR NAZIV, SPAJANJE (CAST(Cijena KAO CHAR(8)),

"Nema na skladištu") AS Cijena

FROM Books;

Kako mogu saznati broj modela PC-a koje proizvodi određeni proizvođač? Kako odrediti prosječnu cijenu računara koji imaju istu specifikacije? Na ova i mnoga druga pitanja u vezi sa nekim statističkim podacima može se odgovoriti uz pomoć zbirne (agregatne) funkcije. Standard pruža sljedeće agregatne funkcije:

Sve ove funkcije vraćaju jednu vrijednost. Istovremeno, funkcije COUNT, MIN i MAX primijeniti na bilo koji tip podataka, dok SUMA i AVG koristi se 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 količina razni modeli proizveden od strane proizvođača A, onda se upit može formulirati na sljedeći način (koristeći činjenicu da je svaki model jednom zabilježen u tablici proizvoda):

Primjer. Pronađite broj dostupnih različitih modela proizvođača A. Upit je sličan prethodnom, u kojem je bilo potrebno odrediti ukupan broj modela proizvedenih od strane proizvođača A. Ovdje je potrebno pronaći broj različitih modela u PC tablici (tj. dostupno za prodaju).

Kako bi se osiguralo da se pri dobijanju statističkih pokazatelja koriste samo jedinstvene vrijednosti, kada argument agregatne funkcije može biti korišteno DISTINCT parametar. Drugi parametar SVE je zadana vrijednost i očekuje da će brojati sve povratne vrijednosti u stupcu. operater,

Ako trebamo dobiti broj PC modela proizvedenih od strane svaki proizvođača, morat ćete koristiti GROUP BY ponuda, sintaktički nakon WHERE klauzule.

GROUP BY ponuda

GROUP BY ponuda koristi se za definiranje grupa izlaznih linija 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. U suprotnom, sve kolone SELECT liste, nisu uključeni u agregatne funkcije, mora biti specificirano u klauzuli GROUP BY. Kao rezultat toga, svi izlazni redovi 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 a ako postoji klauzula GROUP BY, u klauzuli SELECT nema agregatnih funkcija, onda će upit jednostavno vratiti jedan red iz svake grupe. Ova funkcija, zajedno s ključnom riječi DISTINCT, može se koristiti za eliminaciju duplih redova u skupu rezultata.
Razmotrite jednostavan primjer:

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

U ovom upitu se za svaki model računara utvrđuje njihov broj i prosječna cijena. Sve linije sa iste vrijednosti model (broj modela) formiraju grupu, a SELECT izlaz izračunava broj vrijednosti i vrijednosti prosječne 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

Da postoji kolona sa datumom u SELECT, 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 izvršavanje agregatnih funkcija:

• Ako kao rezultat zahtjeva nije primljen nijedan red(ili više od jednog reda za ovu grupu), tada nema početnih podataka za izračunavanje bilo koje od agregatnih funkcija. U ovom slučaju, rezultat izvršavanja COUNT funkcija bit će 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 zahtjevu 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 obrađenih vrijednosti.
• Ako prilikom izvršavanja SUM funkcije dobijen je rezultat koji premašuje maksimalnu vrijednost tipa podataka koji se koristi, a greška.

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

HAVING ponudu

Ako a WHERE klauzula zatim definira predikat za filtriranje nizova HAVING klauzula primijenjen nakon grupisanja za definiranje sličnih grupa filtriranja predikata po vrijednostima agregatne funkcije. Ova klauzula je potrebna za validaciju vrijednosti koje su dobivene pomoću agregatna funkcija ne iz zasebnih redova izvora zapisa definiranog u FROM klauzula, i od grupe takvih linija. Stoga takva provjera ne može biti sadržana u WHERE klauzula.

U tome studijski vodič naučićete kako da koristite Funkcija SUM in SQL Server(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 sintaksu funkcije SUM kada grupišete rezultate po jednoj ili više kolona:

Opcije 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.
aggregate_expression je stupac ili izraz koji će se zbrojiti.
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 jednog polja

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

Na primjer, možete saznati kako je ukupan broj svih proizvoda čiji je broj veći od 10.

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

DISTINCT primjer

Možete koristiti izraz DISTINCT u funkciji SUM. Na primjer, SQL naredba u nastavku vraća ukupnu plaću s jedinstvenim vrijednostima plaće gdje je plata manja od 29.000 USD 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 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 ;

GROUP BY primjer

U nekim slučajevima, morat ćete koristiti klauzulu GROUP BY s funkcijom SUM.

SQL - Lekcija 11. Funkcije sažetka, izračunati stupci i pogledi

Konačne funkcije se također nazivaju statističkim, agregatnim ili sumiranjem. Ove funkcije obrađuju skup redova za brojanje i vraćanje jedne vrijednosti. Postoji pet takvih funkcija:

• AVG() Funkcija 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.

Jedan od njih - COUNT() - već smo upoznali u lekciji 8. Sad hajde da upoznamo ostale. Pretpostavimo da želimo znati minimalnu, maksimalnu i prosječnu cijenu knjiga u našoj radnji. Zatim iz tabele Cijene (cijene) 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 za koliko nam je dobavljač "Štamparija" (id=2) doneo robu. Podnošenje takvog zahtjeva nije lako. Hajde da razmislimo kako da to napravimo:

1. Prvo treba da izaberete identifikatore (id_incoming) onih isporuka koje je izvršio dobavljač „Štamparije“ (id=2) iz tabele Isporuke (dolazne):

2. Sada iz tabele Dnevnik nabavke (magazine_incoming) treba da izaberete robu (id_product) i njene količine (količina), koje su izvršene u isporukama koje se nalaze u stavu 1. Odnosno, zahtjev iz tačke 1 postaje ugniježđen:

3. Sada trebamo dodati u rezultujuću tabelu cijene za pronađenu robu, koje su pohranjene u tablici Cijene. Odnosno, trebamo spojiti tabele Supply journal (magazine_incoming) i Prices (prices) kolonom id_product:

4. U rezultujućoj tabeli kolona Zbir jasno nedostaje, 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, takva kolona će se zvati suma, i ona će izračunati 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, ostaje nam samo da sumiramo rubriku suma i konačno saznamo za koliko nam je dobavljač „Štamparija“ donio 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 KAO upit;

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

KREIRAJTE VIEW 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_incoming= (SELECT id_incoming_incoming_FROM id_incoming_in );

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

SELECT SUM(suma) FROM report_vendor;

Ovdje 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 dobili rezultat, bez toga 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)

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

SELECT col_name_1, col_name_2, col_name_1*col_name_2 AS computed_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č postavlja pseudonime za bilo koju kolonu. Zašto je ovo 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= (ODABIR id_incoming IZ dolaznog WHERE id_vendor=2);

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

Zastupanje

Već smo razmotrili sintaksu za kreiranje pogleda. Jednom kada se kreiraju pogledi, mogu se koristiti na isti način kao i tabele. Odnosno, vršite upite na njima, filtrirajte i sortirajte podatke, kombinirajte jedan pogled s drugim. S jedne strane, ovo je veoma zgodan način skladištenje često korištenih složenih upita (kao u našem primjeru).

Ali treba imati na umu da pogledi nisu tabele, odnosno ne pohranjuju podatke, već ih samo preuzimaju iz drugih tabela. Dakle, prvo, prilikom promjene podataka u tabelama, promijenit će se i rezultati prezentacije. I drugo, kada se traži pregled, traže se potrebni podaci, odnosno smanjuje se performansa DBMS-a. Stoga ih ne treba zloupotrebljavati.

Naučimo da sumiramo. Ne, ovo nisu rezultati učenja SQL-a, već rezultati vrijednosti stupaca tablica baze podataka. Agregatne funkcije SQL radi na vrijednostima stupca kako bi proizveo jednu vrijednost rezultata. Najčešće korišteni agregat SQL funkcije SUM, MIN, MAX, AVG i COUNT. Postoje dva slučaja u kojima treba koristiti agregatne funkcije. Prvo, agregatne funkcije se koriste same i vraćaju jednu vrijednost rezultata. Drugo, agregatne funkcije se koriste sa SQL GROUP BY klauzulom, odnosno s grupiranjem po poljima (kolonama) kako bi se dobile rezultirajuće vrijednosti u svakoj grupi. Razmotrimo prvo slučajeve korištenja agregatnih funkcija bez grupiranja.

SQL SUM funkcija

SQL SUM funkcija vraća zbroj vrijednosti stupca u tablici 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 (NAZIV STUPCA) ...

Nakon ovog izraza slijedi FROM (TABLE_NAME), a zatim se uvjet može specificirati pomoću klauzule WHERE. Osim toga, DISTINCT može biti prefiksiran imenu kolone kako bi se naznačilo da će se uzeti u obzir 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 je opcionalna).

Ako želite da pokrenete upite baze podataka iz ove lekcije na MS SQL Serveru, ali ovaj DBMS nije instaliran na vašem računalu, možete ga instalirati koristeći upute 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 odeljenjima i zaposlenima. Tabela Osoblje takođe ima kolonu sa podacima o platama zaposlenih. Izbor iz tabele ima sledeći oblik (za uvećanje slike kliknite na nju levim tasterom miša):

Za dobijanje sume svih plata koristimo sledeći upit (na MS SQL Server - sa prefiksom USE firma1;):

ODABERITE ZUM (Platu) IZ osoblja

Ovaj upit će vratiti vrijednost 287664.63.

I sada . U vježbama već počinjemo da komplikujemo zadatke, približavajući ih onima koji se susreću 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.

Potrebno je saznati minimalnu plaću zaposlenih u odjeljenju broj 42. Da bismo to učinili, pišemo sljedeći upit (na MS SQL Server - sa prefiksom USE company1;):

Upit će vratiti vrijednost 10505.90.

I opet vježba za samoopredjeljenje. 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 divizijama kompanije. Navedite minimalni broj godina koje je jedan zaposlenik radio u odjelu koji se nalazi u Bostonu.

SQL MAX funkcija

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

Primjer 5

Potrebno je saznati maksimalnu platu zaposlenih u odjeljenju broj 42. Da bismo to učinili, pišemo sljedeći upit (na MS SQL Server - sa prefiksom USE company1;):

Upit će vratiti vrijednost 18352.80

Sada je vrijeme vježbe za samoopredjeljenje.

Primjer 6 Opet radimo sa dva stola - Staff i Org. Prikaz naziva odjeljenja i maksimalnog iznosa provizija koje primi jedan zaposlenik u odjelu koji pripada grupi odjela (Odjeljenje) Istočni. Koristi JOIN (spajanje tablica) .

SQL AVG funkcija

Ono što je rečeno o sintaksi 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.

Neka je potrebno saznati prosječan radni staž zaposlenih u odjeljenju broj 42. Da bismo to učinili, pišemo sljedeći upit (na MS SQL Server - sa prefiksom USE company1;):

Rezultat će biti 6,33

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

SQL COUNT funkcija

SQL COUNT funkcija vraća broj zapisa u tablici baze podataka. Ako navedete SELECT COUNT(COLUMNAME) ... u upitu, tada će rezultat 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 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 stupca Comm nisu NULL vratit će sljedeći upit (na MS SQL Serveru - sa USE firmom1; konstrukt koji se nalazi ispred):

SELECT COUNT (Comm) OD osoblja

Rezultat će biti vrijednost 11.

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

Ako trebate saznati ukupan broj zapisa u tabeli, onda koristimo upit sa zvjezdicom kao argument funkciji COUNT (na MS SQL Serveru - s prefiksom USE company1;):

ODABIR BROJ (*) IZ osoblja

Rezultat će biti vrijednost 17.

Sljedeći vježba za samoopredjeljenje morate koristiti potupit.

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

Agregatne funkcije sa SQL GROUP BY

Pogledajmo sada korištenje agregatnih funkcija zajedno sa SQL GROUP BY klauzulom. SQL GROUP BY klauzula se koristi za grupiranje rezultirajućih vrijednosti po stupcima u tablici baze podataka. Stranica ima lekcija posvećena ovom operateru posebno .

Radićemo sa bazom podataka "Portal najava 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 tablicu Oglasi koja sadrži podatke o oglasima koji su poslani 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 apartmana i vile su dijelovi kategorije Nekretnine). Kolona Jedinice sadrži podatke o broju dostavljenih oglasa, a kolona Novac iznos novca zarađen za slanje oglasa.

Kategorija	dio	Jedinice	Novac
Transport	motorna vozila	110	17600
Nekretnina	Apartmani	89	18690
Nekretnina	Dachas	57	11970
Transport	Motocikli	131	20960
građevinski materijal	Ploče	68	7140
elektrotehnike	televizori	127	8255
elektrotehnike	Frižideri	137	8905
građevinski materijal	Regips	112	11760
Slobodno vrijeme	Knjige	96	6240
Nekretnina	Kuće	47	9870
Slobodno vrijeme	Muzika	117	7605
Slobodno vrijeme	Igre	41	2665

Koristeći klauzulu SQL GROUP BY, pronađite količinu novca generiranu slanjem oglasa u svakoj kategoriji. Pišemo sljedeći upit (na MS SQL Serveru - sa prefiksom 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 je dio svake kategorije upisan najveći broj oglasi. Pišemo sljedeći upit (na MS SQL Serveru - sa prefiksom 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 i SQL jezik