Aritmeetilised tehted veeruavaldistena
SQL pakub võimalust andmete pärimise ajal tabeli veergudega teha aritmeetilisi tehteid. Seda saab kasutada näiteks selleks, et tabelis hoida ruumi kokku. Selle asemel, et teha tabelisse uus veerg uue tunnusega, saame hoopis kasutada juba olemasolevaid veerge ning kuvada soovitav tulemus aritmeetilise tehtega. Veergudega saab teha tehteid omavahel või mõne konstandiga. Näiteks tahame leida, mis aastal said või saavad lemmikloomad 1-aastaseks
SELECT synniaeg +1 FROM lemmikloomad;
Käskusid jooksutades võid tähele panna, et veergudel, millega on tehtud mõni tehe, pole normaalsel kujul nime. See juhtub, kuna DBeaver ei tea, mida veeruga tehtud tehte tulemus peaks tähistama. Selleks, et veerule anda tema väärtuseid illustreeriv pealkiri saab kasutada AS võtmesõna/võtmesõnu. Kui võtmesõnu on enam kui 1, siis kasutada sõnade eristamiseks kas alakriipsu või panna fraas jutumärkide vahele.
SELECT synniaeg +1 AS "saab ühe aastaseks" FROM lemmikloomad;
Mitteagregeerivad funktsioonid
Veeruavaldistena saab veel lisaks kasutada PostgreSQL'i poolt pakutavaid sisseehitatud funktsioone. Need jaotatakse kaheks - mitteagregeerivad ja agregeerivad funktsioonid. Kõigepealt räägime mitteagreveerivatest funktsioonidest.
Mitteagreveerivaid funktsioone on palju ning nende kõikide kohta saab lugeda PostgreSQL dokumentatsioonist. Siin peatükis vaatame läbi mõned kasulikumad nendest.
Stringitüüpi tunnustele saab rakendada järgmiseid funktsioone:
- LENGTH (<veerg>)
- LEFT (<veerg>, arv)
- RIGHT (<veerg>, arv)
- UPPER (<veerg>)
- SUBSTRING(<veerg>,arv,arv)
LEFT ja RIGHT funktsioone kasutatakse sellest, et saada veeru kirjest kätte mingi kindel arv sümboleid, alustades sümbolite loendamist veerus, kas vasakult või paremalt. Arv parameeter funktsioonis määrabki ära sümbolite arvu. Näiteks, kui tahame teada saada, mis tähega algavad loomade nimed, siis kirjutame päringu:
SELECT LEFT(nimi, 1) FROM lemmikloomad;
Soovides aga, et korduvaid tähti poleks, siis nagu päringulause alguses rääkisime, on vaja kasutada SELECT DISTINCT
SELECT DISTINCT LEFT(nimi, 1) FROM lemmikloomad;
LENGTH funktsiooni kasutatakse peamiselt päringu WHERE osas ning sellega saab otsida andmeid veeru kirje tähemärkide arvu põhjal. Näiteks soovime leida loomade nimesid, mis oleks pikemad kui 5 sümbolit:
SELECT nimi FROM lemmikloomad WHERE LENGTH(nimi)>5;
Kuna PostgreSQL eristab väike- ja suurtähti, siis on kasulik teisendada sümbolid mõnikord vajalikku formaati. UPPER funktsioon teisendab kõik veerus olevad väiketähed suurtähtedeks (suurtähed jätab suurtähtedeks) ja LOWER funktsioon väiketähtedeks. SUBSTRING töötab sarnaselt LEFT ja RIGHT funktsioonidele. Erinevus seisneb selles, et me saame täpsemalt valida, kust me tahame veeru kirjet tükeldada ning mitu sümbolit me soovime. Näiteks kui soovime leida isikukoodist sünniaasta kahte viimast numbrit, siis
SELECT SUBSTRING(isikukood, 2, 2) FROM omanikud;
Esimene number funktsioonis määrab ära, millisest indeksist algab tükeldamine. Eelnevas näites on see 2, ehk tükeldamine algab teisest kirje sümbolist. Teine number määrab ära kui pikk see tükeldatud osa peab olema. Meie näites väljastatakse 2 sümbolit.
Funktsioone saab ka koos kasutada. Näiteks:
SELECT UPPER(RIGHT(eesnimi, 1) FROM omanikud;
Lõpuks tasuks ka teada funktsiooni DATE_PART, mida kasutatakse kuupäeva märkivate andmetüüpidega veergude töötlemiseks. Näiteks on sellega võimalik kätte saada TIMESTAMP andmetüübist ainult aasta.
SELECT DATE_PART('year', sisestatud)
FROM lemmikloomad;
DATE_PART võtab esimeseks argumendiks sõne, millega saabki märkid, millist osa kuupäevast sa soovid näha. Veel on võimalik 'year' asemel kirjutada näiteks 'month' ja 'day'.
Iga funktsioon töötab kindla andmetüübiga või kindlate andmetüüpidega. Näiteks kui meil oleks atribuut sisestatud varchar andmetüübiga, siis vaatamata sellele, et oleme sinna sisestanud kuupäeva koos aastaga, funktsioon DATE_PART ei töötaks. Samamoodi ei saaks me juhul kui isikukood on integer tüüpi eraldada sealt SUBSTRING, LEFT või RIGHT funktsioonidega vajalikke osi. See on ka üks põhjus, miks on vaja hoolega mõelda, mis andmetüüp on parim mingi atribuudi jaoks.
Agregeerivad funktsioonid
Agreveerivatest funktsioonidest toome ka välja mõned tähtsamad:
- Count(*)
- Count(distinct <veerg>)
- Max(<veerg>)
- Min(<veerg>)
- Sum(<veerg>)
- Avg (<veerg>)
COUNT funktsiooniga saab vaadata tabelis olevate kirjete arvu.
SELECT COUNT(*) FROM lemmikloomad;
Eelnev lause annab meile kõik tabelis lemmikloomad olevate kirjete arvu. Selle Funktsiooniga saab otsida ka mingi kindla veeru kirjete arvu. Sellisel juhul jäetakse loendamata sisestamata väljad ehk siis järgnev lause annab meile arvu, kui paljudel omanikel on elukoht sisestatud
SELECT COUNT(elukoht) FROM omanikud;
Veel saab funktsiooni sees kasutada DISTINCT märksõna. Näiteks kui sooviksime teada kui palju on erinevaid elukohti, siis kirjutame:
SELECT COUNT(DISTINCT elukoht) FROM omanikud;
MAX ja MIN funktsioonid tagastavad tabeli veeru vastavalt suurima või väikseima kirje. Funktsiooni saab kasutada nii sõnetüüpi tunnuste kui ka numbriliste tunnuste peal. Sõnede puhul vaatavad funktsioonid tähestiku järjekorda, ehk MAX tagastab tähestikus viimase kirje veerus.
SELECT MAX(synniaeg) FROM omanikud;
See lause annab meile omaniku sünniaja, kelle sünniaeg on kõige suurem ehk siis lihtsamalt öeldes kõige noorema omaniku sünniaja.
SUM funktsioon tagastab veerus olevate väärtuste summa ja AVG keskmise väärtuse. Näiteks järgmine lause annab meile lemmikloomade keskmise kaalu:
SELECT AVG(kaal) FROM lemmikloomad;
Mitteagreveerivate ja agreveerivate funktsioonide vahe seisneb selles, et agregeerivad funktsioonid annavad ühe tulemuse veeru kirjete kohta, millele funktsiooni rakendatakse, ehk SQL konsoolis näed tulemusena alati ühte väärtust. Mitteagreveerivad aga annavad ühe tulemuse iga veeru kirje kohta, mille peal funktsiooni kasutatakse. Mitteagregeerivaid ja agregeerivaid funktsioone ei saa korraga läbisegi kasutada. Kuidas seda siiski teha, saad teada päringulause (jätk).
Vaata videost, kuidas kasutada päringus erinevaid funktsioone:
Ja saad katsetada oma teadmisi
| LIKE operaator | Päringulause (jätk) |