PostgreSQL. Получить всю иерархию потомков и родителей в одну строку

Question

PostgreSQL. Получить всю иерархию потомков и родителей в одну строку

Рейтинг: 3Ответов: 1Опубликовано: 04.04.2023

Друзья, доброго времени суток!

Имею такую табличку, где

root - определяет, что все препараты с таким root входят в одну общую категорию.
lvl - определяет уровень внутри этой категории
lft, rgt - определяют границы подкатегории, т.е. у следующего lvl должен быть больше lft и меньше rgt.

Пример условия получения потомка:

t1.root = t2.root AND t1.lvl = t2.lvl + 1 AND t2.lft < t1.lft AND t2.rgt > t1.rgt

Мне нужно каким-то образом получить вот такую таблицу, где для каждого препарата будет вот такая иерархия. Рекурсивно не получилось добиться нужного мне результата, получается только получить двух потомков максимум (уровень владения SQL низко-средний).

Подскажите, пожалуйста, как получим желаемую таблицу?
Ниже код создания исходной таблицы для теста. В идеале предполагать, что кол-во уровней мы не знаем. Но если так не реализуемо, то пусть будет фиксированным (например в тесте 4 уровня максимум, в реальных данных на данный момент 7 уровней максимально бывает).

CREATE TABLE nested_sets (
    id int,
    name_ru varchar(255) NOT NULL,
    root int,   
    lft int,
    rgt int,
    lvl int
);

    INSERT INTO nested_sets (id, name_ru, root, lft, rgt, lvl) VALUES
        (7,   'Лекарственные средства',                                     7,  1,  6,  0),
        (8,   '09 Аромакосметика',                                          7,  2,  5,  1),
        (9,   '9.1. Эфирные и прочие масла',                                7,  3,  4,  2),
        (13,  '14 Прочее',                                                  12, 2,  3,  1),
        (679, '04 Средства по уходу за слизистыми оболочками',              12, 4,  9,  1),
        (680, '4.1. Средства для ухода за полостью рта',                    12, 5,  8,  2),
        (681, '4.1.2. Ополаскиватели для полости рта',                      12, 6,  7,  3),
        (682, '08 Мыла, моющие средства - Товары народного потребления',    12, 10, 11, 1),
        (12,  'Товары народного потребления',                               12, 1,  14, 0),
        (683, '13 Аксессуары',                                              12, 12, 13, 1);

Спасибо всем откликнувшимся :-)

Дополнение к вопросу:

Вариант от Akina, после пивота получаю следующие значения.

Но должно быть так (на примере root=7): все у кого root=7 изначально -> первый lvl будет всегда 7. Второй lvl будет пустым у первой строки, т.к. она "корень дерева" у нее нет второго lvl, у второй строки и третьей строки - 8. Третий lvl заполнен только у третьей строки, поскольку у второй нет третьего lvl.

Т.е. для root 7 результат должен выглядеть так:

Что не так делаю?

sql postgresql рекурсия

Источник: Stack Overflow на русском

Answer 1

▲ 1

Вот половина решения. Превращаем nested set в обычное parent-child дерево, где присутствует полный путь для любого узла:

SELECT t1.id, t1.name_ru, t1.root, t2.id root_id, 
  ROW_NUMBER() OVER (PARTITION BY t2.id ORDER BY t1.lft) rn
FROM nested_sets t1
JOIN nested_sets t2 ON t1.lft <= t2.lft AND t1.rgt >= t2.rgt
ORDER BY root_id, rn;

fiddle

А дальше обычный pivot - разматывай каждую отдельную группу по root_id по колонкам.

PostgreSQL. Получить всю иерархию потомков и родителей в одну строку

Ответы