16 Agregeerivad funktsioonid veeruavaldistena
Mitteagregeeriva ja agregeeriva funktsiooni definitsioonid
Enne, kui hakkame agregeerivaid funktsioone õppima, on hea hetk uurida, mida üldse tähendavad SQL-i kontekstis mitteagregeeriv ja agregeeriv.
Mitteagregeerivad funktsioonid annavad igale funktsioonile vastava kirje kohta ühe tulemuse.
Näiteks, järgneva mitteagregeerivat funktsiooni sisaldava päringu korral on päringu tulemuses sama palju kirjeid kui tabelis endas:
SELECT id, LEFT(nimi, 3) FROM isikud;
See tähendab seda, et mitteagregeeriva funktsiooni LEFT kasutamine ei vähenda iseenesest tulemuses olevate kirjete arvu. Kui peale mitteagregeeriva funktsiooni kasutamise lisada ka piiranguid, siis muidugi väheneb tulemuses olevate kirjete arv.
Mitteagregeeriva funktsiooni definitsiooni on ilmselt lihtsam lahti mõtestada, kui vaadata ka agregeeriva funktsiooni oma.
Agregeerivad funktsioonid annavad kõigi funktsioonile vastavate kirjete kohta ühe tulemuse. Agregeeriv funktsioon võib näiteks kõik mingi veeru väärtused kokku summeerida või arvutada nende keskmise. Nende tehete käigus jääbki kõigi kirjete asemel alles üksainus.
Agregeerivad funktsioonid
Alustame kirjete keskmise leidmisega, kasutades selleks agregeerivat funktsiooni AVG. See on väga kasulik funktsioon, kuna see võimaldab andmetest kiiresti (üldiseid) kokkuvõtteid teha.
Jooksuta järgnevate päringut:
SELECT AVG(rahvastikuarv) FROM riigid;
Näeme, et päringu tulemuses on vaid üks kirje, mis näitab meile kõigi tabelis Riigid olevate riikide keskmist rahvastikuarvu.
Pane tähele, et agregeeriva funktsiooni kasutamisel kaob veeru algne pealkiri nagu ka siis, kui kasutasime aritmeetilisi teheteid veeruavaldistena. Siinkohal asendatakse pealkiri funktsiooni enda nimega.
Kuigi pealkiri avg oli näite puhul enam-vähem sobiv, jääb päringu tulemust vaadates siiski segaseks, mille keskmisega on tegu. Tuleta varasemast meelde, kuidas sai pealkirju ise määrata ning jooksuta päringut uuesti nii, et pealkiri oleks sobiv.
Vaatame nüüd täpsemalt, miks eelnev päring sellise tulemuse andis.
Kui jagada päring kaheks osaks – SELECT nimi ja SELECT AVG(rahvastikuarv) – siis esimene osa tahab meile tagastada kõigi riikide nimed ning teine osa tahab tagastada üheainsa arvu. Seega ei saa neid niimoodi kokku panna, kuna nende ühend ei ole loogiliselt tähenduslik. Lihtsamalt öeldes ei saa neid kahte väärtust tulemuses korraga korrektselt kuvada.
Seda, kuidas siiski koostada huvitavaid päringuid, milles kasutatakse nii agregeerivaid kui ka mitteagregeerivaid osi, vaatame järgmises peatükis.
Erinevalt mitteagregeerivatest funktsioonidest ei ole agregeerivaid funktsioone tavapäraste andmetüüpidega tunnuste jaoks ülemäära palju. Siiski, keskendume taaskord vaid mõnele kasulikemale neist. Soovi korral saab taaskord nende kõigi kohta täpsemalt lugeda PostgreSQL’i dokumentatsioonist, siit.
Peale kirjete keskmise, saab agregeerivate funktsioonide abil leida ka vähimaid ja suurimaid kirjeid. Selle jaoks on funktsioonid MIN ja MAX. Neid kasutatakse täpselt samamoodi nagu funktsiooni AVG.
Kuigi funktsiooni MAX kasutamine sõnede peal võib tunduda veider, tuleta meelde, kuidas me piirasime juba varasemas peatükis päringut sõnelise väärtuse alusel, kirjutades nimi < 'Estonia'.
Veel üks kasulik agregeeriv funktsioon on SUM, mis summeerib kõik ette antavad kirjed kokku. Ka selle kasutus on samalaadne seni õpitud funktsioonidega AVG, MIN ja MAX.
Selleks, et veenduda eelneva vastuse korrektsuses, jooksuta ka nii-öelda õigemat päringut ning võrdle tulemusi:
SELECT AVG(pindala) AS keskmine_pindala FROM riigid;.
Üks olulisim, kuid kohati ka keerukaim agregeeriv funktsioon on COUNT. Selles peatükis vaatame selle lihtsamaid kasutusvõimalusi ning järgmises uurime juba keerukamaid.
Jooksuta järgnevaid päringuid, võrdle nende tulemusi ning mõtiskle, mida funktsioon COUNT teeb:
SELECT COUNT(*) FROM teosed;SELECT COUNT(riigi_id) FROM teosed;
Näeme, et päringute tulemused erinevad. Vajadusel tuleta peatüki Lihtsad päringud abil meelde, mida sümbol * (tärn) tähendas.
Vahe päringute tulemustes tuleneb sellest, et COUNT(*) loeb kokku, mitu kirjet on tabelis Teosed. COUNT(riigi_id) loeb aga kokku, mitu kirjet on tabelis Teosed veerus riigi_id. Kuna veerg riigi_id lubab puuduvaid väärtusi NULL ning neid seal tõepoolest ka on, siis on osa COUNT(riigi_id) sisaldava päringu tulemuses vähem kirjeid.
Nüüd oleme õppinud vahet eristama mitteagregeerivaid ja agregeerivaid funktsioonide ning oskame päringute loomisel erinevaid agregeerivaid funktsioone kasutada. Seega saame hakata õppima agregeerivaid funktsioone kasutavate päringute tulemusi grupeerima, piirama ja järjestama.
