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

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ávky¹ 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.

¹použí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

DISKUZE Umi dnes jiz i poddotazy? Ty snad umi uz hodne davno, ne? 30.6.2006 09:11 Petr Aubrecht   Re: Umi dnes jiz i poddotazy? Ty snad umi uz hodne davno, ne? 30.6.2006 11:04 MaReK Olšavský Zobrazit označené Zobrazit vše Příspívat do diskuze mohou pouze registrovaní uživatelé.