Несколько CTE в одном запросе: цепочки WITH для начинающих

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

Если в обычном коде ты бы написал «обработали данные → отфильтровали → агрегировали → отсортировали», то и в SQL ты можешь написать то же самое через несколько CTE — и это будет читаться как обычный код, а не как гигантская формула.

Зачем нужно несколько CTE

Реальные запросы редко решаются «одним SELECT». Обычно это:

1. Подготовить набор данных (JOIN, фильтры).
2. Что-то посчитать (агрегаты).
3. Сравнить с эталоном или другой таблицей.
4. Отфильтровать по результатам.
5. Собрать финальный отчёт.

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

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

Два и больше CTE — через запятую:

WITH
  cte1 AS (
    SELECT ... FROM ...
  ),
  cte2 AS (
    SELECT ... FROM cte1 ...
  ),
  cte3 AS (
    SELECT ... FROM cte2 JOIN cte1 ON ...
  )
SELECT ... FROM cte3;

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

Пример: «топ-3 заказа каждого клиента»

Таблица orders: id, customer_id, amount, created_at.

Цель: для каждого клиента выбрать его 3 самых дорогих заказа, потом отчёт «клиент → его средний чек среди топ-3».

WITH
  ranked AS (
    SELECT
      customer_id,
      amount,
      ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
    FROM orders
  ),
  top3 AS (
    SELECT customer_id, amount FROM ranked WHERE rn <= 3
  ),
  per_customer AS (
    SELECT customer_id, AVG(amount) AS avg_top3
    FROM top3
    GROUP BY customer_id
  )
SELECT
  c.id,
  c.name,
  pc.avg_top3
FROM customers c
JOIN per_customer pc ON pc.customer_id = c.id
ORDER BY pc.avg_top3 DESC;

Каждый CTE делает одну вещь:

• ranked — пронумеровать заказы каждого клиента по убыванию суммы.
• top3 — оставить только первые три.
• per_customer — посчитать среднее по топ-3 на каждого клиента.

Финальный SELECT — соединить с таблицей customers ради имени и отдать.

Без CTE то же самое выглядело бы как четырёхэтажный подзапрос в FROM, и читать его было бы очень больно.

Переиспользование одного CTE

Если результат одного CTE нужен несколько раз в основном запросе — это самый сильный аргумент в пользу CTE:

WITH
  monthly_revenue AS (
    SELECT
      DATE_TRUNC('month', created_at) AS month,
      SUM(amount) AS total
    FROM orders
    WHERE status = 'paid'
    GROUP BY 1
  )
SELECT
  this_month.month,
  this_month.total,
  last_month.total AS prev_total,
  (this_month.total - last_month.total) AS diff
FROM monthly_revenue this_month
LEFT JOIN monthly_revenue last_month
  ON last_month.month = this_month.month - INTERVAL '1 month'
ORDER BY this_month.month;

monthly_revenue использован дважды — как «текущий месяц» и как «прошлый месяц», через self-join. Без CTE пришлось бы писать одну и ту же агрегацию два раза.

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

Это уже продвинутая тема, но важная — без рекурсивных CTE невозможно обрабатывать иерархии (дерево комментариев, оргструктура, граф).

WITH RECURSIVE org_chart AS (
  -- Базовый случай: топ-менеджер (без manager_id)
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Рекурсия: каждый, у кого manager_id = id из предыдущего шага
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;

Логика:

1. Базовый случай — выбираем тех, у кого нет начальника (топ-менеджер).
2. Рекурсивный шаг — добавляем тех, чей начальник был в предыдущем результате.
3. UNION ALL соединяет все шаги.

Postgres повторяет шаг, пока не перестанут добавляться новые строки. На большой оргструктуре получаешь полный «дерево» с уровнями.

Ключевое слово — WITH RECURSIVE. Без RECURSIVE Postgres не разрешит ссылаться внутри CTE на саму себя.

Цепочки vs одна большая формула

Сравни:

-- Без CTE — формула с тремя уровнями подзапросов
SELECT name, avg_top3
FROM (
  SELECT customer_id, AVG(amount) AS avg_top3
  FROM (
    SELECT customer_id, amount
    FROM (
      SELECT customer_id, amount,
             ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
      FROM orders
    ) t1
    WHERE rn <= 3
  ) t2
  GROUP BY customer_id
) t3
JOIN customers c ON c.id = t3.customer_id;

-- С CTE — последовательность шагов с именами
WITH
  ranked AS (...),
  top3 AS (...),
  per_customer AS (...)
SELECT c.name, pc.avg_top3
FROM customers c JOIN per_customer pc ON pc.customer_id = c.id;

Те же таблицы, тот же план, но второй вариант читаемый. На собесах за такой стиль ставят высокий балл — видно, что человек думает про поддерживаемость.

Частые ошибки новичков

1. WITH повторили перед каждым CTE. Должно быть одно WITH в начале, потом CTE через запятую:

-- НЕПРАВИЛЬНО
WITH a AS (...) WITH b AS (...) SELECT ...

-- ПРАВИЛЬНО
WITH a AS (...), b AS (...) SELECT ...

2. Циклические ссылки между CTE. WITH a AS (SELECT * FROM b), b AS (SELECT * FROM a) — ошибка. Каждое CTE может ссылаться только на предыдущие в списке.

3. Забыли RECURSIVE для рекурсивного CTE. WITH x AS (... UNION ALL SELECT FROM x) — ошибка. Рекурсивные CTE требуют явного ключевого слова RECURSIVE.

4. Бесконечная рекурсия. В рекурсивном CTE если базовый случай не уменьшает множество — Postgres будет крутить вечно. Защита через LIMIT в финальном SELECT или явный counter в CTE с условием WHERE level < 100.

5. Производительность от MATERIALIZED. В Postgres 12+ обычные CTE inline'ятся оптимизатором. Иногда наоборот хочешь зафиксировать промежуточный результат — пиши WITH x AS MATERIALIZED (...). Полезно когда CTE используется несколько раз и его дешевле посчитать один раз.

6. CTE для трёх строк. Если запрос помещается в один SELECT — не оборачивай в CTE «для красоты». CTE окупаются на сложных запросах.

Мини-резюме

• Несколько CTE — через запятую: WITH a AS (...), b AS (...) SELECT ....
• Каждое следующее CTE может ссылаться на предыдущие.
• Один и тот же CTE можно использовать несколько раз в основном запросе.
• Для иерархий — WITH RECURSIVE + UNION ALL: базовый случай + рекурсивный шаг.
• В современном Postgres CTE по умолчанию inline'ится. Если хочешь принудительную материализацию — MATERIALIZED.
• Цепочки CTE превращают «гигантский подзапрос» в «серию шагов» — это и есть главный win.