Bagaimanakah CTE dan recursive query berfungsi dalam PostgreSQL?

Question

Accepted Answer

Sebuah **CTE** (Common Table Expression), ditakrifkan dengan **`WITH`**, ialah set hasil sementara yang dinamakan yang menjadikan query kompleks mudah dibaca. PostgreSQL menyokong CTE sepenuhnya, termasuk CTE **recursive** (`WITH RECURSIVE`) untuk merentasi data berhierarki/pohon.

## CTE asas — pembinaan query yang mudah dibaca

```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 memecahkan query kompleks kepada langkah-langkah yang dinamakan dan mudah dibaca (dirantai, setiap satu merujuk yang sebelumnya) — jauh lebih jelas daripada subquery bersarang.

## CTE recursive — merentasi hierarki

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

Sebuah **CTE recursive** mempunyai kes asas (base case — baris permulaan) dan langkah recursive (`UNION ALL` yang menyertai semula ke CTE itu sendiri), berulang sehingga tiada baris baharu — sesuai untuk **pohon dan hierarki** (carta organisasi, pohon kategori, bill-of-materials, laluan graf).

## Nota tentang materialization CTE (khusus Postgres)

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

Sebelum Postgres 12, CTE sentiasa merupakan "optimization fence" (di-materialize); kini ia boleh di-inline untuk prestasi yang lebih baik — `MATERIALIZED`/`NOT MATERIALIZED` mengawal ini.

## Mengapa ia penting

CTE dan recursive query ialah ciri PostgreSQL yang berharga untuk **kebolehbacaan dan data berhierarki**, jadi memahaminya berguna untuk menulis query kompleks yang boleh diselenggara dan mengendalikan data berstruktur pohon.

Manfaat harian ialah **kebolehbacaan**: query kompleks dengan berbilang tahap subquery menjadi sukar untuk diselenggara, manakala CTE membolehkan anda menguraikannya kepada langkah logikal yang dinamakan dan berurutan (dibaca dari atas ke bawah), meningkatkan kejelasan dengan ketara — menjadikan CTE alat penting untuk menulis SQL yang boleh diselenggara.

Keupayaan yang menonjol ialah **CTE recursive** (`WITH RECURSIVE`), yang membolehkan membuat query terhadap **data berhierarki dan berstruktur pohon** (carta organisasi, pohon kategori, bill-of-materials, komen berbenang, traversal graf) — satu kelas query yang amat sukar dilakukan dalam SQL, menjadikan CTE recursive sebagai penyelesaian standard untuk traversal berhierarki.

Memahami struktur CTE recursive (kes asas + langkah recursive yang menyertai semula ke dirinya sendiri) ialah pengetahuan utama untuk keperluan biasa ini.

Satu butiran khusus Postgres yang berguna ialah tingkah laku **materialization CTE** (Postgres 12+ boleh meng-inline CTE untuk prestasi yang lebih baik, boleh dikawal dengan `MATERIALIZED`/`NOT MATERIALIZED`) — relevan untuk pengoptimuman.

Memandangkan query kompleks adalah biasa (dan kebolehbacaan penting untuk penyelenggaraan), dan memandangkan data berhierarki (yang dikendalikan dengan baik secara unik oleh CTE recursive) muncul dengan kerap (struktur organisasi, kategori bersarang), memahami CTE dan recursive query ialah pengetahuan PostgreSQL yang berharga dan relevan secara praktikal yang meningkatkan kebolehselenggaraan query dan membuka keupayaan query berhierarki — satu keupayaan yang kerap relevan untuk SQL yang bersih dan canggih dan topik biasa, terutamanya bentuk recursive untuk keperluan berulang membuat query terhadap data berstruktur pohon.