SQL CTE: обобщённые табличные выражения и предложение WITH

Урок 6.4 · Время чтения: ~8 мин · Задачи для практики ↓

CTE (обобщённое табличное выражение) в SQL - это именованный временный набор строк, который задаётся с помощью WITH и затем используется в основном запросе как таблица. В этом уроке вы изучите синтаксис CTE, сравните CTE с подзапросом, научитесь объединять несколько CTE в цепочку и строить многошаговый анализ, который остаётся легко читаемым.

В предыдущих уроках вы писали подзапросы внутри FROM и WHERE. Они работают, но по мере усложнения логики вложенные подзапросы становится трудно читать: приходится начинать с самого внутреннего запроса и двигаться наружу. CTE позволяет дать каждому шагу имя и записать шаги сверху вниз - в том порядке, в котором вы о них думаете.

SQL CTE: вложенный подзапрос читается изнутри наружу, а предложение WITH - сверху вниз; цепочка CTE и один CTE, на который ссылаются дважды

Что такое CTE в SQL?

Обобщённое табличное выражение - это именованный результат запроса, заданный в начале оператора. Он существует только во время выполнения этого оператора: в базе данных ничего не сохраняется, и другие запросы его не видят.

CTE можно воспринимать как именованный подзапрос. Основные преимущества:

Читаемость: у каждого шага есть понятное имя.
Повторное использование: основной запрос может ссылаться на один и тот же CTE несколько раз.
Модульность: сложный запрос распадается на небольшие логические шаги.
Отладка: каждый CTE можно запустить и проверить отдельно.

Базовый синтаксис

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

WITH вводит CTE.
cte_name - имя, которое будет использовать основной запрос.
Запрос в скобках определяет строки CTE.
Следующий за ним основной запрос работает с cte_name как с обычной таблицей.

Ваш первый CTE

Найдём клиентов, которые в сумме потратили больше $100:

WITH customer_spending AS (
    SELECT customer_id,
           SUM(amount) AS total_spent,
           COUNT(*) AS payment_count,
           AVG(amount) AS avg_payment
    FROM payment
    GROUP BY customer_id
)
SELECT customer_id, total_spent, payment_count, avg_payment
FROM customer_spending
WHERE total_spent > 100
ORDER BY total_spent DESC;

Результат: клиенты с суммой платежей больше $100, с количеством платежей и средним платежом; первыми идут те, кто потратил больше всех.

CTE вычисляет траты по каждому клиенту, а основной запрос только фильтрует и сортирует. Имя customer_spending сообщает читателю, что это за промежуточные данные, ещё до того, как он прочитает, как они вычисляются.

CTE и подзапрос

Вот тот же запрос, записанный с подзапросом во FROM:

SELECT customer_id, total_spent, payment_count, avg_payment
FROM (
    SELECT customer_id,
           SUM(amount) AS total_spent,
           COUNT(*) AS payment_count,
           AVG(amount) AS avg_payment
    FROM payment
    GROUP BY customer_id
) AS spending_data
WHERE total_spent > 100
ORDER BY total_spent DESC;

Оба запроса возвращают одинаковые строки. Разница в том, как они читаются:

Аспект	CTE	Подзапрос во `FROM`
Порядок чтения	Сверху вниз, шаг за шагом	Изнутри наружу
Имена	У каждого шага понятное имя	Псевдоним часто даётся формально
Повторное использование	Одно определение, много ссылок	Подзапрос повторяется при каждом использовании
Отладка	Каждый CTE запускается отдельно	Вложенный уровень трудно изолировать

Для одного короткого шага подзапрос вполне подходит. Когда в логике два шага и больше или один результат нужен дважды, CTE обычно понятнее.

Несколько CTE в одном запросе

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

WITH customer_spending AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM payment
    GROUP BY customer_id
),
high_spenders AS (
    SELECT customer_id, total_spent
    FROM customer_spending
    WHERE total_spent > 150
)
SELECT c.customer_id,
       CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
       c.email,
       hs.total_spent
FROM high_spenders AS hs
JOIN customer AS c ON c.customer_id = hs.customer_id
ORDER BY hs.total_spent DESC;

Результат: имена и email клиентов, потративших больше $150; первыми идут те, кто потратил больше всех.

Запрос читается как короткий план:

customer_spending суммирует платежи по каждому клиенту.
high_spenders оставляет только клиентов с суммой больше $150.
Основной запрос добавляет имена и email из customer.

Обратите внимание: WITH пишется только один раз, перед первым CTE.

Несколько ссылок на один CTE

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

WITH monthly_sales AS (
    SELECT CAST(DATE_FORMAT(payment_date, '%Y-%m-01') AS DATE) AS month_start,
           SUM(amount) AS monthly_total
    FROM payment
    GROUP BY month_start
)
SELECT cur.month_start,
       cur.monthly_total AS current_sales,
       prev.monthly_total AS previous_sales,
       ROUND((cur.monthly_total - prev.monthly_total) / prev.monthly_total * 100, 2) AS percent_change
FROM monthly_sales AS cur
LEFT JOIN monthly_sales AS prev
       ON prev.month_start = cur.month_start - INTERVAL 1 MONTH
ORDER BY cur.month_start;

Результат: выручка за каждый месяц рядом с выручкой за предыдущий месяц и изменение в процентах. Если в предыдущем месяце платежей не было (май 2005 года - первый месяц - и февраль 2006 года, после перерыва в данных), столбцы сравнения равны NULL.

monthly_sales определён один раз и использован дважды - как cur и как prev. С подзапросами пришлось бы написать одну и ту же агрегацию дважды и следить за синхронностью обеих копий.

Практический пример: последняя аренда каждого клиента

Частая задача - найти самое недавнее событие для каждого клиента. С CTE она распадается на два понятных шага: найти последнюю дату, затем добавить детали.

WITH last_rental AS (
    SELECT customer_id, MAX(rental_date) AS last_rental_date
    FROM rental
    GROUP BY customer_id
)
SELECT c.customer_id,
       CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
       lr.last_rental_date
FROM customer AS c
LEFT JOIN last_rental AS lr ON lr.customer_id = c.customer_id
ORDER BY lr.last_rental_date
LIMIT 20;

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

В модуле 7 вы увидите, как оконные функции решают задачи «последняя строка в группе», когда нужна вся строка, а не только дата.

Практический пример: когортный анализ

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

WITH customer_cohort AS (
    SELECT customer_id,
           CAST(DATE_FORMAT(MIN(rental_date), '%Y-%m-01') AS DATE) AS cohort_month
    FROM rental
    GROUP BY customer_id
),
monthly_activity AS (
    SELECT cc.cohort_month,
           CAST(DATE_FORMAT(r.rental_date, '%Y-%m-01') AS DATE) AS rental_month,
           r.customer_id
    FROM customer_cohort AS cc
    JOIN rental AS r ON r.customer_id = cc.customer_id
)
SELECT cohort_month,
       rental_month,
       COUNT(DISTINCT customer_id) AS active_customers,
       COUNT(*) AS rentals
FROM monthly_activity
GROUP BY cohort_month, rental_month
ORDER BY cohort_month, rental_month;

Результат: для каждой когорты (месяца первой аренды) - сколько её клиентов брали фильмы в каждом следующем месяце и сколько аренд они сделали.

customer_cohort относит каждого клиента к месяцу его первой аренды.
monthly_activity добавляет когорту к каждой аренде.
Основной запрос считает активных клиентов и аренды по когортам и месяцам.

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

CTE - это прежде всего способ организовать запрос. В большинстве современных СУБД простой CTE работает так же, как эквивалентный подзапрос, потому что оптимизатор встраивает его в основной запрос.

Между СУБД есть различия:

PostgreSQL до версии 12 всегда материализовал CTE (вычислял его один раз во временный результат). Начиная с версии 12 CTE, на который ссылаются один раз, встраивается; AS MATERIALIZED и AS NOT MATERIALIZED позволяют выбирать.
MySQL (8.0+) и MariaDB (10.2+) могут как встроить CTE в основной запрос, так и материализовать его. CTE, на который ссылаются несколько раз, обычно вычисляется один раз.

Если запрос с CTE работает медленно, проверьте его план выполнения с помощью EXPLAIN, как для любого другого запроса.

Ключевые выводы:

CTE - именованный временный набор строк, заданный через WITH и доступный только в пределах одного оператора.
CTE позволяют записывать многошаговую логику сверху вниз, давая каждому шагу понятное имя.
Несколько CTE разделяются запятыми, и каждый может использовать CTE, заданные до него.
На один CTE можно сослаться в основном запросе несколько раз, не повторяя один и тот же подзапрос.
Для одного короткого шага подойдёт подзапрос; для многошаговой логики CTE обычно читается лучше.
Производительность обычно такая же, как у подзапросов, но правила материализации различаются в разных СУБД.

Часто задаваемые вопросы

Чем CTE отличается от подзапроса?

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

CTE быстрее подзапроса?

Обычно ни то ни другое не быстрее: большинство оптимизаторов обрабатывают простой CTE и эквивалентный подзапрос одинаково. Разница появляется, когда СУБД материализует CTE: это может помочь, если результат используется повторно, и навредить, если основной запрос сильно его фильтрует. Проверяйте план с помощью EXPLAIN.

CTE - то же самое, что временная таблица?

Нет. CTE существует только во время одного оператора и не сохраняется. Временная таблица создаётся явно, живёт до конца сеанса, может использоваться многими запросами и может иметь индексы.

Какие СУБД поддерживают предложение WITH?

CTE поддерживают PostgreSQL, SQL Server, Oracle, SQLite (3.8.3+), MySQL (8.0+) и MariaDB (10.2+).