sql3-6

8/3/2019 SQL3-6

1/7

1

F. Radulescu. Curs: Baze de date -

Limbajul SQL

1

Capitolul 3

FUNCTII STATISTICE SIGRUPURI


Limbajul SQL

2

STUD MATR NUME AN GRUPA DATAN LOC TUTOR PUNCTAJ CODS

---- ------- -- ------ --------- ---------- ----- ------- ----

1456 GEORGE 4 1141A 12-MAR-82 BUCURESTI 2890 11

1325 VASILE 2 1122A 05-OCT-84 PITESTI 1456 390 11

1645 MARIA 3 1131B 17-JUN-83 PLOIESTI 1400 11

3145 ION 1 2112B 24-JAN-85 PLOIESTI 3251 1670 21

2146 STANCA 4 2141A 15-MAY-82 BUCURESTI 620 21

3251 ALEX 5 2153B 07-NOV-81 BRASOV 1570 21

2215 ELENA 2 2122A 29-AUG-84 BUCURESTI 2146 890 21

4311 ADRIAN 3 2431A 31-JUL-83 BUCURESTI 450 24

3514 FLOREA 5 2452B 03-FEB-81 BRASOV 3230 24

1925 OANA 2 2421A 20-DEC-84 BUCURESTI 4311 760 24

2101 MARIUS 1 2412B 02-SEP-85 PITESTI 3514 310 24

4705 VOICU 2 2421B 19-APR-84 BRASOV 4311 1290 24


Limbajul SQL

3

SPEC si BURSACODS NUME DOMENIU

----- ---------- ---------------

11 MATEMATICA STIINTE EXACTE

21 GEOGRAFIE UMANIST

24 ISTORIE UMANIST

TIP PMIN PMAX SUMA

-------------------- ----- ----- -----

FARA BURSA 0 399

BURSA SOCIALA 400 899 100

BURSA DE STUDIU 900 1799 150

BURSA DE MERIT 1800 2499 200

BURSA DE EXCEPTIE 2500 9999 300


Limbajul SQL

4

OBIECTIVPn acum fiecare linie a rezultatului

era calculat dintr-o linie a unei tabeledin baza de date sau a produsuluicartezian al unor tabele.

n unele cazuri este ns necesar

calculul unor valori statistice pornind dela toate liniile parcurgerii curente sauale unor grupuri de linii care au aceleaivalori pentru o list de expresii.


Limbajul SQL

5

OBIECTIV cont.Rezultatul contine valori care caracterizeaz

ansamblul din care provine i poate conine:Valori ale unor funcii statistice (MIN, MAX,

AVG, SUM, COUNT, STDDEV, VARIANCE).Constante (numerice, ir de caractere sau

dat calendaristic). n categoria constantelorsunt incluse i pseudocoloanele puse ladispoziie de sistem cum este SYSDATE.

Valori ale expresiilor dup care s-a fcutgruparea, n cazul n care aceasta esteprezent n cerere.


Limbajul SQL

6

REZULTATRezultatul unei cereri SELECT care conine funcii

statistice are:O singur linie n cazul n care cererea nu conine

clauza de grupare GROUP BYUn numr de linii egal cu numrul de grupuri formate

pe baza criteriilor de grupare din GROUP BY, dacaceast clauz exist i nu este nsoit de clauzaHAVING.

Un numr de linii egal cu numrul de grupuri formatepe baza criteriilor de grupare din GROUP BY carendeplinesc condiia de grup din HAVING, dacambele clauze sunt prezente n cerere.

Numrul de coloane al rezultatului este ca i nainteegal cu numrul expresiilor aflate pe clauza SELECT.

8/3/2019 SQL3-6

2/7

2


Limbajul SQL

7

FUNCTII STATISTICR

MIN si MAX valoare minima simaximaSUM si AVG suma si medieCOUNT numarareSTDDEV si VARIANCE deviatia

standard si varianta


Limbajul SQL

8

MIN si MAXSintaxa funciilor: MIN([ ALL | DISTINCT ] expresie) MAX([ ALL | DISTINCT ] expresie)Descriere:MIN(expresie) ntoarce valoarea minim nenula din

lista de valori ale expresiei, fiecare valoare fiindcalculat pe baza unei linii din parcurgerea curent.

MAX(expresie) ntoarce valoarea maxim nenula dinaceeai list.

Folosirea cuvintelor cheie ALL (care este implicit) sauDISTINCT nu are n acest caz nici un efect, minimuli maximul fiind acelai dac din list se eliminduplicatele.


Limbajul SQL

9

EXEMPLUSELECT 'MATEMATICA' FACULTATE,

MIN(SUMA) MINIM, MAX(SUMA) MAXIM

FROM STUD, SPEC, BURSA

WHERE STUD.PUNCTAJ BETWEEN PMIN AND

PMAX AND

STUD.CODS = SPEC.CODS AND SPEC.NUME =

'MATEMATICA'

Rezultatul obinut va avea o singurlinie avnd coninutul:

MATEMATICA 150 300


Limbajul SQL

10

SUMSintaxa funciei:

SUM([ ALL | DISTINCT ] expresie)Descriere:SUM(expresie)ntoarce suma valorilor

nenule ale expresiei, fiecare valoare

fiind calculat pe baza unei linii dinparcurgerea curent.SUM(DISTINCT expresie) face acelai

lucru ignornd ns valorile duplicat.


Limbajul SQL

11

AVGSintaxa funciei:AVG([ ALL | DISTINCT ] expresie)Descriere:AVG(expresie)ntoarce media

aritmetic a valorilor nenule aleexpresiei.

AVG(DISTINCT expresie) ignora valorileduplicat.


Limbajul SQL

12

EXEMPLUCererea care calculeaza sume si medii

ale burselor studentilor de la ISTORIE:SELECT 'ISTORIE' FACULT,

SUM(SUMA) SUM1, SUM(DISTINCT SUMA) SUM2,

AVG(SUMA) AVG1, AVG(DISTINCT SUMA) AVG2


WHERE STUD.PUNCTAJ BETWEEN PMIN AND PMAX AND

STUD.CODS = SPEC.CODS AND SPEC.NUME='ISTORIE'

8/3/2019 SQL3-6

3/7

3


Limbajul SQL

13

REZULTATFACULT SUM1 SUM2 AVG1 AVG2------- ----- ----- ----- -----

ISTORIE 650 550 162.5 183.3

Dup cum se observ, suma i media suntdiferite pentru considerarea sau ignorareavalorilor duplicat: 650 = 100 + 100 + 150 + 300 550 = 100 + 150 + 300 162.5 = 650 / 4 183.3 = 550 / 3


Limbajul SQL

14

COUNT

Sintaxa funciei: COUNT(*) COUNT([ ALL | DISTINCT] expresie)Descriere:COUNT(*)ntoarce numrul de linii pe baza crora se

calculeaz rezultatul => nu se poate adugaDISTINCT sau nu se poate vorbi de valori nule

COUNT(expresie)ntoarce numrul de valori nenuleale expresiei.

COUNT(DISTINCT expresie)ntoarce numrul devalori nenule i distincte ale expresiei.


Limbajul SQL

15

EXEMPLUTot pentru ISTORIE se doreste numrului

studentilor, numrul de bursieri i numrul devalori diferite ale sumei primite ca burs.Cererea este:

SELECT 'ISTORIE' FACULT,COUNT(*) NRSTUD,

COUNT(SUMA) BURSIERI,

COUNT(DISTINCT SUMA) BURSE


WHERE STUD.PUNCTAJ BETWEEN PMIN AND PMAXAND STUD.CODS = SPEC.CODS cond.join

AND SPEC.NUME='ISTORIE';


Limbajul SQL

16

REZULTATFACULT NRSTUD BURSIERI BURSE

------- ------ -------- -----

ISTORIE 5 4 3

ntr-adevr, sunt 5 studeni, doar 4 au

bursa nenul i sunt trei valori distinctepentru aceasta: 100, 150 i 300.


Limbajul SQL

17

STDDEV si VARIANCESintaxa funciilor: STDDEV([ ALL | DISTINCT ] expresie) VARIANCE([ ALL | DISTINCT ] expresie)Descriere:Din punct de vedere matematic, deviaia standard d

o msura a abaterii fa de medie iar variana esteptratul deviaiei standard.

STDDEV(expresie)ntoarce deviaia standard avalorilor nenule ale expresiei.

VARIANCE(expresie)ntoarce variana valorilorrespective.

Cuvntul cheie DISTINCT duce la ignorarea valorilorduplicat.


Limbajul SQL

18

EXEMPLUSELECT STDDEV(PUNCTAJ),

VARIANCE(PUNCTAJ)

FROM STUD;

Rezultat:STDDEV(PUNCTAJ) VARIANCE(PUNCTAJ)

--------------- -----------------

949.8465 902208.3

8/3/2019 SQL3-6

4/7

4


Limbajul SQL

19

CLAUZA GROUP BY

n cererile anterioare toate liniile parcurgeriicurente formau un grup din care se calculauvalorile funciilor statistice.

Dac se dorete ns partiionarea acestora ngrupuri pentru a calcula valori statisticepentru fiecare grup n parte este necesarfolosirea clauzei GROUP BY.

Sintaxa acesteia este:

GROUP BY expresie1 [, expresie2,expresie 3 ...]


Limbajul SQL

20

EFECTAceast clauz trebuie s apar n cerere dup cele discutate

anterior (SELECT, FROM i WHERE) i are urmtorul efect:Liniile parcurgerii curente (filtrate anterior de WHERE dac

aceasta este prezent) sunt mprite n grupuri.Fiecare grup este format din liniile care au aceleai valori

(inclusiv valoarea nul) pentru expresiile specificate nGROUP BY.

Funciile statistice se calculeaz pentru fiecare grup nparte, rezultatul avnd un numr de linii egal cu numrulde grupuri formate.

n clauza SELECT, pe lng constante i funcii statistice,pot apare de asemenea i expresiile aflate n GROUP BYdeoarece valorile acestora sunt constante la nivelulfiecrui grup n parte.


Limbajul SQL

21

EXEMPLUList coninnd codurile specializrilor i

numrul de studeni nscrii la fiecare, cerereaeste:SELECT CODS, COUNT(*) NRSTUD

FROM STUD

GROUP BY CODS;

Rezultatul obinut este:

CODS NRSTUD----- ------

11 3

21 4

24 5


Limbajul SQL

22

EXEMPLU cont.Pentru a avea numele specializarii gruparea se face

pe joinul STUD cu SPEC:SELECT SPEC.NUME, COUNT(*) NRSTUD

FROM STUD, SPEC

WHERE STUD.CODS = SPEC.CODS

GROUP BY SPEC.NUME;

obinndu-se rezultatul: NUME NRSTUD

---------- ------

GEOGRAFIE 4

ISTORIE 5

MATEMATICA 3


Limbajul SQL

23

OBSERVATIIDac n cererea anterioar gruparea s-ar fi fcut

dup STUD.CODS sau SPEC.CODS s-ar fi semnalateroarea: ORA-00979: not a GROUP BY expressiondincauza prezenei lui SPEC.NUME n linia SELECT.

Pentru a include n rezultat i numele i codulspecializrii clauza GROUP BY trebuie s le conin peamndou. Grupurile vor fi aceleai dar cererea va fivalid i va putea fi executat:SELECT SPEC.NUME, SPEC.CODS, COUNT(*)NRSTUD

FROM STUD, SPEC

WHERE STUD.CODS = SPEC.CODS

GROUP BY SPEC.NUME, SPEC.CODS;


Limbajul SQL

24

ALT EXEMPLUSe doreste pentru fiecare tip de burs valoarea acesteia,valoarea mrit cu 10% i numrul de studeni beneficiari:SELECT SUMA, SUMA*1.1 BMARITA, COUNT(*)

FROM STUD, BURSA

WHERE PUNCTAJ BETWEEN PMIN AND PMAX

GROUP BY SUMA;

Aceast cerere va semnala de asemenea eroare: gruparease face doar dup SUMA, n consecin prezena nSELECT a expresiei SUMA*1.1 este ilegal, dei esteevident c doi studeni care au burse egale au i bursemrite cu 10% egale.

Sistemul de gestiune nu face astfel de deducii, pentruafiarea datelor cerute fiind necesar adugarea n GROUPBY a expresiei SUMA*1.1.

8/3/2019 SQL3-6

5/7

5


Limbajul SQL

25

ORDONARE IMPLICITA

Ordinea expresiilor din clauza de grupareare efect asupra prezentrii rezultatului:sistemul Oracle face implicit o sortare nfuncie de aceste expresii, ascendent, nordinea n care ele sunt prezente ncerere, ncepnd ns cu valorile nule.


Limbajul SQL

26

EXEMPLUSELECT TUTOR,CODS,COUNT(*)

FROM STUD

WHERE CODS IN (11,24)

GROUP BY TUTOR,CODS;

REZULTAT: ordonare dupa TUTOR si CODSTUTOR CODS COUNT(*)

----- ----- --------

11 2

24 2

1456 11 1

3514 24 1

4311 24 2


Limbajul SQL

27


FROM STUD


GROUP BY CODS, TUTOR;

REZULTAT: ordonare dupa CODS si TUTORTUTOR CODS COUNT(*)

----- ----- --------

11 21456 11 1

24 2

3514 24 1

4311 24 2


Limbajul SQL

28

ORDONARE IMPLICITA cont.Se poate folosi clauza ORDER BY pentru a

schimba ordinea implicit.n ea pot fi prezente doar elementele

constante la nivel de grup (funcii statistice,expresiile de grupare) sau numrul coloaneidin rezultat.

n cererea urmtoare ordonarea se face dupcoloana a treia din rezultat i, la valori egalepe aceasta, dup valoarea minim apunctajului la nivel de grup, dei acest minimnu este prezent n lista SELECT.


Limbajul SQL

29


FROM STUD


GROUP BY CODS, TUTOR

ORDER BY 3, MIN(PUNCTAJ);

Rezultatul este:TUTOR CODS COUNT(*)

----- ----- --------

3514 24 1

1456 11 1

24 2

4311 24 2

11 2


Limbajul SQL

30

IMBRICARE FCT. STATISTICEFunciile statistice se pot imbrica.n acest caz valoarea rezultat se calculeaz

pe baza valorilor ntoarse de funcia imbricatpentru fiecare grup n parte.

De exemplu, dac se dorete o medie aburselor medii pe specializri, aceasta poate ficalculat cu cererea:SELECT AVG(AVG(NVL(SUMA, 0))) MEDIE1

FROM STUD, BURSA


GROUP BY CODS;

8/3/2019 SQL3-6

6/7

6


Limbajul SQL

31

REZULTAT

Rezultatul obinut este urmtorul:MEDIE1--------

135

De remarcat c aceast valoare este diferitde media burselor studenilor care este de133.33 i este calculat de cererea:SELECT AVG(NVL(SUMA, 0)) MEDIE2

FROM STUD, BURSA

WHERE PUNCTAJ BETWEEN PMIN AND PMAX;


Limbajul SQL

32

CLAUZA HAVINGFiecare grup format de GROUP BY are, n cazul

cererilor precedente, o linie corespondent nrezultat.Clauzele descrise pn acum nu permit o filtrare la

nivel de grup.De exemplu, dac se dorete obinerea de date doar

despre specializrile care au studeni cu un punctajmediu mai mare dect 1200, cererea urmtoare vasemnala eroare:SELECT CODS, AVG(PUNCTAJ)

FROM STUD

WHERE AVG(PUNCTAJ) > 1500

GROUP BY CODS;


Limbajul SQL

33

CLAUZA HAVING cont.Eroarea se datoreaz faptului c WHERE

poate conine doar condiii la nivelul unei liniidin parcurgerea curent i nu la nivel degrup.

Pentru filtrarea grupurilor este necesar onou clauz, HAVING, avnd urmtoareasintax:

HAVING conditie_de_grupCondiia poate fi simpl sau compus. n ea

pot fi folosite doar elemente constante lanivel de grup (literali, funcii statistice, etc.).


Limbajul SQL

34

EXEMPLUPentru cererea anterioara formularea corect este:

SELECT CODS, AVG(PUNCTAJ)

FROM STUD

GROUP BY CODS

HAVING AVG(PUNCTAJ) > 1500

Rezultatul va conine doar o linie, celelalte dougrupuri fiind filtrare de condiia din HAVING:CODS AVG(PUNCTAJ)

----- ------------

11 1560


Limbajul SQL

35

HAVING FARA GROUP BYAceast clauz poate fi folosit i n absena lui

GROUP BY. n acest caz cererea ntoarce fie o linie, fie nici o

linie, n funcie de satisfacerea sau nu a condiieiHAVING de liniile parcurgerii curente.

De exemplu, datorit faptului c punctajul mediupentru ntreaga tabel STUD este mai mic decat1500, cererea:SELECT AVG(PUNCTAJ)

FROM STUD

HAVING AVG(PUNCTAJ) > 1500;

nu va ntoarce nici o linie de rezultat.


Limbajul SQL

36

HAVING SI IMBRICARE n cazul n care avem funcii statistice imbricate,rezultatul acestora va fi calculat doar pe baza valorilorfunciei imbricate aplicat grupurilor care satisfaccondiia din HAVING.

De exemplu, urmtoarea cerere va calcula mediaburselor medii pentru dou specializri (cele cu punctajmediu peste 1200) i nu pentru toate. Rezultatul va fi140 i nu 135 ca atunci cnd nu exist HAVING:SELECT AVG(AVG(NVL(SUMA, 0))) MEDIE1

FROM STUD, BURSA


GROUP BY CODS

HAVING AVG(PUNCTAJ) > 1200;

8/3/2019 SQL3-6

7/7

7


Limbajul SQL

37

Sfarsitul capitolului

FUNCTII STATISTICE SIGRUPURI

sql3-6

Documents