Functii de grup

Intr-un capitol anterior am discutat despre functiile singulare, adica despre functiile care opereaza la un moment dat asupra unei singure inregistrari.

Este acum momentul sa discutam despre functiile de grup, care returneaza o singura valoare pentru un grup sau set de linii dintr-un tabel. Puteti calcula cea mai mare valoare dintr-un set de valori, puteti determina numarul de inregistrari ce respecta o anumita conditie etc.

Pentru exemplificarea acestor functii vom folosi tabela VOTURI si tabela JUDETE care contin urmatoarele date[1] .

Tabelul II.4.1. Tabela VOTURI

Tabelul II.4.2. Tabela JUDETE

Vom prezenta in continuare principalele functii de grup.

COUNT(x) - determina numarul de valori ale lui x. Functia, ca de altfel toate functiile de grup ignora campurile completate cu NULL, adica va numara doar valorile nenule ale lui x.

De exemplu, comanda

SELECT COUNT(JUDET), COUNT(numar_voturi)

FROM voturi

va afisa numarul total de inregistrari din tabela, (campul JUDET nu are nici o valoare NULL) precum si numarul de linii pentru care campul numar_voturi este nenul, adica , ultimele doua linii din tabel avand valoare null in campul numar_voturi

Tabelul II.4.3.

Functia COUNT poate fi folosita in combinatie cu clauza DISTINCT, pentru a numara doar valorile distincte dintr-un domeniu. De exemplu daca dorim sa stim pentru cate judete avem rezultatele votarii in tabela noastra, vom folosi comanda:

SELECT count(distinct judet)

FROM voturi

Se va obtine valoarea , intrucat avem doar judete inregistrate (Bucuresti, Iasi, Sibiu).

Tabelul II.4.4.

Sa vedem inca un exemplu

SELECT count(distinct candidat), count(candidat)

FROM voturi

Evident primul apel de functie afiseaza valoarea , deoarece exista candidati pentru care au fost exprimate voturi, iar a doua comanda afiseaza valoarea , adica exact numarul de linii din tabel deoarece toate liniile au completat campul candidat

Tabelul II.4.5.

MAX(x) - determina valoarea maxima a valorilor expresiei x

Sa vedem de exemplu cum putem afla care este cel mai numar de voturi exprimate pentru un candidat intr-un judet.

SELECT MAX(numar_voturi)

FROM voturi

Tabelul II.4.6.

Se poate observa pe tabelul cu datele din tabela voturi ca acest maxim a fost obtinut in Bucuresti de catre candidatul avand codul

Totusi aceasta informatie nu este foarte relevanta pentru ca si populatia din Bucuresti este mult mai mare decat in celelalte judete. Ar trebui sa putem determina numarul de voturi primite de catre un candidat raportat la numarul de alegatori (persoane cu drept de vot). SQL ne permite sa aplicam functiile de grup nu doar pe campuri din baza de date ci si pe expresii, ca in exemplul urmator

SELECT max(100*numar_voturi/numar_alegatori)

FROM voturi v, judete j

WHERE v.judet=j.cod_judet

Tabelul II.4.7.

Prin aceasta comanda am obtinut cel mai mare procent de voturi obtinut de catre un candidat intr-un judet. Acest procent a fost obtinut raportat la totalul persoanelor cu drept de vot si a fost obtinut de catre candidatul cu codul 1 in judetul Iasi

SELECT 100*numar_voturi/numar_alegatori,

j.judet, v.candidat

FROM voturi v, judete j

WHERE v.judet=j.cod_judet

Tabelul II.4.8.

In acest moment nu stim inca sa scriem o comanda pentru a afisa judetul si candidatul pentru care s-a obtinut valoarea maxima, dar vom afla cum realizam acest lucru in capitolul urmator.

MIN(x) - determina valoarea minima a valorilor expresiei x

SUM(x) - determina suma valorilor expresiei x

Cum aflam oare numarul total de voturi valabil exprimate in judetul Sibiu?  Foarte simplu:

SELECT sum(numar_voturi)

FROM voturi

WHERE judet='SB'  Tabelul II.4.9.

AVG(x) - determina media valorilor expresiei x. De exemplu, putem afla procentul mediu obtinut un candidat in toate judetele

SELECT avg(100*numar_voturi/numar_alegatori)

FROM voturi v, judete j

WHERE (candidat=12) and

(v.judet=j.cod_judet)

Comanda afiseaza media procentelor obtinute in fiecare judet de catre candidatul cu codul

Tabelul II.4.10.

Am dori sa afisam un tabel cu procentele obtinute de toti candidatii, insa vom vedea cum realizam acest lucru intr-un paragraf urmator.

Dupa cum am precizat la functia COUNT, functiile de grup, deci si AVG ignora valorile NULL. Asadar daca vom rula comanda:

SELECT avg(numar_voturi)

FROM voturi

WHERE candidat=13

vom obtine valoarea , desi in baza de date exista 3 linii pentru candidatul , si doar o linie are completat campul numar_voturi cu valoarea . Daca dorim sa obtinem valoarea , adica , vom scrie:

SELECT AVG(NVL(numar_voturi,0))

FROM voturi

WHERE candidat=13

adica inlocuim valorile null cu valoarea , pentru ca acestea sa intre in calculul mediei.

STDEV(x) - functie statistica definita ca fiind abaterea patratica a expresiei date. Cu cat valoarea functiei este mai mica cu atat valorile expresiei x sunt mai apropiate de medie.

VARIANCE(x) - este o functie statistica care calculeaza dispersia expresiei x. Se defineste ca patratul abaterii medii patratice.

Observatie. Functiile COUNT MIN MAX pot fi aplicate si datelor de tip sir de caractere sau data calendaristice, celelalte functii fiind aplicabile doar valorilor numerice.

De exemplu comanda urmatoare va afisa data celei mai vechi angajari, data celei mai recente angajari, numarul de date de angajare, si numarul de date distincte de angajare din tabela employees

select min(hire_date), max(hire_date),

count(distinct hire_date), count(hire_date)

from employees

Tabelul II.4.11.