Rekurzív lekérdezések írása PostgreSQL-ben

Üdvözöljük a PostgreSQL izgalmas világában, ahol a rekurzív lekérdezések segítségével olyan adatszerkezeteket is hatékonyan kezelhetünk, melyek elsőre bonyolultnak tűnhetnek! Gondolt már arra, hogyan lehetne lekérdezni egy teljes szervezeti hierarchiát, egy termék összes alkatrészét, vagy egy közösségi hálózatban a „barátom barátjának a barátját” anélkül, hogy végtelenül ismétlődő al lekérdezéseket írna? A válasz a WITH RECURSIVE, egy rendkívül erőteljes funkció, ami gyökeresen megváltoztathatja az adatelemzésről alkotott képét.

Ebben a cikkben alaposan elmerülünk a PostgreSQL rekurzív lekérdezéseinek rejtelmeiben. Megnézzük, miért van rájuk szükség, hogyan működnek, és számos valós életbeli példán keresztül bemutatjuk gyakorlati alkalmazásukat. Emellett szó esik majd a teljesítményoptimalizálásról és a legjobb gyakorlatokról is, hogy ne csak hatékonyan, de a lehető leggyorsabban futtathassa lekérdezéseit.

A Rekurzió Megértése az SQL-ben

A rekurzió lényege, hogy egy probléma megoldása során a függvény vagy eljárás önmagát hívja meg, egészen addig, amíg egy alapfeltétel nem teljesül. Az SQL-ben, különösen a PostgreSQL-ben, ezt a fogalmat a Common Table Expression (CTE) kiterjesztésén, a WITH RECURSIVE-en keresztül valósítjuk meg.

A rekurzív CTE lehetővé teszi, hogy egy lekérdezés eredményhalmaza önmagára hivatkozzon, ezzel iteratív módon építkezve a végső eredményre. Ez kiválóan alkalmas hierarchikus adatok (pl. szervezeti fák, mappastruktúrák) és gráfszerű adatok (pl. útvonalak, kapcsolatok) kezelésére, ahol az elemek közötti kapcsolatok mélysége előre nem ismert.

Hogyan Épül Fel egy Rekurzív CTE?

Minden rekurzív CTE két fő részből áll:

Az Ankorelem (Anchor Member): Ez a lekérdezés kezdeti, nem rekurzív része. Meghatározza a rekurzió kiindulópontját, az alapfeltételt. Az ankorelem eredményhalmaza képezi az első „generációt” vagy „szintet”, amire a rekurzív rész építkezni fog. Fontos, hogy ez a rész ne hivatkozzon a CTE nevére.
A Rekurzív Elem (Recursive Member): Ez az a rész, amelyik önmagára hivatkozik (pontosabban a CTE nevére), és az ankorelem vagy az előző rekurzív lépés eredményhalmazát használja fel a következő iterációhoz. A két részt általában UNION ALL vagy UNION operátorral kapcsoljuk össze.

A rekurzív folyamat addig folytatódik, amíg a rekurzív elem nem ad vissza több sort. Ekkor a rekurzió leáll, és a CTE az összes generált sort visszaadja. Lássuk a szintaxist:


WITH RECURSIVE cte_nev (oszlop1, oszlop2, ...) AS (
    -- Ankorelem (nem rekurzív rész)
    SELECT alap_oszlop1, alap_oszlop2, ...
    FROM kezdeti_tablazat
    WHERE kezdeti_feltetel

    UNION ALL -- vagy UNION (ha nincs szükség duplikált sorokra)

    -- Rekurzív elem
    SELECT rekurziv_oszlop1, rekurziv_oszlop2, ...
    FROM valamilyen_tablazat
    JOIN cte_nev ON valamilyen_feltetel -- Itt hivatkozik önmagára
    WHERE rekurziv_feltetel
)
SELECT * FROM cte_nev;

Fontos megjegyezni, hogy az ankorelem és a rekurzív elem oszlopainak száma és típusa meg kell egyeznie!

Gyakorlati Példa: Egyszerű Számláló

Kezdjük egy egyszerű, de szemléletes példával, hogy megértsük a rekurzió alapjait: generáljunk egy számsort 1-től 10-ig.


WITH RECURSIVE szamlalo (szam) AS (
    -- Ankorelem: A rekurzió kiindulópontja
    SELECT 1 AS szam

    UNION ALL

    -- Rekurzív elem: Hozzáad 1-et az előző számhoz, ha az kisebb mint 10
    SELECT szam + 1
    FROM szamlalo
    WHERE szam < 10
)
SELECT szam FROM szamlalo;

Magyarázat:

Az ankorelem SELECT 1 AS szam visszaadja az 1-et, ami a rekurzió első „szintje”.
A rekurzív elem SELECT szam + 1 FROM szamlalo WHERE szam < 10. Az első iterációban a szamlalo tartalmazza az 1-et. A feltétel (1 < 10) igaz, így visszaadja a 2-t.
Ez a 2 bekerül a szamlalo következő iterációjába. A feltétel (2 < 10) igaz, visszaadja a 3-at.
Ez addig folytatódik, amíg a szamlalo tartalmazza a 9-et. Ekkor a rekurzív elem 10-et ad vissza.
Amikor a szamlalo tartalmazza a 10-et, a feltétel (10 < 10) hamis lesz, így a rekurzív elem nem ad vissza több sort. A rekurzió leáll.

A végeredmény egy számsor lesz 1-től 10-ig.

Valós Életbeli Alkalmazások

Most, hogy megértettük az alapokat, térjünk rá bonyolultabb, valós példákra, ahol a rekurzív lekérdezések ereje igazán megmutatkozik.

1. Szervezeti Hierarchia Lekérdezése

Képzeljen el egy céget, ahol minden alkalmazottnak van egy vezetője (kivéve a vezérigazgatót). Szeretnénk lekérdezni egy adott alkalmazott összes beosztottját, függetlenül attól, hogy hány szinttel alatta helyezkednek el.

Táblázat:


CREATE TABLE alkalmazottak (
    id SERIAL PRIMARY KEY,
    nev VARCHAR(100) NOT NULL,
    vezeto_id INTEGER REFERENCES alkalmazottak(id)
);

INSERT INTO alkalmazottak (id, nev, vezeto_id) VALUES
(1, 'Alice', NULL), -- Vezérigazgató
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2),
(6, 'Frank', 3),
(7, 'Grace', 4);

Lekérdezés: Az összes beosztott megkeresése ‘Alice’ alatt:


WITH RECURSIVE beosztottak_fa AS (
    -- Ankorelem: Alice maga
    SELECT id, nev, vezeto_id, 0 AS szint
    FROM alkalmazottak
    WHERE nev = 'Alice'

    UNION ALL

    -- Rekurzív elem: Azok az alkalmazottak, akiknek a vezetője az előző szinten van
    SELECT a.id, a.nev, a.vezeto_id, bf.szint + 1
    FROM alkalmazottak a
    JOIN beosztottak_fa bf ON a.vezeto_id = bf.id
)
SELECT id, nev, szint FROM beosztottak_fa;

Ez a lekérdezés visszaadja ‘Alice’-t és az összes közvetlen és közvetett beosztottját, a hierarchiában elfoglalt szintjükkel együtt. Ezt a mintát használva könnyen lekérdezhetjük ‘Bob’ vagy ‘Charlie’ összes beosztottját is.

2. Alkatrészjegyzék (Bill of Materials – BOM) Készítése

Egy gyártó cég termékei gyakran más termékekből állnak. Szeretnénk egy listát kapni egy adott termék összes részegységéről és az azokhoz tartozó mennyiségekről, figyelembe véve a beágyazott komponenseket is.

Táblázat:


CREATE TABLE termek_alkatreszek (
    gyarto_termek_id INTEGER NOT NULL,
    alkatresz_id INTEGER NOT NULL,
    mennyiseg INTEGER NOT NULL,
    PRIMARY KEY (gyarto_termek_id, alkatresz_id)
);

INSERT INTO termek_alkatreszek (gyarto_termek_id, alkatresz_id, mennyiseg) VALUES
(100, 10, 2), -- Termék 100 = 2db alkatrész 10
(100, 20, 1), -- Termék 100 = 1db alkatrész 20
(10, 1, 5),   -- Alkatrész 10 = 5db alkatrész 1
(10, 2, 3),   -- Alkatrész 10 = 3db alkatrész 2
(20, 3, 1),   -- Alkatrész 20 = 1db alkatrész 3
(3, 4, 2);    -- Alkatrész 3 = 2db alkatrész 4

Lekérdezés: A ‘Termék 100’ teljes alkatrészlistája:


WITH RECURSIVE bom AS (
    -- Ankorelem: A kezdeti termék és annak közvetlen alkatrészei
    SELECT
        ta.gyarto_termek_id AS gyarto_id,
        ta.alkatresz_id,
        ta.mennyiseg,
        1 AS szint,
        CAST(ARRAY[ta.alkatresz_id] AS INTEGER[]) AS utvonal -- Ciklus detektáláshoz
    FROM termek_alkatreszek ta
    WHERE ta.gyarto_termek_id = 100

    UNION ALL

    -- Rekurzív elem: További alkatrészek, amik az előző alkatrészekből állnak
    SELECT
        b.gyarto_id,
        ta.alkatresz_id,
        ta.mennyiseg * b.mennyiseg AS teljes_mennyiseg, -- Összegzi a mennyiségeket
        b.szint + 1,
        b.utvonal || ta.alkatresz_id
    FROM termek_alkatreszek ta
    JOIN bom b ON ta.gyarto_termek_id = b.alkatresz_id
    WHERE NOT (ta.alkatresz_id = ANY(b.utvonal)) -- Ciklus ellenőrzés
)
SELECT gyarto_id, alkatresz_id, SUM(mennyiseg) AS osszes_mennyiseg
FROM bom
GROUP BY gyarto_id, alkatresz_id
ORDER BY alkatresz_id;

Ez a lekérdezés kiszámítja az egyes alkatrészek összes szükséges mennyiségét a teljes termék előállításához. Az utvonal oszlop segít a ciklusok észlelésében (pl. ha az alkatrész önmagának alkotóeleme lenne), megakadályozva a végtelen rekurziót.

3. Gráf Bejárás és Útvonalkeresés

A rekurzív lekérdezések kiválóan alkalmasak gráfszerű adatszerkezetek, például közlekedési hálózatok vagy szociális gráfok bejárására. Keressünk útvonalakat A pontból B pontba!

Táblázat:


CREATE TABLE utvonalak (
    indulas VARCHAR(50) NOT NULL,
    cel VARCHAR(50) NOT NULL,
    tavolsag INTEGER NOT NULL,
    PRIMARY KEY (indulas, cel)
);

INSERT INTO utvonalak (indulas, cel, tavolsag) VALUES
('A', 'B', 10),
('A', 'C', 15),
('B', 'D', 20),
('C', 'E', 25),
('D', 'F', 30),
('E', 'F', 5);

Lekérdezés: Az összes lehetséges útvonal ‘A’-ból ‘F’-be:


WITH RECURSIVE jarat AS (
    -- Ankorelem: A kiindulópont
    SELECT
        indulas,
        cel,
        tavolsag,
        1 AS ures_hossz,
        CAST(ARRAY[indulas, cel] AS VARCHAR[]) AS utvonal_elertek,
        CAST(ARRAY[indulas] AS VARCHAR[]) AS bejart_csomopontok
    FROM utvonalak
    WHERE indulas = 'A'

    UNION ALL

    -- Rekurzív elem: Hozzáadja a következő szegmenst az útvonalhoz
    SELECT
        j.indulas,
        u.cel,
        j.tavolsag + u.tavolsag,
        j.ures_hossz + 1,
        j.utvonal_elertek || u.cel,
        j.bejart_csomopontok || u.cel
    FROM utvonalak u
    JOIN jarat j ON u.indulas = j.cel
    WHERE NOT (u.cel = ANY(j.bejart_csomopontok)) -- Elkerüli a ciklusokat
)
SELECT indulas, cel, tavolsag, utvonal_elertek AS utvonal
FROM jarat
WHERE cel = 'F'
ORDER BY tavolsag;

Ez a lekérdezés megtalálja az összes útvonalat ‘A’-ból ‘F’-be, megjelenítve az útvonalat és a teljes távolságot. A bejart_csomopontok tömb segít elkerülni, hogy ugyanazon csomópontot többször is bejárjuk egy útvonalon belül, ami végtelen ciklusokhoz vezethetne.

Teljesítményoptimalizálás és Legjobb Gyakorlatok

A rekurzív lekérdezések rendkívül erőteljesek, de hatékony használatukhoz figyelembe kell venni néhány teljesítményre vonatkozó szempontot:

Indexek: Győződjön meg róla, hogy a JOIN feltételben használt oszlopokon (különösen a rekurzív részben) vannak indexek. Ez drámaian gyorsíthatja a lekérdezést. Például a vezeto_id vagy alkatresz_id oszlopokon lévő indexek elengedhetetlenek a fenti példákban.
Ciklusok Kezelése: Ahogy a BOM és a gráf példákban láttuk, a ciklusok (amikor egy elem önmagára hivatkozik, vagy egy korábban meglátogatott elemre) végtelen rekurzióhoz vezethetnek. Mindig implementáljon valamilyen mechanizmust a ciklusok detektálására és elkerülésére (pl. egy tömbben tárolja a már bejárt elemeket, és ellenőrizze, hogy az aktuális elem szerepel-e már benne a NOT (elem = ANY(bejart_utvonal)) feltétellel).
Korlátozott Mélység: Ha tudja, hogy a hierarchia vagy a gráf mélysége nem haladja meg egy bizonyos szintet, használhat egy számlálót a rekurzív részben (mint a szint oszlop az alkalmazotti példában), és korlátozhatja a rekurziót egy WHERE szint < max_szint feltétellel. Ez segíthet a teljesítmény szabályozásában és a memóriahasználat csökkentésében.
UNION ALL vs. UNION: A UNION ALL gyorsabb, mert nem végzi el a duplikátumok eltávolítását. Csak akkor használja a UNION-t, ha feltétlenül szüksége van az egyedi sorokra, és nem tudja más módon kezelni azokat (pl. ciklusdetektálás, vagy GROUP BY a végső SELECT-ben).
EXPLAIN ANALYZE: Mindig használja az EXPLAIN ANALYZE parancsot a lekérdezések teszteléséhez és optimalizálásához. Ez segít megérteni, hogy hol van a szűk keresztmetszet, és milyen indexek hiányoznak.
Mikor Ne Használjuk: Bár a rekurzív lekérdezések erősek, néha vannak jobb alternatívák. Például, ha a hierarchia viszonylag statikus, érdemes lehet előre kiszámítani és tárolni a „path enumeration” (útvonal számozás) vagy „materialized path” (materializált útvonal) szerinti hierarchikus adatokat, ahol egyetlen szöveges oszlopban tároljuk az elem teljes útvonalát (pl. /Alice/Bob/David). Ez rendkívül gyors lekérdezéseket tesz lehetővé, de adatfrissítéskor költségesebb lehet.

PostgreSQL Specifikus Tippek és Megfontolások

A PostgreSQL WITH RECURSIVE implementációja megbízható és szabványos SQL-99 kompatibilis. Néhány extra tipp:

Memória Kezelés: A rekurzív lekérdezések sok memóriát fogyaszthatnak, különösen nagy adathalmazok és mély hierarchiák esetén. A PostgreSQL a memóriát a work_mem konfigurációs paraméter alapján kezeli. Ha túl alacsony, a rendszer diskre írhat, ami lassítja a lekérdezést. Ha túl magas, feleslegesen foglalhat le memóriát.
A LIMIT Használata: A LIMIT záradékot elhelyezheti a külső SELECT részben, hogy korlátozza a végeredmény sorainak számát. Fontos azonban megérteni, hogy a LIMIT nem feltétlenül állítja le a rekurziót azonnal; a rekurzív CTE addig fog futni, amíg az összes lehetséges sort ki nem generálja, mielőtt a külső SELECT alkalmazná a LIMIT-et. Ha a rekurzióban korlátozott számú sorra van szüksége, a korlátot a rekurzív tag WHERE feltételében kell érvényesíteni, ahogyan a számláló példában is láttuk.
NULL Értékek: Ügyeljen a NULL értékekre a JOIN feltételekben, mivel a NULL = NULL kiértékelése hamis (ismeretlen) lesz. Ha a hierarchia tetején lévő elem NULL értékkel rendelkezik (mint a vezérigazgató vezeto_id-je), az ankorelemnek expliciten kell kezelnie ezt (pl. WHERE vezeto_id IS NULL).

Összegzés és Következtetés

A rekurzív lekérdezések a PostgreSQL-ben a WITH RECURSIVE használatával rendkívül hatékony eszközt biztosítanak a fejlesztők és adatelemzők számára, akik hierarchikus adatokkal, gráfokkal és összetett, önmagukra hivatkozó struktúrákkal dolgoznak.

Bár elsőre ijesztőnek tűnhet a koncepció, a mögötte rejlő logika (ankorelem + rekurzív elem + kilépési feltétel) megértése kulcsfontosságú. A gyakorlati példákon keresztül láthattuk, hogyan alkalmazható ez a technika szervezeti fák, alkatrészjegyzékek és útvonalkereső algoritmusok megvalósítására. Ne feledkezzen meg a teljesítményoptimalizálásról, az indexekről és a ciklusok helyes kezeléséről sem, hogy lekérdezései a lehető leggyorsabban és legstabilabban működjenek.

Ne habozzon kísérletezni és kipróbálni a WITH RECURSIVE-t saját adataihoz. Ahogy egyre jobban megismeri, rá fog jönni, hogy ez egy igazán nélkülözhetetlen eszköz a PostgreSQL eszköztárában, amely segít feltárni az adatokban rejlő mélyebb összefüggéseket.