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

Sta se sve moze postici prebrojavanjem....

[es] :: Baze podataka :: Sta se sve moze postici prebrojavanjem....

[ Pregleda: 4017 | Odgovora: 5 ] > FB > Twit

Postavi temu Odgovori

Autor

Pretraga teme: Traži
Markiranje Štampanje RSS

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.



+79 Profil

icon Sta se sve moze postici prebrojavanjem....14.01.2010. u 20:34 - pre 172 meseci
Interesantne stvari se mogu raditi ako znate da brojite. Posto je ovo forum o bazama podataka, brojanje znaci SQL brojanje.

Evo primer jednog problema koji se moze javiti i u praksi.

Imamo skolu i nastavnike. Nastavnici predaju neke predmete. Jedan nastavnik moze predavati vise predmeta. Data je tabela NastavniciPredmeti u kojoj pise koji nastavnik moze da predaje koji predmet. Izgleda ovako (u MS SQL):
Code:

IF OBject_ID('tempdb..#NastavnikPredmet') IS NOT NULL DROP TABLE #NastavnikPredmet
;
CREATE TABLE #NastavnikPredmet
(Nastavnik varchar(10) NOT NULL
, Predmet varchar(15) NOT NULL
, PRIMARY KEY (Nastavnik, predmet)
)
;



Od ovakve tabele, mogu se zahtevati odgovori na ovakva i slicna pitanja:

Koji nastavnici predaju biologiju i hemiju?
Koji nastavnici predaju samo biologiju i hemniju, i ni jedan drugi predmet?
Koji nastavnici predaju sve predmete koji su zastupljeni u skoli?

Unesimo nekoliko redova za testiranje:
Code:

INSERT INTO #NastavnikPredmet VALUES ('Aca','Matematika')
INSERT INTO #NastavnikPredmet VALUES ('Aca','Biologija')
INSERT INTO #NastavnikPredmet VALUES ('Aca','Hemija')
INSERT INTO #NastavnikPredmet VALUES ('Aca','Fizika')
INSERT INTO #NastavnikPredmet VALUES ('Aca','Istorija')
INSERT INTO #NastavnikPredmet VALUES ('Aca','Geografija')
INSERT INTO #NastavnikPredmet VALUES ('Marta','Biologija')
INSERT INTO #NastavnikPredmet VALUES ('Maca','Matematika')
INSERT INTO #NastavnikPredmet VALUES ('Maca','Biologija')
INSERT INTO #NastavnikPredmet VALUES ('Maca','Hemija')
INSERT INTO #NastavnikPredmet VALUES ('Maca','Fizika')
INSERT INTO #NastavnikPredmet VALUES ('Peca','Istorija')
INSERT INTO #NastavnikPredmet VALUES ('Peca','Geografija')
INSERT INTO #NastavnikPredmet VALUES ('Peca','Matematika')
INSERT INTO #NastavnikPredmet VALUES ('Ceca','Biologija')
INSERT INTO #NastavnikPredmet VALUES ('Ceca','Geografija')
INSERT INTO #NastavnikPredmet VALUES ('Laza','Matematika')
INSERT INTO #NastavnikPredmet VALUES ('Laza','Biologija')
INSERT INTO #NastavnikPredmet VALUES ('Laza','Hemija')
INSERT INTO #NastavnikPredmet VALUES ('Laza','Fizika')
INSERT INTO #NastavnikPredmet VALUES ('Laza','Istorija')
INSERT INTO #NastavnikPredmet VALUES ('Laza','Geografija')
INSERT INTO #NastavnikPredmet VALUES ('Goca','Biologija')
INSERT INTO #NastavnikPredmet VALUES ('Goca','Hemija')
INSERT INTO #NastavnikPredmet VALUES ('Juca','Hemija')
INSERT INTO #NastavnikPredmet VALUES ('Juca','Fizika')
INSERT INTO #NastavnikPredmet VALUES ('Juca','Biologija')
INSERT INTO #NastavnikPredmet VALUES ('Pera','Istorija')
INSERT INTO #NastavnikPredmet VALUES ('Pera','Geografija')


Pokusajmo da odgovorimo na postavljena pitanja.

1) Koji nastavnici predaju biologiju i hemiju?
Mozemo da pocnemo ovako:
Code:

SELECT
Nastavnik, Predmet
FROM #NastavnikPredmet 
WHERE Predmet In ('Biologija','Hemija');


Dobicu sve nastavnike koji predaju biologiju, ili hemiju, ili oba predmeta:
Code:

Nastavnik  Predmet
---------- ---------------
Aca        Biologija
Aca        Hemija
Ceca       Biologija
Goca       Biologija
Goca       Hemija
Juca       Biologija
Juca       Hemija
Laza       Biologija
Laza       Hemija
Maca       Biologija
Maca       Hemija
Marta      Biologija

(12 row(s) affected)


Posto me interesuju samo oni koji predaju oba predmeta, trebalo bi nekako da eliminisem one koji predaju samo jedan predmet? Moze ovako:
Code:

SELECT
Nastavnik, COUNT(Predmet)
FROM #NastavnikPredmet 
WHERE Predmet In ('Biologija','Hemija')
GROUP BY Nastavnik
HAVING COUNT(Predmet)=2;

sto daje rezultat:
Code:

Nastavnik             
---------- -----------
Aca                  2
Goca                 2
Juca                 2
Laza                 2
Maca                 2

(5 row(s) affected)

Interesantno je da COUNT() u SELECT delu nije ni potreban. Isto tako, COUNT(Predmet) se moze zameniti sa COUNT(*), tako da konacan kveri moze da izgleda ovako:
Code:

SELECT
Nastavnik
FROM #NastavnikPredmet 
WHERE Predmet IN ('Biologija','Hemija')
GROUP BY Nastavnik
HAVING COUNT(*)=2

2) Koji nastavnici predaju samo biologiju i hemiju, i ni jedan drugi predmet?
Iz pitanja vidimo da se traze nastavnici koji predaju tacno dva predmeta, i ta dva predmeta su bas Biologija i Hemija. Mogu da podelim posao u dva dela:

- (1), inadjem sve nastavnike koji predaju tacno dva predmeta
- (2) nadjem nastavnike koji predaju biologiju i hemiju

Nastavnici koji predaju tacno biologiju i hemiju i nijedan drugi predmet su oni koji se nalaze u rezultujucem skupu za korake (1) i (2).Idemo redom.

Tacno dva predmeta predaju sldeci nastavnici:
Code:

SELECT
Nastavnik
FROM #NastavnikPredmet
GROUP BY Nastavnik
HAVING COUNT(Predmet) = 2;

Nastavnik
----------
Ceca
Goca
Pera

(3 row(s) affected)


Na pocetku price smo pokazali kako se nalaze nastavnici koji predaju biologiju i hemiju:
Code:

SELECT
Nastavnik
FROM #NastavnikPredmet 
WHERE Predmet In ('Biologija','Hemija')
GROUP BY Nastavnik
HAVING COUNT(*)=2

Nastavnik
----------
Aca
Goca
Juca
Laza
Maca

(5 row(s) affected)


Nastavnici koji se nalaze na obe liste su oni koji predaju samo biologiju i hemiju i nista vise. U nasem slucaju to je Goca. Posto zelimo rezultat u jednom kveriju, moze ovako:
Code:

SELECT
Nastavnik
FROM #NastavnikPredmet 
WHERE Nastavnik IN (
                    SELECT
                    Nastavnik
                    FROM #NastavnikPredmet 
                    WHERE Predmet In ('Biologija','Hemija')
                    GROUP BY Nastavnik
                    HAVING COUNT(Premet)=2
                    )
GROUP BY Nastavnik
HAVING COUNT(Predmet) = 2;

i rezultat je naravno
Code:

Nastavnik
----------
Goca

(1 row(s) affected)

3) Koji nastavnici predaju sve predmete koji su zastupljeni u skoli?
Do sada smo uocili da se puno toga moze resiti brojanjem i poredkjenjem izbrojanih vrednosti. Svaki nastavnik predaje N predmeta. Ako prebrojimo predmete koji se predaju u skoli, dobijemo broj K. Oni nastavnici koji predaju tacno K predmeta predaju sve predmete koji su zastupljeni u skoli.

Evo koliko predmeta predaje koji nastavnik:
Code:

SELECT
Nastavnik, COUNT(*) AS BrojPredmeta
FROM #NastavnikPredmet 
GROUP BY Nastavnik;

Nastavnik  BrojPredmeta
---------- ------------
Aca                   6
Ceca                  2
Goca                  2
Juca                  3
Laza                  6
Maca                  4
Marta                 1
Peca                  3
Pera                  2

(9 row(s) affected)

Ukupan broj predmeta koji se predaje u skoli:
Code:

SELECT COUNT(*) 
FROM (SELECt DISTINCT predmet 
        FROM #NastavnikPredmet
        ) AS X

-----------
          6

(1 row(s) affected)

Ako pogledamo listu nastavnika i koliko predmeta ko predaje, vidimo da Aca i Laza predaju po 6 predmeta. Posto s eu skoli predaje ukupno 6 predmeta, onda mora biti da Aca i Laza predaju ama bas svaki od tih sest predmeta. Sada to sastavimo zajedno i dobijemo ovakav kveri:
Code:

SELECT
Nastavnik, COUNT(*) AS BrojPredmeta
FROM #NastavnikPredmet 
GROUP BY Nastavnik
HAVING COUNT(*) = (SELECT COUNT(*) 
                    FROM (SELECt DISTINCT predmet 
                            FROM #NastavnikPredmet
                            ) AS X
                    )

Nastavnik  BrojPredmeta
---------- ------------
Aca                   6
Laza                  6

(2 row(s) affected)


Ovo je bio prikaz interesantnih stvari koje s emogu postici ako se obicnim brojanjem



 
Odgovor na temu

captPicard
programer
more i planine

Član broj: 216084
Poruke: 1119



+19 Profil

icon Re: Sta se sve moze postici prebrojavanjem....15.01.2010. u 08:38 - pre 172 meseci
Bravo Zidar, zanimljiva tema i jako lijepo napisano!

Samo sam ovo primijetio, mislim da se ovo

Code:
SELECT COUNT(*) 
FROM (SELECt DISTINCT predmet 
        FROM #NastavnikPredmet
        ) AS X


može napisati kraće:

Code:
SELECT COUNT(DISTINCT predmet)
          FROM #NastavnikPredmet

F
 
Odgovor na temu

Zidar
Canada

Član broj: 15387
Poruke: 3085
*.100.46-69.q9.net.



+79 Profil

icon Re: Sta se sve moze postici prebrojavanjem....15.01.2010. u 14:00 - pre 172 meseci
@ captPicard : Hvala na zapazanju. Naravno da se moze napisati sa SELECT(DISTINCT).

Medjutim, ideju za temu dobio sam posle jednog pitanja na Access forumu pa sam pokusao da napisem kverije tako da se mogu izvrsavati i u Accessu ili drugim bazama koje mzoda ne podrzavaju SELECT(DISTINCT).

Sve sto sam napisao lepo izgleda kao primer. U praksi to malo drugacije izgleda. Predmeti koje posmatramo u primeru su hard-kodirani. Ispravna resenja za pitanja 1 i 2 trebalo bi da omoguce parametrizaciju. Otprilike, posaljes string 'Biologija,Hemija, Fizika' i procedura se izvrsi za 'Biologija,Hemija,Fizika'. A zabranjeno je koristiti dinamicki kod.

Evo zadatka, neka vrsta mozgalice:

Napisati stored proceduru koja prima listu predmeta odvojenih zarezom (comma delimited string) kao parametar i za tu listu predmata odgovara na pitanje "koji nastavnici predaju ove predmete". Tabela i ulazni podaci ostaju isti kao sto smo dali na pocetku.
Gradjenje dinamickog SQL izkaza nije dozvoljeno. Zabranjeno je dakle sagraditi dinamicki SQL pa ga izvrsiti unutar procedure. Dozvoljeno je pisati korisnicke funkcije, koristiti temp tabele, kreirati i koristiti pomocne stalne tabele ako nekome zatreba.

Na posao.


:-)
 
Odgovor na temu

vlade_k6
Vlade Rajević
Termorad Group
Požega

Član broj: 101958
Poruke: 60
*.static.isp.telekom.rs.



+3 Profil

icon Re: Sta se sve moze postici prebrojavanjem....16.01.2010. u 13:51 - pre 172 meseci
Korisni upiti koji uvek zatrebaju. Hvala...
 
Odgovor na temu

kova1
Dragan Kovacevic
Nakovo

Član broj: 86118
Poruke: 33
*.dynamic.sbb.rs.

Sajt: zaradi-na-internetu-ptc.b..


Profil

icon Re: Sta se sve moze postici prebrojavanjem....17.01.2010. u 14:13 - pre 172 meseci
Stvarno odlična tema,uvek dobro dodju korisni SQL upiti,a i da se nesto novo nauči.

Hvala
 
Odgovor na temu

AMD guy
Miroslav
.NET developer

Član broj: 128930
Poruke: 1007

Sajt: www.its.edu.rs


+38 Profil

icon Re: Sta se sve moze postici prebrojavanjem....19.01.2010. u 16:04 - pre 172 meseci
Slicni primeri se mogu naci u knjizi "Baze podataka" fakulteta za organizacione nauke
Autori:
Branislav Lazarević
Zoran Marjanović
Nenad Aničić
Slađan Babarogić


http://i.imgur.com/V3feW.jpg
http://on.wsj.com/H9yjz6 -- India Graduates Millions, but Too Few Are Fit to Hire
 
Odgovor na temu

[es] :: Baze podataka :: Sta se sve moze postici prebrojavanjem....

[ Pregleda: 4017 | Odgovora: 5 ] > FB > Twit

Postavi temu Odgovori

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