SQL upit - prosjek ovisno o broju ocjena

SQL upit - prosjek ovisno o broju ocjena 09.11.2010. u 16:35 · pre 194 meseca #1

Imam tri tablice: Osoba (idOs, Ime), Predmeti (idPredmet, Naziv, sifraPredmet) i Ocjene (IdOcjene, OsobaID, PredmetiID, Ocjena).
Trebam upit koji će mi za svaki predmet dati broj koliko se puta ponavljala koja ocjena i koja je prosječna ocjena predmeta, ali ako se izuzmu ocjene jedan.

To bi trebalo biti u ovakvom obliku:
Predmet | Ocjena 1 | Ocjena 2 | Ocjena 3 | Ocjena 4 | Ocjena 5 | Prosjek (bez jedinica)

Probao sam na više načina, ali meni je to prekomplicirano. Bio bih vam vrlo zahvalan ukoliko biste ovo riješili.

Pozdrav

Re: SQL upit - prosjek ovisno o broju ocjena 09.11.2010. u 21:34 · pre 194 meseca #2

Mislim,ispravite me ako griješim, da trebaš imati 4 umjesto 3 tabele zbog veze više-više između entiteta OCJENE i PREDMETI.
Predmet može imati više ocjena a isto tako jedna te ista ocjena se može nalaziti u više predmeta.

U tabeli OCJENE mozes imati atribute (Ocjena_ID,Ocjena) dok u novokreiranoj tabeli PREDMETI_OCJENE imas foreign keys ostalih tabela:PredmetID,OsobaID,OcjenaID.

Za tvoj upit će ti pomoći "Group by" i agr.f-ja AVG za prosjek.Nadam se da ne grijesim

Re: SQL upit - prosjek ovisno o broju ocjena 09.11.2010. u 22:47 · pre 194 meseca #3

Ocena moze slbodono da se tretira kao atribut, ne mora da bude entitet, zasto bi imao jos jednu tabelu cija cela svrha je da drzi 5 recorda sa vrednostima 1 do 5? Samo te sputava.

Potpis

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog
naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji
je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan,
sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv - Z.Đinđić

Re: SQL upit - prosjek ovisno o broju ocjena 10.11.2010. u 08:21 · pre 194 meseca #4

Tablice su već kreirane i nemam ih pravo mijenjati.
Probao sam na ovaj način, ali nešto ne ide:

Select predmeti.NazivPredmeta, (select count(ocjena) from ocjene where ocjena=1), (select count(ocjena) from ocjene where ocjena=2),...,
(select count(ocjena) from ocjene where ocjena=5), (select avg(ocjena) from ocjene where ocjena<>1) from predmeti join ocjene on predmeti.predmetiID=ocjene.predmetiID

Ali dobivam za sve predmete iste izlaze.

Re: SQL upit - prosjek ovisno o broju ocjena 10.11.2010. u 09:43 · pre 194 meseca #5

Ovo je tipičan zadatak iz oblasti: "Za šta ne treba koristiti SQL"

Dao sam ti rešenje sa upotrebom CASE komande, s tim da različiti dijalekti SQL-a imaj možda i kraće i elegantnije rešenje.

Code (sql):

SELECT naziv, ocjena_1, ocjena_2, ocjena_3, ocjena_4, ocjena_5,

         (ocjena_2*2 + ocjena_3*3 + ocjena_4*4 + ocjena_5*5)/(ocjena_2 + ocjena_3 + ocjena_4 + ocjena_5) prosjek

FROM (

  SELECT naziv, 

           SUM(ocjena_1) ocjena_1,

           SUM(ocjena_2) ocjena_2,

           SUM(ocjena_3) ocjena_3,

           SUM(ocjena_4) ocjena_4,

           SUM(ocjena_5) ocjena_5

  FROM (

    SELECT naziv,

           CASE ocjena WHEN 1 THEN 1 ELSE 0 END ocjena_1,

           CASE ocjena WHEN 2 THEN 1 ELSE 0 END ocjena_2,

           CASE ocjena WHEN 3 THEN 1 ELSE 0 END ocjena_3,

           CASE ocjena WHEN 4 THEN 1 ELSE 0 END ocjena_4,

           CASE ocjena WHEN 5 THEN 1 ELSE 0 END ocjena_5

    FROM predmeti p, ocjene o

    WHERE p.idPredmet=o.PredmetiID

    GROUP BY naziv, ocjena

  )

  GROUP BY naziv

)

Re: SQL upit - prosjek ovisno o broju ocjena 10.11.2010. u 09:44 · pre 194 meseca #6

Dobro si krenuo, samo ti treba join sa spoljnim upitom

SELECT p.NazivPredmeta, (SELECT count(ocjena) FROM ocjene o JOIN p ON p.PredmetiID = o.PredmetiID WHERE ocjena=1) AS Ocena1
FROM predmeti p

ili tako nekako...

Potpis

Every hamster has his day.

Re: SQL upit - prosjek ovisno o broju ocjena 10.11.2010. u 09:51 · pre 194 meseca #7

Pretece me djoka odgovorom, mada mi njegov deluje kao ili pogresan ili previse komplikovan. Ne znam koji si SQL koristio ali SQLServer podrzava ugnjezdene upite koji su ovde resenje.

A sto se mog resenja tice, zaboravio sam da dodam kako racunas prosek. SQL Server ima funkciju avg koja racuna prosek, kolonu ces dobiti slicno kao za Ocena1 samo ces umesto sum() imati avg() i posto ti trebaju sve ocene vece od 1 imaces WHERE Ocena > 1 umesto =1.

Potpis

Every hamster has his day.

Re: SQL upit - prosjek ovisno o broju ocjena 10.11.2010. u 09:53 · pre 194 meseca #8

Ovo je klasican PIVOT. Ili pivot select u SQL 2005+ ili case u ranijim verzijama.

http://msdn.microsoft.com/en-us/library/ms177410.aspx

Potpis

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog
naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji
je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan,
sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv - Z.Đinđić

Re: SQL upit - prosjek ovisno o broju ocjena 10.11.2010. u 10:10 · pre 194 meseca #9

Citat:

mmix: Ovo je klasican PIVOT. Ili pivot select u SQL 2005+ ili case u ranijim verzijama.

http://msdn.microsoft.com/en-us/library/ms177410.aspx

Tačno to, kao i u Oracle bazi, počevši od verzije 11g http://download.oracle.com/doc...tatements_10002.htm#sthref6839

Re: SQL upit - prosjek ovisno o broju ocjena 10.11.2010. u 10:29 · pre 194 meseca #10

Citat:

Fedya: Pretece me djoka odgovorom, mada mi njegov deluje kao ili pogresan ili previse komplikovan.

Fedya, ne znam da li ti je profil tačan, ali ako jeste, onda sam ja bio DBA kada si ti išao u četvrti razred osnovne škole.
Molim te da ako vidiš grešku u mom programu (a svi programeri greše) ili znaš jednostavniji način, to i potkrepiš kodom, a ne da daješ paušalne ocene.
Znam da nisi imao nameru da vređaš, ali odmeravaj reči u svojim postovima jer ne znaš sa kim komuniciraš, pa ćeš tako ispasti ti smešan...

Re: SQL upit - prosjek ovisno o broju ocjena 10.11.2010. u 12:42 · pre 194 meseca #11

Fedya je u pravu, može jednostavnije.
Ne treba ti onaj donji ugneždeni upit, te case-ove možeš da staviš u sum(...), na primer sum(case ocjena when 3 then 1 else 0 end ocjena_3).
Čak šta više, može da se sve svede na jedan jedini upit ali bi to bilo dugačko, nečitko i neefikasno.

Re: SQL upit - prosjek ovisno o broju ocjena 10.11.2010. u 12:55 · pre 194 meseca #12

Kraci query ne znaci uvek i brze izvrsavanje, case branching je uvek problem za query optimizer. Nisam tako siguran da bi pomeranje unutar SUM dalo bolji rezultat, at best query optmizer bi morao da ih dovede na isto, at worst imao loop/sum/loop. Zanimljiv problem zapravo, Zok101 ajd okaci execution plan za oba.

Zato je u priuncipu bolje koristiti pivot ako imas SQL2005+, query optimizer onda direktno prepoznaje pivot kao takav i moze da optiimzuje operaciju.

Potpis

Sloba je za 12 godina promenio antropološki kod srpskog naroda. On je od jednog
naroda koji je bio veseo, pomalo površan, od jednog naroda koji je bio znatiželjan, koji
je voleo da vidi, da putuje, da upozna,
od naroda koji je bio kosmopolitski napravio narod koji je namršten, mrzovoljan,
sumnjicav, zaplašen, narod koji se stalno nešto žali, kome je stalno neko kriv - Z.Đinđić

Re: SQL upit - prosjek ovisno o broju ocjena 10.11.2010. u 12:59 · pre 194 meseca #13

Kad bolje razmislim, sa jednim upitom bi to izgledalo ovako:

Code:

SELECT  naziv,
       sum(case ocjena when 1 then 1 else 0 end) ocjena_1,
       sum(case ocjena when 2 then 1 else 0 end) ocjena_2,
       sum(case ocjena when 3 then 1 else 0 end) ocjena_3,
       sum(case ocjena when 4 then 1 else 0 end) ocjena_4,
       sum(case ocjena when 5 then 1 else 0 end) ocjena_5,
       sum(case ocjena when 1 then 0 else ocjena end)/sum(case ocjena when 1 then 0 else 1 end) prosjek
FROM  predmeti p, ocjene o
WHERE p.idPredmet=o.PredmetiID
GROUP BY PredmetiID, naziv

Pozdrav!

Re: SQL upit - prosjek ovisno o broju ocjena 10.11.2010. u 13:02 · pre 194 meseca #14

Mmix slažem se da je za ovo pivot prava stvar.

Re: SQL upit - prosjek ovisno o broju ocjena 10.11.2010. u 13:27 · pre 194 meseca #15

@dusans
Namerno sam napravio ugnežđene upite da bi se shvatila logika. Ako pogledaš ponovo moj primer videćeš da sam u najdubljem upitu zaboravio da sklonim GROUP klauzulu, jer sam u prvoj verziji sve uradio jednim upitom.

Evo ga kompletan primer za pivot (ORACLE):

Code (sql):

CREATE TABLE ttt_predmet (id NUMBER, naziv varchar2(80));

INSERT INTO ttt_predmet VALUES (1, 'fizika');

INSERT INTO ttt_predmet VALUES (2, 'biologija');

CREATE TABLE ttt_ocena (id NUMBER, idpredmeta NUMBER, ocena NUMBER);

INSERT INTO ttt_ocena VALUES (1, 1, 1);

INSERT INTO ttt_ocena VALUES (2, 1, 3);

INSERT INTO ttt_ocena VALUES (3, 1, 4);

INSERT INTO ttt_ocena VALUES (4, 1, 5);

INSERT INTO ttt_ocena VALUES (5, 2, 2);

SELECT x.*, y.prosek

  FROM (SELECT p.id, p.naziv, o.ocena

           FROM ttt_predmet p, ttt_ocena o

          WHERE p.id = o.idpredmeta)

        pivot(COUNT(ocena) FOR ocena IN(1 ocena_1,

                                        2 ocena_2,

                                        3 ocena_3,

                                        4 ocena_4,

                                        5 ocena_5)) x,

        (SELECT idpredmeta, avg(ocena) prosek

         FROM ttt_ocena

         WHERE ocena>1

         GROUP BY idpredmeta) y

WHERE x.id = y.idpredmeta;

Rezultat:

rez.JPG12.12k

Forum kao aplikacija na telefonu

Srodne teme