CTE: zapytania wieloetapowe z WITH

Prawdziwe zapytania mają tendencję do wzrostu: trzy JOIN, GROUP BY, podzapytanie, kolejne podzapytanie… i stają się nieczytelne. CTE („Wspólna tabela Wyrażenia”, klauzula WITH) pozwalają nadać nazwę obiektowi krok pośredni i użyj go tak, jakby był tabelą w głównym zapytaniu.

Podstawowa składnia

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

CTE jest ważny tylko dla następującego po nim zapytania: nie przetrwa egzekucja.

Przykład

Znajdź 3 klientów, którzy wydali najwięcej (suma ponad zrealizowane zamówienia):

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;

Przeczytaj po angielsku: ”najpierw oblicz wydatki na klienta, a następnie dołącz do klientów i zdobądź pierwsze 3". Bez CTE potrzebowałbyś inline podzapytanie w FROM (legalne, ale głośniejsze).

Wiele CTE w kaskadzie

Można zdefiniować wiele CTE oddzielonych przecinkami, a każdy z nich może się odnosić do poprzednich:

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, ponieważ average zawsze ma jeden wiersz: Iloczyn kartezjański po prostu dołącza total_average do każdego wiersza wyniku.

CTE a podzapytanie: kiedy którego użyć

CTE świecą, gdy:

• to samo podzapytanie zostało użyte dwa lub więcej razy w zapytaniu głównym;
• chcesz wyraźnie określić strukturę w nazwanych krokach (samodokumentowanie);
• są warunkiem dla rekursywnych CTE (WITH RECURSIVE, następna lekcja w tym module).

Podzapytanie wbudowane jest bardziej zwięzłe w przypadku kroku użytego tylko raz.

Spróbuj

Przejrzyj ćwiczenie