Kodulehed

[390] - Andmebaasid I (ITI0206) (kevad 2026)

Küsimus

Anonüümne (16.02.2024 13:34)
Kuidas kasutada PostgreSQLi küsitlustulemuste analüüsimiseks sh vabatekstiliste vastuste analüüsimiseks?

Vastus (21.09.2026 12:49): Moraal on selles, et vaba teksti väljas olevate väärtuste analüüsimine nõuab rohkem tööd ning kui andmebaasis oleks vaja registreerida andmeid millegi kohta, et nende andmete alusel saaks hiljem teha otsinguid, siis vabateksti asemel tuleks eelistada rohkem struktureeritud väärtuste salvestamist.

Küsitlus Microsoft Forms keskkonnas, kus on kuus küsimust:

  • Kui heaks hindate Te 10 palli skaalal oma SQLi oskust (0 üldse ei oska - 10 väga hea)? (vastuseks arv 0-10)
  • Milline on Teie varasem kokkupuude andmebaaside loomisega? (vastus vabatekst)
  • Milliseid andmebaasisüsteeme Te olete oma elus kasutanud (võib valida mitu)? (vastus etteantud valikust)
  • Kui olete kasutanud mõnda andmebaasisüsteemi, mida eespool ei nimetatud, siis kirjutage see palun siia (vastus vabatekst)
  • Milline on Teie varasem kokkupuude andmebaasirakenduste loomisega? (vastus vabatekst)
  • Millised on Teie ootused "Andmebaasid I" õppeaine suhtes? (vastus vabatekst)

Laadin alla vastused Exceli failina.
Kustutan üleliigsed veerud. Faili jääb kuus veergu. Esimene rida on päis, väärtuste eraldaja on ;
Jätan alles tabeli päise.
Salvestan CSV-vormingus. 

Kõigepealt - tänapäeval saab selliste andmete analüüsimiseks kasutada suuri keelemudeleid.

Vaatlen aga järgnevalt, kuidas selliseid andmeid käsitsi analüüsida ja teha seda PostgreSQL abil. Laen faili Kysitlus_k2026.csv serverisse, kus on PostgreSQL, kataloogi tmp.

Järgnevad laused käivitan PostgreSQL andmebaasis.

/*Loon andmebaasis laienduse, et saaksin andmebaasis kasutada andmebaasivälises failis olevaid andmeid.*/
CREATE EXTENSION IF NOT EXISTS file_fdw;

CREATE SERVER file_fdw_server FOREIGN DATA WRAPPER file_fdw;

/*Loon välise tabeli, mille kaudu saan vaadata serverisse laaditud CSV failis olevaid andmeid.
Määran, et fail on CSV-vormingus, failis on tabelil päis (veergude nimed), näitan faili asukoha, määran kuidas esitatakse jutumärke ning
puuduvaid andmeid ning milline on väärtuste eraldaja.*/

CREATE FOREIGN TABLE kysitlus (
SQL SMALLINT,
varasem TEXT,
andmebaasisysteemid TEXT,
andmebaasisysteemid_muu TEXT,
andmebaasirakendused TEXT,
ootused TEXT)
SERVER file_fdw_server
OPTIONS (format 'csv',header 'true', filename '/tmp/Kysitlus_k2026.csv', quote '"', delimiter ';', null '');

/*Leian vastuste arvu.*/

SELECT Count(*) AS arv
FROM Kysitlus;

/*Mediaanväärtuse leidmiseks saab kasutada percentile_cont kokkuvõttefunktsiooni või alternatiivina kasutaja poolt loodud funktsiooni.

percentile_cont võtab argumendiks soovitud protsentiili vahemikus 0 kuni 1 (näiteks 0.5 tähistab 50. protsentiili ehk mediaani). Kui protsentiili asukoht jääb kahe rea väärtuse vahele, arvutab funktsioon nende väärtuste vahel lineaarse interpolatsiooni teel vahepealse väärtuse.*/

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY SQL) AS mediaan, Round(Avg(SQL), 2) AS aritmeetiline_keskmine
FROM Kysitlus;

/*Moodi e kõige sagedamini esineva väärtuse leidmiseks saab kasutada spetsiaalset kokkuvõttefunktsiooni. NB! Kui on mitu ühesugust väärtust, siis valitakse nendest juhuslikult üks.*/

SELECT mode() WITHIN GROUP (ORDER BY SQL) AS mood
FROM Kysitlus;

/*Erinevate SQL oskuse hinnangute arv sorteerituna arvu järgi kahanevalt.*/

SELECT SQL, Count(*) AS arv
FROM Kysitlus
GROUP BY SQL
ORDER BY arv DESC;

/*Milliseid SQL oskuse hinnanguid ei ole kordagi antud? generate_series funktsioon genereerib antud juhul tabeli, kus on 11 rida ning igas reas on üks täisarv (0-10). Alampäringus peab olema tingimus SQL IS NOT NULL, sest kui küsitluses oleks mõnel juhul SQL oskuse hinnang puudu, siis ilma selle tingimuseta poleks päringu tulemuses kunagi ühtegi rida.

WITH klauslis on ühine tabeli avaldis (common table expression), mis võimaldab defineerida alampäringu, anda sellele nime ning seda hiljem lauses kasutada.*/

WITH voimalikud AS (SELECT generate_series(0,10) AS hinnang)
SELECT hinnang
FROM voimalikud
WHERE hinnang NOT IN (SELECT SQL
FROM Kysitlus
WHERE SQL IS NOT NULL)
ORDER BY hinnang;

/*Jagades hinnangud nelja klassi - 0, 1-3, 4-6, rohkem kui 6, siis kui mitu vastajat kuulub igasse klassi ning milline on igasse klassi kuuluvate vastajate protsent kõigi vastajate arvust. Arvestatakse vaid vastuseid, kus SQL hinnang on antud. Kasutan massiivi konstruktorit ARRAY[], et saada iga hinnangu juurde väärtus, mille alusel sorteerides on hinnangud madalamast kõrgema suunas sorteeritud.*/

WITH eeltootle AS (SELECT CASE WHEN SQL=0 THEN ARRAY['ei tea midagi','a']
WHEN SQL BETWEEN 1 AND 3 THEN ARRAY['teab vähe','b']
WHEN SQL BETWEEN 4 AND 6 THEN ARRAY['teab keskmiselt','c']
ELSE ARRAY['teab hästi','d'] END AS hinnang
FROM Kysitlus
WHERE SQL IS NOT NULL)
SELECT hinnang[1] AS hinnang, Count(*) AS arv, Round(Count(*)*100/(SELECT Count(*) FROM eeltootle),1) AS protsent
FROM eeltootle
GROUP BY hinnang
ORDER BY hinnang[2];

/*Kui mitmes vastuses pole nimetatud ühtegi andmebaasisüsteemi?*/

SELECT Count(*) AS arv
FROM Kysitlus
WHERE andmebaasisysteemid IS NULL;

/*Milline on andmebaasisüsteemide populaarsus e mainimise sagedus? Lause töötleb väljapakutud nimekirjast valitud vastuseid, mis CSV failis on kujul "PostgreSQL;MySQL;" Funktsioon string_to_table võimaldab tükeldada tekst osadeks ja esitada iga tükk eraldi reana.*/

WITH tykelda AS (
    SELECT string_to_table(andmebaasisysteemid, ';') AS andmebaasisysteem
    FROM kysitlus
)
SELECT andmebaasisysteem, Count(*) AS arv
FROM tykelda
WHERE andmebaasisysteem <> ''
GROUP BY andmebaasisysteem
ORDER BY arv DESC;

/*Kui mitu andmebaasisüsteemi on erinevates vastustes mainitud? Kasutatakse aknafunktsiooni ROW_NUMBER(), et saada iga vastuse jaoks unikaalne identifikaator.*/

WITH tykelda AS (SELECT row_number() OVER () AS vastus_id, string_to_table(andmebaasisysteemid, ';') AS andmebaasisysteemid
FROM kysitlus),
systeemide_arv AS (SELECT Count(*) AS andmebaasisysteemide_arv
FROM tykelda
WHERE andmebaasisysteemid<>''
GROUP BY vastus_id)
SELECT andmebaasisysteemide_arv, Count(*) AS arv
FROM systeemide_arv
GROUP BY andmebaasisysteemide_arv
ORDER BY arv DESC;

/*Milliseid andmebaasisüsteemide paare on ühes ja samas vastuses mainitud. Leia kõik paarid, kus esinemiste arv on suurem kui 1.*/

WITH vastused AS (SELECT row_number() OVER () AS vastus_id, string_to_table(andmebaasisysteemid, ';') AS andmebaasisysteem
FROM kysitlus),
vastuse_element AS (SELECT vastus_id, andmebaasisysteem 
FROM vastused
WHERE andmebaasisysteem<>'')
SELECT va1.andmebaasisysteem AS systeem1, va2.andmebaasisysteem AS systeem2, Count(*) AS arv
FROM vastuse_element AS va1,  vastuse_element AS va2
WHERE va1.vastus_id=va2.vastus_id
AND va1.andmebaasisysteem>va2.andmebaasisysteem
GROUP BY va1.andmebaasisysteem, va2.andmebaasisysteem
HAVING Count(*)>1
ORDER BY arv DESC, systeem1, systeem2;

/*Kui mitmes vastuses on mainitud lisaks täiendavaid andmebaasisüsteeme?*/

SELECT Count(*) AS arv
FROM Kysitlus
WHERE andmebaasisysteemid_muu IS NOT NULL;

/*Kas täiendavate andmebaasisüsteemide kirjelduses on korduvaid sõnu?/

WITH tykelda AS (SELECT trim(string_to_table(translate(andmebaasisysteemid_muu,',',';'), ';')) AS andmebaasisysteemid_muu
FROM kysitlus)
SELECT andmebaasisysteemid_muu, Count(*) AS arv
FROM tykelda
WHERE andmebaasisysteemid_muu<>''
GROUP BY andmebaasisysteemid_muu
ORDER BY arv DESC;

/*Millised on kõige sagedasemad varasema andmebaaside loomise kogemuse iseloomustamiseks kasutatavad sõnad? Eemaldatakse stoppsõnad ning esitatakse vaid sõnad, mida esitatakse rohkem kui üks kord.*/

WITH eemalda (sone) AS (VALUES ('ja'),('ning'),('või'),('ka'),('on'),('et'),('mis'),('kui'),('kuid'),('sest'),('vaid'),('siis')),
eeltootle AS (SELECT translate(lower(varasem),';,.-','') AS varasem
FROM kysitlus),
tykelda AS (SELECT regexp_split_to_table(varasem, ' ') AS varasem
FROM eeltootle),
puhasta AS (SELECT varasem
FROM tykelda
WHERE varasem NOT IN (SELECT sone
FROM eemalda))
SELECT varasem, Count(*) AS arv
FROM puhasta
WHERE varasem<>''
GROUP BY varasem
HAVING Count(*)>1
ORDER BY arv DESC;

/*Tulemuse, kus on sõnad ja nende kaalud (näiteks esinemiste arv), saab anda sisendiks tasuta veebipõhisele sõnapilve koostamise tarkvale nagu näiteks Wordclouds. Sinna saab anda sisendi CSV-vormingus ning PostgreSQLis olevaid andmeid saab CSV-vormingus eksportida näiteks COPY TO lausega. Järgneva lausega kopeeritakse tulemus (ilma päiseta CSV) klientrakenduse väljundisse (nt psqli). Väljundi saab soovi korral suunata ka serveris loodavasse faili. Selle faili saaks näiteks WinSCP programmiga alla laadida.*/

COPY (WITH eemalda (sone) AS (VALUES ('ja'),('ning'),('või'),('ka'),('on'),('et'),('mis'),('kui'),('kuid'),('sest'),('vaid'),('siis')),
eeltootle AS (SELECT translate(lower(varasem),';,.-','') AS varasem
FROM kysitlus),
tykelda AS (SELECT regexp_split_to_table(varasem, ' ') AS varasem
FROM eeltootle),
puhasta AS (SELECT varasem
FROM tykelda
WHERE varasem NOT IN (SELECT sone
FROM eemalda))
SELECT varasem, Count(*) AS arv
FROM puhasta
WHERE varasem<>''
GROUP BY varasem
HAVING Count(*)>1
ORDER BY arv DESC)
TO STDOUT
WITH (format csv, header false);

/*Sõnapilve loomise vahendid võimaldavad sisendiks võtta ka lihtsalt teksti, kuid taolise päringu tulemusel sisendi leidmine on kasulik seetõttu, et selle abil saab eemaldada just eesti keele stoppsõnu.*/

/*Kas enesekindlamad õpilased kirjutavad pikemaid vastuseid?
Kas leidub korrelatsioon (seos) inimese SQL-i oskuse hinnangu ja selle vahel, kui pikalt nad oma ootustest ja kogemustest kirjutavad.*/

SELECT 
    SQL AS sql_oskus, 
    Count(*) AS vastajate_arv,
    Round(Avg(char_length(varasem))) AS keskmine_kogemuse_teksti_pikkus,
    Round(Avg(char_length(ootused))) AS keskmine_ootuste_teksti_pikkus
FROM kysitlus
WHERE SQL IS NOT NULL
GROUP BY SQL
ORDER BY SQL DESC;
 
/*Massiivide ühisosa – milline andmebaasisüsteemide kasutamise kogemus on nendel, kes on kasutanud spetsiifilisi tehnoloogiaid koos?
 
Tavaline tekstiotsing (LIKE) võib eksida, aga PostgreSQLi massiivioperaatorid (nt @> ehk "sisaldab") võimaldavad väga täpselt leida näiteks need vastused, kus on andmebaasisüsteemide valikusse märkinud nii PostgreSQL-i kui ka Oracle'i, mis on valikulised andmebaasisüsteemid õppeaines "Andmebaasid II".*/

SELECT andmebaasisysteemid 
FROM kysitlus
WHERE string_to_array(andmebaasisysteemid, ';') @> ARRAY['PostgreSQL', 'Oracle']::text[];


Keskmine hinne: Pole veel hinnanguid!