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 для иерархии: 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? — В PostgreSQL до 12 — нет. CTE был «забором оптимизации». — С PostgreSQL 12+? — По умолчанию может оптимизировать. Если нужно принудительно материализовать — WITH ... AS MATERIALIZED.

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

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

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