Wednesday, 01.09.2010 – Zidar
Moj poslednji clanak, prica o poredjenju tabela, ostao je nezavrsen. http://www.baze-podataka.net/2010/08/31/poredjenje-tabela/
Pokazali smo kako se mogu porediti dve tabele, pod odredjenim uslovima, ali nismo dali jedinstveni kveri. Zavrsili smo sa tri kverija i obecanjem da u sledecm nastavku krenemo nekim drugim putem. Pre nego sto krenemo drugim putem, ipak da zavrsimo nezavrsen posao. Napisacemo kveri koji se bazira na JOINu izmedju tabela koje posmatramo i sakuplja sve slucajeve u jedan.
Ovako smo bili definisali slucajeve kad se tabele razlikuju:
– ID postoji u Original, a nema ga u Kopija
– ID postoji u Kopija a ndma ga u Original
– ID postoji u obe tabele, ali se neke od kolone razlikuju u odgovarajucim redovima
Kreirajmo tabele sa test podacim, tao da su zadovoljeni sledeci uslovi:
– -. U obe tabele ID ima osobine primarnog kljuca – nema NULL i nema duplikata u koloni ID.
– – U svakoj tabeli postoji po neki ID koji ne psotoji u drugoj.
– – Postoje i redovi gde je isti ID ali se razlikuju podaci u ponkoj koloni. – – Imamo i NULL vrednosti u ponekoj koloni koja nije ID.
IF Object_ID(‘tempdb..#Original’) IS NOT NULL DROP TABLE #Original
;
CREATE TABLE #Original (ID int , Ime varchar(12), Prezime varchar(12))
;
INSERT INTO #Original VALUES (1,‘Dusko’,‘Radovic’)
INSERT INTO #Original VALUES (99,‘Simo’,‘Esic’)
INSERT INTO #Original VALUES (3,‘Zuko’,‘Dzumhur’)
INSERT INTO #Original VALUES (4,‘Momo’,‘Kapor’)
INSERT INTO #Original VALUES (5,‘Branko’,‘Copic’)
INSERT INTO #Original VALUES (6,‘Mesa’,‘Selimovic’)
;
IF Object_ID(‘tempdb..#Kopija’) IS NOT NULL DROP TABLE #Kopija
;
CREATE TABLE #Kopija (ID int , Ime varchar(12), Prezime varchar(12))
;
INSERT INTO #Kopija VALUES (1,‘Dusko’,‘Radovic’)
INSERT INTO #Kopija VALUES (2,‘Simo’,‘Esic’)
INSERT INTO #Kopija VALUES (3,‘Zzzzzzuko’,‘Dzumhur’)
INSERT INTO #Kopija VALUES (4,‘Momo’,‘Kapor’)
INSERT INTO #Kopija VALUES (55,‘Branko’,‘Copic’)
INSERT INTO #Kopija VALUES (6,‘Mesa’,NULL)
;
SELECT ‘Original’, * FROM #Original
;
SELECT ‘Kopija’, * FROM #Kopija
;
ID Ime Prezime
——– ———– ———— ————
Original 1 Dusko Radovic
Original 99 Simo Esic
Original 3 Zuko Dzumhur
Original 4 Momo Kapor
Original 5 Branko Copic
Original 6 Mesa Selimovic
(6 row(s) affected)
ID Ime Prezime
—— ———– ———— ————
Kopija 1 Dusko Radovic
Kopija 2 Simo Esic
Kopija 3 Zzzzzzuko Dzumhur
Kopija 4 Momo Kapor
Kopija 55 Branko Copic
Kopija 6 Mesa NULL
(6 row(s) affected)
Napisimo kveri koji pokazuje redve koji se razlikuju medju posmatranim tabelama. Razlike su definisane ovako:
– ID postoji u Original, a nema ga u Kopija
– ID postoji u Kopija a ndma ga u Original
– ID postoji u obe tabele, ali se neke od kolone razlikuju
Kveri:
SELECT
StaJeOvo = ‘ID postoji u #Original, nema ga u #Kopija’
, O.*
FROM #Original AS O
LEFT JOIN #Kopija AS K ON O.ID = K.ID
WHERE K.ID IS NULL
UNION ALL
SELECT
StaJeOvo = ‘ID postoji u #Kopija, nema ga u #Original’
, K.*
FROM #Original AS O
RIGHT JOIN #Kopija AS K ON O.ID = K.ID
WHERE O.ID IS NULL
UNION ALL
SELECT
STaJeOvo = ‘#Original’
, O.*
FROM #Original AS O
JOIN #Kopija AS K ON O.ID = K.ID
WHERE COALESCE(O.Ime,‘Ovde je NULL’) <> COALESCE(K.Ime,‘Ovde je NULL’)
OR COALESCE(O.prezime, ‘Ovde je NULL’) <> COALESCE(K.prezime, ‘Ovde je NULL’)
UNION ALL
SELECT
STaJeOvo = ‘#Kopija’
, K.*
FROM #Original AS O
JOIN #Kopija AS K ON O.ID = K.ID
WHERE COALESCE(O.Ime,‘Ovde je NULL’) <> COALESCE(K.Ime,‘Ovde je NULL’)
OR COALESCE(O.prezime, ‘Ovde je NULL’) <> COALESCE(K.prezime, ‘Ovde je NULL’)
;
Ovaj monstrum od kverija vraca tacan rezultat:
StaJeOvo ID Ime Prezime
—————————————– ———– ———— ————
ID postoji u #Original, nema ga u #Kopija 99 Simo Esic
ID postoji u #Original, nema ga u #Kopija 5 Branko Copic
ID postoji u #Kopija, nema ga u #Original 2 Simo Esic
ID postoji u #Kopija, nema ga u #Original 55 Branko Copic
#Original 3 Zuko Dzumhur
#Original 6 Mesa Selimovic
#Kopija 3 Zzzzzzuko Dzumhur
#Kopija 6 Mesa NULL
(8 row(s) affected)
Teorijski, resili smo problem pordejenja dve tabele koje imaju kolonu (ili kolone) sa osobinama primarnog kljuca po kome se mogu vezati. Nisam siguran da ce ovaj metod zaziveti u praksi, kveri izgleda kao monstrum, nije lak za razumevanje i greske su moguce sto moze dovesti do prividno tacnih rezultata.
Potreban nam je neki jednostavniji kveri, jednostavniji za razumevanje. Postoje bar jos dva nacina. Jedan je standardni SQL, a drugi koristi MS SQL funkcije za rad sa skupovima.
Drugo resenje, standardni SQL
Ako su tabele identicne, red po red, kolona po kolona, njihova unija imace po dva ista reda. Neka imamo identicne tabele, ovako:
— Sample data – identical tables:
IF Object_ID(‘tempdb..#Original’) IS NOT NULL DROP TABLE #Original
CREATE TABLE #Original (ID int PRIMARY KEY, Ime varchar(12), Prezime varchar(12))
;
INSERT INTO #Original VALUES (1,‘Dusko’,‘Radovic’)
INSERT INTO #Original VALUES (2,‘Simo’,‘Esic’)
INSERT INTO #Original VALUES (3,‘Zuko’,‘Dzumhur’)
;
IF Object_ID(‘tempdb..#Kopija’) IS NOT NULL DROP TABLE #Kopija
CREATE TABLE #Kopija (ID int PRIMARY KEY, Ime varchar(12), Prezime varchar(12))
;
INSERT INTO #Kopija VALUES (1,‘Dusko’,‘Radovic’)
INSERT INTO #Kopija VALUES (2,‘Simo’,‘Esic’)
INSERT INTO #Kopija VALUES (3,‘Zuko’,‘Dzumhur’)
;
Unija ove dve table izgleda ovako:
SELECT * FROM #Original
UNION ALL
SELECT * FROM #Kopija
;
Unija dve tabele:
ID Ime Prezime
———– ———— ————
1 Dusko Radovic
2 Simo Esic
3 Zuko Dzumhur
1 Dusko Radovic
2 Simo Esic
3 Zuko Dzumhur
(6 row(s) affected)
Ocigledno je da u uniji svaki red je ponovljen dva puta, dve identicne kopije svakog reda postoje. Ako uradimo GROUP BY nad unijom po svim kolonama dobicemo ovako nesto:
SELECT ID, Ime, Prezime, COUNT(*) AS Cnt
FROM
(
SELECT * FROM #Original
UNION ALL
SELECT * FROM #Kopija
) AS X
GROUP BY ID, Ime, prezime
;
Rezultat poslednje operacije je:
ID Ime Prezime Cnt
———– ———— ———— ———–
1 Dusko Radovic 2
2 Simo Esic 2
3 Zuko Dzumhur 2
(3 row(s) affected)
Uocimo kako je COUNT(*) uvek 2 ako imamo uniju dve identicne tabele. Ako tabele nisu identicne, COUNT(*) nece biti jednak 2. Redovi koji nisu identicni imace COUNT(*)=1.
Evo ponovo test podataka kojim smo testirali kveri sa pocetka clanka.
IF Object_ID(‘tempdb..#Original’) IS NOT NULL DROP TABLE #Original
;
CREATE TABLE #Original (ID int , Ime varchar(12), Prezime varchar(12))
;
INSERT INTO #Original VALUES (1,‘Dusko’,‘Radovic’)
INSERT INTO #Original VALUES (99,‘Simo’,‘Esic’)
INSERT INTO #Original VALUES (3,‘Zuko’,‘Dzumhur’)
INSERT INTO #Original VALUES (4,‘Momo’,‘Kapor’)
INSERT INTO #Original VALUES (5,‘Branko’,‘Copic’)
INSERT INTO #Original VALUES (6,‘Mesa’,‘Selimovic’)
;
IF Object_ID(‘tempdb..#Kopija’) IS NOT NULL DROP TABLE #Kopija
;
CREATE TABLE #Kopija (ID int , Ime varchar(12), Prezime varchar(12))
;
INSERT INTO #Kopija VALUES (1,‘Dusko’,‘Radovic’)
INSERT INTO #Kopija VALUES (2,‘Simo’,‘Esic’)
INSERT INTO #Kopija VALUES (3,‘Zzzzzzuko’,‘Dzumhur’)
INSERT INTO #Kopija VALUES (4,‘Momo’,‘Kapor’)
INSERT INTO #Kopija VALUES (55,‘Branko’,‘Copic’)
INSERT INTO #Kopija VALUES (6,‘Mesa’,NULL)
;
Test podaci:
ID Ime Prezime
——– ———– ———— ————
Original 1 Dusko Radovic
Original 99 Simo Esic
Original 3 Zuko Dzumhur
Original 4 Momo Kapor
Original 5 Branko Copic
Original 6 Mesa Selimovic
(6 row(s) affected)
ID Ime Prezime
—— ———– ———— ————
Kopija 1 Dusko Radovic
Kopija 2 Simo Esic
Kopija 3 Zzzzzzuko Dzumhur
Kopija 4 Momo Kapor
Kopija 55 Branko Copic
Kopija 6 Mesa NULL
(6 row(s) affected)
Golim okom vidimo da se gotovo svi redovi razlikuju.
Da vidimo koliko se puta pojavljuju identicni redovi:
SELECT ID, Ime, Prezime, COUNT(*) AS Cnt
FROM
(
SELECT * FROM #Original
UNION ALL
SELECT * FROM #Kopija
) AS X
GROUP BY ID, Ime, prezime
;
ID Ime Prezime Cnt
———– ———— ———— ———–
1 Dusko Radovic 2
2 Simo Esic 1
3 Zuko Dzumhur 1
3 Zzzzzzuko Dzumhur 1
4 Momo Kapor 2
5 Branko Copic 1
6 Mesa NULL 1
6 Mesa Selimovic 1
55 Branko Copic 1
99 Simo Esic 1
(10 row(s) affected)
Redovi za Duska radovica i Momu Kapora imajupo dve pojave – oni su dakle identicni. Svi ostali redovi nisu identicni. Proizilazi da kveri koji prikazuje redove koji narusavaju idnticnost tabela izgleda ovako:
SELECT ID, Ime, Prezime, COUNT(*) AS Cnt
FROM
(
SELECT * FROM #Original
UNION ALL
SELECT * FROM #Kopija
) AS X
GROUP BY ID, Ime, prezime
HAVING COUNT(*) <> 2
;
Rezultat je isti koji smo dobili kverijem sa pocetka clanka:
ID Ime Prezime Cnt
———– ———— ———— ———–
2 Simo Esic 1
3 Zuko Dzumhur 1
3 Zzzzzzuko Dzumhur 1
5 Branko Copic 1
6 Mesa NULL 1
6 Mesa Selimovic 1
55 Branko Copic 1
99 Simo Esic 1
(8 row(s) affected)
Rezultat smo dobili pomocu mnogo jednostavnijeg kverija.
Sta nedostaje? Nista nam ne kaze koji red u rezultatu dolazi iz koje tabele. Ako hocemo da tucemo kveri sa pocetka clanka, treba i to da dodamo nekako. Evo jedan nacin:
SELECT ID, Ime, Prezime, COUNT(*) AS Cnt, StaJeOvo = MIN(Source)
FROM
(
SELECT * , Source = ‘#Original’ FROM #Original
UNION ALL
SELECT * , Source = ‘#Kopija’ FROM #Kopija
) AS X
GROUP BY ID, Ime, prezime
HAVING COUNT(*) <> 2
;
ID Ime Prezime Cnt StaJeOvo
———– ———— ———— ———– ———
2 Simo Esic 1 #Kopija
3 Zuko Dzumhur 1 #Original
3 Zzzzzzuko Dzumhur 1 #Kopija
5 Branko Copic 1 #Original
6 Mesa NULL 1 #Kopija
6 Mesa Selimovic 1 #Original
55 Branko Copic 1 #Kopija
99 Simo Esic 1 #Original
(8 row(s) affected)
Sami mozete da dodate sortiranje i da rezultat ucinite jso citljivijim.
Nedostatak ovog resenja je sto se moraju kucati nazivi kolona u SELECT i GROUP BY listama. Za tabele sa desetinama i stotinama kolona to moze biti problem, ali se stim vec moze ziveti – nedostatak nije kritican.
Resenje sa razlikom skupova
Ako tabele #Original i #Kopija posmatramo kao skupove ciji su elementi redovi, na njih mozemo primeiti operacije za rad nad skupovima. MS SQL ima operaciju EXCEPT koja daje razliku skupova (A bez B, A\B). verujem da ostali sistemi imaju ekvivalentne operacije za razliku skupova. Ako nema takve operacije – ovo sto sledi ostaje resenje za MS SQL server.
Razlika izmedju nasa dva skupa jeste “Redovi iz jedne tabele kojih nema u drugoj”. Kako u skupovima generalno nija A\B = B\A, to nam trebaju razlike sa obe starne. Pisanje je zaista jednostavno:
Sta ima u originalu a nema u kopiji:
SELECT ID, Ime, Prezime FROM #Original
EXCEPT
SELECT ID, Ime, Prezime FROM #Kopija
;
ID Ime Prezime
———– ———— ————
3 Zuko Dzumhur
5 Branko Copic
6 Mesa Selimovic
99 Simo Esic
(4 row(s) affected)
Sta ima u kopiji a nema u originalu:
SELECT ID, Ime, Prezime FROM #Kopija
EXCEPT
SELECT ID, Ime, Prezime FROM #Original
;
ID Ime Prezime
———– ———— ————
2 Simo Esic
3 Zzzzzzuko Dzumhur
6 Mesa NULL
55 Branko Copic
(4 row(s) affected)
Primetite da smo naveli tacna imena kolona. Nismo morali, SELECT * FROM radi bez problema, ako su sve kolone sa istim imenima, u istom redosledu i istog tipa.
Kveri koji obejdinjuje sve razlike moze se napistai ovako:
SELECT * FROM
(
(
SELECT * FROM #Original
EXCEPT
SELECT * FROM #Kopija
)
UNION
(
SELECT * FROM #Kopija
EXCEPT
SELECT * FROM #Original
)
) AS X
;
Rezultat ce biti tacan:
ID Ime Prezime
———– ———— ————
2 Simo Esic
3 Zuko Dzumhur
3 Zzzzzzuko Dzumhur
5 Branko Copic
6 Mesa NULL
6 Mesa Selimovic
55 Branko Copic
99 Simo Esic
(8 row(s) affected)
Lepota poslednjeg kverija je u tome sto ne morate kucati nazive kolona. Resenje sa UNION ALL i GROUP BY trazi da se navedu sve kolone, bas kao i originalno resenje sa JOINom..
Primetite da su #Kopija i #Original nazivi temp tabela, iza njih moze biti bilo sta, bilo koje proizvoljne dve tabele. To znaci da mozemo napisati sablon za poredjenje bilo koje dve tabele, koje
– Imaju istu strukturu (broj kolone, tip, redosled kolona, nije obavezno da imena kolona budu ista(!), iako je zgodno i pozeljno
– Imaju kolonu ili skup kolona koja se ponasju kao PK (nama NULL, name duplikata)
Sablon bi izgledao ovako:
IF Object_ID(‘tempdb..#Original’) IS NOT NULL DROP TABLE #Original
;
IF Object_ID(‘tempdb..#Kopijal’) IS NOT NULL DROP TABLE #Kopija
;
SELECT INTO #Original FROM <vasa prva tabela>
;
SELECT INTO #Kopija FROM <vasa druga tabela>
;
/* Redovi koji narusavaju identicnost tabela */
SELECT * FROM
(
(
SELECT * FROM #Original
EXCEPT
SELECT * FROM #Kopija
)
UNION ALL
(
SELECT * FROM #Kopija
EXCEPT
SELECT * FROM #Original
)
) AS X
;
Ako imate posla sa tabelama koje zadovoljavaju navedene uslove, sve sto treba da uradite jeste da zamenite vrednosti u <vasa prva tabela> i <vasa druga tabela>.
Za sada, nedostatak metode je sto vam ne kaze koji red rezultata dolazi iz koje tabele. Monstrum kveri sa pocetka je to lepo prikazivao. Zsto ne bi moglo i ovde? NAravno d amoze, ali se stvari komplikuju za nijansu.
/* Redovi koji narusavaju identicnost tabela, prosireno */
SELECT * FROM
(
SELECT StajeOvo = ‘Orignal bez kopije’, * FROM
(
SELECT * FROM #Original
EXCEPT
SELECT * FROM #Kopija
) AS O
UNION ALL
SELECT StaJeOvo = ‘Kopija bez originala’, * FROM
(
SELECT * FROM #Kopija
EXCEPT
SELECT * FROM #Original
) AS K
) AS X
;
Rezultat:
StajeOvo ID Ime Prezime
——————– ———– ———— ————
Orignal bez kopije 3 Zuko Dzumhur
Orignal bez kopije 5 Branko Copic
Orignal bez kopije 6 Mesa Selimovic
Orignal bez kopije 99 Simo Esic
Kopija bez originala 2 Simo Esic
Kopija bez originala 3 Zzzzzzuko Dzumhur
Kopija bez originala 6 Mesa NULL
Kopija bez originala 55 Branko Copic
(8 row(s) affected)
I posle komplikacije, ovo resenje i dalje ostaje sablon, pa mu se upotrebljivost nicim ne umanjuje. I dalje je sve sto treba da uradite da zamenite na pocetku vrednosti u <vasa prva tabela> i <vasa druga tabela>.
Prednost ovog nacina nad originalnim JOIN kverijam i resenjem sa UNION-GROUP BY je sto ne moramo da kucamo nazive kolona. To moze biti znacajno ako imamo desetine ili stotine kolona u tabelama. Znacajno, ali ne i presudno.
Ne zaboravite da sve ovo vazi ako nemate duplikata u tabelama koje poredite. Otuda zahtev da imate kolonu ili skup kolona koje imaju osobine primarnog kljuca.
Namera mi je da u sledecem nastavku (koji ce verovatno stici tek sredinom Septembra) pokazemo kako se tri ponudjena resenja ponasaju sa duplikatima. Zavisno od toga kako su duplikati rasporedjeni, mozete dobiti tacno, netacno ili delimicno tacno resenje. O tome, kako i o mogucem resenju kada imamo duplikate, za dve –tri nedelje.
J
Objavljeno u: Best practice, MS SQL Server | Ukupno komentara: 15 »
