CTE: конструкция WITH для читаемости

CTE (WITH) — именованный временный результат, делающий сложные запросы читаемыми.

CTE (Common Table Expression, конструкция WITH) — это именованный подзапрос в начале запроса, на который потом можно ссылаться, как на таблицу.

Зачем нужны CTE

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

От вложенности к читаемости

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

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount INTEGER);
INSERT INTO orders (customer, amount) VALUES
    ('Аня',500), ('Аня',300), ('Борис',900), ('Вера',100), ('Вера',100);

WITH totals AS (                       -- шаг 1: траты по каждому клиенту
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT customer, total
FROM totals
WHERE total > (SELECT AVG(total) FROM totals)   -- шаг 2: выше средней
ORDER BY total DESC;

Вывод:

Борис|900
Аня|800

CTE totals вычислен один раз, и мы дважды на него ссылаемся: в основном SELECT и в подзапросе со средним. Траты: Аня 800, Борис 900, Вера 200. Средняя из этих троек ≈ 633, выше неё — Борис и Аня.

Несколько CTE подряд

CTE можно объявить несколько, через запятую, и каждый следующий может ссылаться на предыдущий — получается конвейер шагов:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount INTEGER);
INSERT INTO orders (customer, amount) VALUES
    ('Аня',500), ('Аня',300), ('Борис',900), ('Вера',100);

WITH
totals AS (
    SELECT customer, SUM(amount) AS total FROM orders GROUP BY customer
),
ranked AS (
    SELECT customer, total,
           CASE WHEN total >= 800 THEN 'VIP' ELSE 'обычный' END AS tier
    FROM totals
)
SELECT customer, total, tier
FROM ranked
ORDER BY total DESC;

Вывод:

Борис|900|VIP
Аня|800|VIP
Вера|100|обычный

Первый CTE считает суммы, второй навешивает на них метку. Каждый шаг читается отдельно — в этом главная ценность CTE на собеседовании отвечают так: «CTE не столько про скорость, сколько про читаемость и переиспользование».

CTE vs подзапрос

Функционально CTE часто эквивалентен подзапросу в FROM. Разница — в читаемости и в том, что на один CTE можно сослаться несколько раз, не дублируя код. Рекурсивные CTE (WITH RECURSIVE) умеют даже обходить деревья и графы, но это уже отдельная большая тема.

Итог

CTE (WITH) — именованный промежуточный результат в начале запроса.
Делает вложенные запросы линейными и читаемыми, позволяет ссылаться на результат несколько раз.
CTE — в первую очередь про читаемость и переиспользование, а не про скорость.