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.

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

WITH данные AS ( SELECT * FROM logs WHERE уровень = 'ERROR' ), статистика AS ( SELECT дата, COUNT(*) FROM данные GROUP BY дата ) SELECT * FROM статистика ORDER BY дата; Программист: наконец-то мой запрос читается как книга.

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

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

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

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