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

CTE (Common Table Expression) в SQL

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

Синтаксис

WITH cte_name AS (
    -- Подзапрос
    SELECT ...
    FROM ...
),
second_cte AS (
    -- Еще один подзапрос
    SELECT ...
    FROM ...
)
SELECT *
FROM cte_name
JOIN second_cte ON ...

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

1. Читаемость: Без CTE код становится лабиринтом вложенных подзапросов. С CTE логика очевидна.

-- ❌ Плохо — вложенные подзапросы
SELECT * FROM (
  SELECT * FROM (
    SELECT * FROM (
      SELECT user_id, COUNT(*) as count
      FROM events
      GROUP BY user_id
    ) t1
    WHERE count > 10
  ) t2
  JOIN users USING (user_id)
) t3;

-- ✅ Хорошо — CTE
WITH active_users AS (
  SELECT user_id, COUNT(*) as event_count
  FROM events
  GROUP BY user_id
  HAVING COUNT(*) > 10
)
SELECT u.*, au.event_count
FROM users u
JOIN active_users au USING (user_id);

2. Переиспользуемость: Одна CTE может использоваться несколько раз в одном запросе.

WITH base_metrics AS (
  SELECT 
    DATE_TRUNC('day', created_at)::DATE as date,
    COUNT(*) as daily_users,
    SUM(revenue) as daily_revenue
  FROM transactions
  GROUP BY DATE_TRUNC('day', created_at)
)
SELECT 
  date,
  daily_users,
  daily_revenue,
  LAG(daily_users) OVER (ORDER BY date) as prev_users,
  (daily_users - LAG(daily_users) OVER (ORDER BY date)) as growth
FROM base_metrics;

3. Логическая организация: Шаг за шагом строишь решение.

Типы CTE

1. Простая CTE (Non-Recursive)

WITH top_users AS (
  SELECT user_id, SUM(amount) as total_spent
  FROM payments
  GROUP BY user_id
  ORDER BY total_spent DESC
  LIMIT 100
)
SELECT 
  u.user_id,
  u.email,
  tu.total_spent
FROM top_users tu
JOIN users u ON tu.user_id = u.user_id;

2. Рекурсивная CTE

Для иерархических данных (деревья категорий, комментарии к комментариям):

WITH RECURSIVE comment_tree AS (
  -- Якорь: корневые комментарии
  SELECT 
    id,
    parent_id,
    user_id,
    text,
    1 as depth
  FROM comments
  WHERE parent_id IS NULL
  
  UNION ALL
  
  -- Рекурсия: найди ответы на ответы
  SELECT 
    c.id,
    c.parent_id,
    c.user_id,
    c.text,
    ct.depth + 1
  FROM comments c
  JOIN comment_tree ct ON c.parent_id = ct.id
  WHERE ct.depth < 5  -- Ограничение глубины
)
SELECT * FROM comment_tree
ORDER BY parent_id, id;

Примеры Использования

1. Когортный анализ

WITH cohorts AS (
  SELECT 
    DATE_TRUNC('month', created_at)::DATE as signup_month,
    user_id
  FROM users
),
activity AS (
  SELECT 
    c.signup_month,
    DATE_TRUNC('month', e.created_at)::DATE as activity_month,
    COUNT(DISTINCT c.user_id) as users
  FROM cohorts c
  JOIN events e ON c.user_id = e.user_id
  GROUP BY c.signup_month, activity_month
)
SELECT 
  signup_month,
  activity_month,
  users,
  (EXTRACT(YEAR FROM activity_month) - EXTRACT(YEAR FROM signup_month)) * 12 +
  (EXTRACT(MONTH FROM activity_month) - EXTRACT(MONTH FROM signup_month)) as months_since_signup
FROM activity
ORDER BY signup_month, activity_month;

2. Window Functions и CTE

WITH daily_stats AS (
  SELECT 
    DATE(created_at)::DATE as date,
    COUNT(DISTINCT user_id) as dau,
    SUM(amount) as daily_revenue
  FROM transactions
  GROUP BY DATE(created_at)
),
stats_with_trends AS (
  SELECT 
    date,
    dau,
    daily_revenue,
    LAG(dau) OVER (ORDER BY date) as prev_dau,
    AVG(dau) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as dau_7day_avg,
    ROW_NUMBER() OVER (ORDER BY daily_revenue DESC) as revenue_rank
  FROM daily_stats
)
SELECT * 
FROM stats_with_trends
WHERE revenue_rank <= 10;

3. Сложная фильтрация и агрегация

WITH user_purchases AS (
  SELECT 
    user_id,
    product_id,
    COUNT(*) as purchase_count,
    SUM(amount) as total_spent,
    MAX(created_at) as last_purchase
  FROM purchases
  WHERE created_at >= CURRENT_DATE - INTERVAL 365 day
  GROUP BY user_id, product_id
),
frequent_buyers AS (
  SELECT user_id
  FROM user_purchases
  WHERE purchase_count >= 3
),
product_affinity AS (
  SELECT 
    up.product_id,
    COUNT(DISTINCT up.user_id) as buyer_count,
    AVG(up.total_spent) as avg_spent,
    RANK() OVER (ORDER BY COUNT(DISTINCT up.user_id) DESC) as popularity_rank
  FROM user_purchases up
  JOIN frequent_buyers fb ON up.user_id = fb.user_id
  GROUP BY up.product_id
)
SELECT 
  product_id,
  buyer_count,
  avg_spent,
  popularity_rank
FROM product_affinity
WHERE popularity_rank <= 20
ORDER BY popularity_rank;

CTE vs Подзапрос

Оптимизация CTE

1. Ограничивай данные рано

-- ❌ Плохо: много ненужных данных
WITH all_events AS (
  SELECT * FROM events  -- 10M строк
),
filtered_events AS (
  SELECT * FROM all_events
  WHERE created_at >= CURRENT_DATE - INTERVAL 30 day
)
SELECT ...
FROM filtered_events;

-- ✅ Хорошо: фильтр сразу
WITH recent_events AS (
  SELECT * FROM events
  WHERE created_at >= CURRENT_DATE - INTERVAL 30 day  -- 100k строк
)
SELECT ...
FROM recent_events;

2. Используй MATERIALIZED если CTE большая и переиспользуется

WITH large_calculation AS MATERIALIZED (
  SELECT ... -- Дорогой расчёт
  FROM ...
)
SELECT ...
FROM large_calculation
JOIN ...
FROM large_calculation;  -- Переиспользуется

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

1. Простые одноразовые подзапросы

-- Не нужна CTE, просто подзапрос
SELECT * FROM (
  SELECT * FROM users WHERE status = 'active'
) active_users;

2. Очень тяжёлые вычисления для большой таблицы Тогда лучше materialized view или отдельная таблица.

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

1. Давай CTE понятные имена:

   • ✅ WITH active_users AS ...
   • ❌ WITH t1 AS ...

2. Используй CTE для логической последовательности:

   • Сначала базовые данные
   • Потом фильтрация
   • Потом агрегация
   • Потом финальный результат

3. Комментируй сложные CTE:

   -- Пользователи с 3+ покупками в последние 90 дней
   WITH high_value_users AS (...)
   

4. Проверяй результаты каждой CTE отдельно во время разработки

Ключевые Выводы

• CTE делает SQL более читаемым и поддерживаемым
• Можно использовать несколько CTE в одном запросе
• Рекурсивные CTE решают иерархические задачи
• Производительность CTE зависит от СУБД, но обычно такая же как подзапросы
• CTE — твой лучший друг в сложном SQL

Хороший Data Analyst пишет понятный SQL. CTE — инструмент для этого.