Kaip CTEs ir rekursinės užklausos veikia PostgreSQL?

Question

Accepted Answer

**CTE** (Common Table Expression), apibrėžta naudojant **`WITH`**, yra pavadinta laikiną rezultatų aibė, kuri sudėtingas užklausas padaro lengvai suprantamas. PostgreSQL visiškai palaiko CTE, įskaitant **rekursyvias** CTE (`WITH RECURSIVE`) hierarchinių/medžio duomenų peržiūrai.

## Paprastos CTE — lengvai suprantama užklausų konstravimo

```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
```

CTE sudėtingą užklausą suskaldo į pavadintus, lengvai suprantamus žingsnius (grandinėje, kiekvienas nurodydamas ankstesnius) — daug aiškiau nei įdėtos subužklausos.

## Rekursinės CTE — hierarchijų peržiūra

```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
```

**Rekursinė CTE** turi bazinį atvejį (pradines eilutes) ir rekursinį žingsnį (`UNION ALL` susijungiant atgal į pačią CTE), kartojant, kol nėra naujų eilučių — puikiai tinka **medžiams ir hierarchijoms** (organizacijos diagramos, kategorijų medžiai, medžiagų sudėtis, grafiko keliai).

## Pastaba apie CTE materializavimą (PostgreSQL specifika)

```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)
```

Prieš PostgreSQL 12, CTE visada buvo "optimizavimo užtvaras" (materializuota); dabar jos gali būti įtrauktos geresniame našumui — `MATERIALIZED`/`NOT MATERIALIZED` tai kontroliuoja.

## Kodėl tai svarbu

CTE ir rekursinės užklausos yra vertingos PostgreSQL funkcijos **sudėtingų užklausų suprantamumui ir hierarchiniams duomenims**, todėl jų supratimas yra naudingas rašant palaikomas sudėtingas užklausas ir dirbant su medžio struktūra duomenimis.

Kasdienis privalumas yra **suprantamumas**: sudėtingos užklausos su keliais įdėtų subužklausų lygiais tampa sunkios prižiūrėti, o CTE leidžia jas suskaidyti į pavadintus, nuoseklius loginius žingsnius (skaitant iš viršaus į apačią), dramatiškai pagerinant aiškumą — dėl to CTE yra svarbi priemonė rašant palaikomas SQL.

Išskirtinis gebėjimas yra **rekursinės CTE** (`WITH RECURSIVE`), kurios leidžia klausti **hierarchinių ir medžio struktūros duomenų** (organizacijos diagramos, kategorijų medžiai, medžiagų sudėtis, gijoti komentarai, grafiko peržiūra) — užklausų klasę, kuri kitu atveju labai sunki SQL, dėl to rekursinės CTE yra standartinis sprendimas hierarchinei peržiūrai.

Supratimas rekursinės CTE struktūros (bazinis atvejis + rekursinis žingsnis susijungiant atgal į save) yra svarbi žinių dalis šiam bendram poreikiui.

Naudinga PostgreSQL specifika yra **CTE materializavimo** elgesys (PostgreSQL 12+ gali įtraukti CTE geresniame našumui, kontroliuojama su `MATERIALIZED`/`NOT MATERIALIZED`) — svarbu optimizavimui.

Kadangi sudėtingos užklausos yra dažnos (ir suprantamumas svarbus priežiūrai), ir kadangi hierarchiniai duomenys (kuriuos rekursinės CTE unikaliai gerai tvarko) pasirodo dažnai (organizacijos struktūros, įdėtos kategorijos), CTE ir rekursinių užklausų supratimas yra vertinga, praktiškai aktuali PostgreSQL žinių, kuri pagerina užklausų palaikomumą ir atrakina hierarchinę užklausą — dažnai aktualus gebėjimas švariam, sudėtingam SQL ir dažna tema, ypač rekursinė forma pasikartojančiam poreikiui klausti medžio struktūros duomenų.