CTE: İLE çok adımlı sorgular

Gerçek sorgular büyüme eğilimindedir: üç JOIN, bir GROUP BY, bir alt sorgu, başka bir alt sorgu… ve okunamaz hale geliyorlar. CTE'ler ("Ortak Tablo İfadeler", WITH yan tümcesi), bir öğeye bir ad vermenize olanak tanır ara adıma geçin ve bunu ana sorguda bir tablo gibi kullanın.

Temel sözdizimi

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

Bir CTE yalnızca onu takip eden sorgu için geçerlidir: hayatta kalamaz infaz.

Örnek

En fazla harcama yapan 3 müşteriyi bulun (teslim edilen siparişlerin toplamı):

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;

İngilizce okuyun: "önce müşteri başına harcamayı hesaplayın; ardından şuna katılın: Müşteriler ve ilk 3"'ü alın. CTE olmadan satır içi bir satıra ihtiyacınız olur FROM alt sorgusu (yasal ama daha gürültülü).

Kademeli olarak birden fazla CTE

Virgüllerle ayrılmış birden çok CTE tanımlayabilirsiniz ve her biri başvuruda bulunabilir. öncekilere:

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 çünkü average her zaman tek bir satıra sahiptir: Kartezyen çarpımı sonucun her satırına yalnızca total_average ekler.

CTE ve alt sorgu: ne zaman kullanılmalı, hangisi

CTE'ler şu durumlarda parlar:

• aynı alt sorgunun ana sorguda iki veya daha fazla kez kullanılması;
• yapıyı adlandırılmış adımlarda açık hale getirmek istiyorsunuz (kendi kendini belgeleme);
• bunlar özyinelemeli CTE'ler için önkoşuldur (WITH RECURSIVE, Bu modüldeki bir sonraki ders).

Satır içi alt sorgu, yalnızca bir kez kullanılan bir adım için daha kısadır.

Deneyin

Alıştırmayı gözden geçirin