Srodne teme
Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.

SQL upit - prosjek ovisno o broju ocjena

[es] :: MS SQL :: SQL upit - prosjek ovisno o broju ocjena

[ Pregleda: 3990 | Odgovora: 14 ] > FB > Twit

Postavi temu Odgovori

Autor
Pretraga teme: Traži
Markiranje Štampanje RSS

Zok101
Zagreb

Član broj: 39701
Poruke: 12
*.opti.carnet.hr.



Profil

icon SQL upit - prosjek ovisno o broju ocjena09.11.2010. u 15:35 - pre 176 meseci
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
 
Odgovor na temu

svemirski

Član broj: 220762
Poruke: 15



+1 Profil

icon Re: SQL upit - prosjek ovisno o broju ocjena09.11.2010. u 20:34 - pre 176 meseci
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
 
Odgovor na temu

mmix
Miljan Mitrović
Chief Software Architect
Financial Intelligence Software Ltd.
Passau, Deutschland

SuperModerator
Član broj: 17944
Poruke: 6050



+4631 Profil

icon Re: SQL upit - prosjek ovisno o broju ocjena09.11.2010. u 21:47 - pre 176 meseci
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.

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ć
 
Odgovor na temu

Zok101
Zagreb

Član broj: 39701
Poruke: 12
*.racunarstvo.hr.



Profil

icon Re: SQL upit - prosjek ovisno o broju ocjena10.11.2010. u 07:21 - pre 175 meseci
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.
 
Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3570

Jabber: djoka_l


+1527 Profil

icon Re: SQL upit - prosjek ovisno o broju ocjena10.11.2010. u 08:43 - pre 175 meseci
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
)
 
 
Odgovor na temu

Fedya
Fedor Hajdu
Solution Architect
Emaratech
Dubai, UAE

Član broj: 28246
Poruke: 790
*.dynamic.sbb.rs.

Jabber: fedya@elitesecurity.org
Sajt: devtechgroup.com


+34 Profil

icon Re: SQL upit - prosjek ovisno o broju ocjena10.11.2010. u 08:44 - pre 175 meseci
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...
Every hamster has his day.
 
Odgovor na temu

Fedya
Fedor Hajdu
Solution Architect
Emaratech
Dubai, UAE

Član broj: 28246
Poruke: 790
*.dynamic.sbb.rs.

Jabber: fedya@elitesecurity.org
Sajt: devtechgroup.com


+34 Profil

icon Re: SQL upit - prosjek ovisno o broju ocjena10.11.2010. u 08:51 - pre 175 meseci
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.
Every hamster has his day.
 
Odgovor na temu

mmix
Miljan Mitrović
Chief Software Architect
Financial Intelligence Software Ltd.
Passau, Deutschland

SuperModerator
Član broj: 17944
Poruke: 6050



+4631 Profil

icon Re: SQL upit - prosjek ovisno o broju ocjena10.11.2010. u 08:53 - pre 175 meseci
Ovo je klasican PIVOT. Ili pivot select u SQL 2005+ ili case u ranijim verzijama.

http://msdn.microsoft.com/en-us/library/ms177410.aspx
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ć
 
Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3570

Jabber: djoka_l


+1527 Profil

icon Re: SQL upit - prosjek ovisno o broju ocjena10.11.2010. u 09:10 - pre 175 meseci
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
 
Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3570

Jabber: djoka_l


+1527 Profil

icon Re: SQL upit - prosjek ovisno o broju ocjena10.11.2010. u 09:29 - pre 175 meseci
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...
 
Odgovor na temu

dusans
Stojanov Dušan
Pančevo

Član broj: 9551
Poruke: 1343
*.dynamic.sbb.rs.



+311 Profil

icon Re: SQL upit - prosjek ovisno o broju ocjena10.11.2010. u 11:42 - pre 175 meseci
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.


 
Odgovor na temu

mmix
Miljan Mitrović
Chief Software Architect
Financial Intelligence Software Ltd.
Passau, Deutschland

SuperModerator
Član broj: 17944
Poruke: 6050



+4631 Profil

icon Re: SQL upit - prosjek ovisno o broju ocjena10.11.2010. u 11:55 - pre 175 meseci
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.
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ć
 
Odgovor na temu

dusans
Stojanov Dušan
Pančevo

Član broj: 9551
Poruke: 1343
*.dynamic.sbb.rs.



+311 Profil

icon Re: SQL upit - prosjek ovisno o broju ocjena10.11.2010. u 11:59 - pre 175 meseci
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!
 
Odgovor na temu

dusans
Stojanov Dušan
Pančevo

Član broj: 9551
Poruke: 1343
*.dynamic.sbb.rs.



+311 Profil

icon Re: SQL upit - prosjek ovisno o broju ocjena10.11.2010. u 12:02 - pre 175 meseci
Mmix slažem se da je za ovo pivot prava stvar.
 
Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3570

Jabber: djoka_l


+1527 Profil

icon Re: SQL upit - prosjek ovisno o broju ocjena10.11.2010. u 12:27 - pre 175 meseci
@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:
Prikačeni fajlovi
rez.JPG rez.JPG - 12.12k
 
Odgovor na temu

[es] :: MS SQL :: SQL upit - prosjek ovisno o broju ocjena

[ Pregleda: 3990 | Odgovora: 14 ] > FB > Twit

Postavi temu Odgovori

Srodne teme
Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.