CTE และ recursive query ทำงานอย่างไรใน PostgreSQL?

Question

Accepted Answer

**CTE** (Common Table Expression) ซึ่งกำหนดด้วย **`WITH`** เป็นชุดผลลัพธ์ชั่วคราวที่มีชื่อ ทำให้ query ที่ซับซ้อนอ่านง่ายขึ้น PostgreSQL รองรับ CTE อย่างเต็มที่ รวมถึง **recursive** CTE (`WITH RECURSIVE`) สำหรับการท่องผ่านข้อมูลแบบลำดับชั้น/ต้นไม้

## CTE พื้นฐาน การสร้าง query ที่อ่านง่าย

```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 แบ่ง query ที่ซับซ้อนออกเป็นขั้นตอนที่มีชื่อและอ่านง่าย (ต่อกันเป็นลูกโซ่ โดยแต่ละขั้นอ้างถึงขั้นก่อนหน้า) ซึ่งชัดเจนกว่า subquery ซ้อนกันมาก

## recursive CTE ท่องผ่านลำดับชั้น

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

**recursive CTE** มี base case (แถวเริ่มต้น) และ recursive step (`UNION ALL` ที่ join กลับมายัง CTE ตัวมันเอง) วนซ้ำจนกว่าจะไม่มีแถวใหม่ เหมาะสมอย่างยิ่งสำหรับ **ต้นไม้และลำดับชั้น** (org chart, category tree, bill-of-materials, graph path)

## หมายเหตุเรื่อง CTE materialization (เฉพาะ 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)
```

ก่อน Postgres 12, CTE จะเป็น "optimization fence" เสมอ (ถูก materialize) ตอนนี้สามารถถูก inline เพื่อประสิทธิภาพที่ดีขึ้นได้ โดย `MATERIALIZED`/`NOT MATERIALIZED` ควบคุมพฤติกรรมนี้

## ทำไมจึงสำคัญ

CTE และ recursive query เป็นฟีเจอร์ของ PostgreSQL ที่มีคุณค่าต่อ **ความอ่านง่ายและข้อมูลแบบลำดับชั้น** ดังนั้นการเข้าใจมันจึงเป็นประโยชน์ต่อการเขียน query ที่ซับซ้อนซึ่งบำรุงรักษาได้และการจัดการข้อมูลแบบโครงสร้างต้นไม้

ประโยชน์ในชีวิตประจำวันคือ **ความอ่านง่าย**: query ที่ซับซ้อนซึ่งมี subquery หลายระดับจะบำรุงรักษายาก ในขณะที่ CTE ช่วยให้คุณแยกมันออกเป็นขั้นตอนเชิงตรรกะที่มีชื่อและเรียงลำดับ (อ่านจากบนลงล่าง) ปรับปรุงความชัดเจนอย่างมาก ทำให้ CTE เป็นเครื่องมือสำคัญสำหรับการเขียน SQL ที่บำรุงรักษาได้

ความสามารถที่โดดเด่นคือ **recursive CTE** (`WITH RECURSIVE`) ซึ่งช่วยให้ query **ข้อมูลแบบลำดับชั้นและโครงสร้างต้นไม้** ได้ (org chart, category tree, bill-of-materials, threaded comment, graph traversal) ซึ่งเป็น query กลุ่มที่ทำได้ยากมากใน SQL ทำให้ recursive CTE เป็นโซลูชันมาตรฐานสำหรับการท่องผ่านลำดับชั้น

การเข้าใจโครงสร้างของ recursive CTE (base case + recursive step ที่ join กลับมาหาตัวเอง) เป็นความรู้สำคัญสำหรับความต้องการที่พบบ่อยนี้

รายละเอียดเฉพาะ Postgres ที่มีประโยชน์คือพฤติกรรม **CTE materialization** (Postgres 12+ สามารถ inline CTE เพื่อประสิทธิภาพที่ดีขึ้น ควบคุมได้ด้วย `MATERIALIZED`/`NOT MATERIALIZED`) ซึ่งเกี่ยวข้องกับการ optimize

เนื่องจาก query ที่ซับซ้อนพบได้บ่อย (และความอ่านง่ายมีความสำคัญต่อการบำรุงรักษา) และเนื่องจากข้อมูลแบบลำดับชั้น (ซึ่ง recursive CTE จัดการได้ดีอย่างเป็นเอกลักษณ์) ปรากฏบ่อย (โครงสร้างองค์กร, หมวดหมู่ที่ซ้อนกัน) การเข้าใจ CTE และ recursive query จึงเป็นความรู้ PostgreSQL ที่มีคุณค่าและเกี่ยวข้องเชิงปฏิบัติ ซึ่งช่วยปรับปรุงความสามารถในการบำรุงรักษา query และปลดล็อกการ query แบบลำดับชั้น ซึ่งเป็นความสามารถที่เกี่ยวข้องบ่อยสำหรับ SQL ที่สะอาดและซับซ้อน และเป็นหัวข้อที่พบบ่อย โดยเฉพาะรูปแบบ recursive สำหรับความต้องการที่เกิดขึ้นซ้ำในการ query ข้อมูลแบบโครงสร้างต้นไม้