Что такое обобщенное табличное выражение?

Что такое обобщённое табличное выражение (Common Table Expression, CTE)?

CTE (also known as WITH clause) — это **временно именованный результирующий набор**, определённый внутри SELECT, INSERT, UPDATE или DELETE. Это мощный инструмент для структурирования и упрощения сложных SQL запросов.

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

WITH cte_name AS (
    SELECT ... FROM ... WHERE ...
)
SELECT * FROM cte_name;

Простой пример: подсчёт количества заказов

Без CTE (сложно читать):

SELECT 
    u.user_id,
    u.name,
    COUNT(o.order_id) as order_count,
    AVG(o.amount) as avg_amount
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.name
HAVING COUNT(o.order_id) > 5;

С CTE (более читаемо):

WITH user_orders AS (
    SELECT 
        u.user_id,
        u.name,
        COUNT(o.order_id) as order_count,
        AVG(o.amount) as avg_amount
    FROM users u
    LEFT JOIN orders o ON u.user_id = o.user_id
    GROUP BY u.user_id, u.name
)
SELECT *
FROM user_orders
WHERE order_count > 5;

Реальный пример из backend разработки

Задача: найти пользователей, которые в последний месяц сделали покупки на сумму > 1000, с их средним чеком и количеством покупок.

WITH recent_orders AS (
    -- ШАГ 1: получаем заказы за последний месяц
    SELECT 
        o.user_id,
        o.order_id,
        o.amount,
        o.created_at
    FROM orders o
    WHERE o.created_at >= CURRENT_DATE - INTERVAL '1 month'
),

user_stats AS (
    -- ШАГ 2: считаем статистику для каждого пользователя
    SELECT 
        user_id,
        COUNT(*) as purchase_count,
        SUM(amount) as total_spent,
        AVG(amount) as avg_purchase,
        MAX(created_at) as last_purchase_date
    FROM recent_orders
    GROUP BY user_id
),

qualified_users AS (
    -- ШАГ 3: фильтруем только тех кто потратил > 1000
    SELECT 
        u.user_id,
        u.email,
        s.purchase_count,
        s.total_spent,
        s.avg_purchase,
        s.last_purchase_date
    FROM users u
    INNER JOIN user_stats s ON u.user_id = s.user_id
    WHERE s.total_spent > 1000
)

-- ИТОГОВЫЙ ЗАПРОС
SELECT 
    user_id,
    email,
    purchase_count,
    ROUND(total_spent::numeric, 2) as total_spent,
    ROUND(avg_purchase::numeric, 2) as avg_purchase,
    last_purchase_date
FROM qualified_users
ORDER BY total_spent DESC;

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

• Каждый шаг логически отделён
• Легче отладить (проверить каждый CTE отдельно)
• Переиспользуемость (qualified_users может использоваться в нескольких queries)
• Читаемость: шаг за шагом понимаешь что происходит

Рекурсивный CTE (для иерархических данных)

Задача: вывести всю иерархию категорий товаров

-- Таблица с иерархией
-- id | name        | parent_id
-- 1  | Электроника | NULL
-- 2  | Смартфоны   | 1
-- 3  | iPhone      | 2
-- 4  | Samsung     | 2

WITH RECURSIVE category_hierarchy AS (
    -- БАЗОВЫЙ СЛУЧАЙ: корневые категории (parent_id IS NULL)
    SELECT 
        id,
        name,
        parent_id,
        0 as depth,
        name as path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- РЕКУРСИВНЫЙ СЛУЧАЙ: дочерние категории
    SELECT 
        c.id,
        c.name,
        c.parent_id,
        ch.depth + 1,
        ch.path || ' > ' || c.name as path
    FROM categories c
    INNER JOIN category_hierarchy ch 
        ON c.parent_id = ch.id
    WHERE ch.depth < 5  -- Максимальная глубина
)
SELECT 
    id,
    name,
    parent_id,
    depth,
    path
FROM category_hierarchy
ORDER BY path;

Вывод:

id | name      | parent_id | depth | path
1  | Электроника | NULL    | 0     | Электроника
2  | Смартфоны | 1         | 1     | Электроника > Смартфоны
3  | iPhone    | 2         | 2     | Электроника > Смартфоны > iPhone
4  | Samsung   | 2         | 2     | Электроника > Смартфоны > Samsung

Множественные CTE

WITH 
-- CTE 1
active_users AS (
    SELECT user_id FROM users WHERE status = 'active'
),

-- CTE 2
recent_activity AS (
    SELECT user_id, COUNT(*) as actions
    FROM activity_log
    WHERE created_at > NOW() - INTERVAL '7 days'
    GROUP BY user_id
)

SELECT 
    au.user_id,
    ra.actions
FROM active_users au
LEFT JOIN recent_activity ra ON au.user_id = ra.user_id
ORDER BY ra.actions DESC;

CTE vs Подзапросы (Subqueries)

С подзапросом (трудно читать):

SELECT 
    au.user_id,
    ra.actions
FROM (
    SELECT user_id FROM users WHERE status = 'active'
) au
LEFT JOIN (
    SELECT user_id, COUNT(*) as actions
    FROM activity_log
    WHERE created_at > NOW() - INTERVAL '7 days'
    GROUP BY user_id
) ra ON au.user_id = ra.user_id;

С CTE (чище):

WITH active_users AS (
    SELECT user_id FROM users WHERE status = 'active'
),
recent_activity AS (
    SELECT user_id, COUNT(*) as actions
    FROM activity_log
    WHERE created_at > NOW() - INTERVAL '7 days'
    GROUP BY user_id
)
SELECT 
    au.user_id,
    ra.actions
FROM active_users au
LEFT JOIN recent_activity ra ON au.user_id = ra.user_id;

Пример из реальной системы: рекомендации товаров

WITH user_purchases AS (
    -- Что купил пользователь?
    SELECT DISTINCT category_id
    FROM order_items oi
    JOIN products p ON oi.product_id = p.id
    WHERE oi.user_id = 123
),

similar_buyers AS (
    -- Кто ещё покупал эти категории?
    SELECT DISTINCT u.user_id
    FROM order_items oi
    JOIN products p ON oi.product_id = p.id
    JOIN order_items oi2 ON oi.order_id = oi2.order_id
    JOIN users u ON oi2.user_id = u.user_id
    WHERE p.category_id IN (SELECT category_id FROM user_purchases)
    AND u.user_id != 123  -- Исключаем самого пользователя
),

recommended_products AS (
    -- Что купили похожие покупатели?
    SELECT 
        p.id,
        p.name,
        p.price,
        COUNT(*) as popularity_score
    FROM order_items oi
    JOIN products p ON oi.product_id = p.id
    WHERE oi.user_id IN (SELECT user_id FROM similar_buyers)
    AND p.id NOT IN (SELECT product_id FROM order_items WHERE user_id = 123)
    GROUP BY p.id, p.name, p.price
)

SELECT *
FROM recommended_products
ORDER BY popularity_score DESC
LIMIT 10;

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

ИСПОЛЬЗУЙ CTE когда:

1. Запрос имеет несколько логических шагов
2. Хочешь переиспользовать результат несколько раз
3. Нужна рекурсия (иерархия, граф)
4. Хочешь улучшить читаемость

НЕ ИСПОЛЬЗУЙ когда:

1. Простой одношаговый запрос (overhead)
2. Нужна высокая производительность (иногда subqueries быстрее)
3. CTE не влияет на читаемость

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

CTE обычно materialised (вычисляется один раз):

WITH expensive_calculation AS (
    SELECT ... -- сложный запрос
)
SELECT * FROM expensive_calculation;  -- Использование 1
SELECT COUNT(*) FROM expensive_calculation;  -- Использование 2
-- expensive_calculation вычислена один раз, результат переиспользован

В PostgreSQL можно контролировать materialisation:

WITH expensive_calculation AS NOT MATERIALIZED (
    -- Не материализовать (inline), может быть медленнее если использовать много раз
    SELECT ...
)
SELECT * FROM expensive_calculation;

Итог

CTE (WITH clause) — это:

• Способ структурировать сложные SQL запросы
• Инструмент для улучшения читаемости
• Возможность использовать рекурсию
• Способ переиспользовать результаты
• Обязателен в modern SQL для production запросов

В backend разработке CTEs помогают писать понятные, maintainable SQL запросы, которые легко отлаживать и оптимизировать.