CTE: многошаговые запросы с помощью With

Реальные запросы имеют тенденцию к увеличению: три JOIN, GROUP BY, подзапрос, еще один подзапрос… и они становятся нечитаемыми. CTE («Общая таблица Выражения", предложение WITH) позволяет вам присвоить имя промежуточный шаг и используйте его, как если бы это была таблица в основном запросе.

Основной синтаксис

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

CTE действителен только для следующего за ним запроса: он не сохраняется. исполнение.

Пример

Найдите трех клиентов, которые потратили больше всего (сумма сверх доставленных заказов):

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 в каскаде

Вы можете определить несколько 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, следующий урок этого модуля).

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

Попробуйте это

Обзор упражнения