🧩

CTE в SQL: оператор WITH

CTE (Common Table Expression) — именованный подзапрос, объявленный через WITH. Делает сложные запросы читаемыми, позволяет переиспользовать промежуточные результаты и строить иерархии через рекурсию.

Содержание

1. Базовый синтаксис WITH
2. Несколько CTE в одном запросе
3. CTE vs подзапрос: что лучше
4. Рекурсивный CTE
5. Генерация последовательностей

Базовый синтаксис WITH

CTE объявляется до основного SELECT через WITH ... AS (...). Можно объявить несколько CTE через запятую. Они видны только внутри текущего запроса.

CTE вместо подзапроса

-- Вместо вложенного подзапроса — читаемый CTE
WITH active_users AS (
  SELECT id, name
  FROM users
  WHERE last_login >= NOW() - INTERVAL '30 days'
)
SELECT u.name, COUNT(o.id) AS orders
FROM active_users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.name;

Несколько CTE в одном запросе

Можно объявить несколько CTE. Каждый следующий может ссылаться на предыдущий. Это позволяет строить логику пошагово — как временные таблицы.

Три шага через CTE

WITH
-- Шаг 1: выручка по пользователям
user_revenue AS (
  SELECT user_id, SUM(amount) AS total
  FROM orders
  GROUP BY user_id
),
-- Шаг 2: процентиль
revenue_stats AS (
  SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY total) AS p90
  FROM user_revenue
)
-- Шаг 3: топ-10% пользователей
SELECT u.user_id, u.total
FROM user_revenue u, revenue_stats s
WHERE u.total >= s.p90
ORDER BY u.total DESC;

CTE vs подзапрос: что лучше

Читаемость: CTE выигрывает — логика разбита на именованные шаги. Производительность: в PostgreSQL CTE может «огородить» оптимизатор (MATERIALIZED). С MATERIALIZED=false — ведёт себя как подзапрос. Переиспользование: CTE можно использовать несколько раз в одном запросе — подзапрос нет.

CTE + оконная функция

-- Переиспользование CTE
WITH monthly AS (
  SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT
  m.month,
  m.revenue,
  LAG(m.revenue) OVER (ORDER BY m.month) AS prev,
  m.revenue / NULLIF(LAG(m.revenue) OVER (ORDER BY m.month), 0) - 1 AS growth
FROM monthly m;

Рекурсивный CTE

WITH RECURSIVE позволяет обходить иерархические данные: дерево категорий, оргструктуру, граф.

Состоит из двух частей: базовый случай (anchor) + рекурсивная часть, соединённые UNION ALL.

Рекурсивный CTE: обход дерева

-- Дерево категорий: все подкатегории "Электроника"
WITH RECURSIVE cat_tree AS (
  -- Базовый случай: корневая категория
  SELECT id, name, parent_id, 0 AS depth
  FROM categories
  WHERE name = 'Электроника'

  UNION ALL

  -- Рекурсия: дочерние категории
  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM categories c
  JOIN cat_tree ct ON c.parent_id = ct.id
)
SELECT * FROM cat_tree ORDER BY depth, name;

Генерация последовательностей

Рекурсивный CTE умеет генерировать числовые и временные ряды — например, все даты за период.

CTE для генерации дат

-- Все даты января 2024
WITH RECURSIVE dates AS (
  SELECT '2024-01-01'::date AS d
  UNION ALL
  SELECT d + 1 FROM dates WHERE d < '2024-01-31'
)
SELECT d FROM dates;

-- Заполнить пропуски: 0 продаж в дни без заказов
WITH RECURSIVE dates AS (
  SELECT '2024-01-01'::date AS d
  UNION ALL
  SELECT d + 1 FROM dates WHERE d < '2024-01-31'
)
SELECT d.d, COALESCE(SUM(o.amount), 0) AS revenue
FROM dates d
LEFT JOIN orders o ON o.created_at::date = d.d
GROUP BY d.d
ORDER BY d.d;

Закрепи знания на практике

Решай реальные задачи с собеседований прямо в браузере — без установки.

Решить задачи с CTE →

Смотри также

📊GROUP BY и агрегатные функции 🪟Оконные функции SQL 🔍Подзапросы в SQL