15 Aritmeetilised tehted ja mitteagregeerivad funktsioonid veeruavaldistena

Aritmeetilised tehted veeruavaldistena

Üldiselt päritakse andmebaasist kõigepealt sobivad andmed kätte ning alles siis tehakse nende abil tehteid. Siiski, vahel on mugav või lausa vajalik teha tehteid juba pärimise ajal. Näiteks, aritmeetilisi tehteid saab kasutada veeruavaldistena selleks, et päringuid uutmoodi piirata. Vaatame peatselt ka näidete põhjal, kuidas see käib.

Jooksuta järgnevat päringut ning mõtiskle, mida sa päringu tulemuses näed:

• SELECT nimi, rahvastikuarv/pindala FROM riigid;

Pane tähele, et kuigi me kasutasime kolme erinevat tabelis olevat tunnust (nimi, rahvastikuarv ja pindala), on päringu tulemuses veerge vaid kaks.

Võisid ka märgata, et teisel veerul pole korralikku pealkirja. Seda seetõttu, et PostgreSQL, DBeaver ega miski muu tarkvara ei oska ennustada, mida tähistavad tehete tulemusena tekkivad veerud. Meie ise siiski mõistame, et rahvastikuarvu pindalaga jagades saame rahvastikutiheduse.

Selleks, et teisel veerul oleks inimeste jaoks loetaval ja kenal kujul pealkiri, kirjutame eelneva päringu ümber nii:

• SELECT nimi, rahvastikuarv/pindala AS 'rahvastikutihedus' FROM riigid;

Õppisime siinkohal ära uue märksõna AS. Selle kasutus ei piirdu aritmeetiliste tehetega, vaid seda võib kasutada ükskõik milliste veergude ümber nimetamiseks.

Näiteks, kui me oleks eelmise päringu alguses kirjutanud nimi asemel nimi AS riigi_nimi paistaksid päringu tulemuses veerud riigi_nimi ja rahvastikutihedus.

Pane tähele, et jagasime tunnuse rahvastikuarv väärtusi arvuga 1000000.0, mitte 1000000. Tegime seda seetõttu, et tunnuse rahvastikuarv andmetüüp on BIGINT ehk suur täisarv. Sama loogika kehtiks ka andmetüüpidega INTEGER ja SMALLINT. Jagades PostgreSQL’s omavahel kahte täisarvu toimub täisarvuline jagamine, mis tähendab seda, et näiteks avaldise 50/100 väärtus on 0, sest arv 50 sisaldab arvu 100 null korda.

Katsetage, mis juhtub, kui jagada eelnevas päringus tunnuse rahvastikuarv väärtusi täisarvuga 1000000.

Tasub teada, et märksõnaga AS määratud veeru pealkirja on mõnes SQL-i implementatsioonis võimalik kasutada sama päringu piiramiseks päringu WHERE osas, kuid PostgreSQL’i puhul see võimalik ei ole. Seda seetõttu, et lihtsalt öeldes täidetakse päringut jooksutades kõigepealt päringu WHERE osa ja alles siis SELECT osa. Vaatame järgnevat illustreerivat näidet.

Oletame, et me soovime leida vaeseid riike ning et meie mõiste vaesusest on see, et riigi SKP elaniku kohta on vähemalt 10 korda väiksem kui 50000.

PostgreSQL’i kasutades saame selliste riikide andmed ja kümnekordse SKP kätte järgneva päringuga:

• SELECT nimi, skp_elaniku_kohta * 10 AS kumnekordne_skp FROM riigid WHERE skp_elaniku_kohta * 10 <= 50000;

Mõnes SQL-i implementatsioonis saaks WHERE järel oleva avaldise skp_elaniku_kohta * 10 asendada eelnevalt määratud veeru pealkirjaga kumnekordne_skp. Selle funktsionaalsuse tugi teeb laused paremini loetavaks ning aitab vähendada päringute kirjutamisel tekkivate vigade arvu, eriti keerukamate tehete korral.

Mitteagregeerivad funktsioonid

Peale aritmeetiliste tehete saab veeruavaldistena kasutada ka erinevaid PostgreSQL’s olemasolevaid funktsioone. Neid funktsioone jaotatakse kaheks – mitteagregeerivad ja agregeerivad.

Ka andmebaasi kasutajatel on võimalik uusi funktsioone luua, kuid seda me selles moodulis ei käsitle.

Siin peatükis alustame mitteagregeerivate funktsioonide uurimisega, agregeerivaid vaatame järgmises. Mõlemad mõisted seletame samuti alles järgmises peatükis lahti. Seniks võib mitteagreegerivaid funktsioone vaadata kui viise päritavate tunnuste mingitmoodi töötlemiseks.

PostgreSQL’s on mitteagregeerivaid funktsioone väga palju ning neid kõiki ei jõua me siinkohal käsitleda. Seega, keskendume mõningale kasulikemale neist.

Soovi korral saab kõigi funktsioonide kohta täpsemalt lugeda PostgreSQL’i ametlikust dokumentatsioonist. Näiteks siit leiab sõnedele mõeldud funktsioonide dokumentatsiooni. Kasulikku infot ja abimaterjale leiab kindlasti ka muudest veebiallikatest, kuid enne tasub veenduda nende usaldusväärsuses.

Vaatame esmalt ühte ülesannet ja selle lahendust ning seejärel esimest kasulikku mitteagregeerivat funktsiooni, mis selle lahendamise märksa lihtsamaks teeb.

Eelolevale kirjeldusele vastavat päringut on võimalik kirja panna ka palju lühemalt. Selleks saame kasutada mitteagregeerivat funktsiooni LENGTH, mida kasutav päring näeb välja järgnev:

• SELECT pealkiri FROM teosed WHERE LENGTH(pealkiri) <= 3;

Näeme, et õigete tööriistade (funktsioonide) teadmisest on palju kasu, kuna see võimaldab meil päringuid lihtsamini moodustada. Samuti säilitame niiviisi päringute loetavuse.

Kuigi me kasutasime eelnevas näites mitteagregeerivat funktsiooni päringu piiramiseks, võib neid vabalt ka SELECT osas kasutada.

Oletame, et me soovime leida kõigi riikide (mitteametlikke) kolmetähelisi lühendid, jättes iga riigi nime puhul alles selle kolm esimest tähte. Seda saame teha järgneva päringuga:

• SELECT LEFT(nimi, 3) FROM riigid;

Nagu näha, siis funktsioonile LEFT järgnevad sulud, nii nagu ka funktsiooni LENGTH puhul. Siinkohal on sulgude sees aga kaks elementi – esimene on töödeldav sõne, siinkohal riigi nimi ning teine on arv, mitu sümbolit vasakult lugedes alles jätta.

Nagu võib arvata, on olemas ka funktsioon RIGHT. See töötab vastupidiselt ehk alles jäetakse sõnede parempoolsed sümbolid.

Tasub teada, et kui tunnuse väärtuse pikkus (eelnevate näidete puhul riigi nime pikkus) on väiksem kui arv, mis näitab mitu sümbolit alles jätta, ei teki viga, vaid jäetakse alles kõik selle väärtuse sümbolid.

Kuigi eelmise päringu tulemus oli üpriski huvitav ning kasulik, tahame me üldjuhul, et riikide lühendid oleks üleni suurtähtedega.

Seda saame teha järgneva modifitseeritud päringuga:

• SELECT UPPER(LEFT(nimi, 3)) FROM riigid;

Näeme, et funktsioon UPPER teisendab sellele järgnevate sulgude sisu üleni suurtäheliseks. Siinkohal võiks kirjutada UPPER ka tunnuse nimi ümber, kuid jõudluse poolest on ülal esitatud versioon parem, kuna teisendamist rakendatakse vaid 3 sümboli, mitte terve sõne peal.

Taaskord, nagu võib arvata, on olemas ka märksõna LOWER, mis töötab vastupidiselt ehk muudab ette antud tunnuse väärtused üleni väiketäheliseks.

Selleks, et sõnesi paindlikumalt tükeldada, kui võimaldavad funktsioonid LEFT ja RIGHT, saame kasutada uut funktsiooni SUBSTRING, mis töötab neile üpriski sarnaselt.

Vaatame taaskord riigi nimede lühendite näidet. Seekord soovime, et lühend algaks nime 2. sümbolist, oleks 5 sümbolit pikk ning et kõik lühendi tähed oleks väikesed.

Seda saame teha järgneva päringuga:

• SELECT LOWER(SUBSTRING(nimi, 2, 5)) FROM riigid;

Näeme, et funktsioon SUBSTRING võtab samuti esimese elemendina töödeldava tunnuse. Teine element on aga indeks, mis näitab, mis on esimene alles jäetav sümbol ning kolmas element on arv, mis näitab, mitu sümbolit tuleb alates indeksist (kaasa arvatud) alles jätta.

Tasub teada, et erinevalt enamustest programmeerimiskeeltest, ´kus indekseerimine algab 0-st, algavad PostgreSQL’s indeksid 1-st. Seega oligi eelnevas näites esimene alles jäetav sümbol riigi nime teine, mitte kolmas sümbol.

Siiani oleme päringutes kasutanud erinevaid tehteid ja funktsioone nii arvu kui ka sõne kujul olevate tunnuste töötlemiseks. Funktsioone aga leidub ka paljude teiste andmetüüpide jaoks, nagu TIMESTAMP.

PostgreSQL’s on tehtud kuupäevade töötlemine väga lihtsaks funktsiooni DATE_PART abil. Jooksuta järgnevat päringut ning proovi mõista, kuidas DATE_PART töötab ning kuidas seda veel kasutada saaks:

• SELECT pealkiri, vanusepiirang, zanr, DATE_PART('year', lisamise_kuupaev) FROM teosed;

Tasub teada, et DATE_PART aktsepteerib täpsustava argumendina veel näiteks sõnesid hour, minute ja second. Kuna tunnuse lisamise_kuupaev väärtused hetkel nii täpsed ei ole ehk need ei sisalda tunde, minuteid ja sekundeid, siis tagastatakse nende argumentide kasutamisel alati 0-i, sest vaikeväärtusena on kellaaeg 00:00:00.

Tasub ka teada, et funktsioon DATE_PART aktsepteerib kuupäevana spetsiaalset märksõna CURRENT_DATE. Nii saab päringuid luues garanteerida, et alati kasutatakse käesolevat aastat, kuud, päeva jne.

Kuigi lisamise kuupäeva põhjal ei ole eriti keeruline teose Netflixis olemise aega peast arvutada, on taolisi ülesandeid mõistlik ikkagi päringu abil sooritada, sest nii saab vanust kasutada näiteks piiramiseks. Vanuse leidmiseks on olemas funktsioon AGE, mida on ka funktsiooniga DATE_PART võimalik kombineerida.

Jooksuta järgnevat päringut, et leida teoste Netflixis olemise aega:

• SELECT pealkiri, AGE(lisamise_kuupaev) FROM teosed;

Nüüd oleme õppinud kasutama nii aritmeetilisi tehteid kui ka mitteagregeerivaid funktsioone veeruavaldistena. Seega saame hakata õppima ka keerukamaid, agregeerivaid funktsioone ning uurime, kuidas neid varasemalt õpituga kombineerida.