CTE (WITH) в PostgreSQL: как превратить лапшу из SQL в читаемый конвейер

Вы открываете запрос на 500 строк, написанный вашим предшественником. Там `JOIN` внутри `JOIN`, а в `WHERE` зарыт подзапрос, который считает среднее. Грязный ди это код? И нужно ли переписать это через CTE?

Что это такое?
Простыми словами: это временная именованная таблица, которую вы создаете в начале запроса и используете дальше.
Вместо того чтобы прятать логику глубоко в скобках, вы выносите её наверх, даете имя и используете как обычную таблицу.

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

Без CTE (Спагетти-код):

SELECT e.name, b.name AS boss
FROM employees e
JOIN employees b ON e.boss_id = b.id
WHERE e.salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e.department_id
);

Какая тут проблема: Подзапрос в `WHERE` может выполняться для каждой строки. Читать это тяжело.

С CTE (Чистый код):

WITH avg_by_dept AS (
SELECT department_id, AVG(salary) as avg_sal
FROM employees
GROUP BY department_id
)
SELECT e.name, b.name as boss
FROM employees e
JOIN employees b ON e.boss_id = b.id
JOIN avg_by_dept a ON e.department_id = a.department_id
WHERE e.salary > a.avg_sal;

Почему это лучше? Мы читаем код сверху вниз, как обычную программу. Сначала подготовили «ингредиенты» (`avg_by_dept`), потом из них «приготовили» результат.

Магия конвейера
Настоящая сила CTE — в разбиении сложной задачи на шаги.
Представьте аналитику для интернет-магазина: нужно найти активных пользователей, посчитать их выручку и сегментировать. Без CTE это монстр. С CTE это конвейер:

WITH
-- Шаг 1: Кто покупал в этом месяце?
active_users AS (
SELECT user_id FROM orders WHERE order_date >= NOW() - INTERVAL '30 days'
),
-- Шаг 2: Сколько они потратили?
recent_revenue AS (
SELECT user_id, SUM(amount) as total
FROM orders
WHERE user_id IN (SELECT user_id FROM active_users)
GROUP BY user_id HAVING SUM(amount) > 1000
)
-- Финал: Собираем всё вместе
SELECT u.name, r.total
FROM users u
JOIN recent_revenue r ON u.id = r.user_id;

Каждая часть запроса — отдельный бизнес-этап. Тестировать и отлаживать такой код в разы проще.

Важный нюанс: Оптимизация (Ограда оптимизатора)
Это то, что нужно знать, чтобы не выстрелить себе в ногу.
* До Postgres 12: CTE всегда вычислялись целиком и сохранялись во временную память. Это был «барьер»: планировщик не мог заглянуть внутрь CTE и применить к нему фильтры. Если вы делали `WITH cte AS (SELECT * FROM huge_table) SELECT * FROM cte WHERE id = 1`, база сначала читала *всю* огромную таблицу, и только потом фильтровала.
* Postgres 12+: Теперь CTE по умолчанию «встраиваются» (inlined) в основной запрос, как обычный подзапрос. Оптимизатор умнеет.
* Когда использовать `MATERIALIZED`? Если вы используете тяжелый CTE несколько раз в запросе. Чтобы не считать его заново, напишите: `WITH heavy_calc AS MATERIALIZED (...)`.
* Когда использовать `NOT MATERIALIZED`? Если CTE большой, а вам из него нужна всего пара строк через `LIMIT`. Это заставит базу «протолкнуть» лимит внутрь CTE.

Рекурсивные CTE: Лифт по дереву
Нужно обойти дерево категорий или структуру компании (где есть `parent_id`)?
Раньше для этого писали циклы на бэкенде или хранимые процедуры. Теперь это 10 строк SQL:

WITH RECURSIVE org_tree AS (
-- База: Топ-менеджмент (у кого нет начальника)
SELECT id, name, parent_id, 1 AS level
FROM departments WHERE parent_id IS NULL
UNION ALL
-- Рекурсия: Ищем подчиненных
SELECT d.id, d.name, d.parent_id, t.level + 1
FROM departments d
JOIN org_tree t ON d.parent_id = t.id
)
SELECT * FROM org_tree ORDER BY level;

База сама будет ходить по ссылкам, пока не найдет всех сотрудников.

CTE — это не про скорость (хотя иногда помогает). Это про читаемость и поддерживаемость. Код, написанный через `WITH`, говорит: «Смотрите, я сначала считаю это, потом это, а в конце соединяю всё здесь».