Posted By: Jovo () on 'CZdatabases'
Title: Jovo 2
Date: Thu Oct 5 19:40:25 2000
Ahoj.
Do tohoto problemu jsem se fakt zazral ... no spis jsem se v nem zamotal :-)
A nez jsem se vymotal, tak jsem pekne bloudil. Diky tomu text nakynul na
velikost kolem 10kB. Kdo nechce cist, at mackne 'q'.
PS: Slibuju, ze takove traktaty uz ode mne neuvidite ;-]
Jovo.
------------------------------------------------------------------------------
Z Jovova zapisniku - 2:"Jak lze take pouzit UNION"
==================================================
Kazdy programator obcas po obtezovani uzivatelem dospeje do stadia, kdy je
treba data v nejake forme dostat ven. Jelikoz u nas UNLOADy nezabraly,
programujeme "vystupni zpravy", cesky "sestavy", anglicky "report"y :-)
Ve vetsine sestav je neco jako master-detail. Treba tohle mame v DB ... :
TABLE misto TABLE lidi
id mesto ulice RC Bydli Jmeno
====================================== ===================================
(serial) (char20) (char30) (char11) (int) (char30)
-------------------------------------- -----------------------------------
1 J.HRADEC Tyrsova 626/II 741213,1234 2 SuE
2 LIBEREC Veveri 2018 691234,1576 4 Vodnik
3 PRAHA Konipaskova 15 156611,4896 Bezdomovec
4 ZA TOPOLEM Podle skal 1 168312,1236 1 Jovo
496213,8965 Druhej Bezdomovec
156793,1265 2 RoboShim
... chceme udelat vystupni zpravu, kde bude neco jako
Misto1 <-Blok mest (prvni iterace = master)
vsichni, kdo bydli v miste 1 <-Blok lidi (druha iterace = detail)
Misto2
vsichni, kdo bydli v miste 2
.................
Pokud opomenu naprosto brutalni, ale zato velmi ucinnou metodu, ve ktere se
vyrobi jeden SELECT id,mesto,ulice,RC,Bydli,Jmeno FROM misto, lidi ...
(pak se v prvni iteraci vybiraji jen id,mesto,ulice a v druhem zbytek),
pak se vetsinou delaji dva SELECTy, pro kazdou iteraci jeden.
V nasem pripade tedy neco jako
1. iterace :
SELECT id,mesto,ulice
FROM misto, lidi
WHERE misto.id = lidi.Bydli --tato podminka zaruci, ze se vyberou
--jen ty mista, kde nekdo bydli
--treba PRAHA se nevypise
2. iterace
SELECT Jmeno
FROM lidi
WHERE lidi.Bydli = <id z prvni iterace>
Tohle nam hodi:
---------------
Misto : 1 J.HRADEC Tyrsova 626/II
Jovo
Misto : 3 BRNO Veveri 12
SuE
RoboShim
Misto : 4 ZA TOPOLEM Podle skal 1
Vodnik
Tak, a ted k cemu jsem se chtel dostat :-) Ukol zni : Vypsat i lidi,
kteri nikde nebydli. Jak na to, kdyz Bezdomovci nemaji bydliste a neni jak
tedy svazat druhou iteraci s prvni ? Reseni je nekolik:
1) Vyrobime jeste jednu iteraci, tu dame za tu prvni a druhou iteraci a
definujeme ji jako
SELECT Jmeno FROM lidi WHERE lidi.Bydli IS NULL
To nekdy jde, nekdy ne a nekdy je to jen moc pracne.
2) Vyrobime si docasnou (TEMPORARY) tabulku, do ni nahrneme jak mista, kde
nekdo bydli, tak i radek navic, ktery bude pro Bezdomovce.
To jde, ale je to spousta prace navic a jeste se musi opravovat SELECTY
obou iteraci.
3) Pouzijeme UNION :
1. iterace :
SELECT id,mesto,ulice
FROM misto, lidi
WHERE misto.id = lidi.Bydli
UNION
SELECT DISTINCT -1,"nema misto","nema ulici"
FROM misto
2. iterace
SELECT Jmeno
FROM lidi
WHERE isnull(lidi.Bydli,-1) = <id z prvni iterace>
- pozn.: funkce isnull(par1,par2) dela toto :
- kdyz je par1 NULL, pak vrati par2
- jinak vrati par1
- takze u Bezdomovcu vyhodi, ze maji Bydli nastavenou na -1
to nam vyhodi:
Misto : 1 J.HRADEC Tyrsova 626/II
Jovo
Misto : 3 BRNO Veveri 12
SuE
RoboShim
Misto : 4 ZA TOPOLEM Podle skal 1
Vodnik
Misto: -1 nema misto nema ulici
Bezdomovec
Druhej Bezdomovec
Elegantni a jednoduche. A ted prichazim k tomu, o co jsme si nabili drzku -
"vono to vobcas nefunguje, cece" :-) Uskali je nekolik :
1) nikdo nam nezaruci, ze id v tabulce misto bude vzdy ruzne od -1. Nejaky
proces muze byt naprogramovan tak, aby do serial nacpal svoje vlastni
cislo. Kdyz chceme pouzit cislo, muzeme se trefit do neceho, co tam uz je.
Tomu se da zabranit, ze si zjistime nejake cislo idcka, ktere tam neni
nez zacneme delat iterace. Da se pouzit treba
SELECT MAX(id) INTO CISLO FROM misto
CISLO = CISLO + 1
1. iterace :
SELECT id,mesto,ulice
FROM misto, lidi
WHERE misto.id = lidi.Bydli
UNION
SELECT DISTINCT CISLO,"nema misto","nema ulici"
FROM misto
2. iterace
SELECT Jmeno
FROM lidi
WHERE isnull(lidi.Bydli,CISLO) = <id z prvni iterace>
- v tomto pripade bude CISLO rovno 5
- pokud delate v cistem SQL, ve vyrazu
SELECT DISTINCT CISLO,"nema misto","nema ulici"
nahradte "CISLO" vyrazem
(SELECT (MAX(id)+1) FROM misto)
To ma zase tu vadu, ze pokud nad DB pracuje vic uzivatelu, muze se stat, ze
do tabulky misto nekdo mezitim vlozi dalsi misto (c.5) a lidi, kteri budou
bydlet zrovna "na petce", sestava zaradi mezi Bezdomovce. Co s tim ?
Navic ten UNION muze selhat, viz [2].
a - vykaslat se na to s tim, ze to nastane jednou za milion let. Pokud muzu
rict, jednou za milion let znamena obvykle zitra :-)
b - CISLO = CISLO + 1500000 To take neni moc systemove, ze, ale lepri nez [a]
c - zamknout tabulky (LOCK TABLE) v dobe vytvareni sestavy pro zapis. Ok, ale
proces nesmi spadnout (aby nezustaly viset zamky) a bezet hodne rychle, at
se ostatni uzivatele cekanim nezblazni. Nepouzitelne pro viceuzivatelske
systemy s rychlou obmenou dat. Navick kdyz mate tabulku distribuovanou
do dalsich patnacti mist ...
d - vyhrnout si do TEMPORARY tabulek data a pak v klidu z nich udelat sestavu
(CREATE TEMP TABLE misto_tmp (...jako misto...);
INSERT INTO SELECT * FROM misto;
CREATE TEMP TABLE lidi_tmp (...jako lidi...);
INSERT INTO SELECT * FROM lidi
WHERE lidi.Bydli=misto_tmp;
vlozeni radku pro bezdomovce;).
Prace navic, je to o DOST pomalejsi, zere to cas procesoru a hlavne
potrebuje misto na disku.
2) Kviz.
a - Co vyhodi SELECT -1,"nema misto","nema ulici" FROM misto (Bez DISTINCT) ?
- odpoved: ctyrikrat to same, v nasem pripade da :
-1,"nema misto","nema ulici"
-1,"nema misto","nema ulici"
-1,"nema misto","nema ulici"
-1,"nema misto","nema ulici"
proto tam musi byt DISTINCT
b - A ted to, co nas stalo pul hodiny ladeni :-) Co vyhodi
SELECT DISTINCT -1,"nema misto","nema ulici" pokud bude tabulka
misto prazdna ?
- odpoved: VUBEC NIC ! :-) Pricina je, ze SELECT prochazi tabulku radek
po radku a kdyz podminka sedi, tak vypise to, co je za klauzuli SELECT.
Proto se musi kod opravit
SELECT MAX(id) INTO CISLO FROM misto ->
SELECT isnull(MAX(id),1) INTO CISLO FROM misto
3) Vzpomenete, co se pise v prirucce o typu serial :
Pokud do databaze vkladate radek stylem
INSERT INTO misto VALUES(0,mesto,ulice), pak se vam misto nuly do DB
spocita idcko. To znamena, ze nula tam nikdy nebude. Kdybychom udelali v
nasem pripade INSERT INTO misto VALUES(0,"PARDUBICE","Pernikova 11"), do
databaze se vlozi radek
5,PARDUBICE,Pernikova 11
(pokud nebude DB stroj nakonfigurovany, aby k serialu pridaval neco jineho,
nebo pokud treba stroj nespadne a pak zacne pridelovat od jineho cisla).
Takze finalni reseni je:
1. iterace :
SELECT id, mesto,ulice
FROM misto, lidi
WHERE misto.id = lidi.Bydli
UNION
SELECT DISTINCT 0,"nema misto","nema ulici"
FROM lidi
2. iterace
SELECT Jmeno
FROM lidi
WHERE isnull(lidi.Bydli,0) = <id z prvni iterace>
Vsimnete si, ze SELECT za UNION vybira (FROM) z tabulky lidi. Duvod :
viz bod [2b] - pokud bude tabulka mist prazdna, SELECT za UNIONem
nevybere nic. Jelikoz tiskneme lidi, tak nam pripad, ze se nevybere
nic z lidi nevadi, protoze by to stejne zadne lidi nevytisklo.
Konec postupu. Uffff :-)
Vas Jovo.
============================================================================
PS:
- Zkuste vymyslet reseni pro pripad, kdyz sloupec id nebude serial,
ale treba jen INT ... spravne, tady jde pouzit uz jen [1], popripade
[2d] :-)
- Pozor! : UNION vyhazuje duplicity, takze pokud date
SELECT DISTINCT 1 FROM lidi
UNION
SELECT DISTINCT 1 FROM lidi, pak to nevybere
1
1
, ale pouze jednu jednicku !
1
V nasi modelove situaci vyhazovani duplicit to nevadi, ale jinak s
UNIONEM opatrne !
- Pokud byste chteli optimalizovat vyraz az do mrte, pak si muzete
vsimnout, ze tabulka lidi muze mit milion radku. Potom
SELECT DISTINCT 0,"nema misto","nema ulici" FROM lidi
muze na spatne nakonfigurovanych strojich bezet hodne dlouho. Da se to
vyresit pres docasnou tabulku.
CREATE TEMP TABLE dummy (num INTEGER);
INSERT INTO dummy VALUES(0);
a pak jen 1. iteraci upravit na :
SELECT id,mesto,ulice
FROM misto, lidi
WHERE misto.id = lidi.Bydli
UNION
SELECT num,"nema misto","nema ulici"
FROM dummy
- Pro ty, kteri/ktere si priklady chteji overit :
create temp table misto (
id serial,
mesto char(20),
ulice char(30)
);
create temp table lidi (
RC char(11),
Bydli int,
Jmeno char(30)
);
insert into misto values(1,"J.HRADEC","Tyrsova 626/II");
insert into misto values(2,"LIBEREC","Veveri 2018");
insert into misto values(3,"PRAHA","Konipaskova 15");
insert into misto values(4,"ZA TOPOLEM","Podle skal 1");
insert into lidi values("741213,1234",2,"SuE");
insert into lidi values("691234,1576", 4, "Vodnik");
insert into lidi values("156611,4896",NULL, "Bezdomovec");
insert into lidi values("168312,1236", 1, "Jovo");
insert into lidi values("496213,8965",NULL, "Druhej Bezdomovec");
insert into lidi values("156793,1265", 2, "RoboShim");
select * from lidi;
select * from misto;
drop table dummy;
drop table misto;
drop table lidi;