CTE в SQL: как писать читаемые запросы с WITH

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

Открыть тренажёр → https://sqllab.ru/practice

Синтаксис

WITH название AS (
SELECT ...
)
SELECT *
FROM название;

Простой пример

Без CTE:

SELECT user_id, COUNT(*) AS orders_count
FROM (
SELECT user_id FROM orders WHERE status = 'completed'
) sub
GROUP BY user_id
HAVING COUNT(*) > 5;

С CTE:

WITH completed_orders AS (
SELECT user_id
FROM orders
WHERE status = 'completed'
)
SELECT user_id, COUNT(*) AS orders_count
FROM completed_orders
GROUP BY user_id
HAVING COUNT(*) > 5;

Результат одинаковый, но CTE легче читать и отлаживать.

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

WITH
-- Шаг 1: выручка по пользователям
user_revenue AS (
SELECT user_id, SUM(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY user_id
),
-- Шаг 2: перцентили
user_percentiles AS (
SELECT
user_id,
total,
NTILE(4) OVER (ORDER BY total DESC) AS quartile
FROM user_revenue
)
-- Финальный результат
SELECT quartile, COUNT(*) AS users, AVG(total) AS avg_revenue
FROM user_percentiles
GROUP BY quartile
ORDER BY quartile;

Каждый CTE — один логический шаг. Запрос читается как последовательность действий.

CTE vs подзапросы

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

WITH monthly_sales AS (
SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) AS revenue
FROM orders GROUP BY 1
)
SELECT
curr.month,
curr.revenue,
prev.revenue AS prev_revenue,
curr.revenue - prev.revenue AS growth
FROM monthly_sales curr
LEFT JOIN monthly_sales prev ON curr.month = prev.month + INTERVAL '1 month';

Рекурсивные CTE

Рекурсивный CTE ссылается сам на себя. Классический пример — иерархические данные.

-- Дерево категорий: id, parent_id, name
WITH RECURSIVE category_tree AS (
-- Базовый случай: корневые категории
SELECT id, name, parent_id, 0 AS depth, name AS path
FROM categories
WHERE parent_id IS NULL

UNION ALL

-- Рекурсивный шаг: дочерние категории
SELECT c.id, c.name, c.parent_id,
ct.depth + 1,
ct.path || ' > ' || c.name
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT depth, path
FROM category_tree
ORDER BY path;

Числовая последовательность

WITH RECURSIVE nums AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;
-- → 1, 2, 3, ..., 10

Цепочка событий

-- Все шаги воронки для пользователя
WITH RECURSIVE funnel AS (
SELECT user_id, event, created_at, 1 AS step
FROM events
WHERE event = 'visit'

UNION ALL

SELECT e.user_id, e.event, e.created_at, f.step + 1
FROM events e
JOIN funnel f ON e.user_id = f.user_id
AND e.created_at > f.created_at
WHERE f.step < 5
)
SELECT * FROM funnel ORDER BY user_id, step;

Типичные применения CTE

1. Разбить сложный запрос на шаги — каждый CTE = один логический шаг
2. Переиспользование подзапроса — написать один раз, использовать дважды
3. Иерархические данные — с RECURSIVE
4. Оконные функции в несколько этапов — сначала ранжировать, потом фильтровать

-- Фильтрация по оконной функции — без CTE не обойтись
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
FROM products
)
SELECT * FROM ranked WHERE rn = 1;

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

Тренажёр с реальными задачами — бесплатно и без регистрации

Открыть тренажёр → https://sqllab.ru/practice