Как лучше подсчитать данные для каждого узла (включая вложения) дерева?

Ссылка скопирована
PostgreSQL
1 ответ

По вводным: есть таблица описывающая дерево

Create Table tree(id int, parent_id int, name string(10));

Каким способом при использовании WITH RECURSIVE подсчитать кол-во дочерних узлов (в том числе вложенных)?
Сейчас ситуация такая: при попытке обратится к данным, пишет что подзапросы не поддерживаются.

Нужно понять: зЫ может кто знает способ в посTelegramресе создавать временные таблицы для расчетов, как MS sql server?

Нужно решить такую задачу?

Опишите проблему, и специалист поможет с настройкой, исправлением ошибки или доработкой сайта. Подберём понятный план работ без лишней переписки.

Заказать помощь
Лучший ответ
1
Кирилл JS Ответ

В PostgreSQL количество всех вложенных дочерних узлов удобно считать через WITH RECURSIVE, но обычно это делается не “подзапросом для каждой строки”, а построением таблицы связей ancestor → descendant. Тогда для каждого узла можно посчитать всех потомков одним GROUP BY.

Сначала исправим DDL: в PostgreSQL нет string(10), используйте varchar(10) или text.

CREATE TABLE tree (
  id INT PRIMARY KEY,
  parent_id INT REFERENCES tree(id),
  name text
);

CREATE TABLE tree ( id int PRIMARY KEY, parent_id int REFERENCES tree(id), name text );

Запрос для подсчета всех потомков:

WITH RECURSIVE rel AS (
  SELECT
    id AS ancestor_id,
    id AS descendant_id,
    parent_id
  FROM tree
 
  UNION ALL
 
  SELECT
    rel.ancestor_id,
    t.id AS descendant_id,
    t.parent_id
  FROM rel
  JOIN tree t ON t.parent_id = rel.descendant_id
)
SELECT
  t.id,
  t.name,
  COUNT(rel.descendant_id) - 1 AS descendants_count
FROM tree t
JOIN rel ON rel.ancestor_id = t.id
GROUP BY t.id, t.name
ORDER BY t.id;

WITH RECURSIVE rel AS ( SELECT id AS ancestor_id, id AS descendant_id, parent_id FROM tree UNION ALL SELECT rel.ancestor_id, t.id AS descendant_id, t.parent_id FROM rel JOIN tree t ON t.parent_id = rel.descendant_id ) SELECT t.id, t.name, COUNT(rel.descendant_id) - 1 AS descendants_count FROM tree t JOIN rel ON rel.ancestor_id = t.id GROUP BY t.id, t.name ORDER BY t.id;

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

SELECT parent_id, COUNT(*)
FROM tree
GROUP BY parent_id;

SELECT parent_id, COUNT(*) FROM tree GROUP BY parent_id;

Временные таблицы в PostgreSQL есть:

CREATE TEMP TABLE tmp_tree_counts AS
SELECT ...;

CREATE TEMP TABLE tmp_tree_counts AS SELECT ...;

Они живут в рамках текущей сессии. Но для обычного расчета дерева лучше сначала попробовать CTE. Если дерево большое, добавьте индекс на parent_id:

CREATE INDEX tree_parent_id_idx ON tree(parent_id);

CREATE INDEX tree_parent_id_idx ON tree(parent_id);

Если появляется ошибка про неподдерживаемые подзапросы, скорее всего, подзапрос был вставлен в место, где PostgreSQL его не принимает, или вы пытались обращаться к рекурсивному CTE из вложенного подзапроса неправильно. Стройте рекурсивный набор один раз, а потом агрегируйте его внешним SELECT. Это проще и быстрее.

Другие ответы (0)

Пока нет других ответов. Будьте первым, кто поможет автору.

Ответить на вопрос

комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Вам также может быть интересно

VPN Как правильно настроить vless для Android TV? 1 ответ Pyrogram Как правильно зарегистрировать юзер бота в Telegram? 1 ответ печатные-платы Как заставить запускаться программу M3.exe от компании Hanxing AOI в инспекционной машине на Windows 7 Pro? 1 ответ Figma Баг с переносом слов в Figma? 1 ответ