CTE: WITH-выражения

CTE: WITH-выражения

Подзапросы решают многие задачи, но при вложенности в несколько уровней код быстро становится трудночитаемым. Common Table Expressions (CTE) — способ именовать промежуточные результаты, вынести их наверх запроса и работать с ними как с обычными таблицами. Результат — сложная логика, разбитая на понятные именованные шаги.

Синтаксис CTE

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

WITH предшествует основному запросу. В скобках после имени — любой SELECT. После определения CTE его можно использовать в основном запросе по имени как обычную таблицу.

Базовый пример

Задача из предыдущих уроков — сотрудники с зарплатой выше средней по компании. Через производную таблицу:

SELECT name, salary
FROM   employees
WHERE  salary > (SELECT AVG(salary) FROM employees);

Через CTE:

WITH avg_salary AS (
    SELECT AVG(salary) AS avg
    FROM   employees
)
SELECT e.name, e.salary
FROM   employees e, avg_salary
WHERE  e.salary > avg_salary.avg;

Или через CROSS JOIN:

WITH avg_salary AS (SELECT AVG(salary) AS avg FROM employees)
SELECT e.name, e.salary
FROM   employees e
CROSS JOIN avg_salary
WHERE  e.salary > avg_salary.avg;

Пока это выглядит длиннее. Преимущество CTE раскрывается при нескольких шагах.

Несколько CTE: пошаговое решение

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

WITH
dept_stats AS (
    SELECT department,
           AVG(salary) AS avg_salary,
           COUNT(*)    AS headcount
    FROM   employees
    GROUP BY department
),
top_depts AS (
    SELECT department, avg_salary, headcount
    FROM   dept_stats
    WHERE  headcount >= 3
)
SELECT d.department,
       d.avg_salary,
       d.headcount
FROM   top_depts d
ORDER BY d.avg_salary DESC;

Два CTE: dept_stats считает статистику по отделам, top_depts фильтрует отделы с 3+ сотрудниками. Основной запрос читает уже отфильтрованный результат.

Без CTE это была бы вложенная производная таблица или несколько подзапросов. С CTE — три чётких шага с именами.

Проверь себя: можно ли написать то же самое одним SELECT с HAVING? Когда CTE оправданы?

CTE vs производная таблица vs подзапрос

Все три способа часто взаимозаменяемы. Выбор — вопрос читаемости:

-- Производная таблица: вложенность мешает читать
SELECT name FROM (SELECT name, salary * 12 AS annual FROM employees) s WHERE s.annual > 1000000;

-- CTE: линейный порядок, легче читать
WITH annual_salaries AS (
    SELECT name, salary * 12 AS annual FROM employees
)
SELECT name FROM annual_salaries WHERE annual > 1000000;

Ключевое преимущество CTE: видимость порядка вычислений. Запрос читается сверху вниз как алгоритм. Каждый шаг именован и изолирован.

Недостаток: CTE немного многословнее. Для простых задач подзапрос в WHERE компактнее. Но для трёх и более шагов CTE — значительно лучше.

CTE и повторное использование

CTE в пределах одного запроса можно использовать несколько раз:

WITH active_orders AS (
    SELECT * FROM orders WHERE status = 'completed'
)
SELECT COUNT(*) AS total FROM active_orders
UNION ALL
SELECT SUM(amount) FROM active_orders;

Подзапрос пришлось бы повторить дважды. CTE определяется один раз и используется дважды.

Важный нюанс: в PostgreSQL CTE по умолчанию materializes — вычисляется один раз и сохраняется во временном буфере. Это иногда выгодно (один проход по данным), иногда нет (нельзя вытолкнуть условия из внешнего запроса внутрь CTE). PostgreSQL 12+ позволяет управлять этим через MATERIALIZED / NOT MATERIALIZED.

Практический пример: аналитический пайплайн

Задача: топ-5 клиентов по сумме завершённых заказов за прошлый месяц, с долей от общей выручки:

WITH
completed_orders AS (
    SELECT *
    FROM   orders
    WHERE  status = 'completed'
      AND  created_at >= DATE_TRUNC('month', NOW() - INTERVAL '1 month')
      AND  created_at <  DATE_TRUNC('month', NOW())
),
customer_totals AS (
    SELECT customer_id,
           SUM(amount) AS total_spent
    FROM   completed_orders
    GROUP BY customer_id
),
overall_revenue AS (
    SELECT SUM(total_spent) AS grand_total
    FROM   customer_totals
)
SELECT c.name,
       ct.total_spent,
       ROUND(ct.total_spent * 100.0 / r.grand_total, 2) AS pct_of_revenue
FROM   customer_totals ct
JOIN   customers       c  ON ct.customer_id = c.id
CROSS JOIN overall_revenue r
ORDER BY ct.total_spent DESC
LIMIT 5;

Четыре именованных шага: фильтрация, агрегация по клиентам, общая выручка, финальный вывод с долями. Это читается как список шагов алгоритма.

Рекурсивные CTE (обзор)

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

WITH RECURSIVE hierarchy AS (
    -- Базовый случай: директор
    SELECT id, name, manager_id, 1 AS depth
    FROM   employees WHERE manager_id IS NULL

    UNION ALL

    -- Рекурсивный шаг: подчинённые
    SELECT e.id, e.name, e.manager_id, h.depth + 1
    FROM   employees e
    JOIN   hierarchy h ON e.manager_id = h.id
)
SELECT name, depth FROM hierarchy ORDER BY depth, name;

Рекурсивные CTE — продвинутая тема. Здесь важно знать, что они существуют. Для двух-трёх уровней иерархии достаточно SELF JOIN из урока 6-5.

Отладка сложных запросов через CTE

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

-- Весь запрос
WITH dept_stats AS (
    SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department
),
top_depts AS (
    SELECT * FROM dept_stats WHERE avg_salary > 90000
)
SELECT * FROM top_depts;

-- Отладка: проверяем только dept_stats
WITH dept_stats AS (
    SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department
)
SELECT * FROM dept_stats;  -- временно заменить основной запрос

Поменять последнюю строку на SELECT * FROM dept_stats — и видим промежуточный результат. Вернуть обратно — и запрос снова работает полностью. Это удобнее, чем выделять подзапрос из глубины вложенных скобок.

CTE и транзакции

CTE исполняются в рамках той же транзакции, что и основной запрос. Если основной запрос — SELECT, все CTE тоже только читают данные. Если внутри CTE INSERT, UPDATE или DELETE (в PostgreSQL это возможно через Writeable CTE) — все изменения происходят атомарно как часть одной транзакции. Подробнее о транзакциях — в модуле 10.

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

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

• Запрос состоит из 3+ логических шагов
• Одно промежуточное выражение используется несколько раз
• Нужна читаемость: запрос читают другие люди или вы вернётесь к нему через месяц
• Сложная аналитика с несколькими агрегациями

Подзапрос в WHERE достаточен когда:

• Один простой шаг (одно вложение)
• Однократное использование
• Запрос пишется для одноразового анализа

CTE как документация

Хорошо написанный CTE — это документация сама по себе. Имена CTE должны отражать содержимое: active_orders, dept_stats, top_customers — лучше, чем cte1, tmp, sub.

-- Плохо: непонятные имена
WITH a AS (SELECT * FROM orders WHERE status = 'completed'),
     b AS (SELECT customer_id, SUM(amount) FROM a GROUP BY 1)
SELECT * FROM b;

-- Хорошо: говорящие имена
WITH completed_orders AS (SELECT * FROM orders WHERE status = 'completed'),
     customer_spending AS (SELECT customer_id, SUM(amount) AS total FROM completed_orders GROUP BY 1)
SELECT c.name, cs.total
FROM customer_spending cs JOIN customers c ON cs.customer_id = c.id;

Второй вариант не нуждается в комментариях — имена CTE объясняют всё. Через месяц вы вернётесь к этому запросу и сразу поймёте логику.

Краткий итог

• WITH name AS (SELECT ...) — именованный промежуточный результат, используется в основном запросе
• Несколько CTE через запятую; каждый следующий видит предыдущие
• CTE превращает сложный запрос в линейный список именованных шагов
• Можно использовать несколько раз в одном запросе
• WITH RECURSIVE — для обхода иерархий произвольной глубины

CTE в разных СУБД

WITH-выражения поддерживаются во всех основных СУБД: PostgreSQL, MySQL 8+, SQL Server, SQLite 3.35+. В Oracle они тоже есть. Синтаксис идентичен стандарту SQL. Это делает CTE одним из самых портируемых инструментов SQL — код, написанный в PostgreSQL, с высокой вероятностью заработает в другой СУБД.

Рекурсивные CTE (WITH RECURSIVE) поддерживаются чуть менее универсально, но PostgreSQL, MySQL 8+ и SQL Server их все поддерживают.

Что дальше

Вы завершили модуль 7 — подзапросы, UNION, CTE. Следующий модуль — DML: изменение данных. Вы научитесь вставлять строки (INSERT), обновлять (UPDATE) и удалять (DELETE).