Ne volite fiziku i biologiju? A možda SQL?

Saturday, 24.11.2007 – Zidar

Kompanija za koju radim prikuplja podatke o učenicima srednjih i osnovnih škola u Ontariju. Podaci koje prikupljamo menjaju se od godine do godine. Da ne bi menjali bazu svaki čas, imamo dve tabele:


CREATE TABLE Osobe
(Ime varchar(25) NOT NULL PRIMARY KEY)
GO

CREATE TABLE Predmeti
(Ime varchar(25) NOT NULL
, Predmet varchar(25) NOT NULL
PRIMARY KEY (Ime,Predmet)
)
GO

U tabelama imamo ovo:


SELECT * FROM Osobe;

Ime
--------
Cica   
Goca   
Laza   
Maca   
Melanija   
Pera   
Verica   
Zika   
  
(8 row(s) affected)  

Za svaku osobu imamo podatke koje skupljamo, u ovom slučaju školske predmete:


SELECT Ime, Predmet
FROM Predmeti;


Ime Predmet
———- ——-
Cica Bio
Cica Fiz
Laza Bio
Laza Geo
Laza Math
Maca Bio
Maca Fiz
Maca Geo
Maca Math
Melanija Geo
Pera Bio
Pera Fiz
Pera Geo
Pera Math
Verica Bio
Verica Math
Zika Bio
Zika Fiz
Zika Math

Postavljeno je pitanje Ko NEMA predmet ‘Fiz’?

Klasično rešenje problema ide ovako:


SELECT Ime
FROM Osobe
WHERE Ime NOT IN(SELECT Ime
FROM Predmeti
WHERE Predmet = ‘Fiz’);

i daje korektno rešenje:


Ime
———
Goca
Laza
Melanija
Verica

(4 row(s) affected)

Neko je predložio da probamo da pokažemo sve osobe na levoj strani, a na desnoj njihove predmete, ali samo ‘Fiz’ i pogledamo gde je Predmet NULL. Probali smo prvo ovako:


SELECT A.Ime, B.Predmet
FROM Osobe AS A
LEFT JOIN Predmeti AS B
ON A.Ime = B.Ime
WHERE B.Predmet = ‘Fiz’
AND B.predmet IS NULL;

što naravno nije vratilo ni jedan red. Ne može nešto da bude istovremeno ‘Fiz’ i NULL. Šteta, a delovalo je zaista obećavajuće. Onda se neko drugi setio da napiše upit ovako:


SELECT A.Ime, B.Predmet
FROM Osobe AS A
LEFT JOIN Predmeti AS B
ON A.Ime = B.Ime
AND B.Predmet = ‘Fiz’
WHERE B.predmet IS NULL;

Deo WHERE prebacili smo u deo JOIN ON. I dobili tačan rezultat iz cuga. Ništa subquery, ništa IN. Upravo smo otkrili način da zadamo WHERE i na desnoj strani LEFT JOINa.

Isti efekat mogli smo postići bez upotrebe temp tabela ovako:


WITH A AS
(SELECT Ime, Predmet = ‘Fiz’
FROM Osobe
)
SELECT A.Ime, B.Predmet
FROM A
LEFT JOIN Predmeti AS B
ON A.Ime = B.Ime AND A.Predmet = B.Predmet
WHERE B.Predmet IS NULL

Problem je što ovo podrazumeva da znamo da upotrebimo CTE (common table expressions), pa da se setimo da ‘pomnožimo’ tabelu Osobe konstantom ‘Fiz’. Ostalo je lako. 🙂

Zato smo ostali kod našeg ‘proširenog JOINa’.

Onda smo proširili pitanje. Rekli smo ‘Ko NEMA ni jedan od predmeta (Fiz, Bio), dakle ni jedan od ova dva?


SELECT A.Ime, B.Predmet
FROM Osobe AS A
LEFT JOIN Predmeti AS B
ON A.Ime = B.Ime
AND (B.Predmet = ‘Fiz’ OR B.Predmet = ‘Bio’)
WHERE B.predmet IS NULL

Ime       Predmet
--------- -------
Goca      NULL
Melanija  NULL

(2 row(s) affected)

Elegantno, zar ne?

Pitali smo dalje Ko ima bar jedan od ova dva predmeta, (Fiz,Bio)?

Pokušali smo i dalje istim putem i za malo promašili:


SELECT A.Ime, B.Predmet
FROM Osobe AS A
LEFT JOIN Predmeti AS B
ON A.Ime = B.Ime
AND (B.Predmet = ‘Fiz’ OR B.Predmet = ‘Bio’)
WHERE B.predmet IS NOT NULL;

To nam je dalo zaista sve osobe koje imaju bar jedan od dva predmeta, ali su osobe koje imaju oba predmeta prikazane dva puta u izlaznom skupu:

Ime       Predmet
--------- -------
Cica      Bio
Cica      Fiz
Laza      Bio
Maca      Bio
Maca      Fiz
Pera      Bio
Pera      Fiz
Verica    Bio
Zika      Bio
Zika      Fiz

(10 row(s) affected)

Resili smo se duplikata na jednostavan način (minimum rada), ovako:


SELECT DISTINCT A.Ime —-, B.Predmet
FROM Osobe AS A
LEFT JOIN Predmeti AS B
ON A.Ime = B.Ime
AND (B.Predmet = ‘Fiz’ OR B.Predmet = ‘Bio’)
WHERE B.predmet IS NOT NULL;

Ubaciš DISTINCT, komentuješ Predmet i dobije se:

Ime
---------
Cica
Laza
Maca
Pera
Verica
Zika
   
(6 row(s) affected)

Postoji bolji način za eliminisanje duplikata. Zahteva malčice više rada u ovom momentu, ali donosi značajnu korist u sledećem koraku. Elem, ovako:


SELECT A.Ime , KolikoPredmeta = COUNT(*)
FROM Osobe AS A
LEFT JOIN Predmeti AS B
ON A.Ime = B.Ime
AND (B.Predmet = ‘Fiz’ OR B.Predmet = ‘Bio’)
WHERE B.predmet IS NOT NULL
GROUP BY A.Ime;

Ime    BrojPredmeta
------ -------------
Cica               2
Laza               1
Maca               2
Pera               2
Verica             1
Zika               2

(6 row(s) affected)

Dobili smo i broj predmeta, ko ima koliko od traženih dva predmeta. To može lepo da se iskoristi, ako se postavi pitanje Ko ima oba tražena predmeta?

Pa svi oni iz poslednjeg kverija koji imaju broj predmeta jednak 2. Ovako:


SELECT A.Ime , KolikoPredmeta = COUNT(*)
FROM Osobe AS A
LEFT JOIN Predmeti AS B
ON A.Ime = B.Ime
AND (B.Predmet = ‘Fiz’ OR B.Predmet = ‘Bio’)
WHERE B.predmet IS NOT NULL
GROUP BY A.Ime
HAVING COUNT(*)=2;

Ime   KolikoPredmeta
----- -------------- 
Cica               2
Maca               2
Pera               2
Zika               2

(4 row(s) affected)

Dopao mi se ovakav pristup, jer kad jednom u glavi svarite proširivanje JOIN filtera delom WHERE uslova, mnoga nezgodna pitanja postaju jednostavna. Međutim, najveća dobit jeste mogućnost da radite LEFT JOIN i da na desnoj strani ne dobijete baš sve redove, nego samo one koji vam trebaju. Ne sve predmete za svaku osobu, nego tačno onaj predmet koji vas u datom trenutku interesuje. Inače vam ne ginu temp tabele ili upotreba WITH.

Ako ovo nije bilo suviše suvoparno i zamorno, za koji dan pozabavićemo se upotrebom WITH, sa i bez rekurzije.

  1. One Response to “Ne volite fiziku i biologiju? A možda SQL?”

  2. Zidar, ti bi mogao lagano da kreneš da sastavljaš neku knjigu tipa “SQL mozgalice” 🙂

    By degojs on Dec 10, 2007

Post a Comment