5. Functii de grup

Functii de grup

Functiile de grup sunt functii care opereaza pe un set de randuri pentru a da un rezultat pe intreg setul.

Parametrii si descrierea functiilor de grup

Functiile de grup sunt: AVG, COUNT, MAX, MIN, STDDEV, SUM and VARIANCE. Fiecare dintre aceste functii accepta anumiti parametrii:

Functia	Descriere
AVG([DISTINCT|ALL]n)	Valoarea medie pentru grup, ignorand valorile nule
COUNT({*|[DISTINCT|ALL]expr})	Numarul de randuri unde expr evalueaza altceva in afara de null (folosind * sunt numarate toate randurile, incluzand duplicatele si pe cele cu valoare nula)
MAX([DISTINCT|ALL]expr)	Valoarea maxima a expr, ignorand valorile nule
MIN([DISTINCT|ALL]expr)	Valoarea minima a expr, ignorand valorile nule
STDDEV([DISTINCT|ALL]x)	Deviatia standard pentru grup, ignorand valorile nule
SUM([DISTINCT|ALL]x)	Suma valorilor pentru grup, ignorand valorile nule
VARIANCE([DISTINCT|ALL]x)	Variatia pentru grup, ignorand valorile nule

DISTINCT face ca functia sa ignore valorile duplicat. ALL face ca functia sa afiseze si valorile duplicat. Valoarea implicita este ALL, deci nu este necesar sa fie specificata.
Tipul de data returnat de functia expr poate fi CHAR, VARCHAR2, NUMBER sau DATE.
Toate functiile de grup ignora valorile nule. Pentru a lua in considerare si valorile nule se folosesc functiile NVL, NVL2 sau COALESCE.

Sintaxa functiilor de grup

SELECT [coloana,] functie_de_grup(coloana), ... FROM tabel [WHERE conditie] [GROUP BY coloana] [HAVING conditie_de_grupare] [ORDER BY coloana];

Rezultatele sunt sortate implicit crescator. Pentru o ordonare descrescatoare se va folosi clauza DESC dupa ORDER BY.

Exemplul 1

Afisarea salariului mediu, maxim, minim si suma tuturor salariilor angajatilor cu job SALESMAN.

Exemplul 2

Datele la care s-au facut prima si ultima angajare.

Exemplul 3

Primul si ultimul nume de angajat in ordine alfabetica.

Functia COUNT

Functia COUNT are 3 formate:

• COUNT(*)
• COUNT(expr)
• COUNT(DISTINCT expr)

Exemplul 4

Numarul angajatilor din departamentul cu id-ul 30.

Exemplul 5

Numarul angajatilor care iau comision din departamentul 30.

Exemplul 6

Numarul de departamente din firma (varianta incorecta si varianta corecta).

Exemplul 7

Comisionul mediu in departamentul 30 (ignorand sau nu valorile nule).

Clauza GROUP BY

Pana acum toate functiile de grup au fost aplicate intregii tabele. Pentru a putea imparti tabela in grupuri mai mici se foloseste clauza GROUP BY. Folosirea acesteia returneaza informatii sumare despre fiecare grup.

Folosind GROUP BY nu se pot estrage si coloane individuale, ci doar coloane ce raman identice in tot grupul
Folosind WHERE se pot exclude randuri, inaintea impartirii lor in grupuri.
Nu pot fi folosite aliasuri de coloane in clauza GROUP BY.
Implicit, randurile sunt sortate crescator dupa coloana (coloanele) specificate in GROUP BY. Acest lucru poate fi schimbat folosind ORDER BY.

Exemplul 8

Salariul mediu pe fiecare departament.

Exemplul 9

Salariul mediu pe fiecare departament, iar rezultatele ordonate dupa salariul mediu pe departament.

Gruparea dupa mai multe coloane

Cateodata este necesara obtinerea de rezultate pentru grupuri in alte grupuri. Atunci in dreptul clauzei group by vom intalni mai multe coloane.

Exemplul 10

Salariul mediu pe fiecare departament, iar rezultatele ordonate dupa salariul mediu pe departament.

Excluderea grupurilor (clauza HAVING)

Clauza HAVING functioneaza in mare ca si clauuza WHERE, diferenta fiind ca HAVING este folosit pentru a exclude anumite grupuri din rezultat, nu randuri cum facea WHERE.
Clauza HAVING poate fi folosit inainte de GROUP BY, insa este mai logic sa fie folosita dupa. Ordinea executiei va ramane aceeasi.

Exemplul 11

Salariul mediu pe fiecare departament unde acesta depaseste 3000$.

Exemplul 12

Salariul maxim pe fiecare departament unde acesta depaseste 3000$.

Exemplul 13

Salariul total pe fiecare functie, fara a lua in calcul MANAGERII, excluzand functiile cu suma salariilor sub 6000$ cu ordonatre dupa total.

Ordinea de executie a functiilor de grup

Serverul Oracle executa functiile de grup intr-o anumita ordine:

• Selectia randurilor ce respecta clauza WHERE
• Gruparea randurilor obtinute, respectand clauza GROUP BY
• Calcularea rezultatelor functiilor de grup pentru fiecare grup in parte
• Eliminarea grupurilor ce nu respecta clauza HAVING
• Oronarea rezultatelor respectand clauza GROUP BY.

Imbricarea functiilor de grup

Functiile de grup pot fi imbricate cu o adancime de 2.

Exemplul 14

Salariul mediu maxim.

Exercitii

1. Afisati salariul maxim, minim, suma salariilor si salariul mediu pentru toti angajatii. Rotunjiti salariul mediu la cel mai apropiat intreg.

Maxim	Minim	Suma	Medie
8132	1157	47547	3396