تعبيرات الجداول الشائعة (WITH)

ما هو CTE؟

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

لماذا نستخدم CTEs؟

تسلسل CTEs

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

CTEs التي تُعدّل البيانات

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

CTEs في 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;

ترتيب التنفيذ

CTEs مقابل الاستعلامات الفرعية وViews

لا تُفرط في استخدام CTEs

مراجعة

تحقق سريع