sql3-6
Post on 06-Apr-2018
216 Views
Preview:
TRANSCRIPT
-
8/3/2019 SQL3-6
1/7
1
F. Radulescu. Curs: Baze de date -
Limbajul SQL
1
Capitolul 3
FUNCTII STATISTICE SIGRUPURI
F. Radulescu. Curs: Baze de date -
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
F. Radulescu. Curs: Baze de date -
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
F. Radulescu. Curs: Baze de date -
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.
F. Radulescu. Curs: Baze de date -
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.
F. Radulescu. Curs: Baze de date -
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
F. Radulescu. Curs: Baze de date -
Limbajul SQL
7
FUNCTII STATISTICR
MIN si MAX valoare minima simaximaSUM si AVG suma si medieCOUNT numarareSTDDEV si VARIANCE deviatia
standard si varianta
F. Radulescu. Curs: Baze de date -
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.
F. Radulescu. Curs: Baze de date -
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
F. Radulescu. Curs: Baze de date -
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.
F. Radulescu. Curs: Baze de date -
Limbajul SQL
11
AVGSintaxa funciei:AVG([ ALL | DISTINCT ] expresie)Descriere:AVG(expresie)ntoarce media
aritmetic a valorilor nenule aleexpresiei.
AVG(DISTINCT expresie) ignora valorileduplicat.
F. Radulescu. Curs: Baze de date -
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
FROM STUD, SPEC, BURSA
WHERE STUD.PUNCTAJ BETWEEN PMIN AND PMAX AND
STUD.CODS = SPEC.CODS AND SPEC.NUME='ISTORIE'
-
8/3/2019 SQL3-6
3/7
3
F. Radulescu. Curs: Baze de date -
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
F. Radulescu. Curs: Baze de date -
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.
F. Radulescu. Curs: Baze de date -
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
FROM STUD, SPEC, BURSA
WHERE STUD.PUNCTAJ BETWEEN PMIN AND PMAXAND STUD.CODS = SPEC.CODS cond.join
AND SPEC.NUME='ISTORIE';
F. Radulescu. Curs: Baze de date -
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.
F. Radulescu. Curs: Baze de date -
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.
F. Radulescu. Curs: Baze de date -
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
F. Radulescu. Curs: Baze de date -
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 ...]
F. Radulescu. Curs: Baze de date -
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.
F. Radulescu. Curs: Baze de date -
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
F. Radulescu. Curs: Baze de date -
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
F. Radulescu. Curs: Baze de date -
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;
F. Radulescu. Curs: Baze de date -
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
F. Radulescu. Curs: Baze de date -
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.
F. Radulescu. Curs: Baze de date -
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
F. Radulescu. Curs: Baze de date -
Limbajul SQL
27
EXEMPLUSELECT TUTOR,CODS,COUNT(*)
FROM STUD
WHERE CODS IN (11,24)
GROUP BY CODS, TUTOR;
REZULTAT: ordonare dupa CODS si TUTORTUTOR CODS COUNT(*)
----- ----- --------
11 21456 11 1
24 2
3514 24 1
4311 24 2
F. Radulescu. Curs: Baze de date -
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.
F. Radulescu. Curs: Baze de date -
Limbajul SQL
29
EXEMPLUSELECT TUTOR,CODS,COUNT(*)
FROM STUD
WHERE CODS IN (11,24)
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
F. Radulescu. Curs: Baze de date -
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
WHERE PUNCTAJ BETWEEN PMIN AND PMAX
GROUP BY CODS;
-
8/3/2019 SQL3-6
6/7
6
F. Radulescu. Curs: Baze de date -
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;
F. Radulescu. Curs: Baze de date -
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;
F. Radulescu. Curs: Baze de date -
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.).
F. Radulescu. Curs: Baze de date -
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
F. Radulescu. Curs: Baze de date -
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.
F. Radulescu. Curs: Baze de date -
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
WHERE PUNCTAJ BETWEEN PMIN AND PMAX
GROUP BY CODS
HAVING AVG(PUNCTAJ) > 1200;
-
8/3/2019 SQL3-6
7/7
7
F. Radulescu. Curs: Baze de date -
Limbajul SQL
37
Sfarsitul capitolului
FUNCTII STATISTICE SIGRUPURI
top related