CTE: багатокрокові запити з WITH

Реальні запити мають тенденцію до зростання: три JOIN, 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 (законно, але шумніше).

Кілька КТР у каскаді

Ви можете визначити кілька CTE, розділених комами, і кожен з них може посилатися до попередніх:

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 проти підзапиту: коли використовувати який

CTE сяють, коли:

• той самий підзапит використовується два або більше разів в основному запиті;
• ви хочете зробити структуру явною в іменованих кроках (самодокументування);
• вони є передумовою для рекурсивних CTE (WITH RECURSIVE, наступний урок у цьому модулі).

Вбудований підзапит більш стислий для кроку, який використовується лише один раз.

Спробуйте

Повторна вправа