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

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

Komerční sdělení: Pořádáme Kurzy PgSQL

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ů

13.9.2017 8:00 /František Kučera
Máš rád svobodný software a hardware nebo se o nich chceš něco dozvědět? Zajímá tě DIY, CNC, SDR nebo morseovka? Přijď na sraz spolku OpenAlt – tentokrát netradičně v pondělí: 18. září od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5).
Přidat komentář

3.9.2017 20:45 /Redakce Linuxsoft.cz
PR: Dne 21. září 2017 proběhne v Praze konference "Mobilní řešení pro business". Hlavní tématy konference budou: nejnovější trendy v oblasti mobilních řešení pro firmy, efektivní využití mobilních zařízení, bezpečnostní rizika a řešení pro jejich omezení, správa mobilních zařízení ve firmách a další.
Přidat komentář

15.5.2017 23:50 /František Kučera
Máš rád svobodný software a hardware nebo se o nich chceš něco dozvědět? Zajímá tě DIY, CNC, SDR nebo morseovka? Přijď na sraz spolku OpenAlt, který se bude konat ve čtvrtek 18. května od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5).
Přidat komentář

12.5.2017 16:42 /Honza Javorek
PyCon CZ, česká konference o programovacím jazyce Python, se po dvou úspěšných ročnících v Brně bude letos konat v Praze, a to 8. až 10. června. Na konferenci letos zavítá např. i Armin Ronacher, známý především jako autor frameworku Flask, šablon Jinja2/Twig, a dalších projektů. Těšit se můžete na přednášky o datové analytice, tvorbě webu, testování, tvorbě API, učení a mentorování programování, přednášky o rozvoji komunity, o použití Pythonu ve vědě nebo k ovládání nejrůznějších zařízení (MicroPython). Na vlastní prsty si můžete na workshopech vyzkoušet postavit Pythonem ovládaného robota, naučit se učit šestileté děti programovat, efektivně testovat nebo si v Pythonu pohrát s kartografickým materiálem. Kupujte lístky, dokud jsou.
Přidat komentář

2.5.2017 9:20 /Eva Rázgová
Putovní konference československé Drupal komunity "DrupalCamp Československo" se tentokrát koná 27. 5.2017 na VUT FIT v Brně. Můžete načerpat a vyměnit si zkušenosti z oblasti Drupalu 7 a 8, UX, SEO, managementu týmového vývoje, využití Dockeru pro Drupal a dalších. Vítáni jsou nováčci i experti. Akci pořádají Slovenská Drupal Asociácia a česká Asociace pro Drupal. Registrace na webu .
Přidat komentář

1.5.2017 20:31 /Pavel `Goldenfish' Kysilka
PR: 25.5.2017 proběhne v Praze konference na téma Firemní informační systémy. Hlavními tématy jsou: Informační systémy s vlastní inteligencí, efektivní práce s dokumenty, mobilní přístup k datům nebo využívání cloudu.
Přidat komentář

15.4.2017 15:20 /František Kučera
Máš rád svobodný software a hardware nebo se o nich chceš něco dozvědět? Zajímá tě IoT a radiokomunikace? Přijď na sraz spolku OpenAlt, který se bude konat ve středu 19. dubna od 18:30 v Šenkovně (Sokolská 60, Praha 2).
Přidat komentář

5.3.2017 19:12 /Redakce Linuxsoft.cz
PR: 23. března proběhne v Praze konferenci na téma Cloud computing v praxi. Hlavními tématy jsou: Nejžhavější trendy v oblasti cloudu a cloudových řešení, Moderní cloudové služby, Infrastruktura současných cloudů, Efektivní využití cloudu, Nástrahy cloudových řešení a jak se jim vyhnout.
Přidat komentář

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

> Poslední diskuze

18.9.2017 14:37 / Rojas
high security vault

15.9.2017 7:33 / Wilson
new zealand childcare jobs

31.8.2017 12:11 / Jaromir Obr
Re: ukůládání dat ze souboru

30.7.2017 11:12 / Jaromir Obr
Národní znaky

27.7.2017 12:24 / Jaromir Obr
Cteni/zapis

Více ...

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