Hogyan működnek a CTE-k és a rekurzív lekérdezések a PostgreSQL-ben?

Question

Accepted Answer

A **CTE** (Common Table Expression), a **`WITH`** kulcsszóval definiálva, egy elnevezett ideiglenes eredményhalmaz, amely olvashatóbbá teszi az összetett lekérdezéseket. A PostgreSQL teljes mértékben támogatja a CTE-ket, beleértve a **rekurzív** CTE-ket (`WITH RECURSIVE`) a hierarchikus/fa adatok bejárásához.

## Alapvető CTE-k — olvasható lekérdezésépítés

```sql
WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
order_counts AS (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
)
SELECT u.name, oc.cnt
FROM active_users u
JOIN order_counts oc ON u.id = oc.user_id;   -- compose named steps cleanly
```

A CTE-k egy összetett lekérdezést elnevezett, olvasható lépésekre bontanak (láncolt, mindegyik az előzőekre hivatkozik) — sokkal világosabb, mint a beágyazott allekérdezések.

## Rekurzív CTE-k — hierarchiák bejárása

```sql
-- find all subordinates under employee 1 (an org-chart hierarchy)
WITH RECURSIVE subordinates AS (
  -- BASE CASE: the starting point
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  -- RECURSIVE STEP: find employees whose manager is in the result so far
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;          -- the entire tree under employee 1
```

Egy **rekurzív CTE**-nek van egy alapeset (a kezdő sorok) és egy rekurzív lépés (a `UNION ALL` vissza a CTE-re önmagára csatlakozik), amely addig ismétlődik, amíg nincs új sor — tökéletes a **fák és hierarchiák** (szervezeti diagramok, kategóriafák, anyagjegyzékek, gráf útvonalak) kezeléséhez.

## Megjegyzés a CTE materializálásáról (PostgreSQL-specifikus)

```sql
-- Postgres 12+ may INLINE CTEs (optimize them) by default; force behavior if needed:
WITH x AS MATERIALIZED (...)      -- force materialization (compute once, store)
WITH x AS NOT MATERIALIZED (...)  -- force inlining (optimize into the main query)
```

A PostgreSQL 12 előtt a CTE-k mindig "optimalizálási korlát" voltak (materializáltak); most inline-olhatók a jobb teljesítmény érdekében — a `MATERIALIZED`/`NOT MATERIALIZED` ezt irányítja.

## Miért számít ez

A CTE-k és rekurzív lekérdezések értékes PostgreSQL-funkciók az **olvashatóság és hierarchikus adatok** kezeléséhez, így fontos megérteni őket ahhoz, hogy karbantartható összetett lekérdezéseket írjon és fa szerkezetű adatokat kezeljen.

A napi gyakorlatban az **olvashatóság** az előny: az összetett lekérdezések több allekérdezési szinttel nehezen karbantarthatók, míg a CTE-k lehetővé teszik, hogy azokat elnevezett, szekvenciális logikai lépésekre bontsuk (felülről lefelé olvasható), amely drámaian javítja az érthetőséget — így a CTE-k a karbantartható SQL írásának fontos eszköze.

A kiemelkedő lehetőség a **rekurzív CTE-k** (`WITH RECURSIVE`), amelyek lehetővé teszik a **hierarchikus és fa szerkezetű adatok** lekérdezését (szervezeti diagramok, kategóriafák, anyagjegyzékek, szálazott megjegyzések, gráf bejárás) — egy olyan lekérdezésclass, amely az SQL-ben egyébként nagyon nehéz, így a rekurzív CTE-k a hierarchikus bejárás standard megoldása.

A rekurzív CTE-szerkezet megértése (alapeset + rekurzív lépés, amely visszacsatlakozik önmagára) kulcsfontosságú ismeret ehhez a gyakori igényhez.

Egy hasznos PostgreSQL-specifikus részlet a **CTE-materializálás** viselkedése (PostgreSQL 12+ inline-olhatja a CTE-ket a jobb teljesítmény érdekében, a `MATERIALIZED`/`NOT MATERIALIZED` vezérli) — releváns az optimalizáláshoz.

Mivel az összetett lekérdezések gyakoriak (és az olvashatóság számít a karbantartáshoz), és mivel a hierarchikus adatok (amelyeket a rekurzív CTE-k jól kezelnek) gyakran fordulnak elő (szervezeti struktúrák, beágyazott kategóriák), a CTE-k és rekurzív lekérdezések megértése értékes, gyakorlatban releváns PostgreSQL-ismeret, amely javítja a lekérdezés karbantarthatóságát és lehetővé teszi a hierarchikus lekérdezéseket — egy gyakran alkalmazható lehetőség a tiszta, szofisztikált SQL-hez és egy általános téma, különösen a rekurzív forma az fa szerkezetű adatok lekérdezésének ismételt szükséglete miatt.