LINUXSOFT.cz Přeskoč levou lištu

ARCHIV



   

> PostgreSQL (22) - Poddotazy

PostgreSQL server kromě klasického spojování výběrů z několika tabulek pospojováním pomocí klauzule JOIN a kromě využívání spojování výsledků, které bylo probráno v minulém díle umí dnes již i standardní poddotazy.

30.6.2006 06:00 | MaReK Olšavský | Články autora | přečteno 13095×

Podvýběry

Minule probrané spojování výsledků několika dotazů do jediného výsledku je možnost, jak dostat jednu výstupní množinu z několika dotazů, při zachování jednoduchého omezení zmíněného na počátku. Podvýběry jsou také spojení výstupů z několika dotazů do jediné výsledkové množiny, ale rozdílem je použití výsledků vnitřních dotazů jako parametrů dotazů vnějších, ať už na místě konstant, závislých či nezávislých na zpracovávaném řádku, nebo jako seznam hodnot, proti kterému se porovnávají podmínky. Jiným způsobem použití dotazu je naplnění nové tabulky, například z důvodů rychlosti používané agregace, které postačí obnovit jednou za určitou periodu, ideálně v době, kdy je databázový server málo zatížen.

Drtivou většinu dotazů, pro které lze použít poddotazy lze přepsat jako spojení tabulek pomocí JOIN, ale pokud hodnoty z podvýběru není potřeba mít ve výsledné množině (jsou použity jen ve WHERE části), je použití joinů zbytečné. Na druhé straně spojování výběrů pomocí JOINů je většinou přehlednější a podstatně "řiditelnější", tzn. máte podstatně lepší přehled o tom, která data spojujete a máte kontrolu nad tím, v jakém pořadí jsou výsledky spojovány.

Pro potřeby tohoto dílu si opět budeme muset nadefinovat nové tabulky, které i s daty naleznete na našem serveru. Příklady, které zde budou uvedeny asi nebudou z těch nejpraktičtějších o možná vám budou připadat "přitažené za vlasy". Pro demostraci toho k čemu se dají použít poddotazy jsou však dostatečné.

CREATE TABLE dealers(
  dealers_id SERIAL,
  name CHARACTER VARYING(150),
  active BOOLEAN
);

CREATE TABLE orders(
  orders_id BIGSERIAL
  dealers_id INTEGER,
  order_date TIMESTAMP WITHOUT TIME ZONE DEFAULT '01-01-0001 00:00:00',
  total_price DECIMAL(10,2)
);
-- Pokud si nestahnete priklady, naplneni daty je na Vas :-)

Začneme tím jednodušším, použitím poddotazů místo konstant. Konstanty mohou být závislé, či nezávislé na zpracovávaném řádku. Konstanty jsou v klasickém pojetí vždy stejné, v případě poddotazů není toto tvrzení 100% pravdivé.

Pokud chceme využít poddotazu na místě konstanty, ukažme si napřed jeden klasický postup bez poddotazů. Z tabulky prodejců chete získat ty prodejce, jejichž objednávky jsou nadprůměrné. Klasicky si prvním dotazem získáte výši průměrné objednávky1 a dalším dotazem (který bude spojovat tabulky DEALERS a ORDERS) získáme jména dealerů, kteří měli nadprůměrné objednávky. Pokud využijete možností vnořeného dotazu, ušetříte nejen čas pro komunikaci mezi aplikací a databází, ale opět bude platit, že jeden složený dotaz je zpracován rychleji, než dva po sobě jdoucí dotazy. Vnitřní dotaz je proveden při každém průchodu tabulkou.

-- napred bez subselectu
-- ziskame prumernou cenu:
SELECT AVG(total_price) as avg_price FROM orders;
-- nyni tuto hodnotu zakomponujeme
SELECT name FROM dealers AS t1 LEFT JOIN orders AS t2 
  ON t1.dealers_id=t2.dealers_id WHERE t2.total_price>avg_price;

--nyni poddotazem
SELECT name FROM dealers AS t1 LEFT JOIN orders AS t2 
  ON t1.dealers_id=t2.dealers_id WHERE 
  t2.total_price>(SELECT AVG(total_price) as avg_price FROM orders);

Korelované konstanty jsou trochu jiné, získávají se pro každou získanou hodnotu z vnějšího dotazu znovu. Na našich tabulkách to může být například získání největšího obchodu každého z našich prodejců. Napřed si ukážeme, jak se dají tyto hodnoty získat prostým spojováním tabulky objednávek sama se sebou a použití modifikátoru HAVING (při použití tohoto omezení výsledkové množiny musíte GROUPOvat), následně se podíváme na to, jak totéž napsat mnohem jednodušeji pomocí vnořeného dotazu. Všimněte si přes který sloupec je prováděno spojení tabulky orders s sama sebou, kdybyste se ji pokusili spojit přes položku orders_id, dostali byste zcela jinou mnoužinu výsledků - vyzkoušejte si sami, jakou.

SELECT t1.name, t2.total_price FROM (dealers AS t1 LEFT JOIN orders AS t2
ON t1.dealers_id=t2.dealers_id) LEFT JOIN orders AS t3 ON
(t2.dealers_id=t3.dealers_id)
GROUP BY t1.name, t2.total_price
HAVING t2.total_price=MAX(t3.total_price);

--nyni prehledneji poddotazem
SELECT t1.name, t2.total_price FROM (dealers AS t1 LEFT JOIN orders AS t2
ON t1.dealers_id=t2.dealers_id) WHERE t2.total_price=
  (SELECT max(t3.total_price) FROM orders AS t3 WHERE 
  t2.dealers_id=t3.dealers_id
);

Jako poslední, z ryze výběrových dotazů, si probereme použití poddotazů pro vygenerování seznamu hodnot použitých porovnávání. Přesné fungování bude zřejmé z příkladů a ze všeho nejdříve je zapotřebí začít potřebnými operátory. Toto použití poddotazů je přesně ten příklad, kdy lze většinou použít poddotaz.

  • EXISTS – Vrátí-li poddotaz na pravé straně tohoto operátoru libovolnou neprázdnou hodnotu (neprázdný řádek), je proveden dotaz, ve kterém je tento vložen. Vnitřní dotaz se nevykonává úplně, PgSQL server se z něj nesnaží vracet hodnoty, pouze ověří, zda-li bude nějaká hodnota vrácena na základě jeho podmínek.
  • ANY/SOME – Poddotaz, který je napravo od tohoto výrazu generuje množinu hodnot vůči které je porovnáván sloupec na levé straně operátoru. Operátory SOME a ANY jsou synonyma. Hodnota sloupce nalevo je přes porovnání spojena množinou hodnot získaných z podtotazu vpravo od operátoru SOME/ANY, a je-li pravdivostní hodnota takovéhoto výrazu správná, tj. jeho hodnota splňuje podmínku vůči alespoň jedné z hodnot získané dotazem napravo, je řádek přiřazen do výstupní množiny hodnot složeného dotazu.
  • ALL – Pro poddotaz napravo od operátoru platí stejná pravidla jako u operátorů ANY/SOME. Hodnota sloupce nalevo je přes porovnání spojena množinou hodnot získaných z podtotazu vpravo od operátoru ALL, a je-li pravdivostní hodnota takovéhoto výrazu správná, tj. jeho hodnota splňuje podmínku vůči všem hodnotám získaným dotazem napravo, je řádek přiřazen do výstupní množiny hodnot složeného dotazu.
  • IN – Tento operátor je specifickým případem předchozích klauzulí. Dotaz na pravé straně vygeneruje množinu hodnot a je-li hodnota sloupce nalevo od tohoto operátoru rovna alespoň jedné hodnotě získané vnořeným dotazem, je řádek předán do množiny výsledů. Poddotazy, která vrací seznam hodnot, vůči nimž se porovnává nemusejí vracet hodnoty z jediného sloupce, ale mohou vybírat třeba z hodnot více sloupců, poté musíte jen správně sestavit seznam hodnot před klauzulí IN {… (col1, col2) IN (SELECT col1, col2 FROM …}

K operátorům existuje i modifikátor NOT, který neguje jejich význam. Jak bylo napsáno před výčtem operátorů, příklady napoví více. Poslední příklad vymaže zakázky deaktivovaných dealerů, kdy se jako parametr použije podvýběr, podobně lze podvýběr použít jako parametr pro příkaz UPDATE.

-- vyberu prodejce, ktery uskutecnil alespon jeden obchod
SELECT t1.name, t1.dealers_id FROM dealers AS t1 WHERE EXISTS (
  SELECT dealers_id FROM orders AS t2 WHERE t1.dealers_id = t2.dealers_id);
-- vyber zakazek od deaktivovanych prodejcu
SELECT t1.orders_id, t1.total_price FROM orders AS t1 WHERE t1.dealers_id IN
  (SELECT t2.dealers_id FROM dealers AS t2 WHERE t2.active = 'False'::Boolean);
-- vybereme vsechny zakazky, ktere maji vyssi celkovou cenu,
-- nezli zakazky od Jiřího Golána
SELECT t1.orders_id, t1.total_price FROM orders AS t1 WHERE t1.total_price > ALL 
  (SELECT t2.total_price FROM orders AS t2 LEFT JOIN dealers AS t3 ON 
  t2.dealers_id=t3.dealers_id WHERE t3.name='Jiří Golán');
-- vymaz zakazek deaktivovanych dealeru
DELETE FROM orders WHERE dealers_id IN
  (SELECT t2.dealers_id FROM dealers AS t2 WHERE t2.active = 'False'::Boolean);

Podvýběry, jako zdroje dat

Nejkratší částí tohoto dílu bude ukázání si použití podvýběrů, jakožto zdroje pro naplnění jiné tabulky (třeba sumarizace). V SQL existuje příkaz SELECT … INTO jmeno … FROM zdrojove_tabulky …, který je v podstatě sekvencí pro vytvoření tabulky pojmenované jmeno a její naplnění ze zdrojových tabulek, případně omezené podmínkami.

SELECT t1.name, SUM(t2.total_price) AS price INTO sumarization FROM
  dealers AS t1 LEFT JOIN orders AS t2 ON t1.dealers_id=t2.dealers_id WHERE
  active = 'True'::Boolean GROUP BY t1.name;
-- prepsat lze podle SQL2003
CREATE TABLE sumarization AS SELECT t1.name, SUM(t2.total_price) FROM
  dealers AS t1 LEFT JOIN orders AS t2 ON t1.dealers_id=t2.dealers_id WHERE
  active = 'True'::Boolean GROUP BY t1.name;

Závěr

Poddotazy jsou velice mocným nástrojem, který vám může usnadnit život. Jsou velice výkonné, byť řadu ze zmíněných příkladů v tomto díle lze přepsat na prosté JOINování tabulek.


1používám určité zjednodušení, proměnnou avg_price byste obvykle předali do programu a z něj pak odeslali nový dotaz, kde by tato hodnota již byla vložena.

Verze pro tisk

pridej.cz

 

DISKUZE

Umi dnes jiz i poddotazy? Ty snad umi uz hodne davno, ne? 30.6.2006 09:11 Petr Aubrecht
  L Re: Umi dnes jiz i poddotazy? Ty snad umi uz hodne davno, ne? 30.6.2006 11:04 MaReK Olšavský




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 ...

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