LINUXSOFT.cz Přeskoč levou lištu
Uživatel: Heslo:  
   CZUKPL

> 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 50312×

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

pridej.cz

 

DISKUZE

Rychlost subselectu 4.7.2005 14:03 Risa
  L Re: Rychlost subselectu 6.7.2005 18:03 Jan Houštěk
    L Re: Rychlost subselectu 10.7.2005 07:51 Petr Zajíc




Příspívat do diskuze mohou pouze registrovaní uživatelé.
> Vyhledávání software
> Vyhledávání článků

18.6.2018 0:43 /František Kučera

Červnový pražský sraz spolku OpenAlt se koná již tento čtvrtek – 21. 6. 2018 od 18:00 v Kavárně Ideál (Sázavská 30, Praha), kde máme rezervovaný salonek. Tentokrát na téma: F-Droid, aneb svobodný software do vašeho mobilu. Kromě toho budou k vidění i vývojové desky HiFive1 se svobodným/otevřeným čipem RISC-V.


Přidat komentář

23.5.2018 20:55 /Ondřej Čečák
Od pátku 25.5. proběhne na Fakultě informačních technologií ČVUT v Praze openSUSE Conference. Můžete se těšit na spostu zajímavých přednášek, workshopů a také na Release Party nového openSUSE leap 15.0. V na stejném místě proběhne v sobotu 26.5. i seminář o bezpečnosti CryptoFest.
Přidat komentář

20.5.2018 17:45 /Redakce Linuxsoft.cz
Ve čtvrtek 31. května 2018 připravuje webový magazín BusinessIT ve spolupráci s Best Online Média s.r.o. pátý ročník odborné konference Firemní informační systémy 2018. Akce proběhne v kongresovém centru Vavruška (palác Charitas), Karlovo náměstí 5, Praha 2 (u metra Karlovo náměstí) od 9:00 hod. dopoledne do cca 15 hod. odpoledne. Konference je zaměřena na efektivní využití firemních informačních systémů a na to, jak plně využít jejich potenciál. Podrobnější informace na webových stránkách konfrence.
Přidat komentář

14.5.2018 7:28 /František Kučera
Květnový pražský sraz spolku OpenAlt se koná již tento čtvrtek – 17. 5. 2018 od 18:00 v Kavárně Ideál (Sázavská 30, Praha), kde máme rezervovaný salonek. Tentokrát na téma: Audio – zvuk v GNU/Linuxu.
Přidat komentář

7.5.2018 16:20 /František Kučera
Na stránkách spolku OpenAlt vyšla fotoreportáž Pražské srazy 2017 dokumentující srazy za uplynulý rok. Květnový pražský sraz na téma audio se bude konat 17. 5. 2018 (místo a čas ještě upřesníme).
Přidat komentář

17.4.2018 0:46 /František Kučera
Dubnový pražský sraz spolku OpenAlt se koná již tento čtvrtek – 19. 4. 2018 od 18:00 v Kavárně Ideál (Sázavská 30, Praha), kde máme rezervovaný salonek. Tématem tohoto srazu bude OpenStreetMap (OSM) aneb svobodné mapy.
Přidat komentář

16.3.2018 22:01 /František Kučera
Kulatý OpenAlt sraz v Praze oslavíme klasicky: u limonády a piva! Přijďte si posedět, dát si dobré jídlo a vybrat z mnoha piv do restaurace Kulový blesk, který najdete v centru Prahy nedaleko metra I. P. Pavlova na adrese Sokolská 13, Praha 2. Sraz se koná ve čtvrtek 22. března a začínáme v 18:00. Heslo: OpenAlt. Vezměte s sebou svoje hračky! Uvítáme, když si s sebou na sraz vezmete svoje oblíbené hračky. Jestli máte nějaký drobný projekt postavený na Arduinu, nějakou zajímavou elektronickou součástku, či třeba i pěkný úlovek z crowdfundingové akce, neváhejte. Oslníte ostatní a o zábavu bude postaráno.
Přidat komentář

13.2.2018 0:41 /František Kučera
Únorový pražský sraz OpenAltu se koná 15. 2. 2018 a tentokrát se vydáme na návštěvu do jednoho pražského datacentra. Sejdeme se v 17:50 v severovýchodní části nástupiště tramvajové zastávky Koh-I-Noor. Po exkurzi se přesuneme do restaurace U Pštrosa (Moskevská 49), kde probereme tradiční témata (svobodný software a hardware, DIY, CNC, SDR, 3D tisk…) a tentokrát bude k vidění i IoT brána od The Things Network.
Přidat komentář

   Více ...   Přidat zprávičku

> Poslední diskuze

20.2.2018 18:48 / Ivan Majer
portal

20.2.2018 15:57 / Jan Havel
Jak využíváte služby cloudu v podnikání?

16.1.2018 1:08 / Ivan Pittner
verejna ip od o2 ubuntu

15.1.2018 17:26 / Mira Harvalik
Re: Jak udělat HTML/Javascript swiping gallery do mobilu?

30.12.2017 20:16 / Michal Knoll
odmocnina

Více ...

ISSN 1801-3805 | Provozovatel: Pavel Kysilka, IČ: 72868490 (2003-2018) | mail at linuxsoft dot cz | Design: www.megadesign.cz | Textová verze