PostgreSQL: Dokumentáció: 13: 7.8. Lekérdezésekkel (közös Táblázatkifejezések)

a SELECT alapértéke WITH – ben a bonyolult lekérdezések egyszerűbb részekre bontása. Egy példa:

WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales))SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_salesFROM ordersWHERE region IN (SELECT region FROM top_regions)GROUP BY region, product;

amely csak a legjobb értékesítési régiókban jeleníti meg a termékenkénti értékesítési összegeket. A WITH záradék két regional_sales és top_regions nevű segédutasítást határoz meg, ahol a regional_sales kimenetét a top_regions – ben, a top_regions kimenetét pedig az elsődleges SELECT lekérdezésben használják. Ezt a példát WITH nélkül is meg lehetett volna írni, de két szintű beágyazott al-SELECTs-re lett volna szükségünk.

az opcionális RECURSIVE módosító a WITH egyszerű szintaktikai kényelemből olyan funkcióvá változik, amely a szokásos SQL-ben egyébként nem lehetséges dolgokat valósít meg. A RECURSIVE használatával a WITH lekérdezés hivatkozhat a saját kimenetére. Nagyon egyszerű példa erre a lekérdezés az egész számok összegzésére 1-től 100-ig:

WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100)SELECT sum(n) FROM t;

a rekurzív WITH lekérdezés általános formája mindig nem rekurzív kifejezés, majd UNION (vagy UNION ALL), majd rekurzív kifejezés, ahol csak a rekurzív kifejezés tartalmazhat hivatkozást a lekérdezés saját kimenetére. Egy ilyen lekérdezés a következőképpen történik:

a fenti példában a munkaasztalnak csak egy sora van minden lépésben, és az 1-től 100-ig terjedő értékeket egymást követő lépésekben veszi fel. A 100. lépésben a WHERE záradék miatt nincs kimenet, így a lekérdezés megszűnik.

a rekurzív lekérdezéseket általában hierarchikus vagy fa-strukturált adatok kezelésére használják. Hasznos példa erre a lekérdezés a termék összes közvetlen és közvetett alrészének megkeresésére, csak egy táblázatot adva, amely azonnali zárványokat mutat:

WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part)SELECT sub_part, SUM(quantity) as total_quantityFROM included_partsGROUP BY sub_part

ha rekurzív lekérdezésekkel dolgozik, fontos megbizonyosodni arról, hogy a lekérdezés rekurzív része végül nem ad vissza sorokat, különben a lekérdezés végtelenségig hurkolódik. Néha a UNION használata a UNION ALL helyett ezt úgy érheti el, hogy eldobja azokat a sorokat, amelyek megismétlik az előző kimeneti sorokat. A ciklus azonban gyakran nem tartalmaz teljesen ismétlődő kimeneti sorokat: szükség lehet csak egy vagy néhány mező ellenőrzésére, hogy ugyanazt a pontot elérték-e már korábban. Az ilyen helyzetek kezelésének szokásos módszere a már meglátogatott értékek tömbjének kiszámítása. Vegyük például a következő lekérdezést, amely graph táblában keres link mezővel:

WITH RECURSIVE search_graph(id, link, data, depth) AS ( SELECT g.id, g.link, g.data, 1 FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1 FROM graph g, search_graph sg WHERE g.id = sg.link)SELECT * FROM search_graph;

ez a lekérdezés hurok, ha a link kapcsolatok ciklusokat tartalmaznak. Mivel “mélység” kimenetre van szükségünk, csak a UNION ALL UNION – re változtatása nem szüntetné meg a hurkolást. Ehelyett fel kell ismernünk, hogy újra elértük-e ugyanazt a sort, miközben egy adott hivatkozási utat követtünk. Két oszlopot adunk hozzá path és cycle a hurokra hajlamos lekérdezéshez:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY, false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || g.id, g.id = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle)SELECT * FROM search_graph;

a ciklusok megakadályozásán kívül a tömb értéke gyakran önmagában is hasznos, mivel az adott sor eléréséhez szükséges “utat” képviseli.

általános esetben, ha egynél több mezőt kell ellenőrizni a ciklus felismeréséhez, használjon sortömböt. Például, ha össze kell hasonlítanunk a f1 mezőket és f2:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY, false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || ROW(g.f1, g.f2), ROW(g.f1, g.f2) = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle)SELECT * FROM search_graph;

hasznos trükk a lekérdezések teszteléséhez, ha nem biztos abban, hogy ciklusosak-e, ha LIMIT – ot helyez el a szülő lekérdezésben. Például ez a lekérdezés örökre hurkolna a LIMIT:

WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t)SELECT n FROM t LIMIT 100;

ez azért működik, mert a PostgreSQL implementációja csak annyi sort értékel a WITH lekérdezésből, amennyit a szülő lekérdezés ténylegesen lekér. Ennek a trükknek a használata a gyártásban nem ajánlott, mert más rendszerek másképp működhetnek. Ezenkívül általában nem fog működni, ha a külső lekérdezést rendezi a rekurzív lekérdezés eredményeit, vagy összekapcsolja őket egy másik táblával, mert ilyen esetekben a külső lekérdezés általában megpróbálja lekérni a WITH lekérdezés összes kimenetét.

a WITH lekérdezések hasznos tulajdonsága, hogy általában a szülő lekérdezés végrehajtása során csak egyszer értékelik őket, még akkor is, ha a szülő lekérdezés vagy a testvér WITH lekérdezések többször hivatkoznak rájuk. Így a több helyen szükséges drága számítások WITH lekérdezésen belül helyezhetők el a redundáns munka elkerülése érdekében. Egy másik lehetséges alkalmazás a mellékhatások nemkívánatos többszörös értékelésének megakadályozása. Ennek az éremnek a másik oldala azonban az, hogy az optimalizáló nem képes a korlátozásokat a szülő lekérdezésből egy többszörösen hivatkozott WITH lekérdezésbe tolni, mivel ez hatással lehet a WITH lekérdezés kimenetének minden felhasználására, amikor csak egyet érinthet. A többszörösen hivatkozott WITH lekérdezés írott formában kerül kiértékelésre, anélkül, hogy elnyomná azokat a sorokat, amelyeket a szülő lekérdezés később elvethet. (De, mint fentebb említettük, az értékelés Korán leállhat, ha a lekérdezésre vonatkozó hivatkozás(ok) csak korlátozott számú sort igényelnek.)

Ha azonban egy WITH lekérdezés nem rekurzív és mellékhatásmentes (azaz SELECT nem tartalmaz illékony függvényeket), akkor a szülő lekérdezésbe hajtható, lehetővé téve a két lekérdezési szint közös optimalizálását. Alapértelmezés szerint ez akkor történik, ha a szülő lekérdezés csak egyszer hivatkozik a WITH lekérdezésre, de nem, ha egynél többször hivatkozik a WITH lekérdezésre. Felülbírálhatja ezt a döntést a MATERIALIZED megadásával, hogy a WITH lekérdezés külön számítását kényszerítse, vagy a NOT MATERIALIZED megadásával kényszerítheti a szülő lekérdezés egyesítésére. Ez utóbbi választás megkockáztatja a WITH lekérdezés duplikált kiszámítását, de akkor is nettó megtakarítást eredményezhet, ha a WITH lekérdezés minden egyes használatához a WITH lekérdezés teljes kimenetének csak egy kis részére van szükség.

ezeknek a szabályoknak egy egyszerű példája a

WITH w AS ( SELECT * FROM big_table)SELECT * FROM w WHERE key = 123;

ez a WITH lekérdezés hajtogatásra kerül, ugyanazt a végrehajtási tervet készítve, mint a

SELECT * FROM big_table WHERE key = 123;

különösen, ha van index a key – en, akkor valószínűleg csak a key = 123 sorokat fogja letölteni. Másrészt a

WITH w AS ( SELECT * FROM big_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.refWHERE w2.key = 123;

— ben a WITH lekérdezés megvalósul, létrehozva a big_table ideiglenes példányát, amelyet aztán összekapcsolnak önmagával-bármilyen index előnye nélkül. Ez a lekérdezés sokkal hatékonyabban lesz végrehajtva, ha

WITH w AS NOT MATERIALIZED ( SELECT * FROM big_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.refWHERE w2.key = 123;

néven íródik, így a szülő lekérdezés korlátozásai közvetlenül alkalmazhatók a big_tablebeolvasására.

egy példa, ahol a NOT MATERIALIZED nemkívánatos lehet

WITH w AS ( SELECT key, very_expensive_function(val) as f FROM some_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;

itt a WITH lekérdezés materializációja biztosítja, hogy a very_expensive_function csak egyszer kerül kiértékelésre táblázatsoronként, nem kétszer.

a fenti példák csak azt mutatják, hogy a WITH – et SELECT – vel együtt használják, de ugyanúgy csatolható a INSERT, UPDATEvagy DELETE – hez. Minden esetben hatékonyan nyújt ideiglenes tábla(ok), hogy lehet hivatkozni a fő parancs.