ARCHIV |
|||||
Software (10844)
Distribuce (131)
Skripty (697)
Menu
Diskuze
Informace
|
PostgreSQL (22) - PoddotazyPostgreSQL 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. PodvýběryMinule 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.
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 datNejkratší čá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 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ěrPoddotazy 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.
Související články
Předchozí Celou kategorii (seriál) Další
PostgreSQL (1) - Historie a pohledy jinam
PostgreSQL (2) - Proč PgSQL, data a relace PostgreSQL (3) - Instalace, základní administrace PostgreSQL (4) - Datové typy, vytvoření tabulek PostgreSQL (5) - Další datové typy a práce s časem i binarními řetězci PostgreSQL (6) - Uložení, aktualizace a mazání dat. PostgreSQL (7) - Výběr dat z databáze PostgreSQL (8) - SELECT II. PostgreSQL (9) – SELECT III PostgreSQL (10) - SELECT IV PostgreSQL (11) - Výběr pomocí vzorků PostgreSQL 12 - urychlení výběrů PostgreSQL (13) - Na co se zapomnělo PostgreSQL (14) - omezení dat (Constraints) PostgreSQL (15) - Transakce PostgreSQL (16) - Zamykání PostgreSQL (17) - Datový typ pole PostgreSQL (18) - Datový typ pole II PostgreSQL (19) - Vlastní datové typy PostgreSQL (20) - Vlastní datové typy II PostgreSQL (21) - Spojování dotazů PostgreSQL (23) - Optimalizujeme rychlost PostgreSQL (24) - Views (Pohledy) PostgreSQL (25) - Administrace skupin a uživatelů PostgreSQL (26) - Rozšiřujeme funkčnost Předchozí Celou kategorii (seriál) Další
|
Vyhledávání software
Vyhledávání článků
28.11.2018 23:56 /František Kučera 12.11.2018 21:28 /Redakce Linuxsoft.cz 6.11.2018 2:04 /František Kučera 4.10.2018 21:30 /Ondřej Čečák 18.9.2018 23:30 /František Kučera 9.9.2018 14:15 /Redakce Linuxsoft.cz 12.8.2018 16:58 /František Kučera 16.7.2018 1:05 /František Kučera
Poslední diskuze
31.7.2023 14:13 /
Linda Graham 30.11.2022 9:32 /
Kyle McDermott 13.12.2018 10:57 /
Jan Mareš 2.12.2018 23:56 /
František Kučera 5.10.2018 17:12 /
Jakub Kuljovsky | |||
ISSN 1801-3805 | Provozovatel: Pavel Kysilka, IČ: 72868490 (2003-2024) | mail at linuxsoft dot cz | Design: www.megadesign.cz | Textová verze |