CTE vs подзапросы: когда что выбрать?

Мы уже знаем, что CTE делает код читаемее. Но всегда ли стоит их использовать? Иногда простой подзапрос справляется лучше и быстрее. Разберемся, когда каждый инструмент работает в свою пользу, и научимся делать осознанный выбор.

Подзапросы: быстро и просто

Вы уже помните, что подзапрос — это SQL внутри SQL. Он встраивается прямо в основной запрос и выполняется "по месту". Отлично подходит для простых одноразовых операций:

-- Найти товары дороже средней цены
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

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

Производительность: кто быстрее?

Подзапросы часто выигрывают в скорости для простых операций. PostgreSQL может оптимизировать их "на лету", особенно когда подзапрос выполняется один раз:

-- Быстро: подзапрос выполнится один раз
SELECT customer_id, order_total
FROM orders
WHERE order_date = (SELECT MAX(order_date) FROM orders);

CTE по умолчанию материализуются — PostgreSQL сначала вычисляет результат CTE, сохраняет его как временную таблицу, а потом использует. Это может замедлить простые запросы:

-- Медленнее: CTE материализуется в временную таблицу
WITH latest_date AS (
    SELECT MAX(order_date) AS max_date FROM orders
)
SELECT customer_id, order_total
FROM orders, latest_date
WHERE order_date = max_date;

Но! Начиная с PostgreSQL 12 можно управлять материализацией:

-- Принудительно НЕ материализовать
WITH latest_date AS NOT MATERIALIZED (
    SELECT MAX(order_date) AS max_date FROM orders
)
SELECT customer_id, order_total
FROM orders, latest_date
WHERE order_date = max_date;

Многократное использование: здесь CTE рулит

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

-- С подзапросом: повторяем одну логику дважды
SELECT
    (SELECT COUNT(*) FROM orders WHERE status = 'completed') AS completed_orders,
    (SELECT COUNT(*) FROM orders WHERE status = 'completed') * 100.0 / COUNT(*) AS completion_rate
FROM orders;

-- С CTE: вычисляем один раз, используем дважды
WITH completed_orders AS (
    SELECT COUNT(*) AS count FROM orders WHERE status = 'completed'
)
SELECT
    co.count AS completed_orders,
    co.count * 100.0 / (SELECT COUNT(*) FROM orders) AS completion_rate
FROM completed_orders co;

Сложная аналитика: CTE выигрывает по очкам

Для многоэтапной аналитики CTE превращает хаос в порядок. Сравните отчет по продажам:

С подзапросами (каша в голове):

SELECT 
    category,
    revenue,
    revenue * 100.0 / (
        SELECT SUM(p.price * oi.quantity)
        FROM order_items oi
        JOIN products p ON oi.product_id = p.product_id
        JOIN orders o ON oi.order_id = o.order_id
        WHERE EXTRACT(year FROM o.order_date) = 2024
    ) AS revenue_share
FROM (
    SELECT 
        p.category,
        SUM(p.price * oi.quantity) AS revenue
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE EXTRACT(year FROM o.order_date) = 2024
    GROUP BY p.category
) category_revenue;

С CTE (всё по полочкам):

WITH yearly_sales AS (
    SELECT 
        p.category,
        p.price * oi.quantity AS sale_amount
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE EXTRACT(year FROM o.order_date) = 2024
),
category_revenue AS (
    SELECT 
        category,
        SUM(sale_amount) AS revenue
    FROM yearly_sales
    GROUP BY category
),
total_revenue AS (
    SELECT SUM(sale_amount) AS total FROM yearly_sales
)
SELECT 
    cr.category,
    cr.revenue,
    cr.revenue * 100.0 / tr.total AS revenue_share
FROM category_revenue cr, total_revenue tr;

Рекурсия: монополия CTE

Для иерархических структур подзапросы бессильны.

Только рекурсивные CTE справляются с задачами типа "найти всех подчиненных менеджера":

WITH RECURSIVE employee_hierarchy AS (
    -- Начинаем с CEO
    SELECT employee_id, manager_id, name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Добавляем подчиненных каждого уровня
    SELECT e.employee_id, e.manager_id, e.name, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy ORDER BY level, name;

Отладка и поддержка кода

CTE можно легко отлаживать по частям:

-- Проверяем первый этап
WITH active_customers AS (
    SELECT customer_id FROM customers WHERE status = 'active'
)
SELECT COUNT(*) FROM active_customers; -- Убеждаемся, что логика правильная

-- Добавляем второй этап
WITH active_customers AS (...),
recent_orders AS (
    SELECT customer_id, COUNT(*) as order_count
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY customer_id
)
SELECT COUNT(*) FROM recent_orders; -- Проверяем и этот этап

Подзапросы сложнее отлаживать — нужно вытаскивать их из контекста.

Практические рекомендации

Используйте подзапросы когда:

• Логика простая и укладывается в одну строчку
• Нужна максимальная производительность для простых операций
• Промежуточный результат используется только один раз
• Работаете с небольшими объемами данных

Используйте CTE когда:

• Запрос сложный и делится на логические этапы
• Нужно многократно использовать промежуточные результаты
• Важна читаемость и поддерживаемость кода
• Работаете с иерархиями (рекурсивные CTE)
• Отлаживаете сложную логику по частям

Золотое правило

Начинайте с подзапроса. Если становится сложно читать или логика повторяется — переходите на CTE. Ваш будущий коллега (или вы сами через полгода) скажет спасибо!

2

Задача

SQL SELF, 28 уровень, 1 лекция

Недоступна

Использование CTE для фильтрации данных

Использование CTE для фильтрации данных

Открыть