7.8. WITH lekérdezések (közös táblázatkifejezések)

7.8.1. SELECT in WITH

A SELECT in WITH alapvető értéke 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 a termékenkénti értékesítési összegeket csak a legmagasabb értékesítési régiókban jeleníti meg. A WITH záradék két kiegészítő utasítást definiál regional_sales és top_regions néven, ahol a regional_sales kimenete a top_regions-ban, a top_regions kimenete pedig az elsődleges SELECT lekérdezésben kerül felhasználásra. Ezt a példát meg lehetett volna írni WITH nélkül is, de akkor két szintnyi egymásba ágyazott sub-SELECT-re lett volna szükség. Így egy kicsit könnyebb követni.

Az opcionális RECURSIVE módosító a WITH-t puszta szintaktikai kényelmi funkcióból olyan funkcióvá változtatja, amely olyan dolgokat valósít meg, amelyek egyébként a standard SQL-ben nem lehetségesek. A RECURSIVE használatával a WITH lekérdezés hivatkozhat a saját kimenetére. Egy nagyon egyszerű példa ez a lekérdezés az 1-től 100-ig terjedő egész számok összegzésére:

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 egy nem rekurzív kifejezés, majd UNION (vagy UNION ALL), majd egy 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 végrehajtása a következőképpen történik:

A fenti példában a munkatábla minden lépésben csak egyetlen sort tartalmaz, és az egymást követő lépésekben 1-től 100-ig veszi fel az értékeket. A 100. lépésben a WHERE záradék miatt nincs kimenet, így a lekérdezés befejeződik.

A rekurzív lekérdezéseket általában hierarchikus vagy fa struktúrájú adatok kezelésére használják. Hasznos példa ez a lekérdezés, amely egy termék összes közvetlen és közvetett alrészletét keresi, ha csak egy táblázatot adunk meg, amely a közvetlen zárványokat mutatja:

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

A rekurzív lekérdezésekkel való munka során fontos, hogy a lekérdezés rekurzív része végül nem ad vissza tuplikat, különben a lekérdezés végtelen ciklusba kerül. Néha az UNION ALL helyett az UNION használatával ezt el lehet érni azáltal, hogy a korábbi kimeneti sorokat duplikáló sorokat elvetjük. Gyakran azonban egy ciklus nem tartalmaz teljesen duplikált kimeneti sorokat: szükség lehet arra, hogy csak egy vagy néhány mezőt ellenőrizzünk, hogy ugyanazt a pontot már korábban is elértük-e. Az ilyen helyzetek kezelésére a szabványos módszer 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 egy táblázatgráfban keres egy kapcsolati mezőt használva:

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 ciklusos lesz, ha a kapcsolati kapcsolatok ciklusokat tartalmaznak. Mivel “mélységi” kimenetet kérünk, az UNION ALL-nak UNION-ra való módosítása nem szünteti meg a ciklusolást. Ehelyett fel kell ismernünk, hogy a kapcsolatok egy adott útvonalát követve újra eljutottunk-e ugyanahhoz a sorhoz. A ciklusra hajlamos lekérdezéshez két oszlopot adunk hozzá path és cycle:

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 reprezentálja az adott sor eléréséhez megtett “utat”.

Az általános esetben, amikor egynél több mezőt kell ellenőrizni a ciklus felismeréséhez, használjunk sortömböt. Például, ha az f1 és f2 mezőket kellene összehasonlítanunk:

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;

Egy hasznos trükk a lekérdezések teszteléséhez, amikor nem vagyunk biztosak abban, hogy esetleg hurokba kerülhetnek, ha LIMIT-et helyezünk a szülő lekérdezésbe. Ez a lekérdezés például a LIMIT nélkül örökké hurokban maradna:

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 ki egy WITH lekérdezésből, amennyit a szülő lekérdezés ténylegesen lekérdez. Ennek a trükknek a használata termelésben nem ajánlott, mert más rendszerek másképp működhetnek. Emellett általában akkor sem működik, ha a külső lekérdezéssel rendezi a rekurzív lekérdezés eredményeit, vagy összekapcsolja azokat valamilyen más táblával, mert ilyen esetekben a külső lekérdezés általában úgyis megpróbálja lekérni a WITH lekérdezés összes kimenetét.

A WITH lekérdezések hasznos tulajdonsága, hogy a szülő lekérdezés végrehajtásakor csak egyszer kerülnek kiértékelésre, 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ásokat a felesleges munka elkerülése érdekében egy WITH lekérdezésen belül lehet elhelyezni. Egy másik lehetséges alkalmazás a mellékhatásokkal rendelkező függvények nem kívánt többszörös kiértékelésének megakadályozása. Az érem másik oldala azonban az, hogy az optimalizáló kevésbé képes a szülő lekérdezésből származó korlátozásokat egy WITH lekérdezésbe tolni, mint egy közönséges al-lekérdezésbe. A WITH lekérdezés általában úgy kerül kiértékelésre, ahogyan meg van írva, a szülő lekérdezés által esetleg utólag elvetett sorok elnyomása nélkül. (De, mint fentebb említettük, a kiértékelés idő előtt leállhat, ha a lekérdezésre való hivatkozás(ok) csak korlátozott számú sort igényelnek.)

A fenti példákban a WITH csak SELECT-tel együtt használható, de ugyanúgy csatolható INSERT-hez, UPDATE-hoz vagy DELETE-hez is. Minden esetben gyakorlatilag ideiglenes táblázat(ok)at biztosít, amelyekre a főparancsban hivatkozhatunk.