CTE: שאילתות מרובות שלבים עם WITH

שאילתות אמיתיות נוטות לגדול: שלוש JOINs, GROUP BY, שאילתת משנה, שאילתת משנה נוספת... והן הופכות בלתי קריאות. CTEs ("טבלה משותפת ביטויים", סעיף WITH) מאפשר לך לתת שם ל שלב ביניים והשתמש בו כאילו הוא טבלה בשאילתה הראשית.

תחביר בסיסי

WITH nome_cte AS (
  SELECT …    -- query interna
)
SELECT …      -- query principale, usa nome_cte come fosse una tabella
FROM   nome_cte;

CTE תקף רק עבור השאילתה שאחריה: הוא לא שורד את ההוצאה להורג.

דוּגמָה

מצא את 3 הלקוחות שהוציאו הכי הרבה (סכום על פני הזמנות שנמסרו):

WITH total_spent AS (
  SELECT o.customer_id,
         SUM(oi.quantity * oi.unit_price) AS total
  FROM   orders      o
  JOIN   order_items oi ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY o.customer_id
)
SELECT c.email,
       s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
ORDER BY s.total DESC
LIMIT  3;

קרא באנגלית: "תחילה מחשב את ההוצאה לכל לקוח; ואז הצטרף עם לקוחות ולקחת את ה-3" המובילים. ללא CTE תצטרך מוטבע שאילתת משנה ב-FROM (חוקית, אך רועשת יותר).

מספר CTEs במפל

אתה יכול להגדיר מספר CTEs מופרדים בפסיקים, וכל אחד יכול להפנות לקודמים:

WITH total_spent AS (
  SELECT customer_id, SUM(quantity * unit_price) AS total
  FROM   order_items oi
  JOIN   orders      o ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY customer_id
),
average AS (
  SELECT AVG(total) AS total_average
  FROM   total_spent
)
SELECT c.email, s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
CROSS JOIN average
WHERE  s.total > average.total_average
ORDER BY s.total DESC;

CROSS JOIN average מכיוון של-average יש תמיד שורה בודדת: מוצר קרטזיאני פשוט מוסיף את total_average לכל שורה של התוצאה.

CTE לעומת שאילתת משנה: מתי להשתמש באיזה

CTEs זורחים כאשר:

• נעשה שימוש באותה שאילתת משנה פעמיים או יותר בשאילתה הראשית;
• אתה רוצה להפוך את המבנה למפורש בשלבים בעלי שם (תיעוד עצמי);
• הם תנאי מוקדם עבור CTEs רקורסיבי (WITH RECURSIVE, השיעור הבא במודול זה).

שאילתת משנה מוטבעת היא תמציתית יותר עבור שלב בשימוש פעם אחת בלבד.

נסה את זה

סקירת התרגיל