Përdorimi i funksioneve agregate
SQL përcakton shumë funksione të integruara të kategorive të ndryshme, ndër të cilat një vend të veçantë zënë funksionet agregate që funksionojnë në vlerat e kolonave në një grup rreshtash dhe kthejnë një vlerë të vetme. Argumentet e funksioneve agregate mund të jenë si kolonat e tabelës ashtu edhe rezultatet e shprehjeve mbi to. Funksionet agregate mund të përfshihen vetë në shprehje të tjera aritmetike. Tabela e mëposhtme liston funksionet standarde të përbashkëta unare më të përdorura.
Formati i përgjithshëm i një funksioni agregat unar është si më poshtë:
emri_funksionit ([TË GJITHA | TË DISTINCT] shprehje)
ku DISTINCT tregon se funksioni duhet të marrë parasysh vetëm vlera të ndryshme argumenti, dhe ALL - të gjitha vlerat, duke përfshirë vlerat e kopjuara (ky është parazgjedhja). Për shembull, funksioni AVG me fjalën kyçe DISTINCT për rreshtat e kolonave me vlera 1, 1, 1 dhe 3 do të kthejë 2, dhe nëse fjala kyçe ALL është e pranishme, do të kthejë 1.5.
Funksionet agregate përdoren në klauzolat SELECT dhe HAVING. Këtu do të shikojmë përdorimin e tyre në klauzolën SELECT. Në këtë rast, shprehja në argumentin e funksionit zbatohet për të gjitha rreshtat në tabelën hyrëse të klauzolës SELECT. Për më tepër, funksionet e përmbledhura dhe kolonat e tabelës (ose shprehjet me to) nuk mund të përdoren në klauzolën SELECT, përveç nëse klauzola GROUP BY, të cilën do ta diskutojmë në seksionin tjetër, nuk mungon.
Funksioni COUNT ka dy formate. Në rastin e parë, numri i rreshtave në tabelën hyrëse kthehet, në rastin e dytë, numri i vlerave të argumenteve në tabelën hyrëse:
- COUNT (*)
- COUNT (shprehje)
Mënyra më e thjeshtë për të përdorur këtë funksion është të numëroni numrin e rreshtave në një tabelë (të gjitha ose duke përmbushur një kusht të caktuar). Për këtë përdoret sintaksa e parë.
Pyetje: Numri i llojeve të produkteve, informacioni për të cilin është i disponueshëm në bazën e të dhënave.
ZGJIDH NUMRIN (*) SI "Numri i llojeve të produkteve"
NGA Produkti
Në variantin e dytë të sintaksës për funksionin COUNT, emri i një kolone të vetme mund të përdoret si argument. Në këtë rast, numri i ose i të gjitha vlerave në këtë kolonë të tabelës hyrëse llogaritet, ose vetëm vlerave që nuk përsëriten (kur përdorni fjalën kyçe DISTINCT).
Pyetje: Numri i emrave të ndryshëm që përmbahen në tabelën e Klientit.
ZGJIDH NUMRIN (EMRI I NDRYSHËM)
NGA Klienti
Përdorimi i pjesës tjetër të funksioneve agregate unare është i njëjtë si për COUNT, përveç se për funksionet MIN dhe MAX, përdorimi i fjalëve kyçe DISTINCT dhe ALL është i pakuptimtë. Përveç fushave numerike, fushat e karaktereve mund të përdoren me funksionet COUNT, MAX dhe MIN. Nëse argumenti i funksionit agregat nuk përmban vlera, funksioni COUNT kthen 0 dhe të gjithë të tjerët kthejnë NULL.
ZGJIDH MAX (Data e porosisë)
NGA
KU Urdhri Data "1.09.2010"
Detyrë vetë-studimi: Formuloni pyetje në SQL për të zgjedhur të dhënat e mëposhtme:
- Kostoja totale e të gjitha porosive;
- Numri i qyteteve të veçanta të përfshira në tabelën e Klientit.
Mësimi do të mbulojë temën sql riemërimi i një kolone (fushash) duke përdorur fjalën e funksionit AS; trajtoi gjithashtu temën e funksioneve agregate në sql. Do të analizohen shembuj specifikë të kërkesave
Ju mund të riemërtoni emrat e kolonave në pyetje. Kjo i bën rezultatet më të lexueshme.
Në SQL, riemërtimi i fushave shoqërohet me përdorimin e fjalë kyçe AS e cila përdoret për të riemërtuar emrat e fushave në grupet e rezultateve
Sintaksë:
ZGJIDH<имя поля>AS<псевдоним>NGA...
Le të shohim një shembull të riemërtimit në SQL:
Shembull DB "Instituti": Shfaqni emrat e mësuesve dhe pagat e tyre, për ata mësues pagat e të cilëve janë nën 15000, riemërtoni fushën zarplata në "Paga e ulët"
✍ Zgjidhja:
Riemërtimi i kolonave në SQL është shpesh i nevojshëm kur llogaritni vlerat e lidhura me fusha të shumta tabelat. Le të shqyrtojmë një shembull:
Shembull DB "Instituti": Nga tabela e mësuesve, nxirrni fushën e emrit dhe llogaritni shumën e pagës dhe bonusit, duke emërtuar fushën "Shpërblim paga"
✍ Zgjidhja:
| 1 2 | SELECT emrin, (zarplata + premia) AS zarplata_premia FROM mësues; |
SELECT emrin, (zarplata + premia) AS zarplata_premia FROM mësues;
Rezultati:
Funksionet Agregate në SQL
Funksionet agregate në sql përdoren për të marrë totale dhe për të llogaritur shprehjet:
Të gjitha funksionet agregate kthejnë një vlerë të vetme.
Funksionet COUNT, MIN dhe MAX zbatohen për çdo lloj të dhënash.
Funksionet SUM dhe AVG përdoren vetëm për fushat numerike.
Ekziston një ndryshim midis funksioneve COUNT (*) dhe COUNT (): i dyti nuk merr parasysh vlerat NULL kur numëron.
E rëndësishme: kur punoni me funksione agregate në SQL, përdoret një fjalë e veçantë AS
Shembull DB "Instituti": Merrni vlerën e pagës më të lartë mes mësuesve, shfaqni rezultatin si "Max_zp"
✍ Zgjidhja:
| SELECT MAX (zarplata) AS max_zp NGA mësuesit; |
SELECT MAX (zarplata) AS max_zp NGA mësuesit;
Rezultatet:
Le të shqyrtojmë një shembull më kompleks të përdorimit të funksioneve agregate në sql.
✍ Zgjidhja:
Klauzola GROUP BY në SQL
Grupi sipas klauzolës në sql zakonisht përdoret në lidhje me funksionet agregate.
Funksionet e përgjithshme kryhen në të gjitha rreshtat e pyetjeve që rezultojnë. Nëse pyetja përmban një klauzolë GROUP BY, çdo grup rreshtash i specifikuar në klauzolën GROUP BY përbën një grup dhe funksionet agregate ekzekutohen. për secilin grup veç e veç.
Shqyrtoni një shembull me tabelën e mësimeve: 
Shembull:
E rëndësishme: Kështu, si rezultat i përdorimit të GROUP BY, të gjitha rreshtat dalëse të pyetjes ndahen në grupe të karakterizuara nga të njëjtat kombinime vlerash në këto kolona (d.m.th., funksionet agregate kryhen për secilin grup veç e veç).
Duhet të kihet parasysh se kur grupohen nga një fushë që përmban vlera NULL, të gjitha regjistrimet e tilla do të bien në një grup.
Për lloje të ndryshme printerësh, përcaktoni koston dhe sasinë mesatare të tyre (d.m.th. veçmas për lazer, inkjet dhe matricë). Përdorni funksionet agregate. Rezultati duhet të duket si ky:
Duke pasur deklaratë SQL
Klauzola HAVING në SQL nevojitet për të vërtetuar vlerat, të cilat fitohen duke përdorur funksionin agregat pas grupimit(pas përdorimit GROUP BY). Një kontroll i tillë nuk mund të përfshihet në një klauzolë WHERE.
Shembull: Dyqan kompjuterësh DB... Llogaritni çmimin mesatar të kompjuterëve me të njëjtën shpejtësi procesori. Llogaritni vetëm për ato grupe çmimi mesatar i të cilëve është më pak se 30,000.
- Funksionet agregate përdoren si emrat e fushave në deklaratën SELECT, me një përjashtim: ata marrin emrin e fushës si argument. Me funksione SHUMË dhe AVG mund të përdoren vetëm fushat numerike. Me funksione COUNT, MAX dhe MIN mund të përdoren si fushat numerike ashtu edhe ato të karaktereve. Kur përdoret me fushat e karaktereve MAX dhe MIN do t'i përkthejë ato në ekuivalentin ASCII dhe do t'i përpunojë sipas rendit alfabetik. Disa DBMS lejojnë përdorimin e agregateve të mbivendosur, por ky është një devijim nga standardi ANSI me të gjitha pasojat që pasojnë.
Për shembull, mund të llogarisni numrin e studentëve që kanë kaluar provimet në secilën disiplinë. Për ta bërë këtë, duhet të ekzekutoni një pyetje të grupuar sipas fushës "Disiplina" dhe të shfaqni si rezultat emrin e disiplinës dhe numrin e rreshtave në grup për këtë disiplinë. Përdorimi i karakterit * si argument për funksionin COUNT do të thotë që të gjitha rreshtat në grup janë të numëruara.
ZGJIDH R1. Disiplinë, COUNT (*)
GRUP NGA R1 Disiplina;
Rezultati:
SELECT R1 Disiplinë, COUNT (*)
KU R1. NUK ËSHTË rezultati NULL
GRUP NGA R1 Disiplina;
Rezultati:
nuk do të përfshihet në grupin e tupave përpara grupimit, kështu që numri i tupave në grup për disiplinën "Teoria e Informacionit" do të jetë 1 më pak.
Një rezultat i ngjashëm mund të merret nëse e shkruani kërkesën në mënyrën e mëposhtme:
ZGJIDH R1. Disiplinë, COUNT (R1. Vlerësim)
GRUPI NGA R1. Disipline;
Funksioni COUNT (EMRI ATRIBUTE) numëron numrin e vlerave të përcaktuara në një grup, në krahasim me një funksion COUNT (*), i cili numëron numrin e rreshtave në grup. Në të vërtetë, në grupin me disiplinën "Teoria e informacionit" do të ketë 4 rreshta, por vetëm 3 vlera specifike të atributit "Vlerësimi".
Rregullat për trajtimin e vlerave null në funksionet agregate
Nëse ndonjë vlerë në kolonë është e barabartë I PAVLEFSHËM ato përjashtohen gjatë llogaritjes së rezultatit të funksionit.
Nëse të gjitha vlerat në një kolonë janë të barabarta I PAVLEFSHËM, pastaj Shuma maksimale minimale mesatare = NULL, numërim = 0 (zero).
Nëse tabela është bosh, numërimi (*) = 0 .
Funksionet agregate mund të përdoren edhe pa operacionin paraprak të grupimit, në këtë rast e gjithë relacioni konsiderohet si një grup dhe për këtë grup mund të llogaritet një vlerë për grup.
Rregulla për interpretimin e funksioneve agregate
Funksionet agregate mund të përfshihen në listën e daljes dhe më pas ato aplikohen në të gjithë tabelën.
SELECT MAX (Rezultati) nga R1 do të japë vlerësimin maksimal në seancë;
ZGJIDH SHUMEN nga R1 do të japë shumën e të gjitha vlerësimeve për sesion;
ZGJIDHNI AVG (Rezultati) nga R1 do të japë një rezultat mesatar për të gjithë seancën.
2; Rezultati: "width = 640" Duke iu referuar sërish bazës së të dhënave Session (tabelat R1), do të gjejmë numrin e provimeve të mbartura me sukses:
ZGJIDH NUMRIN (*) Si Me qira _ provimet
KU Klasa 2;
Rezultati:
Argumenti i funksioneve agregate mund të jetë kolona të veçanta tabelash. Për të llogaritur, për shembull, numrin e vlerave të dallueshme të një kolone të caktuar në një grup, është e nevojshme të përdorni fjalën kyçe DISTINCT së bashku me emrin e kolonës. Le të llogarisim numrin e notave të ndryshme të marra për secilën disiplinë:
ZGJIDH Disiplinë R1, COUNT (Vlerësim DISTINCT R1)
KU R1. NUK ËSHTË rezultati NULL
GRUP NGA R1 Disiplina;
Rezultati:
I njëjti rezultat arrihet nëse përjashtoni kushtin eksplicit në pjesën WHERE, në të cilin rast pyetja do të duket kështu:
ZGJIDH R1. Disiplinë, COUNT (DISTINCT R1. Vlerësim)
GRUPI NGA R1. Disipline;
Funksioni COUNT (DISTINCT R1. Vlerësim) konsideron vetëm të sigurt të ndryshme vlerat.
Për të marrë rezultatin e dëshiruar në këtë rast, është e nevojshme të bëhet një transformim paraprak i llojit të të dhënave të kolonës "Score", duke e sjellë atë në një lloj real, atëherë rezultati i llogaritjes së mesatares nuk do të jetë një numër i plotë. Në këtë rast, kërkesa do të duket si kjo:
2 Grupi sipas R2. Grupi, R1. Disipline; Këtu, funksioni CAST () konverton kolonën "Score" në një lloj të vlefshëm të dhënash. "gjerësia = 640" Zgjidhni R2.Group, R1.Discipline, Numëroni (*) si Total, AVG (cast (Rezultati si dhjetor (3,1))) si Pika_mesatare
Nga R1, R2
ku R1. Emri i plotë = R2. Emri dhe R1. rezultati nuk është nul
dhe R1. Klasa 2
Grupi sipas R2. Grupi, R1. Disipline;
Këtu funksioni CAST () konverton kolonën Score në një lloj të vlefshëm të dhënash.
Ju nuk mund të përdorni funksione agregate në klauzolën WHERE sepse kushtet në këtë seksion vlerësohen në terma të një rreshti të vetëm, dhe funksionet e përgjithshme vlerësohen në terma të grupeve të rreshtave.
Klauzola GROUP BY ju lejon të përcaktoni një nëngrup vlerash në një fushë të caktuar në terma të një fushe tjetër dhe të aplikoni një funksion agregat në nëngrup. Kjo bën të mundur kombinimin e fushave dhe funksioneve të përmbledhura në një klauzolë të vetme SELECT. Funksionet agregate mund të përdoren si në shprehjen për nxjerrjen e rezultateve të rreshtit SELECT, ashtu edhe në shprehjen për përpunimin e grupeve të krijuara HAVING. Në këtë rast, çdo funksion agregat llogaritet për çdo grup të përzgjedhur. Vlerat e marra gjatë llogaritjes së funksioneve agregate mund të përdoren për të shfaqur rezultatet përkatëse ose për gjendjen e përzgjedhjes së grupeve.
Le të ndërtojmë një pyetje që shfaq grupet në të cilat janë marrë më shumë se një deuce në një disiplinë në provime:
një; Rezultati: "width = 640" ZGJIDH R2. Grupi
NGA R1, R2
KU R1. Emri i plotë = R2. Emri DHE
Rezultati R1 = 2
GRUPI NGA R2.Grupi,R1.Disiplina
Duke numëruar (*) 1;
Rezultati:
Ne kemi një DB "Bankë", e përbërë nga një tabelë F, e cila ruan relacionin F, që përmban informacione për llogaritë në degët e një banke të caktuar:
Gjeni gjendjen totale të llogarisë në degë. Ju mund të bëni një pyetje të veçantë për secilën prej tyre duke zgjedhur SUM nga tabela për secilën degë, por operacioni GROUP BY do t'i vendosë të gjitha në një komandë:
ZGJIDH Dega , SUM ( Pjesa e mbetur )
GRUPI SIPAS Degës;
GRUP NGA zbaton funksionet agregate në mënyrë të pavarur për çdo grup të identifikuar nga vlera e fushës Degë. Grupi përbëhet nga linja me vlerë të njëjtë të fushës Dega, dhe funksioni SHUMË zbatohet veçmas për secilin grup të tillë, pra gjendja totale e llogarisë llogaritet veçmas për secilën degë. Vlera e fushës në të cilën zbatohet GRUP NGA, ka, sipas përkufizimit, vetëm një vlerë për grupin e daljes, ashtu si rezultati i një funksioni agregat.
5000; Argumentet në klauzolën HAVING ndjekin të njëjtat rregulla si në klauzolën SELECT, e cila përdor GROUP BY. Ata duhet të kenë një vlerë për grupin e daljes. "gjerësia = 640" Le të supozojmë se ju zgjidhni vetëm ato degë, vlerat totale të gjendjeve të llogarive të të cilave tejkalojnë 5000 dollarë, si dhe gjendjet totale për degët e zgjedhura. Për të shfaqur degët me gjendje totale mbi 5000 dollarë, duhet të përdorni klauzolën HAVING. Klauzola HAVING përcakton kriteret e përdorura për të hequr grupe specifike nga dalja, ashtu siç bën klauzola WHERE për rreshtat individualë.
Komanda e saktë do të ishte si më poshtë:
SELECT Degë, SUM (Balanca)
GRUP NGA Dega
duke pasur shumën ( Pjesa e mbetur ) 5 000;
Argumentet në një fjali DUKE respektoni të njëjtat rregulla si në fjali ZGJIDH ku përdoret GRUP NGA... Ata duhet të kenë një vlerë për grupin e daljes.
Komanda e mëposhtme do të ndalohet:
SELECT Degë, SUM (Balanca)
GRUPI SIPAS Degës
HAVING Data e hapjes = 27/12/2004 ;
Fusha Data e hapjes nuk mund të përdoret në një fjali DUKE sepse mund të ketë më shumë se një vlerë për grupin e shfaqjes. Për të shmangur një situatë të tillë, propozimi DUKE duhet t'i referohet vetëm agregateve dhe fushave të zgjedhura GRUP NGA... Ekziston një mënyrë e saktë për të bërë pyetjen e mësipërme:
SELECT Degë, SUM (Balanca)
WHERE Data e hapjes = '27/12/2004'
GRUPI SIPAS Degës;
Kuptimi i këtij pyetësori është si më poshtë: gjeni shumën e gjendjeve për çdo degë të llogarive të hapura më 27 dhjetor 2004.
Siç u tha më herët, HAVING mund të përdorë vetëm argumente që kanë të njëjtën vlerë për grupin e daljes. Në praktikë, referencat për funksionet agregate janë më të përgjithshmet, por fushat e zgjedhura me GROUP BY janë gjithashtu të vlefshme. Për shembull, ne duam të shohim gjendjen totale të llogarive të degëve në Shën Petersburg, Pskov dhe Uryupinsk:
SELECT Degë, SUM (Balanca)
NGA F, Q
KU F. Dega = Q. Dega
GRUPI SIPAS Degës
KANË Degë NË (‘Shën Petersburg’, ‘Pskov’, ‘Uryupinsk’);
100,000; Nëse bilanci total është më shumë se 100,000 dollarë, atëherë do ta shohim në raportin që rezulton, përndryshe do të marrim një raport bosh. "gjerësia = 640"
Prandaj, në shprehjet aritmetike të kallëzuesve të përfshirë në kushtin e përzgjedhjes së klauzolës HAVING, mund të përdorni drejtpërdrejt vetëm specifikimet e kolonave të specifikuara si kolona grupimi në klauzolën GROUP BY. Pjesa tjetër e kolonave mund të specifikohet vetëm brenda specifikimeve të funksioneve agregate COUNT, SUM, AVG, MIN dhe MAX, të cilat në këtë rast llogarisin një vlerë agregate për të gjithë grupin e rreshtave. Rezultati i ekzekutimit të klauzolës HAVING është një tabelë e grupuar që përmban vetëm ato grupe rreshtash për të cilat rezultati i vlerësimit të kushtit të përzgjedhjes në pjesën HAVING është TRUE. Në veçanti, nëse klauzola HAVING është e pranishme në një pyetje që nuk përmban një GROUP BY, atëherë rezultati i ekzekutimit të tij do të jetë ose një tabelë bosh, ose rezultat i seksioneve të mëparshme të shprehjes së tabelës, i konsideruar si një grup pa grupimi i kolonave. Le të shohim një shembull. Le të themi se duam të shfaqim shumën totale të bilanceve për të gjitha degët, por vetëm nëse është më shumë se 100,000 dollarë. Në këtë rast, pyetja jonë nuk do të përmbajë operacione grupimi, por do të përmbajë një seksion HAVING dhe do të duket kështu:
ZGJIDH SHUMEN ( Pjesa e mbetur )
duke pasur shumën ( Pjesa e mbetur ) 100 000;
Nëse bilanci total është më shumë se 100,000 dollarë, atëherë do ta shohim në raportin që rezulton, përndryshe do të marrim një raport bosh.
Për të përmbledhur informacionin e përfshirë në bazën e të dhënave, SQL ofron funksione agregate. Një funksion agregat merr një kolonë të tërë të dhënash si argument dhe kthen një vlerë të vetme që e përmbledh atë kolonë në një mënyrë specifike.
Për shembull, funksioni agregat AVG () merr një kolonë numrash si argument dhe llogarit mesataren e tyre.
Për të llogaritur të ardhurat mesatare për frymë të një banori të Zelenogradit, ju nevojitet pyetja e mëposhtme:
ZGJIDH "TË ARDHURAT MESATORE =", AVG (SUMD)
SQL ka gjashtë funksione agregate që ofrojnë lloje të ndryshme përmbledhjesh (Figura 1):
- SUM () llogarit shumën e të gjitha vlerave të përfshira në kolonë;
- AVG () llogarit mesataren midis vlerave të përmbajtura në kolonë;
- MIN () gjen më të voglin midis të gjitha vlerave të përfshira në kolonë;
- MAX () gjen më të madhin midis të gjitha vlerave të përfshira në kolonë;
- COUNT () numëron numrin e vlerave që përmbahen në kolonë;
- COUNT (*) numëron numrin e rreshtave në tabelën e rezultateve të pyetjes.
Argumenti për një funksion agregat mund të jetë një emër i thjeshtë kolone, si në shembullin e mëparshëm, ose një shprehje, si në pyetjen e mëposhtme që llogarit taksën për frymë:
SELECT AVG (SUMD * 0.13)
Ky pyetje krijon një kolonë të përkohshme që përmban vlerat (SUMD * 0.13) për çdo rresht në tabelën PERSON, dhe më pas llogarit mesataren e kolonës së përkohshme.
Shuma e të ardhurave të të gjithë banorëve të Zelenograd mund të llogaritet duke përdorur funksionin agregat SUM:
ZGJIDH SHUMËN (SHUMË) NGA PERSONI
Funksioni agregat mund të përdoret gjithashtu për të llogaritur totalet për një tabelë të rezultateve të marra duke bashkuar disa tabela burimore. Për shembull, ju mund të llogaritni shumën totale të të ardhurave që banorët marrin nga një burim i quajtur "Bursa":
ZGJIDH SHUMEN (PARAJA)
NGA FITIMI, HAVE_D
KU PROFIT.ID = HAVE_D.ID
AND PROFIT.SOURCE = 'Bursë'
Funksionet agregate MIN () dhe MAX () ju lejojnë të gjeni, respektivisht, vlerat më të vogla dhe më të mëdha në tabelë. Sidoqoftë, kolona mund të përmbajë vlera numerike ose vargje, ose vlera datë ose orë.
Për shembull, mund të përcaktoni:
(a) të ardhurat totale më të ulëta të fituara nga rezidentët dhe tatimi më i lartë i pagueshëm:
SELECT MIN (SUMD), MAX (SUMD * 0,13)
(b) datat e lindjes së banorit më të vjetër dhe më të ri:
ZGJIDH MINI (RDATE), MAX (RDATE)
(c) mbiemrat, emrat dhe patronimet e banorëve të parë dhe të fundit në listë, të renditura sipas alfabetit:
ZGJIDH MIN (FIO), MAX (FIO)
Kur aplikoni këto funksione agregate, duhet të mbani mend se të dhënat numerike krahasohen sipas rregullave aritmetike, datat krahasohen në mënyrë sekuenciale (vlerat e mëparshme të datës konsiderohen më të vogla se ato të mëvonshme), intervalet kohore krahasohen në bazë të kohëzgjatjes së tyre.
Kur përdorni MIN () dhe MAX () me të dhëna vargu, rezultati i krahasimit të dy vargjeve varet nga tabela e kodimit të karaktereve të përdorur.
Funksioni agregat COUNT () numëron numrin e vlerave në një kolonë të çdo lloji:
(a) sa apartamente ka në mikrodistriktin 1?
ZGJIDH NUMRIN (ADR) FROM FLAT WHERE ADR LIKE "%, 1_ _-%"
(b) sa banorë kanë burime të ardhurash?
ZGJIDH NUMRIN (NOM I NDRYSHME) NGA HAVE_D
(c) sa burime të ardhurash përdoren nga banorët?
SELECT COUNT (ID DISTINCT) FROM HAVE_D (Fjala kyçe DISTINCT specifikon që vlerat jo dublikatë në kolonë numërohen).
Funksioni i agregatit special COUNT (*) numëron rreshtat në tabelën e rezultateve, jo vlerat e të dhënave:
(a) sa apartamente ka në mikrodistriktin e dytë?
ZGJIDH NUMRIN (*) FROM FLAT WHERE ADR LIKE "%, 2 __-%"
(b) sa burime të ardhurash ka Ivan Ivanovich?
ZGJIDH NUMRIN (*) NGA PERSONI, HAVE_D WHERE FIO = "Ivanov Ivan Ivanovich" DHE PERSON.NOM = HAVE_D.NOM
(c) sa banorë jetojnë në një apartament në një adresë të caktuar?
ZGJIDH NUMRIN (*) FROM PERSON WHERE ADR = "Zelenograd, 1001-45"
Një mënyrë për të kuptuar se si ekzekutohen pyetjet agregate është të mendojmë për ekzekutimin e pyetjeve në dy pjesë. Së pari, përcaktohet se si pyetja do të funksiononte pa funksione agregate, duke kthyer rreshta të shumtë të rezultateve. Funksionet e grumbulluara më pas aplikohen në rezultatet e pyetjes, duke kthyer një rresht të vetëm përmbledhës.
Për shembull, merrni parasysh pyetjen komplekse të mëposhtme: gjeni të ardhurat totale mesatare për frymë, shumën e të ardhurave totale të banorëve dhe të ardhurat mesatare të burimit si përqindje e të ardhurave totale të banorit. Përgjigja jepet nga operatori
SELECT AVG (SUMD), SUM (SUMD), (100 * AVG (PARA / SUMD)) FROM PERSON, FITIMI, HAVE_D WHERE PERSON.NOM = HAVE_D.NOM DHE HAVE_D.ID = PROFIT.ID
Pa funksione agregate, pyetja do të dukej kështu:
ZGJIDH SUMD, SUMD, PARA / SUMD FROM PERSON, FITIMI, HAVE_D WHERE PERSON.NOM = HAVE_D.NOM DHE HAVE_D.ID = PROFIT.ID
dhe do të kthente një rresht rezultatesh për çdo banor dhe burim specifik të ardhurash. Funksionet agregate përdorin kolonat në tabelën e rezultateve të këtij pyetësori për të prodhuar një tabelë me një rresht me rezultatet përmbledhëse.
Në rreshtin e kolonave të kthyera, mund të specifikoni një funksion agregat në vend të çdo emri kolone. Për shembull, mund të përfshihet në një shprehje që shton ose zbret vlerat e dy funksioneve agregate:
SELECT MAX (SUMD) -MIN (SUMD) NGA PERSONI
Megjithatë, një funksion agregat nuk mund të jetë një argument për një funksion tjetër agregat, d.m.th. Funksionet agregate të mbivendosur janë të ndaluara.
Për më tepër, funksionet agregate dhe emrat e kolonave të rregullta nuk mund të përdoren në listën e kolonave të kthyera në të njëjtën kohë, pasi nuk ka asnjë pikë në këtë, për shembull:
ZGJIDH FIO, SHUMË (SHUMË) NGA PERSONI
Këtu, elementi i parë i listës udhëzon DBMS të krijojë një tabelë që do të përbëhet nga disa rreshta dhe do të përmbajë një rresht për çdo banor. Artikulli i dytë në listë kërkon që DBMS të marrë një vlerë rezultati, që është shuma e vlerave në kolonën SUMD. Këto dy udhëzime kundërshtojnë njëra-tjetrën, duke rezultuar në një gabim.
Sa më sipër nuk zbatohet për rastet e përpunimit të nënpyetjeve dhe pyetjeve me grupim.
Si mund të zbuloj numrin e modeleve të PC-ve të prodhuara nga një shitës i caktuar? Si të përcaktohet çmimi mesatar për kompjuterët me të njëjtat specifika? Këto dhe shumë pyetje të tjera që lidhen me disa informacione statistikore mund të përgjigjen duke përdorur funksionet përmbledhëse (agregate).... Standardi parashikon funksionet e mëposhtme agregate:
Të gjitha këto funksione kthejnë një vlerë të vetme. Në këtë rast, funksionet COUNT, MIN dhe MAX janë të zbatueshme për çdo lloj të dhënash, ndërsa SHUMË dhe AVG përdoren vetëm për fushat numerike. Dallimi midis funksionit COUNT (*) dhe COUNT (<имя поля>) është se e dyta nuk merr parasysh vlerat NULL gjatë llogaritjes.
Shembull. Gjeni çmimin minimal dhe maksimal për kompjuterët personalë:
Shembull. Gjeni numrin e kompjuterëve të disponueshëm nga prodhuesi A:
Shembull. Nëse jemi të interesuar për numrin e modeleve të ndryshme të prodhuara nga prodhuesi A, atëherë pyetja mund të formulohet si më poshtë (duke përdorur faktin se në tabelën e produktit çdo model regjistrohet një herë):
Shembull. Gjeni numrin e modeleve të ndryshme të disponueshme nga prodhuesi A. Pyetja është e ngjashme me atë të mëparshme, në të cilën kërkohej të përcaktohej numri i përgjithshëm i modeleve të prodhuara nga prodhuesi A. Këtu duhet të gjeni edhe numrin e modeleve të ndryshme në Tabela për PC (dmth. e disponueshme në treg).
Për të siguruar që përdoren vetëm vlera unike kur merren tregues statistikorë, kur argumenti i funksioneve agregate mund të përdoret Parametri DISTINCT... Një tjetër parametri ALLështë parazgjedhja dhe supozon që të gjitha vlerat e kthyera në kolonë janë numëruar. Operator,
Nëse duhet të marrim numrin e modeleve të PC-ve të prodhuara çdo prodhuesi, do t'ju duhet të përdorni GRUPI PËR klauzolë në vijim sintaksor KU klauzolat.
GRUPI PËR klauzolë
GRUPI PËR klauzolë përdoret për të përcaktuar grupet e linjave të daljes në të cilat mund të aplikohen funksionet e përgjithshme (COUNT, MIN, MAX, AVG dhe SUM)... Nëse kjo klauzolë mungon dhe përdoren funksionet e përgjithshme, atëherë të gjitha kolonat me emrat e përmendur në ZGJIDH duhet të përfshihen në funksionet agregate, dhe këto funksione do të aplikohen në të gjithë grupin e rreshtave që plotësojnë kallëzuesin e pyetjes. Përndryshe, të gjitha kolonat e listës SELECT, nuk përfshihen në funksionet agregate, duhet të specifikohet në klauzolë GRUPI SIPAS... Si rezultat, të gjitha linjat e daljes së pyetjes ndahen në grupe të karakterizuara nga të njëjtat kombinime vlerash në këto kolona. Pas kësaj, funksionet agregate do të aplikohen për secilin grup. Vini re se për GROUP BY të gjitha vlerat NULL trajtohen si të barabarta, d.m.th. kur grupohen nga një fushë që përmban vlera NULL, të gjitha rreshtat e tillë do të bien në një grup.Nëse nëse ka një klauzolë GROUP BY, në klauzolën SELECT nuk ka funksione agregate, atëherë pyetja thjesht do të kthejë një rresht nga secili grup. Kjo veçori, së bashku me fjalën kyçe DISTINCT, mund të përdoret për të eliminuar rreshtat e dyfishta në grupin e rezultateve.
Le të hedhim një vështrim në një shembull të thjeshtë:
| SELECT model, COUNT (model) AS Qty_model, AVG (çmim) AS çmim_mesatar NGA PC GRUPI SIPAS modelit; |
Në këtë kërkesë, për çdo model PC, përcaktohet numri i tyre dhe kostoja mesatare. Të gjitha rreshtat me të njëjtat vlera modeli formojnë një grup, dhe dalja SELECT llogarit numrin e vlerave dhe vlerat mesatare të çmimeve për secilin grup. Kërkesa do të rezultojë në tabelën e mëposhtme:
| model | Sasia_model | Çmimi_mesatar |
| 1121 | 3 | 850.0 |
| 1232 | 4 | 425.0 |
| 1233 | 3 | 843.33333333333337 |
| 1260 | 1 | 350.0 |
Nëse SELECT kishte një kolonë me një datë, atëherë do të ishte e mundur të llogariteshin këta tregues për çdo datë specifike. Për ta bërë këtë, ju duhet të shtoni një datë si një kolonë grupimi, dhe më pas do të llogariten funksionet e përgjithshme për çdo kombinim vlerash (model-datë).
Ka disa specifike rregullat për kryerjen e funksioneve agregate:
- Nëse si rezultat i pyetjes asnjë rresht nuk është marrë(ose më shumë se një rresht për një grup të caktuar), atëherë mungojnë të dhënat fillestare për llogaritjen e ndonjë prej funksioneve agregate. Në këtë rast, rezultati i ekzekutimit të funksioneve COUNT do të jetë zero, dhe rezultati i të gjitha funksioneve të tjera do të jetë NULL.
- Argumenti funksioni agregat vetë nuk mund të përmbajë funksione agregate(funksioni nga funksioni). ato. në një pyetje, ju nuk mund të merrni, të themi, vlerat mesatare maksimale.
- Rezultati i ekzekutimit të funksionit COUNT është numër i plotë(SHUMË I PLOTË). Funksionet e tjera agregate trashëgojnë llojet e të dhënave të vlerave që përpunohen.
- Nëse, gjatë ekzekutimit të funksionit SUM, është marrë një rezultat që tejkalon vlerën maksimale të llojit të të dhënave të përdorur, gabim.
Pra, nëse kërkesa nuk përmban GRUP SIPAS klauzolave, pastaj funksionet agregate të përfshira në Klauzola SELECT, ekzekutohen në të gjitha linjat e pyetjeve që rezultojnë. Nëse kërkesa përmban GRUPI PËR klauzolë, çdo grup rreshtash që ka të njëjtat vlera të grupit të kolonave ose kolonave të specifikuara në GRUPI PËR klauzolë, përbën një grup, dhe funksionet agregate kryhen për secilin grup veç e veç.
Klauzola e PASJES
Nëse Klauzola WHERE përcakton një kallëzues për filtrimin e rreshtave, më pas Duke pasur ofertë aplikuar pas grupimit për të përcaktuar një grup të ngjashëm filtrues kallëzues sipas vlerave funksionet agregate... Kjo klauzolë është e nevojshme për të testuar vlerat që janë marrë duke përdorur funksioni agregat jo nga linja të veçanta të burimit të regjistrimit të përcaktuar në Klauzola NGA, dhe nga grupe të linjave të tilla... Prandaj, një kontroll i tillë nuk mund të përfshihet Klauzola WHERE.
