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 завершені_замовлення,
    (SELECT COUNT(*) FROM orders WHERE status = 'completed') * 100.0 / COUNT(*) AS відсоток_завершення
FROM orders;

-- З CTE: рахуємо один раз, використовуємо двічі
WITH completed_orders AS (
    SELECT COUNT(*) AS count FROM orders WHERE status = 'completed'
)
SELECT
    co.count AS завершені_замовлення,
    co.count * 100.0 / (SELECT COUNT(*) FROM orders) AS відсоток_завершення
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 частка_виручки
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 сума_продажу
    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(сума_продажу) AS revenue
    FROM yearly_sales
    GROUP BY category
),
total_revenue AS (
    SELECT SUM(сума_продажу) AS total FROM yearly_sales
)
SELECT 
    cr.category,
    cr.revenue,
    cr.revenue * 100.0 / tr.total AS частка_виручки
FROM category_revenue cr, total_revenue tr;

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

Для ієрархічних структур підзапити безсилі.

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

WITH RECURSIVE employee_hierarchy AS (
    -- Починаємо з CEO
    SELECT employee_id, manager_id, name, 1 AS рівень
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Додаємо підлеглих кожного рівня
    SELECT e.employee_id, e.manager_id, e.name, eh.рівень + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy ORDER BY рівень, 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 кількість_замовлень
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY customer_id
)
SELECT COUNT(*) FROM recent_orders; -- Перевіряємо і цей етап

Підзапити складніше відлагоджувати — треба витягувати їх із контексту.

Практичні поради

Використовуй підзапити коли:

• Логіка проста і вкладається в один рядок
• Потрібна максимальна продуктивність для простих операцій
• Проміжний результат використовується лише один раз
• Працюєш з невеликими обсягами даних

Використовуй CTE коли:

• Запит складний і ділиться на логічні етапи
• Потрібно багаторазово використовувати проміжні результати
• Важлива читабельність і підтримка коду
• Працюєш з ієрархіями (рекурсивні CTE)
• Відлагоджуєш складну логіку по частинах

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

Починай з підзапиту. Якщо стає складно читати або логіка повторюється — переходь на CTE. Твій майбутній колега (або ти сам через пів року) скаже дякую!