Kasutajatugi

Vastus (21.02.2024 09:33): 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 neli 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? (vastus vabatekst)
Millised on Teie ootused "Andmebaasid I" õppeaine suhtes? (vastus vabatekst)

Laadin alla vastused Exceli failina.
Kustutan üleliigsed veerud. Faili jääb neli veergu.
Jätan alles tabeli päise.
Salvestan CSV formaadis.
Laen faili Kysitlus.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,

ootused TEXT)

SERVER file_fdw_server

OPTIONS (format 'csv',header 'true', filename '/tmp/Kysitlus.csv', quote '"', delimiter ',', null '');

/*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.*/

SELECT mode() WITHIN GROUP (ORDER BY SQL) AS mood

FROM Kysitlus;

/*Erinevate SQL oskuse hinnangute arv.*/

SELECT SQL, Count(*) AS arv

FROM Kysitlus

GROUP BY SQL

ORDER BY SQL;

/*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.*/

WITH eeltootle AS (SELECT CASE WHEN SQL=0 THEN 'ei oska midagi'

WHEN SQL BETWEEN 1 AND 3 THEN 'oskab vähe'

WHEN SQL BETWEEN 4 AND 6 THEN 'oskab keskmiselt'

ELSE 'oskab hästi' END AS hinnang

FROM Kysitlus

WHERE SQL IS NOT NULL)

SELECT hinnang, Count(*) AS arv, Round(Count(*)*(SELECT Count(*) FROM eeltootle)/100,1) AS protsent

FROM eeltootle

GROUP BY hinnang
ORDER BY arv DESC;

/*Milline on andmebaasisüsteemide populaarsus e mainimise sagedus? Lause töötleb vabatekstilisi vastuseid.
Alampäringud:
eemalda - esitatakse tabel sõnadega (iga sõna eraldi reas), mida analüüsis ei arvestata.
eeltootle - tekstis muudetakse kõik tähed väiketähtedeks (lower funktsioon), fraas "sql server" asendatakse "sqlserver" (replace funktsioon) ning tekstist kustutatakse eraldajateks olevad sümbolid (punktid, komad, semikoolonid) (trasnslate funktsioon - etteantud sümbolid asendatakse mitte millegagi).
tykelda - tekst jaotatakse sõnadeks arvestusega, et eraldajaks on tühikud, ning luuakse tabel, kus iga sõna on eraldi reas.
puhasta - CASE avaldisega koondatakse erinevad samale andmebaasisüsteemile viitavad sõnad üheks sõnaks ning eemaldatakse analüüsis mittearvestatavad sõnad.
Kõige lõpuks leitakse andmebaasisüsteemide mainimiste arv ning tulemus sorteeritakse arvu järgi kahanevalt.
*/

WITH eemalda AS (VALUES ('ja'),('ning'),('ei'),('pole'),('-'),('on'),('ole'),('kasutanud'),('ka'),('töötan'),('kasutan')),

eeltootle AS (SELECT translate(replace(lower(andmebaasisysteemid),'sql server','sqlserver'),',.;','') AS andmebaasisysteemid

FROM kysitlus),

tykelda AS (SELECT regexp_split_to_table(andmebaasisysteemid, ' ') AS andmebaasisysteemid

FROM eeltootle),

puhasta AS (SELECT CASE WHEN andmebaasisysteemid IN ('postgres','postgre','postgressql') THEN 'postgresql'

WHEN andmebaasisysteemid IN ('ms','microsoft') THEN 'microsoft'

WHEN andmebaasisysteemid IN ('msaccess','access') THEN 'access'

ELSE andmebaasisysteemid END AS andmebaasisysteemid

FROM tykelda

WHERE andmebaasisysteemid NOT IN (SELECT column1

FROM eemalda))

SELECT andmebaasisysteemid, Count(*) AS arv

FROM puhasta

WHERE andmebaasisysteemid<>''

GROUP BY andmebaasisysteemid

ORDER BY arv DESC;

/*Kuna andmebaasisüsteemide nimed võivad olla pandud kirja ebatäpselt või samale andmebaasisüsteemile viidatakse sõnaga, kus on mõni täht erinevust (nt postgresql vs postgresqli), siis saab kasutada lauset, kus antakse ette kõige populaarsemate andmebaasisüsteemide nimed ning leitakse vastusest eraldatud sõnade Levenshteini kaugus nende andmebaasisüsteemide nimedest. Kui see kaugus on piisavalt väike, siis järelikult sõna viitab sellele andmebaasisüsteemile. Levenshteini kaugus kahe sõne vahel näitab ühemärgiliste muudatuste arvu, mida tuleb teha, et jõuda ühest sõnest teisini. Levenshteini kauguse arvutamise funktsioonid sisaldavad PostgreSQLi laienduses fuzzystrmatch. Enne lõpliku tulemuse leidmist tuleb vastuses esitatud sõnadest eemaldada sõnad, mis ei esita andmebaasisüsteemide nimesid, kuid mille Levenshteini kaugus mõne andmebaasisüsteemi nimest on väike. Levenshteini kauguse alusel sõnade võrdlemine on näide hägusast otsingust (fuzzy search).

Hägus otsing tuvastas PostgreSQLina ka sõnad: progresql, postgressql, posrtgresql
*/
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

WITH eemaldatavad AS (VALUES ('ms'),('nosql'),('sql'),('sqli'),('ole'),('on'),('ole'),('ei'),('rollis')),

populaarseimad AS (VALUES ('postgresql'),('mysql'),('access'),('oracle'),('sqlite'),('sqlserver'),('vertica'),('mongodb'),('redis'),('mariadb')),

eeltootle AS (SELECT translate(replace(lower(andmebaasisysteemid),'sql server','sqlserver'),'-,.;','') AS andmebaasisysteemid

FROM kysitlus),
tykelda AS (SELECT regexp_split_to_table(andmebaasisysteemid, ' ') AS andmebaasisysteemid
FROM eeltootle),
eemalda AS (SELECT andmebaasisysteemid
FROM tykelda
WHERE andmebaasisysteemid NOT IN (SELECT column1
FROM eemaldatavad))
SELECT populaarseimad.column1 AS andmebaasisysteem, Count(*) AS arv
FROM populaarseimad, eemalda
WHERE levenshtein_less_equal(eemalda.andmebaasisysteemid, populaarseimad.column1,3)<=3
GROUP BY andmebaasisysteem
ORDER BY arv DESC;

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

WITH eemalda AS (VALUES ('ja'),('ning'),('või'), ('-'),('ka')),

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 column1

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 AS (VALUES ('ja'),('ning'),('või'), ('-'),('ka')),

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 column1

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.