CTE и рекурсивные запросы в SQL: полный гайд с примерами

Эта статья — практический разбор: что такое CTE, когда их использовать, как писать рекурсивные запросы для иерархий и временных рядов, и какие задачи на CTE дают на собесах в Яндекс, Авито, Ozon.

Что такое CTE — простыми словами

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

WITH active_users AS (
    SELECT user_id, MAX(last_seen_at) AS last_active
    FROM sessions
    WHERE last_seen_at >= CURRENT_DATE - INTERVAL '7 days'
    GROUP BY user_id
)
SELECT u.email, a.last_active
FROM active_users a
JOIN users u ON u.id = a.user_id;

То же самое можно написать через подзапрос — но CTE даёт три преимущества:

• Читаемость: каждый блок — отдельная мысль с именем
• Переиспользование: можно ссылаться на CTE несколько раз в основном запросе
• Отладка: SELECT * FROM active_users — и видишь промежуточный результат

Multiple CTE — главная сила

Можно цеплять CTE через запятую — каждый последующий видит предыдущие. Это превращает гигантский запрос в pipeline.

WITH
  -- 1. Активные за неделю
  active_users AS (
    SELECT user_id FROM sessions
    WHERE ts >= CURRENT_DATE - 7
    GROUP BY user_id
  ),
  -- 2. Их выручка
  revenue AS (
    SELECT user_id, SUM(amount) AS total
    FROM payments
    WHERE user_id IN (SELECT user_id FROM active_users)
    GROUP BY user_id
  ),
  -- 3. Топ-10
  top_users AS (
    SELECT user_id, total
    FROM revenue
    ORDER BY total DESC
    LIMIT 10
  )
SELECT u.email, t.total
FROM top_users t
JOIN users u ON u.id = t.user_id;

Каждый шаг — самостоятельный, читается за 5 секунд. Отладка: закомментируй финальный SELECT, напиши SELECT * FROM revenue — увидишь промежуточный результат.

Рекурсивные CTE — для иерархий

Это уровень выше. WITH RECURSIVE позволяет ссылаться на сам CTE внутри его определения. Используется когда нужно пройти по дереву или сгенерировать последовательность.

Кейс 1: организационная структура

WITH RECURSIVE org_tree AS (
    -- Anchor: топ-уровень (CEO, нет manager_id)
    SELECT id, name, manager_id, 1 AS level, name AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive part: подчинённые предыдущего уровня
    SELECT e.id, e.name, e.manager_id, t.level + 1, t.path || ' → ' || e.name
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT level, name, path FROM org_tree
ORDER BY path;

Структура рекурсивного CTE всегда одинаковая:

• Anchor — стартовая выборка (база рекурсии)
• UNION ALL — соединение с recursive часть
• Recursive — выборка ссылается на CTE по имени
• Termination — рекурсия останавливается когда recursive часть вернёт 0 строк

Кейс 2: дерево комментариев

WITH RECURSIVE comment_thread AS (
    SELECT id, parent_id, text, 0 AS depth
    FROM comments WHERE parent_id IS NULL AND post_id = 42

    UNION ALL

    SELECT c.id, c.parent_id, c.text, t.depth + 1
    FROM comments c
    JOIN comment_thread t ON c.parent_id = t.id
)
SELECT REPEAT('  ', depth) || text AS indented FROM comment_thread;

Рекурсия для временных рядов

Часто нужен календарь — все даты за период, чтобы JOIN-ить с разреженными данными и видеть нули в дни без событий.

WITH RECURSIVE date_range AS (
    SELECT DATE '2026-01-01' AS day
    UNION ALL
    SELECT day + INTERVAL '1 day'
    FROM date_range
    WHERE day < DATE '2026-04-30'
)
SELECT
    d.day,
    COALESCE(SUM(e.revenue), 0) AS revenue
FROM date_range d
LEFT JOIN events e ON DATE(e.ts) = d.day
GROUP BY d.day
ORDER BY d.day;

В PostgreSQL для этого есть generate_series('2026-01-01'::date, '2026-04-30'::date, '1 day') — короче. В MySQL/SQLite generate_series нет — рекурсивный CTE единственный путь.

CTE vs subquery vs temp table — что выбрать

В современных БД (PostgreSQL 12+, ClickHouse, BigQuery) CTE оптимизируются inline — производительность как у подзапроса.

Что спросят на собеседовании

Задача-классика: иерархия категорий

«Дана таблица categories(id, parent_id, name). Найди все подкатегории "Электроника" на любой глубине».

WITH RECURSIVE subcats AS (
    SELECT id, parent_id, name FROM categories WHERE name = 'Электроника'
    UNION ALL
    SELECT c.id, c.parent_id, c.name
    FROM categories c
    JOIN subcats s ON c.parent_id = s.id
)
SELECT * FROM subcats WHERE id != (SELECT id FROM categories WHERE name = 'Электроника');

Задача: заполнить пропуски в датах

«Дана таблица daily_sales(day, revenue) — есть пропуски. Сгенерируй полный календарь и подставь 0 для пропущенных дней».

Решается рекурсивным CTE с date_range и LEFT JOIN — пример выше.

Задача: путь к корню в дереве

«Дано дерево, найди полный путь от листа до корня».

WITH RECURSIVE path_up AS (
    SELECT id, parent_id, name, name AS path FROM nodes WHERE id = 42
    UNION ALL
    SELECT n.id, n.parent_id, n.name, n.name || ' → ' || p.path
    FROM nodes n
    JOIN path_up p ON p.parent_id = n.id
)
SELECT path FROM path_up WHERE parent_id IS NULL;

Связанные материалы

• SQL-тренажёр: задачи на CTE — реши 325 задач с автопроверкой
• Оконные функции SQL — комбинируется с CTE на сложных аналитических задачах
• ClickHouse для аналитика — есть нюансы по CTE в OLAP
• Конспекты SQL — теория по агрегации, JOIN, оконным функциям
• AI-собеседование — потренируй разбор задач с обратной связью

Открой SQL-тренажёр, найди задачу с тегом «иерархия» или «когорты» и попробуй переписать через CTE — увидишь как код становится в 2 раза короче и в 10 раз понятнее.