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 медленнее подзапроса? — Когда PostgreSQL не может оптимизировать его как часть основного запроса. — Например? — CTE с большим результатом, который потом фильтруется — база вычислит весь CTE, потом отфильтрует. — Подзапрос? — Оптимизатор может «протолкнуть» условие внутрь.

Рекурсивный CTE для иерархии: WITH RECURSIVE tree AS ( SELECT id, name, parent_id, 0 AS depth FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, c.parent_id, t.depth + 1 FROM categories c JOIN tree t ON c.parent_id = t.id ) SELECT * FROM tree ORDER BY depth, name; Программист: это чище чем 10 JOIN!

— Зачем нужен CTE? — Чтобы сложный запрос стал читаемым. — А чем он лучше подзапроса? — Подзапрос — это как объяснить рецепт блюда прямо внутри инструкции по приготовлению. CTE — как написать рецепт отдельно и сослаться на него.

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

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