Подсчет дочерних узлов в PostgreSQL с WITH RECURSIVE

Работа с иерархическими данными в PostgreSQL часто требует подсчёта всех дочерних узлов, включая вложенные. Если у вас есть таблица tree(id, parent_id, name), рекурсивные запросы - мощный инструмент, но новички сталкиваются с ограничениями подзапросов. Разберём, как правильно использовать WITH RECURSIVE для подсчёта потомков и обойти типичные ошибки.

Проблема: подзапросы не поддерживаются

При попытке обратиться к данным внутри рекурсивной части запроса PostgreSQL может выдавать ошибку, что подзапросы не поддерживаются. Это связано с тем, что рекурсивный CTE (Common Table Expression) не может напрямую ссылаться на самого себя во вложенных запросах. Вместо этого нужно использовать агрегацию на финальном шаге.

Решение: подсчёт дочерних узлов через рекурсивный CTE

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

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

Пример запроса:

WITH RECURSIVE descendants AS (
  SELECT id, parent_id, id AS root_id
  FROM tree
  UNION ALL
  SELECT t.id, t.parent_id, d.root_id
  FROM tree t
  JOIN descendants d ON t.parent_id = d.id
)
SELECT root_id AS node_id, COUNT(*) - 1 AS total_children
FROM descendants
GROUP BY root_id
ORDER BY node_id;

Этот запрос возвращает для каждого узла количество всех его потомков (исключая сам узел).

Альтернативный способ: сперва получить путь, затем подсчитать

Если вам нужно не только количество, но и иерархия, можно сначала собрать все пути с помощью рекурсии, а затем использовать оконные функции или подзапросы уже вне рекурсивной части:

WITH RECURSIVE tree_path AS (
  SELECT id, parent_id, name, 1 AS level
  FROM tree
  WHERE parent_id IS NULL
  UNION ALL
  SELECT t.id, t.parent_id, t.name, tp.level + 1
  FROM tree t
  JOIN tree_path tp ON t.parent_id = tp.id
)
SELECT id, name, (
  SELECT COUNT(*) FROM tree_path tp2 WHERE tp2.parent_id = tp.id
) AS direct_children
FROM tree_path tp;

Этот вариант показывает только прямых потомков, но легко модифицируется для подсчёта всех вложенных.

Создание временных таблиц в PostgreSQL

В отличие от MS SQL Server, в PostgreSQL временные таблицы создаются с помощью CREATE TEMPORARY TABLE или CREATE TEMP TABLE. Они существуют в рамках сессии и автоматически удаляются при её завершении. Пример:

CREATE TEMP TABLE temp_tree AS
WITH RECURSIVE ... SELECT ...;

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

Типичные ошибки и их решение

• Ошибка: «подзапросы не поддерживаются» - не пытайтесь ссылаться на рекурсивное имя внутри подзапроса в рекурсивной части. Выносите агрегацию наружу.
• Ошибка: бесконечная рекурсия - убедитесь, что в дереве нет циклов. Добавьте условие WHERE id != parent_id и ограничьте глубину.
• Ошибка: неправильный подсчёт - проверьте, что вы исключаете сам узел из количества потомков (COUNT(*) - 1).

Используйте эти методы для эффективной работы с иерархическими структурами в PostgreSQL. Если у вас остались вопросы, обратитесь к официальной документации по WITH RECURSIVE.