นิพจน์ตารางร่วม (WITH)

CTE คืออะไร

WITH paid_orders AS (
  SELECT * FROM orders WHERE status = 'paid'
)
SELECT user_id, COUNT(*)
FROM paid_orders
GROUP BY user_id;

เหตุใดจึงใช้ CTE

การเชื่อม CTE

WITH last_30 AS (
  SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '30 days'
),
per_user AS (
  SELECT user_id, SUM(total) AS revenue FROM last_30 GROUP BY user_id
)
SELECT u.email, p.revenue
FROM users u
JOIN per_user p ON p.user_id = u.id
ORDER BY p.revenue DESC LIMIT 20;

การนำ CTE กลับมาใช้ซ้ำ

WITH recent_users AS (
  SELECT id FROM users WHERE created_at >= NOW() - INTERVAL '7 days'
)
SELECT 'new orders'  AS metric, COUNT(*) FROM orders
  WHERE user_id IN (SELECT id FROM recent_users)
UNION ALL
SELECT 'new revenue', SUM(total) FROM orders
  WHERE user_id IN (SELECT id FROM recent_users);

การทำให้ CTE เป็นรูปธรรม (PostgreSQL ≤ 11)

-- Force the old materialise behaviour (rarely needed):
WITH x AS MATERIALIZED (SELECT ...) ...

-- Force inlining (default):
WITH x AS NOT MATERIALIZED (SELECT ...) ...

CTE ที่ปรับเปลี่ยนข้อมูล

WITH moved AS (
  DELETE FROM orders WHERE status = 'archived' RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;

CTE ใน DML

WITH cancelled AS (
  UPDATE orders SET status = 'cancelled'
  WHERE created_at < NOW() - INTERVAL '14 days'
    AND status = 'pending'
  RETURNING id
)
INSERT INTO audit_log (event, order_id)
SELECT 'auto-cancel', id FROM cancelled;

ลำดับการทำงาน

CTE เทียบกับซับคิวรีและมุมมอง

อย่าใช้ CTE มากเกินไป

สรุป

ตรวจสอบความเข้าใจ