CTE: interogări în mai mulți pași cu WITH

Interogările reale tind să crească: trei JOINs, o GROUP BY, o subinterogare, o altă subinterogare... și devin ilizibile. CTE ("Tabel comun Expresii”, clauza WITH) vă permite să dați un nume unui pas intermediar și folosiți-l ca și cum ar fi un tabel în interogarea principală.

Sintaxa de bază

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

Un CTE este valabil numai pentru interogarea care îl urmează: nu supraviețuiește execuția.

Exemplu

Găsiți cei 3 clienți care au cheltuit cel mai mult (suma peste comenzile livrate):

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;

Citiți în engleză: "calculați mai întâi cheltuielile per client; apoi alăturați-vă clienții și luați top 3". Fără un CTE, ai avea nevoie de un inline subinterogare în FROM (legal, dar mai zgomotos).

CTE-uri multiple în cascadă

Puteți defini mai multe CTE-uri separate prin virgule și fiecare se poate referi la cele anterioare:

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 deoarece average are întotdeauna un singur rând: Produsul cartezian adaugă doar total_average la fiecare rând al rezultatului.

CTE vs subinterogare: când să folosiți care

CTE-urile strălucesc atunci când:

• aceeași subinterogare este folosită de două sau mai multe ori în interogarea principală;
• doriți să faceți structura explicită în pași denumiți (auto-documentare);
• sunt condiția preliminară pentru CTE recursive (WITH RECURSIVE, următoarea lecție din acest modul).

O subinterogare inline este mai concisă pentru un pas folosit o singură dată.

Încearcă

Exercițiu de revizuire