Kuidas kasutada PostgreSQLi küsitlustulemuste analüüsimiseks sh vabatekstiliste vastuste analüüsimiseks?

Postitas Erki Eessaar 16.02.2024 13:40 (muudeti 09.03.2025 11:31)
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 viis 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)
  • Millised on Teie ootused "Andmebaasid I" õppeaine suhtes? (vastus vabatekst)
Laadin alla vastused Exceli failina.
Kustutan üleliigsed veerud. Faili jääb viis veergu. Esimene rida on päis, väärtuste eraldaja on ;
Jätan alles tabeli päise.
Salvestan CSV formaadis. 
Laen faili Kysitlus_k2025.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 formaadis, 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,
ootused TEXT)
SERVER file_fdw_server
OPTIONS (format 'csv',header 'true', filename '/tmp/Kysitlus_k2025.csv', quote '"', delimiter ';', null '');

/*Leian vastuste arvu.*/
SELECT Count(*) AS arv
FROM Kysitlus;

/*Mediaanväärtuse leidmiseks kasutan andmebaasis kasutaja poolt loodud funktsiooni.*/
SELECT Median(SQL) AS mediaan, Avg(SQL) 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(*)*(SELECT Count(*) FROM eeltootle)/100,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 regexp_split_to_table võimaldab tükeldada tekst osadeks ja esitada iga tükk eraldi reana.*/
WITH tykelda AS (SELECT regexp_split_to_table(andmebaasisysteemid, ';') AS andmebaasisysteemid
FROM kysitlus)
SELECT andmebaasisysteemid, Count(*) AS arv
FROM tykelda
WHERE andmebaasisysteemid<>''
GROUP BY andmebaasisysteemid
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, regexp_split_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, regexp_split_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(regexp_split_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')),
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;

/*Millised on kõige ootuste 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')),
eeltootle AS (SELECT translate(lower(ootused),';,.-','') AS ootused
FROM kysitlus),
tykelda AS (SELECT regexp_split_to_table(ootused, ' ') AS ootused
FROM eeltootle),
puhasta AS (SELECT ootused
FROM tykelda
WHERE ootused NOT IN (SELECT sone
FROM eemalda))
SELECT ootused, Count(*) AS arv
FROM puhasta
WHERE ootused<>''
GROUP BY ootused
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 SEE. Sinna saab anda sisendi CSV formaadis ning PostgreSQLis olevaid andmeid saab CSV formaadis eksportida näiteks COPY TO lausega. Sellised 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.

Hinda postitust:

Keskmine hinne : Pole veel hinnanguid!