Что такое CTE (Common Table Expression) в SQL и когда их стоит использовать?

Common Table Expression (CTE) в SQL

CTE (Общее табличное выражение) — это временная именованная таблица, которая существует только в пределах одного SQL запроса. Это мощный инструмент для написания чистого и поддерживаемого кода при анализе данных.

Синтаксис и базовое использование

WITH имя_cte AS (
  SELECT ...
)
SELECT * FROM имя_cte;

Пример 1: Простая подготовка данных

WITH last_month_orders AS (
  SELECT 
    user_id,
    order_date,
    amount
  FROM orders
  WHERE order_date >= DATE_TRUNC('month', NOW() - INTERVAL '1 month')
    AND order_date < DATE_TRUNC('month', NOW())
)
SELECT 
  user_id,
  COUNT(*) as order_count,
  SUM(amount) as total_spending
FROM last_month_orders
GROUP BY user_id
ORDER BY total_spending DESC;

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

WITH monthly_revenue AS (
  SELECT 
    DATE_TRUNC('month', order_date) as month,
    SUM(amount) as revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
),
monthly_orders AS (
  SELECT 
    DATE_TRUNC('month', order_date) as month,
    COUNT(*) as order_count
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT 
  r.month,
  r.revenue,
  o.order_count,
  ROUND(r.revenue / o.order_count, 2) as avg_order_value
FROM monthly_revenue r
JOIN monthly_orders o ON r.month = o.month
ORDER BY r.month DESC;

Когда стоит использовать CTE

1. Улучшение читаемости

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

2. Избежание дублирования логики

Вместо повторения одного и того же подзапроса несколько раз используй CTE один раз.

3. Пошаговое строительство результата

Для сложного анализа лучше разделить на этапы: сначала агрегирование по дням, потом по когортам, потом анализ.

4. Рекурсивные вычисления

Для иерархических данных (деревья, цепочки событий) используй рекурсивные CTE.

Когда НЕ стоит использовать CTE

• Очень простые запросы — добавляет ненужной сложности
• Производительность критична — в некоторых БД CTE может быть медленнее (нужно тестировать с EXPLAIN)
• Используется только один раз в основном SELECT — можно использовать подзапрос

Лучшие практики

1. Именуй CTE понятно: user_monthly_stats, не temp1
2. Один CTE — одна логическая единица: не смешивай разные операции
3. Упорядочивай CTE: от простого к сложному
4. Комментируй сложные CTE: объясни, что происходит
5. Проверяй производительность: используй EXPLAIN ANALYZE

Практические примеры для Product Analytics

Анализ воронки конверсии:

WITH signup_users AS (
  SELECT DISTINCT user_id
  FROM events
  WHERE event_name = 'signup'
),
verified_users AS (
  SELECT DISTINCT user_id
  FROM events
  WHERE event_name = 'email_verified'
),
first_purchase AS (
  SELECT DISTINCT user_id
  FROM events
  WHERE event_name = 'purchase'
)
SELECT 
  'signup' as stage,
  COUNT(DISTINCT s.user_id) as count,
  100.0 as percentage
FROM signup_users s
UNION ALL
SELECT 
  'verified' as stage,
  COUNT(DISTINCT v.user_id),
  ROUND(COUNT(DISTINCT v.user_id) * 100.0 / (SELECT COUNT(*) FROM signup_users), 2)
FROM verified_users v
UNION ALL
SELECT 
  'purchased' as stage,
  COUNT(DISTINCT p.user_id),
  ROUND(COUNT(DISTINCT p.user_id) * 100.0 / (SELECT COUNT(*) FROM signup_users), 2)
FROM first_purchase p;

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