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 боль, радость, тревогу, ROW_NUMBER() OVER (PARTITION BY день_недели ORDER BY кофеин DESC) FROM психика)... Ой, кажется, я только что создал три новых партиции самосознания.

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

— Оптимизатор может «заглянуть» внутрь CTE? — В PostgreSQL до 12 — нет. CTE был «забором оптимизации». — С PostgreSQL 12+? — По умолчанию может оптимизировать. Если нужно принудительно материализовать — WITH ... AS MATERIALIZED.

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

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