CTE — Common Table Expressions

CTE — это «временная таблица» внутри запроса. Главный инструмент для читабельности сложного SQL. После джунского уровня без CTE невозможно.

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

WITH active_users AS (
    SELECT * FROM users WHERE last_login > CURRENT_DATE - INTERVAL '30 days'
)
SELECT COUNT(*) FROM active_users;

Что внутри WITH — это именованный подзапрос, доступный в основном SELECT.

Несколько CTE цепочкой

WITH
recent_orders AS (
    SELECT * FROM orders WHERE created_at > CURRENT_DATE - INTERVAL '30 days'
),
top_customers AS (
    SELECT user_id, SUM(amount) AS total
    FROM recent_orders
    GROUP BY user_id
    ORDER BY total DESC
    LIMIT 10
)
SELECT u.name, tc.total
FROM top_customers tc
JOIN users u ON u.id = tc.user_id;

Каждый следующий CTE может ссылаться на предыдущие.

CTE vs подзапрос — когда что

-- Подзапрос (хуже)
SELECT * FROM (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
) sub
WHERE sub.total > 100000;

-- CTE (лучше)
WITH user_totals AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
)
SELECT * FROM user_totals WHERE total > 100000;

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

• Читабельнее — каждая логическая ступень с именем
• Можно использовать несколько раз в одном запросе
• Легче дебажить — выделил CTE → запустил отдельно

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

Для иерархий, последовательностей, дерева:

WITH RECURSIVE fibonacci AS (
    -- база
    SELECT 1 AS n, 0 AS a, 1 AS b
    UNION ALL
    -- рекурсия
    SELECT n + 1, b, a + b
    FROM fibonacci
    WHERE n < 10
)
SELECT n, a FROM fibonacci;

Иерархия категорий (категория → подкатегория):

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 1 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON ct.id = c.parent_id
)
SELECT * FROM category_tree;

Производительность

CTE НЕ обязательно медленнее подзапроса. В Postgres ≥ 12 CTE inlined по умолчанию (как subquery). Но для читабельности — всегда выбирай CTE.

Best practices

1. Имя CTE = описание — active_users, не t1
2. Каждый CTE решает ОДНУ задачу — фильтрация, агрегация, обогащение
3. 3-7 CTE максимум — больше = пора разбить на views
4. Финальный SELECT внизу простой — JOIN'ы между CTE

Что важно запомнить

• CTE = именованный подзапрос. Делает SQL читаемым
• WITH name AS (...) — синтаксис
• WITH RECURSIVE — для иерархий и последовательностей
• Подзапрос → CTE — почти всегда лучше
• На собеседовании middle DA: точно спросят про CTE и сравнение с подзапросом