Osa 2

Yhteenvetokyselyiden tekeminen ja tulosten ryhmittely

Tiedon hakemiseen käyttämämme SQL-kyselyt ovat tähän mennessä tuottaneet listauksia tietokantataulujen sisällöistä. Listauksia tuottavat kyselyt ovat erittäin hyödyllisiä, kun halutaan vastata esimerkiksi kysymyksiin kuten "Listaa kaikki opiskelijat, jotka ovat osallistuneet kurssille tietokantojen perusteet" tai "Listaa kaikki kurssit, joille annettu opiskelija on ilmoittautunut". Kysymykset kuten "Kuinka moni opiskelija on osallistunut kurssille tietokantojen perusteet" ovat kuitenkin vaatineet manuaalista työtä, sillä kyselyn tulosrivit on pitänyt laskea käsin.

SQL-kieli tarjoaa välineitä yhteenvetokyselyiden tekemiseen. Tällaisia kyselyitä ovat esimerkiksi juurikin yllä mainittu "kuinka moni" — eli tulosrivien määrä — sekä erilaiset summa- ja keskiarvokyselyt. Käytännössä yhteenvetokyselyt tehdään SQL-kielen tarjoamien funktioiden avulla. Alla on listattuna muutamia tyypillisimpiä funktioita, joita tietokantakyselyissä käytetään.

Tavoite Funktio Esimerkki
Rivien lukumäärän selvittäminenCOUNT SELECT COUNT(*) FROM Taulu
Sarakkeen lukujen summan laskeminenSUM SELECT SUM(sarake) FROM Taulu
Sarakkeen lukujen keskiarvon laskeminenAVGSELECT AVG(sarake) FROM Taulu
Sarakkeen lukujen keskihajonnan laskeminenSTDDEV SELECT STDDEV(sarake) FROM Taulu
Sarakkeen lukujen minimin selvittäminenMIN SELECT MIN(sarake) FROM Taulu
Sarakkeen lukujen maksimin selvittäminenMAX SELECT MAX(sarake) FROM Taulu

Tarkastellaan näitä kyselyitä hieman tarkemmin. Oletetaan, että käytössämme on lentomatkoja kuvaava tietokantataulu Lentomatka, joka sisältää sarakkeet yhtio, lahtopaikka, maaranpaa, pituus. Pituus esitetään minuutteina.

yhtiolahtopaikkamaaranpaapituus
Air BerlinHelsinkiBerliini205
FinnairHelsinkiOulu70
FinnairHelsinkiBerliini200
FinnairHelsinkiTukholma50
FinnairHelsinkiMallorca230
NorwegianHelsinkiMallorca240

Yhteenvetokyselyiden avulla saamme selville erilaisia tilastoja. Alla muutamia esimerkkejä:

  • Kuinka monta matkaa tietokantataulussa Lentomatka on yhteensä?

    SELECT COUNT(*) FROM Lentomatka;
  • Kuinka monta lentoyhtiötä on tietokantataulussa lentomatka? (Huomaa avainsanan DISTINCT käyttö)

    SELECT COUNT(DISTINCT yhtio) FROM Lentomatka;
  • Kuinka monta lentoa taulussa on Helsingistä Mallorcalle?

    SELECT COUNT(*) FROM Lentomatka
    WHERE lahtopaikka = 'Helsinki' AND maaranpaa = 'Mallorca';
  • Mikä on keskimääräinen Finnairin lennon pituus?

    SELECT AVG(pituus) FROM Lentomatka
    WHERE yhtio = 'Finnair';
  • Mikä on lyhin matkan kesto Helsingistä Berliiniin?

    SELECT MIN(pituus) FROM Lentomatka
    WHERE lahtopaikka = 'Helsinki' AND maaranpaa = 'Berliini';
  • Mikä on Finnairin lentojen pituuksien minimi, maksimi, keskiarvo ja keskihajonta?

    SELECT MIN(pituus), MAX(pituus), AVG(pituus), STDDEV(pituus)
    FROM Lentomatka
    WHERE yhtio = 'Finnair';

Kyselyn tulosten ryhmittely

Entä jos haluaisimme saada selville yhtiökohtaisia tietoja kuten vaikkapa jokaisen yhtiön lyhimmän lennon? Tarkastellaan tätä seuraavaksi.

Tulosten ryhmittely tapahtuu komennon GROUP BY avulla. Komento lisätään SELECT-kyselyyn taulujen listauksen ja mahdollisten rajausehtojen jälkeen. Komentoa GROUP BY seuraa sarake, jonka perusteella tulokset ryhmitellään. Jotta ryhmittelystä tulee mielekäs, näkyy ryhmittelyn peruste tyypillisesti myös SELECT-komentoa seuraavassa sarakelistauksessa.

SELECT ryhmittelysarake, FUNKTIO(laskettava) FROM Taulu
    GROUP BY ryhmittelysarake;

Jatketaan lentoyhtiöiden parissa. Alla esimerkkejä ryhmittelyn toiminnasta.

  • Kuinka monta matkaa kullakin lentoyhtiöllä on tarjolla?

    SELECT yhtio, COUNT(*) FROM Lentomatka GROUP BY yhtio;
  • Kuinka monta alle 100 minuutin pituista lentomatkaa eri kaupungeista lähtee?

    SELECT lahtopaikka, COUNT(*) FROM Lentomatka
    WHERE pituus < 100
    GROUP BY lahtopaikka;
  • Kuinka pitkiä kunkin lentoyhtiön matkat ovat keskimäärin?

    SELECT yhtio, AVG(pituus) FROM Lentomatka GROUP BY yhtio;
  • Mikä on kunkin yhtiön tarjoamien matkojen pituuksien keskiarvo ja keskihajonta?

    SELECT yhtio, AVG(pituus), STDDEV(pituus)
    FROM Lentomatka GROUP BY yhtio;

Useampaa taulua käsittelevät yhteenvetokyselyt

Yhteenvetokyselyt ovat lisätoiminnallisuus normaaleihin kyselyihin, joten taulujen yhdistäminen toimii myös yhteenvetokyselyissä — ryhmittelykomento tulee mahdollisten WHERE-ehtojen jälkeen.

Oletetaan seuraavat taulut Kurssi ja Kurssitehtava.

CREATE TABLE Kurssi (
    id INTEGER,
    nimi VARCHAR(64),
    opintopisteet INTEGER,
    PRIMARY KEY (id)
);

CREATE TABLE Kurssitehtava (
    id INTEGER,
    kurssi_id INTEGER,
    tehtava VARCHAR(1024),
    PRIMARY KEY (id),
    FOREIGN KEY (kurssi_id) REFERENCES Kurssi (id)
);

Taulujen sisältö on seuraava. Ensin taulu Kurssi.

idnimiopintopisteet
1Ohpe5
2Ohja5
3Tikape5
4OTM5

Ja sitten taulu Kurssitehtava.

idkurssi_idtehtava
11Kirjoita ohjelmia uudelleen ja uudelleen
21Ja vielä vähän lisää
31Ja kertaa toki myös käsitteistöä
42Jatka Ohpen hengessä
52Ja huomaat että olet päässyt pitkälle
63Pohdi tiedon syvintä merkitystä

Kurssikohtaisten tehtävien lukumäärän laskeminen onnistuu seuraavasti. Avainsana AS muuntaa tuloksena saatavassa taulussa olevan sarakkeen nimen.

SELECT Kurssi.nimi AS kurssi, COUNT(*) AS tehtavia FROM Kurssi
    JOIN Kurssitehtava ON Kurssi.id = Kurssitehtava.kurssi_id
    GROUP BY Kurssi.nimi;

Kyselyn tulos on seuraava.

kurssi tehtavia
Ohpe3
Ohja2
Tikape1

Tarkastellessamme kyselyn tuloksia huomaamme, että tuloksissa ei ole yhtäkään tehtävätöntä kurssia. Kurssi OTM puuttuu täysin listauksesta. Tämä selittyy kyselyllämme — olemme valinneet mukaan vain rivit, joilla hakuehdot täyttyvät. Kirjoitetaan edellinen kysely siten, että otamme huomioon kurssit vaikka niihin ei liittyisikään yhtäkään toisen taulun riviä — käytämme siis LEFT JOIN-kyselyä.

SELECT Kurssi.nimi AS kurssi, COUNT(Kurssitehtava.id) AS tehtavia FROM Kurssi
    LEFT JOIN Kurssitehtava ON Kurssi.id = Kurssitehtava.kurssi_id
    GROUP BY Kurssi.nimi

Kyselyn tulos on seuraava.

kurssi tehtavia
Ohpe3
Ohja2
Tikape1
OTM0

Edellä COUNT-funktiolle annetaan parametrina kurssitehtävän id. Jos funktiolle annetaan parametrina *, myös null-arvo — eli tyhjä — lasketaan arvoksi(ainakin joissain tietokannanhallintajärjestelmissä).

Ryhmittely useamman sarakkeen perusteella

Oletetaan edellä kuvattujen taulujen lisäksi taulut Kurssisuoritus ja Opiskelija. Taulut on määritelty seuraavasti.

CREATE TABLE Opiskelija (
    id INTEGER,
    opiskelijanumero INTEGER,
    nimi VARCHAR(64),
    syntymavuosi INTEGER,
    PRIMARY KEY (id)
);

CREATE TABLE Kurssisuoritus (
    id INTEGER,
    kurssi_id INTEGER,
    opiskelija_id INTEGER,
    arvosana INTEGER,
    paivamaara DATE,
    PRIMARY KEY (id),
    FOREIGN KEY (kurssi_id) REFERENCES Kurssi(id),
    FOREIGN KEY (opiskelija_id) REFERENCES Opiskelija(id)
);

Komennolle GROUP BY voi antaa useampia sarakkeita, jolloin ryhmittely tapahtuu sarakeryhmittäin. Esimerkiksi ryhmittely GROUP BY kurssi, arvosana — mikäli tällaiset sarakkeet olisi olemassa — ryhmittelisi taulussa olevat rivit ensin kurssin perusteella, jonka jälkeen kurssikohtaiset ryhmät ryhmiteltäisiin vielä arvosanan perusteella. Tällöin jokaiselle kurssille tulisi erilliset arvosanaryhmät.

Kurssikohtaiset arvosanaryhmät saa selville seuraavalla kyselyllä.

SELECT Kurssi.nimi AS kurssi, Kurssisuoritus.arvosana AS arvosana, COUNT(*) AS lukumaara
    FROM Kurssi
    JOIN Kurssisuoritus ON Kurssi.id = Kurssisuoritus.kurssi_id
    GROUP BY Kurssi.nimi, Kurssisuoritus.arvosana;

Hakutulosten rajaaminen yhteenvetokyselyissä

Yhteenvetokyselyissä laskettavat tulokset kuten summa, rivien lukumäärä ja keskiarvo muodostetaan vasta, kun kaikki kyselyn rivit on selvillä. Kyselyiden tuloksen rajaamiseen käytetty WHERE toimii siten, että se tarkastelee tuloksia riveittäin — se ei osaa odottaa summan laskemisen lopputulosta.

Mikäli yhteenvetokyselyn tuloksen perusteella halutaan rajata tuloksia, tulee käyttää HAVING-käskyä. Käskyn HAVING kuvaama ehto tarkastetaan vasta sitten, kun yhteenvetokyselyn tulokset ovat selvillä. Ehto HAVING lisätään ryhmittelykyselyn jälkeen esimerkiksi seuraavalla tavalla.

SELECT Kurssi.nimi AS kurssi, AVG(Kurssisuoritus.arvosana) AS keskiarvo
    FROM Kurssi
    JOIN Kurssisuoritus ON Kurssi.id = Kurssisuoritus.kurssi_id
    GROUP BY Kurssi.nimi
    HAVING keskiarvo < 2;

Yllä olevalla kyselyllä saadaan selville ne kurssit, joihin liittyvien kurssisuoritusten keskiarvo on alle 2.

Hakutulosten järjestäminen

Yhteenvetokyselyn tulosten järjestäminen tapahtuu samalla tavalla kuin aiemmin. Järjestyskäsky tulee kyselyn loppuun — alla kurssit järjestetään niiden keskiarvon perusteella.

SELECT Kurssi.nimi AS kurssi, AVG(Kurssisuoritus.arvosana) AS keskiarvo
    FROM Kurssi
    JOIN Kurssisuoritus ON Kurssi.id = Kurssisuoritus.kurssi_id
    GROUP BY Kurssi.nimi
    HAVING keskiarvo < 2
    ORDER BY keskiarvo;
Pääsit aliluvun loppuun! Jatka tästä seuraavaan osaan: