Sql funkcije agregacije. SQL agregatne funkcije - SUM, MIN, MAX, AVG, COUNT

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 na neki način sažima tu kolonu.

Na primjer, AVG() agregatna funkcija 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 PRIHODI=', AVG(SUMD)

SQL ima šest agregatnih funkcija koje vam omogućavaju da dobijete različite vrste sažetih informacija (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 stupcu;

– COUNT() broji broj vrijednosti sadržanih u stupcu;

– COUNT(*) broji broj redova u tabeli rezultata upita.

Argument agregatne funkcije može biti jednostavno ime stupca, kao u prethodnom primjeru, ili izraz, kao u sljedećem upitu koji specificira obračun poreza 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 dobivenu spajanjem nekoliko izvornih tablica. Na primjer, možete izračunati ukupan iznos prihoda koji su stanovnici primili iz izvora pod nazivom "Stipendija":

ODABIR SUM(NOVAC)

OD PROFITA, HAVE_D

WHERE PROFIT.ID=IMAM_D.ID

AND PROFIT.SOURCE='Stipendija'

Agregatne funkcije MIN() i MAX() omogućavaju vam da pronađete najmanju i najveću vrijednost u tabeli. 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 koji primaju rezidenti i najveći porez koji se plaća:

ODABIR MIN(SUMD), MAX(SUMD*0,13)

(b) datume rođenja najstarijeg i najmlađeg stanara:

ODABIR MIN (DATUM), MAX (DATUM)

(c) prezimena, imena i patronimika prvih i najnovijih stanovnika na listi, 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 funkcije MIN() i MAX() sa nizovima podataka, 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 specificira da se vrijednosti koje se ne ponavljaju u koloni broje).

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 Ivanov Ivan Ivanovič?

SELECT COUNT(*) FROM PERSON, HAVE_D WHERE FIO="Ivanov Ivan Ivanovich" I PERSON.NOM=HAVE_D.NOM

(c) koliko ljudi živi u stanu na određenoj adresi?

SELECT COUNT(*) FROM PERSON WHERE ADR="Zelenograd, 1001-45"

Jedan od načina da se shvati kako se izvode zbirni upiti sa agregatnim funkcijama je da zamislite izvršenje upita kao podijeljeno na 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 AVG(SUMD), SUM(SUMD), (100*AVG(NOVAC/SUMD)) IZ OSOBE, PROFIT, IMAM_D GDJE PERSON.NOM=IMA_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=IMA_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 tablice rezultata upita da proizvedu tablicu u jednom redu sa rezimeom rezultata.

Možete navesti agregatnu funkciju u vraćenom nizu stupaca umjesto imena bilo kojeg stupca. Na primjer, može biti dio izraza 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.

Također, ne možete koristiti agregatne funkcije i regularna imena kolona u isto vrijeme na listi vraćenih kolona, ​​jer to nema smisla, na primjer:

ODABERITE FIO, SUM(SUMD) OD OSOBE

Ovdje prvi element liste govori DBMS-u da kreira tabelu koja će se sastojati od nekoliko redova i sadržavati jedan red za svakog stanovnika. Drugi element liste traži od DBMS-a da vrati jednu vrijednost rezultata, koja je zbir vrijednosti u SUMD koloni. Ova dva smjera su u suprotnosti, što rezultira greškom.

Gore navedeno se ne odnosi na slučajeve obrade podupita i upita sa grupisanjem.

Korištenje agregatnih funkcija

SQL definira mnoge ugrađene funkcije različitih kategorija, među kojima posebno mjesto zauzimaju agregatne funkcije koje rade na vrijednostima stupaca više redova i vraćaju jednu vrijednost. Argumenti agregatnih funkcija mogu biti i kolone tabela i rezultati izraza nad njima. Agregatne funkcije mogu same biti uključene u druge aritmetičke izraze. Sljedeća tabela navodi najčešće korištene standardne unarne agregatne funkcije.

Opći format unarne agregatne funkcije je sljedeći:

function_name([SVE | DISTINCT] izraz)

gdje DISTINCT specificira da funkcija treba uzeti u obzir samo različite vrijednosti argumenta, a ALL specificira sve vrijednosti, uključujući duplikate (ovo je zadana postavka). Na primjer, funkcija AVG sa ključnom riječi DISTINCT za redove stupaca sa vrijednostima 1, 1, 1 i 3 će vratiti 2, ali s ključnom riječi ALL vraća 1,5.

Agregatne funkcije se koriste u klauzulama SELECT i HAVING. Ovdje ćemo pogledati njihovu upotrebu u klauzuli SELECT. U ovom slučaju, izraz u argumentu funkcije primjenjuje se na sve redove ulazne tablice klauzule SELECT. Također, ne možete koristiti i agregatne funkcije i stupce tablice (ili izraze s njima) u SELECT klauzuli osim ako ne postoji GROUP BY klauzula, koju ćemo pogledati u sljedećem odjeljku.

Funkcija COUNT ima dva formata. U prvom slučaju vraća se broj redova ulazne tablice, u drugom slučaju broj vrijednosti argumenata u ulaznoj tablici:

• COUNT(*)
• COUNT( izraz)

Najjednostavniji način korištenja ove funkcije je prebrojavanje broja redova u tablici (bilo svih ili onih koji ispunjavaju dati uvjet). Za to se koristi prva varijanta sintakse.

Upit: Broj vrsta proizvoda, informacije o kojima su dostupne u bazi podataka.

SELECT COUNT(*) AS "Broj proizvoda"

IZ proizvoda

U drugoj varijanti sintakse funkcije COUNT, ime jedne kolone može se koristiti kao argument. U ovom slučaju se računa broj ili svih vrijednosti u ovoj koloni ulazne tablice ili samo onih koje se ne ponavljaju (kada se koristi ključna riječ DISTINCT).

Upit: Broj različitih imena sadržanih u tabeli Kupci.

ODABIR BROJ (DISTINCT FNAME)

OD kupca

Upotreba ostalih unarnih agregatnih funkcija slična je COUNT, osim što za funkcije MIN i MAX upotreba ključnih riječi DISTINCT i ALL nema smisla. Uz funkcije COUNT, MAX i MIN, pored numeričkih polja mogu se koristiti i znakovna polja. Ako argument agregatne funkcije ne sadrži nikakve vrijednosti, funkcija COUNT vraća 0, a sve ostale vraćaju NULL.

SELECT MAX(OrdDate)

OD

WHERE OrdDate"1.09.2010"

Zadatak za samostalan rad: Formulirati upite u SQL jeziku za odabir sljedećih podataka:

• Ukupni trošak svih narudžbi;
• Broj različitih gradova sadržanih u tabeli kupaca.

• Agregatne funkcije se koriste kao imena polja u SELECT izrazu, s jednim izuzetkom: uzimaju ime polja kao argument. Sa karakteristikama SUMA i AVG mogu se koristiti samo numerička polja. Sa karakteristikama 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 njihov ASCII ekvivalent i obraditi ih po abecednom redu. Neki DBMS dozvoljavaju ugniježđene agregate, ali ovo je odstupanje od ANSI standarda, sa svim njegovim implikacijama.

Na primjer, možete izračunati broj studenata koji su polagali ispite u svakoj disciplini. Da biste to učinili, potrebno je izvršiti upit grupiran po polju "Subject" i kao rezultat prikazati naziv discipline i broj redova u grupi za ovu disciplinu. Upotreba znaka * kao argumenta funkciji COUNT znači brojati sve redove u grupi.

SELECT R1. Disciplina, COUNT(*)

GRUPA PO R1 Disciplina;

rezultat:

SELECT R1.Disciplina, COUNT (*)

GDJE R1. Evaluacija NIJE NULL

GRUPA PO R1 Disciplina;

rezultat:

neće upasti u skup torki prije grupisanja, pa će broj torki u grupi za disciplinu "Teorija informacija" biti 1 manji.

Sličan rezultat se može dobiti ako se upit napiše na sljedeći način:

SELECT R1. Disciplina, COUNT(R1. Rezultat)

GRUPA PO R1. Disciplina;

Funkcija COUNT (NAZIV ATRIBUTA) broji broj definiranih vrijednosti u grupi, za razliku od funkcije COUNT(*), koji broji broj redova u grupi. Zaista, u grupi sa disciplinom "Teorija informacija" bit će 4 reda, ali samo 3 određene vrijednosti atributa "Evaluacija".

Pravila za rukovanje NULL vrijednostima u agregatnim funkcijama

Ako su bilo koje vrijednosti u koloni jednake NULL kada se računa rezultat funkcije, oni su isključeni.

Ako su sve vrijednosti u koloni jednake NULL, onda Maks. Min. zbroj pros = NULL , count = 0 (nula).

Ako je tabela prazna, count(*) = 0 .

Agregatne funkcije se mogu koristiti i bez operacije prethodnog grupisanja, u kom slučaju se cijela relacija tretira kao jedna grupa, a jedna vrijednost po grupi se može izračunati za ovu grupu.

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(Score) iz R1će dati zbir svih ocjena za sesiju;

SELECT AVG(Score) iz R1će dati prosječnu ocjenu tokom cijele sesije.

2; Rezultat: "width="640"

Ponovo se pozivajući na bazu podataka „Session“ (tabele R1), nalazimo broj uspješno položenih ispita:

ODABIR BROJ(*) Iznajmljeno _ ispiti

WHERE rezultat 2;

rezultat:

Agregatne funkcije mogu uzeti pojedinačne stupce tablica kao argumente. Da bi se izračunao, na primjer, broj različitih vrijednosti za određenu kolonu u grupi, ključna riječ DISTINCT mora se koristiti zajedno s nazivom kolone. Izračunajmo broj različitih ocjena dobijenih u svakoj disciplini:

SELECT R1.Discipline, COUNT (DISTINCT R1.Score)

GDJE R1. Evaluacija NIJE NULL

GRUPA PO R1 Disciplina;

rezultat:

Isti rezultat se dobija ako se isključi eksplicitni uslov u dijelu WHERE, u kom slučaju će upit izgledati ovako:

SELECT R1. Disciplina, COUNT(DISTINCT R1. Score)

GRUPA PO R1. Disciplina;

Funkcija COUNT(RAZLIČITI R1.Score) računa se samo izvesno razne vrijednosti.

Da bi se u ovom slučaju dobio željeni rezultat, potrebno je izvršiti preliminarnu konverziju tipa podataka kolone “Score”, dovodeći je 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 stupac Score u važeći tip podataka. "width="640"

Odaberite R2.Grupa, R1.Subject,Count(*) kao Total, AVG(cast(Score as decimal(3,1))) kao Average_Score

Od R1,R2

gdje je R1. Puno ime = R2. Puno ime i R1. evaluacija nije ništavna

i R1. Razred 2

Grupirajte po R2. Grupa, R1. Disciplina;

Evo funkcije CAST() 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, dok se agregatne funkcije 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 kako u izlaznom izrazu rezultata SELECT reda, tako iu izrazu uslova za obradu formiranih HAVING grupa. U ovom slučaju, svaka agregatna funkcija se izračunava za svaku odabranu grupu. Vrijednosti dobivene u proračunu agregatnih funkcija mogu se koristiti za prikaz odgovarajućih rezultata ili za uvjete odabira grupe.

Napravimo upit koji prikazuje grupe u kojima je primljeno više od jedne dvojke u jednoj disciplini na ispitima:

jedan; Rezultat: "width="640"

SELECT R2. Grupa

OD R1, R2

GDJE R1. Puno ime = R2. Puno ime AND

R1.Score = 2

GRUPA PO R2.Grupa, R1.Disciplina

IMATI broj(*) 1;

rezultat:

Imamo bazu podataka "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 na računima u filijalama. Možete napraviti poseban upit za svaku od njih odabirom SUM (Remainder) 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 redova sa istom vrijednošću u polju 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, baš kao rezultat agregatne funkcije.

5000; Argumenti u klauzuli HAVING slijede ista pravila kao u klauzuli SELECT gdje se koristi GROUP BY. Moraju imati jednu vrijednost po izlaznoj grupi. "width="640"

Pretpostavimo da odaberete samo one filijale čija su ukupna stanja na računu veća od 5.000 USD, kao i ukupna stanja za odabrane filijale. Za prikaz filijala sa ukupnim stanjem preko 5.000 USD, koristite klauzulu HAVING. Klauzula HAVING specificira 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:

SELECT Ogranak, SUM (Stanje)

GROUP BY Filijala

IMATI SUMU ( Ostatak ) 5 000;

Argumenti u rečenici HAVING podliježu istim pravilima kao u prijedlogu SELECT gdje se koristi GROUP BY. Moraju imati jednu vrijednost po izlaznoj grupi.

Sljedeća komanda će biti zabranjena:

SELECT Ogranak,SUM(Stanje)

GRUPA PO Ogranku

IMAJUĆI Datum otvaranja = 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 ova situacija, prijedlog HAVING treba da se odnosi samo na agregate i odabrana polja GROUP BY. Postoji ispravan način da se uputi gornji zahtjev:

SELECT Ogranak,SUM(Stanje)

WHERE OpenDate = '27/12/2004'

GROUP BY Ogranak;

Značenje ovog upita je sljedeće: pronaći zbir stanja za svaku filijalu računa otvorenih 27. decembra 2004. godine.

Kao što je ranije rečeno, HAVING može uzeti samo argumente koji imaju jednu vrijednost po izlaznoj grupi. U praksi, reference na agregatne funkcije su najčešće, ali polja odabrana pomoću GROUP BY su također važeća. Na primjer, želimo vidjeti ukupna stanja na računima filijala u Sankt Peterburgu, Pskovu i Uryupinsku:

SELECT Ogranak, SUM (Stanje)

OD F,Q

GDJE F. Grana = Q. Grana

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 odnosu, inače ćemo dobiti praznu relaciju. "width="640"

Stoga, u aritmetičkim izrazima predikata uključenih u klauzulu za odabir klauzule HAVING, mogu se direktno koristiti samo specifikacije stupaca specificiranih kao grupiranje stupaca u klauzuli GROUP BY. Preostale kolone se mogu 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 sekcije HAVING je grupisana tabela koja sadrži samo one grupe redova za koje je rezultat izračunavanja 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 tablica ili rezultat izvršenja prethodnih dijelova izraza tablice, tretiran kao jedan grupa bez grupisanja kolona. Razmotrimo primjer. Recimo da želimo prikazati ukupan iznos stanja za sve grane, ali samo ako je veći od 100 000 USD. U ovom slučaju, naš upit neće sadržavati operaciju grupisanja, već će sadržavati odjeljak HAVING i izgledat će ovako:

ODABIR SUM( Ostatak )

IMATI SUM( Ostatak ) 100 000;

Ako je ukupni saldo veći od 100.000$, tada ćemo ga vidjeti u rezultirajućem odnosu, inače ćemo dobiti praznu relaciju.

Oni mogu izvršiti generaliziranu grupnu obradu vrijednosti polja. Ovo se radi pomoću agregatnih funkcija. Agregatne funkcije proizvode jednu vrijednost za cijelu grupu tablica. V SQL Obezbeđene su sledeće agregatne funkcije:

• COUNT– broji broj redova tablice s vrijednostima koje nisu NULL u polju navedenom kao argument.
• SUMA– izračunava aritmetički zbir svih odabranih vrijednosti datog polja.
• AVG– prosječuje sve odabrane vrijednosti datog polja.
• MAX- Prikazuje najveću vrijednost od svih odabranih vrijednosti datog polja.
• MIN– Prikazuje najmanju vrijednost od svih odabranih vrijednosti u datom polju.

  Korištenje agregatnih funkcija

  Agregatne funkcije se koriste kao imena polja u rečenici SELECT upit, ali sa jednim izuzetkom: oni uzimaju imena polja kao argument. Mogu se koristiti samo numerička polja SUMA i AVG. WITH 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 njihov ASCII ekvivalent. To znači da MINće izabrati prvu, i MAX zadnja vrijednost po abecednom redu.

  Da bismo pronašli ukupan iznos prodaje u tabeli prodaje, moramo napisati sljedeći upit:

  SELECT SUM(SSum) IZ Prodaje

  Kao rezultat, dobijamo:

  Ovaj upit je izbrojao broj nepraznih vrijednosti u polju SNum tabele Sells. Ako prepišemo upit na sljedeći način:

  SELECT COUNT(SDate) FROM Sells

  Tada kao rezultat dobijamo:

  COUNT OF SDate

  4

  Različiti rezultati upita pri izračunavanju naizgled iste stvari dobijaju se jer jedna od vrijednosti polja SDate ima praznu vrijednost ( NULL). Budite oprezni kada koristite takve zahtjeve.