MySQL (27) - Složitější dotazy
Jak napsat některé specifické dotazy? Dnes o vyhledávání duplicitních položek a chybějících záznamů pomocí jazyka SQL.
1.7.2005 06:00 |
Petr Zajíc
| Články autora
| přečteno 53979×
Tím, jak jsme se postupně seznámili s jednotlivými příkazy jazyka
SQL pro výběrové dotazy, otevřel se před námi svět databázových dat
dokořán. Většina pojednání o SQL, která se mi dostala do rukou v tomto
bodě popis příkazu SELECT končí. To je škoda, protože sice jsme si
ukázali na mnoho věcí z teorie, stále nám však chybí potřebná praxe.
Při dotazování pomocí SQL se běžně setkáváme s úlohami, které nejsou
na první pohled zase až tak jednoduché. Jak byste například provedli
dotaz, který vrátí chybějící data v jedné tabulce ve srovnání s jinou
tabulkou? Co dotaz na duplicitní položky? Umíte napsat dotaz, který
vrátí nejvyšší hodnoty v několika různých kategoriích? Co dotaz
vracející jako součást sady záznamů číslování řádků, nebo takový, co
umí vracet průběžné součty? Dost mi vadí, že nemám takové náročnější
příklady hned po ruce, když je potřebuji. Pokusím se to napravit; v
seriálu teď několik takových věcí popíšu. Máte se na co těšit - pokud
vím, nebyly tyto informace na českých webech v podobné formě a rozsahu
dosud uveřejněny.
Chybějící data
S tímto dotazem jsme se již setkali a je poměrně běžný. Zadání je
jednoduché - máme dvě tabulky, které obsahují minimálně jeden shodný
sloupec (typicky jsou to tabulky v relaci 1:N) a cílem je zjistit,
které údaje z první tabulky se vůbec neobjevují v tabulce druhé. Mějme
třeba knihovnu a dvě tabulky - čtenáře a výpůjčky. Cílem bude zjistit,
kdo ze čtenářů v knihovně ještě nikdy nic nepůjčil. Tabulka čtenářů by
mohla být:
create table ctenari
(ctenar varchar(20));
insert into ctenari (ctenar) values ('Petr'), ('Karel'), ('Pavel'),
('Josef');
insert into ctenari (ctenar) values ('Petra'), ('Jana'), ('Veronika'),
('Lenka');
a výpůjčky by mohly vypadat takto:
create table vypujcky
(datum datetime, ctenar varchar(20));
insert into vypujcky (datum, ctenar) values ('20050520','Petra');
insert into vypujcky (datum, ctenar) values ('20050528','Petra');
insert into vypujcky (datum, ctenar) values ('20050522','Jana');
insert into vypujcky (datum, ctenar) values ('20050615','Veronika');
insert into vypujcky (datum, ctenar) values ('20050528','Lenka');
Jak vidíte, kluci vůbec nečtou. Jak sestavit dotaz, který by to
odhalil?
Řešení se spojením
Jedno dobré řešení je spojit obě tabulky levým spojením a výsledný
dotaz filtrovat podle vypujcky.ctenar. Jestliže tam bude hodnota null,
jde o čtenáře, který si nikdy nic nepůjčil - a toho právě hledáme.
select ctenari.* from
ctenari left join vypujcky on ctenari.ctenar = vypujcky.ctenar where
vypujcky.ctenar is null;
Toto řešení bude vracet výsledky velmi rychle a tudíž se používá
velmi často.
Řešení s poddotazem
Existuje však další možnost, a to poddotaz. Odpovídalo by to českému
"vyber všechny čtenáře, kteří nejsou obsaženi v tabulce výpůjček".
Zapsáno v SQL by to bylo takto:
select ctenari.* from
ctenari where ctenar not in (select ctenar from vypujcky);
Kromě toho, že se takto zapsaný dotaz dobře čte, má vlastně jen samé
nevýhody. Předně - nebude ve většině případů dost rychlý, a to zejména
tehdy, když se budou obě tabulky zvětšovat. Málo zřejmý je ale druhý,
mnohem závažnější problém. Pokud by totiž v tabulce výpůjček existoval
byť jen jeden záznam s hodnotou NULL ve sloupci ctenar, celé to
přestane fungovat úplně. Důvodem je skutečnost, že nelze porovat dvě
hodnoty
NULL ani hodnotu NULL s jinou známou hodnotou (výsledkem je vždy
hodnota NULL). Problém namodelujete takto:
insert into vypujcky
(datum, ctenar) values (now(), NULL);
select ctenari.* from ctenari where ctenar not in (select ctenar from
vypujcky);
Není to tak hrozné - situace má východisko. Pokud byste na řešení s
poddotazem trvali, musíte v něm omezit výběr na ty řádky, kde je zadán
nějaký čtenář v tabulce výpůjček, takto:
select ctenari.* from
ctenari where ctenar not in (select ctenar from vypujcky where ctenar
is not null);
Jelikož řešení se spojením podobnými neduhy netrpí, doporučuji
používat ten první přístup.
Pozn.: Příklad byl zjednodušen. V reálné
aplikaci bychom pravděpodobně v tabulce výpůjček měli ještě další pole,
a čtenáři by zřejmě byli identifikováni čislem, ne křestním jménem. Na
principu to však nic nemění.
Duplicitní položky
Na stejných datech můžeme demonstrovat i další obvyklou úlohu - a to
výběr položek, které se v dané tabulce vyskytují vícekrát než jednou.
Mohli bychom chtít třeba vědět, kdo má více než jednu výpůjčku.
Naštěstí i tady je řešení celkem prosté. Spočívá v použití klauzule
HAVING poté, co tabulku výpůjček seskupíme podle sloupce čtenář, nějak
takto:
select ctenar from
vypujcky group by ctenar having count(*) >=2;
Není v tom žádná magie. Tabulku jsme seskupili podle pole "čtenář" a
zajímají nás jen ti, kteří byli v knihovně dvakrát (nebo častěji).
Dotaz však není tak podrobný, jak by mohl být. Může se stát, že nás
zajímají nejen čtenáři, kteří byli v knihovně častěji než jednou, ale i
ostatní data (neboli, v našem případě kdy
konkrétně byli v knihovně). Tady musíte být opatrní, protože
snadno lze podlehnout léčce a myslet si, že rozšíření prvního dotazu ve
smyslu:
select * from vypujcky
group by ctenar having count(*) >=2;
zařídí to, co potřebujeme. Kdepak! Tento dotaz vrátí stejný počet
záznamů jako dotaz předchozí s tím, že bude uvedeno vždy jen jedno
datum výpůjčky. Čili, musíme na to jinak.
Pozn.: Abych byl upřímný, zazlívám
MySQL, že dotaz výše jde vůbec provést. V některých DBMS skončí podobný
pokus chybovým hlášením ve smyslu tom, že sloupec vypujcky.datum nelze
do výsledné sady záznamů zahrnout, neb není v klauzuli GROUP BY. Obecně
byste se měli hvězdičkové konvenci vyhýbat vždy, když pracujete se
seskupováním záznamů.
Řešením je použít ten první dotaz jako poddotaz, nějak takto:
select * from vypujcky
where ctenar in
(select ctenar from vypujcky group
by ctenar having count(*) >=2);
Tento kód bude fungovat ve všech DBMS a je syntakticky čistý. V
rozporu s tím, co tvrdí někteří vývojáři lze tento poddotaz přepsat
poměrně jednoduše na spojení:
select vypujcky.* from
vypujcky join
(select ctenar from vypujcky group by ctenar having count(*) >=2)
pravidelni_ctenari
on vypujcky.ctenar=pravidelni_ctenari.ctenar;
Je ale pravda, že specielně v tomto případě nebude rychlostní užitek
ze spojení tak výrazný. Důvod je prostý - poddotazem nebyla skutečná
tabulka, ale výsledek výpočtu. Tabulka pravidelni_ctenari tedy v
databázi neexistuje a MySQL ji musí v obou případech dopočítat.
Verze pro tisk
|
Příspívat do diskuze mohou pouze registrovaní uživatelé.
|
|

Vyhledávání software

Vyhledávání článků
28.11.2018 23:56 /František Kučera Prosincový sraz spolku OpenAlt se koná ve středu 5.12.2018 od 16:00 na adrese Zikova 1903/4, Praha 6. Tentokrát navštívíme organizaci CESNET. Na programu jsou dvě přednášky: Distribuované úložiště Ceph (Michal Strnad) a Plně šifrovaný disk na moderním systému (Ondřej Caletka). Následně se přesuneme do některé z nedalekých restaurací, kde budeme pokračovat v diskusi.
Komentářů: 1
12.11.2018 21:28 /Redakce Linuxsoft.cz 22. listopadu 2018 se koná v Praze na Karlově náměstí již pátý ročník konference s tématem Datová centra pro business, která nabídne odpovědi na aktuální a často řešené otázky: Jaké jsou aktuální trendy v oblasti datových center a jak je optimálně využít pro vlastní prospěch? Jak si zajistit odpovídající služby datových center? Podle jakých kritérií vybírat dodavatele služeb? Jak volit vhodné součásti infrastruktury při budování či rozšiřování vlastního datového centra? Jak efektivně datové centrum spravovat? Jak co nejlépe eliminovat možná rizika? apod. Příznivci LinuxSoftu mohou při registraci uplatnit kód LIN350, který jim přinese zvýhodněné vstupné s 50% slevou.
Přidat komentář
6.11.2018 2:04 /František Kučera Říjnový pražský sraz spolku OpenAlt se koná v listopadu – již tento čtvrtek – 8. 11. 2018 od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5). Tentokrát bez oficiální přednášky, ale zato s dobrým jídlem a pivem – volná diskuse na téma umění a technologie, IoT, CNC, svobodný software, hardware a další hračky.
Přidat komentář
4.10.2018 21:30 /Ondřej Čečák LinuxDays 2018 již tento víkend, registrace je otevřená.
Přidat komentář
18.9.2018 23:30 /František Kučera Zářijový pražský sraz spolku OpenAlt se koná již tento čtvrtek – 20. 9. 2018 od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5). Tentokrát bez oficiální přednášky, ale zato s dobrým jídlem a pivem – volná diskuse na téma IoT, CNC, svobodný software, hardware a další hračky.
Přidat komentář
9.9.2018 14:15 /Redakce Linuxsoft.cz 20.9.2018 proběhne v pražském Kongresovém centru Vavruška konference Mobilní řešení pro business.
Návštěvníci si vyslechnou mimo jiné přednášky na témata: Nejdůležitější aktuální trendy v oblasti mobilních technologií, správa a zabezpečení mobilních zařízení ve firmách, jak mobilně přistupovat k informačnímu systému firmy, kdy se vyplatí používat odolná mobilní zařízení nebo jak zabezpečit mobilní komunikaci.
Přidat komentář
12.8.2018 16:58 /František Kučera Srpnový pražský sraz spolku OpenAlt se koná ve čtvrtek – 16. 8. 2018 od 19:00 v Kavárně Ideál (Sázavská 30, Praha), kde máme rezervovaný salonek. Tentokrát jsou tématem srazu databáze prezentaci svého projektu si pro nás připravil Standa Dzik. Dále bude prostor, abychom probrali nápady na využití IoT a sítě The Things Network, případně další témata.
Přidat komentář
16.7.2018 1:05 /František Kučera Červencový pražský sraz spolku OpenAlt se koná již tento čtvrtek – 19. 7. 2018 od 18:00 v Kavárně Ideál (Sázavská 30, Praha), kde máme rezervovaný salonek. Tentokrát bude přednáška na téma: automatizační nástroj Ansible, kterou si připravil Martin Vicián.
Přidat komentář
Více ...
Přidat zprávičku
 Poslední diskuze
31.7.2023 14:13 /
Linda Graham iPhone Services
30.11.2022 9:32 /
Kyle McDermott Hosting download unavailable
13.12.2018 10:57 /
Jan Mareš Re: zavináč
2.12.2018 23:56 /
František Kučera Sraz
5.10.2018 17:12 /
Jakub Kuljovsky Re: Jaký kurz a software by jste doporučili pro začínajcího kodéra?
Více ...
|