17 Grupeerimine ja gruppide piiramine

Grupeerimine

Vaatame, kuidas on võimalik agregeerivaid funktsioone kasutavate päringute tulemusi grupeerida, mida see üldse tähendab ning kuidas see meile kasulik on.

Eelmise peatükis katsetasime me järgnevat päringut:

• SELECT nimi, AVG(rahvastikuarv) FROM riigid;

Päringu jooksutamisel nägime veateadet, kus mainiti märksõnapaari GROUP BY ning seda, et tunnus nimi peab sellega koos esinema. Jooksutame seega järgnevalt parandatud päringut:

• SELECT nimi, AVG(rahvastikuarv) FROM riigid GROUP BY nimi;

Näeme, et selline “parandatud” päring töötab, kuid et see on võrdlemisi mõttetu, kuna selle tulemus on võrdne järgneva, lihtsama päringu tulemusega:

• SELECT nimi, rahvastikuarv FROM riigid;

Kas GROUP BY kasutamine ning grupeerimine on seega mõttetu? Kindlasti mitte.

Põhjus, miks ülalolev märksõnu GROUP BY kasutav päring nii-öelda huvitav ei ole, on lihtne. Päringu jooksutamisel täidetakse esmalt GROUP BY osa ehk riike grupeeritakse tunnuse nimi alusel. See tähendab seda, et kõik sama nimega riigid pannakse ühte gruppi. Seejärel kuvatakse tulemuses iga grupi nimi ning gruppides olevate riikide keskmine rahvastikuarv. Kuna kõigil riikidel on erinevad nimed, on igas grupis vaid üks riik ning iga grupi keskmine rahvastikuarv ongi selle riigi rahvastikuarv.

Jooksutame nüüd järgnevat päringut:

• SELECT LEFT(nimi, 1), AVG(rahvastikuarv) FROM riigid GROUP BY LEFT(nimi, 1);

Seekord saime juba palju huvitavama tulemuse. Nüüd grupeeriti riike nimede algustähtede alusel ning keskmised rahvastikuarvud arvutati nendesse gruppidesse kuuluvate riikide rahvastikuarvude põhjal.

Pane tähele, et kasutades päringulause SELECT osas mitteagregeerivaid avaldisi või funktsioone, nagu LEFT(nimi, 1) või ka lihtsalt nimi, peab see kindlasti esinema samal kujul ka GROUP BY osas. See ei kehti aga agregeerivate funktsioonide puhul, nagu eelnevas lauses kasutatud AVG(rahvastikuarv).

Kasutades grupeerimist võib SELECT osas olla mitteagregeerivaid avaldisi või funktsioone vabalt ka mitu, kuid samamoodi tuleb need kõik ka GROUP BY osas üles loetleda. See teadmine on ennekõike kasulik siis, kui luua päringuid mitme tabeli põhjal, mida vaatame järgmises peatükis.

Kuna grupeerimine on midagi täiesti uut ja pöörab seni õpitu kohati pea peale, on parim viis selle kasutama õppimiseks seda palju harjutada. Proovi ka ise koostada erinevaid huvitavaid päringuid kasutades märksõnu GROUP BY ja kombineeri seda kõige muu varem õpituga.

Gruppide piiramine

Siiani oleme me päringuid piiranud vaid märksõna WHERE abil. Ka õpitud grupeerimist kasutavaid päringuid saab edukalt piirata, kuid selleks tuleb lisaks märksõnale WHERE kasutada aeg-ajalt ka uut märksõna HAVING. Kuna kumba neist kasutada, vaatame peatselt.

Jooksuta järgnevat päringut:

• SELECT naitleja_id, COUNT(*) FROM naitlemine GROUP BY naitleja_id HAVING COUNT(*) >= 25;

Näeme, et märksõna HAVING ja sellele järgnev osa käib seniõpitud päringustruktuuri lõppu, nii nagu WHERE käis tavapäraste päringute puhul.

Agregeeriva funktsiooni kasutamine koos mitteagregeerivate tunnuste, avaldiste, funktsioonide ja muu taolisega tähendab automaatselt seda, et on tarvis grupeerida. Seega saab agregeeriva funktsiooni väärtuse põhjal piiramiseks kasutada vaid märksõna HAVING. Mitteagregeerivate päringu osade põhjal piiramiseks saab kasutada nii märksõna WHERE kui ka märksõna HAVING. Esimene neist on enne ning teine pärast grupeerimist piiramiseks.

Seega, kui tahta eelnevat päringut piirata ka mitteagregeeriva tunnuse naitleja_id põhjal, saaks seda teha enne GROUP BY osa märksõnaga WHERE või pärast grupeerimist märksõnaga HAVING.

Tänu piirangu kasutamisele nägime eelneva päringu tulemuses vaid nende näitlejate ID-sid, kes on näidelnud 25-s või rohkemas Netflixis olevas teoses. Mõnes mõttes võib seda tõlgendada kui populaarsete (või ka töökate) näitlejate nimekirja.

Kui soovime ka teada saada, mis on nende populaarsete näitlejate nimed, peaksime (praeguste teadmiste kohaselt) nende ID-d meelde jätma ning neid seejärel tabelist Isikud otsima hakkama. Kuna tabelis Isikud tabelis on tuhandeid kirjed, on seda väga tüütu teha.

Õnneks on selle protsessi kiirendamiseks võimalik tabelite Data vaates kasutada filtreerimist, nii nagu alloleval pildil.

Luues päringut mitme tabeli põhjal on päringulauset võimalik koostada ka nii, et selle tulemuses oleksidki koheselt nimed, mitte ID-d. Seda õpime juba järgmises peatükis.

Tasub teada, et üldjoontes töötab agregeeritud väärtuse piiramine vägagi sarnaselt tavapärasele piiramisele ning et see toetab kõiki varasemalt õpitud piiramise abimärksõnu.

Mõlemaid piiramiseks mõeldud märksõnu, WHERE ja HAVING, võib (ja sageli ka tuleb) kasutada ühes päringulauses erinevate tunnuste, avaldiste, funktsioonide jm alusel piiramiseks. Sealjuures tuleb hoolikalt valida, kumma märksõna abil midagi piiratakse.

Nüüd oleme õppinud kasutama grupeerimist agregeerivaid funktsioone kasutavates päringutes ning oskame ka loodud gruppe piirata. Seega saame õppida päringute tulemusi järjestama ning seda, kuidas päringuid mitme tabeli põhjal koostada.