सीटीई: साथ में बहु-चरणीय प्रश्न

वास्तविक क्वेरीज़ बढ़ती रहती हैं: तीन JOINs, एक GROUP BY, एक सबक्वेरी, एक और सबक्वेरी... और वे अपठनीय हो जाते हैं। सीटीई ("सामान्य तालिका एक्सप्रेशन", WITH क्लॉज) आपको एक नाम देने देता है मध्यवर्ती चरण और इसका उपयोग ऐसे करें जैसे कि यह मुख्य क्वेरी में एक तालिका हो।

मूल वाक्यविन्यास

WITH nome_cte AS (
  SELECT …    -- query interna
)
SELECT …      -- query principale, usa nome_cte come fosse una tabella
FROM   nome_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" लें। सीटीई के बिना आपको इनलाइन की आवश्यकता होगी FROM में सबक्वेरी (कानूनी, लेकिन अधिक शोर वाला)।

कैस्केड में एकाधिक सीटीई

आप अल्पविराम द्वारा अलग किए गए एकाधिक सीटीई को परिभाषित कर सकते हैं, और प्रत्येक को संदर्भित कर सकते हैं पिछले वाले के लिए:

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 जोड़ता है।

सीटीई बनाम सबक्वेरी: कब किसका उपयोग करना है

सीटीई तब चमकते हैं जब:

• मुख्य क्वेरी में एक ही सबक्वेरी का उपयोग दो या अधिक बार किया जाता है;
• आप नामित चरणों में संरचना को स्पष्ट बनाना चाहते हैं (स्वयं-दस्तावेजीकरण);
• वे पुनरावर्ती सीटीई के लिए पूर्वावश्यकता हैं (WITH RECURSIVE, इस मॉड्यूल में अगला पाठ)।

केवल एक बार उपयोग किए गए चरण के लिए इनलाइन सबक्वेरी अधिक संक्षिप्त है।

इसे अजमाएं

व्यायाम की समीक्षा करें