CTE (Common Table Expression)

Именованный подзапрос, объявленный через WITH. Делает сложные запросы читаемыми.

Синтаксис

WITH cte_name AS (
  SELECT ...
)
SELECT ... FROM cte_name;

Объяснение

CTE объявляется через WITH name AS (...) перед основным запросом. Можно объявить несколько CTE через запятую. Плюсы: читаемость, возможность переиспользовать результат в одном запросе. Минусы: PostgreSQL до 12 не оптимизировал CTE (materializes barrier). PostgreSQL 12+ может инлайнить. Рекурсивные CTE (WITH RECURSIVE) позволяют обходить иерархические структуры.

Пример

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(total) AS revenue
  FROM orders
  WHERE status = 'paid'
  GROUP BY 1
),
growth AS (
  SELECT month, revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue
  FROM monthly_revenue
)
SELECT month, revenue,
  ROUND((revenue - prev_revenue) * 100.0 / prev_revenue, 1) AS growth_pct
FROM growth
ORDER BY month;

Связанные термины

OVER (оконные функции)Ключевое слово, превращающее агрегатную функцию в оконную — вычисляет значение без схлопывания строк.ROW_NUMBER / RANK / DENSE_RANKОконные функции нумерации строк. Различаются поведением при одинаковых значениях.Подзапрос (Subquery)SELECT внутри другого SQL выражения. Может быть в FROM, WHERE, HAVING или SELECT.

Анекдоты по теме

— Можно ли использовать CTE для UPDATE? — Да: WITH to_update AS ( SELECT id FROM orders WHERE status = 'pending' AND created_at < NOW() - INTERVAL '7 days' ) UPDATE orders SET status = 'expired' WHERE id IN (SELECT id FROM to_update); — Читаемо! — И безопасно — сначала видишь что будет обновлено.

— Доктор, у меня шизофрения. — Это лечится. Расскажите подробно. — WITH моей_личности AS (SELECT боль, радость, тревогу, ROW_NUMBER() OVER (PARTITION BY день_недели ORDER BY кофеин DESC) FROM психика)... Ой, кажется, я только что создал три новых партиции самосознания.

— Что такое рекурсивный CTE? — Это когда CTE ссылается на самого себя. — Зачем? — Чтобы обходить деревья и графы. Иерархия сотрудников, категории товаров. — Это не приведёт к бесконечному циклу? — Приведёт, если забыть условие остановки.

Все анекдоты →

Потренироваться →Вопросы на собесе →← Все термины