Что такое CTE?

Что такое CTE (Common Table Expression)

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

Синтаксис

Основная структура CTE:

WITH cte_name AS (
  SELECT column1, column2
  FROM table1
  WHERE condition
)
SELECT *
FROM cte_name;

Практический пример

Представь, что нужно найти топ 5 покупателей и их средний чек:

WITH customer_sales AS (
  SELECT
    customer_id,
    COUNT(*) as order_count,
    SUM(amount) as total_spent,
    AVG(amount) as avg_order_value
  FROM orders
  GROUP BY customer_id
),
top_customers AS (
  SELECT *
  FROM customer_sales
  WHERE total_spent > 10000
  ORDER BY total_spent DESC
  LIMIT 5
)
SELECT
  c.customer_id,
  c.total_spent,
  c.avg_order_value,
  u.email
FROM top_customers c
JOIN users u ON c.customer_id = u.id;

Заметь, что здесь два CTE (customer_sales и top_customers), и они используются последовательно.

Преимущества CTE

1. Читаемость — сложный запрос разбивается на логические блоки
2. Переиспользование — один CTE можно вызвать несколько раз в одном запросе
3. Отладка — легче проверить промежуточные результаты
4. Структурированность — иерархическая организация логики
5. Рекурсивные CTE — решение задач с иерархией данных

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

Рекурсивные CTE полезны для работы с иерархией (например, структура компании, категории товаров):

WITH RECURSIVE category_hierarchy 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,
    ch.depth + 1
  FROM categories c
  INNER JOIN category_hierarchy ch
    ON c.parent_id = ch.id
  WHERE ch.depth < 10  -- ограничение глубины
)
SELECT * FROM category_hierarchy
ORDER BY depth, id;

CTE vs подзапросы

Sравним CTE с вложенным подзапросом:

-- С подзапросом (менее читаемо)
SELECT customer_id, total_spent
FROM (
  SELECT
    customer_id,
    SUM(amount) as total_spent
  FROM orders
  GROUP BY customer_id
) subquery
WHERE total_spent > 1000;

-- С CTE (более читаемо)
WITH customer_totals AS (
  SELECT
    customer_id,
    SUM(amount) as total_spent
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, total_spent
FROM customer_totals
WHERE total_spent > 1000;

Важные замечания

Область видимости: CTE существует только в пределах одного запроса (не сохраняется, как хранимая процедура).

Материализация: В некоторых БД (Snowflake, BigQuery) CTE может быть материализован, создав временную таблицу.

Производительность: CTE обычно не хуже подзапросов по производительности, часто лучше благодаря оптимизаторам БД.

CTE — это мощный инструмент для написания чистого и поддерживаемого SQL кода.