Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Составление SQL-запросов > Update с использованием рекурсивного запроса

Автор: polin11 10.6.2019, 15:21

Использую postgresql. Есть иерархическая таблица, поле с иерархией parent_id, нужно заполнить level - уровень вложенности, hier - строка с названиями родителями (исключая корень таблицы), root - значения корня иерархии (если у предка в поле new_root = true, то у потомков в поле root должен быть id этого предка).
Пример можно посмотреть https://www.db-fiddle.com/f/ezdc4n5ivhHrHk6rDJgZUo/0

Код


CREATE TABLE geo (
    id int not null primary key, 
    parent_id int references geo(id),  
    name varchar(1000),
     level int,
     hier varchar(1000),
    root int,
    new_root bool     
);

INSERT INTO geo 
(id, parent_id, name, level, hier,root, new_root) 
VALUES 
(1, null, 'Планета Земля', null, null,null, null),
(2, 1, 'Континент Евразия', null, null,null, null),
(3, 1, 'Континент Северная Америка', null, null,null, null),
(4, 2, 'Европа', null, null,null, null),
(5, 4, 'Россия', null, null,null, true),
(6, 4, 'Германия', null, null,null, null),
(7, 5, 'Москва', null, null,null, null),
(8, 5, 'Санкт-Петербург', null, null,null, null),
(9, 6, 'Берлин', null, null,null, null);

Написал запрос:

Код


WITH RECURSIVE r AS (
   SELECT id, parent_id, name, 2 AS level, null AS hier, 1 as root
   FROM geo
   WHERE parent_id = 1

   UNION ALL

   SELECT geo.id, geo.parent_id, geo.name, r.level + 1 AS level,  case when r.hier is NULL then (select name from geo tmp where tmp.id=geo.parent_id limit 1) else  r.hier::text || '#' || (select name from geo tmp where tmp.id=geo.parent_id limit 1) end AS hier,
CASE WHEN (SELECT new_root FROM geo tmp WHERE tmp.id=geo.parent_id LIMIT 1) IS true 
   THEN (SELECT id FROM geo tmp WHERE tmp.id=geo.parent_id LIMIT 1)
   ELSE r.root END AS root
   FROM geo inner JOIN r ON (geo.parent_id = r.id)
),
T AS(
select id,  parent_id, name, 1 AS level, null::text AS hier, id as root
from geo
where id = 1
union
SELECT * FROM r
)
Update geo
set(level, hier, root) = (T.level, T.hier, T.root)
from T
where geo.id=T.id;
select *
from geo

Посоветуйте, как можно улучшить запрос???

Автор: Akina 10.6.2019, 16:59

Господи, подзапросы-то зачем???

Покажите требуемый вывод (непонятно, что должно получиться в поле hier...)

Автор: polin11 10.6.2019, 17:42

см. скрин, как это сделать без подзапросов, ведь hier формируется как скленые через # название всех родителей (кроме корня иерархии)?

Автор: Akina 10.6.2019, 22:24

Почему для ID=7 и ID=8 значение root равно 5, а для всех остальных 1?

Почему "Планета Земля" не включается в hier?

Что за поле new_root?

Автор: Zloxa 10.6.2019, 23:20

Цитата(polin11 @ 10.6.2019, 16:21

)

Посоветуйте, как можно улучшить запрос???

А что именно вас не устраивает в том запросе, который вы просите улучшить?

Автор: Zloxa 10.6.2019, 23:52

Код


with recursive
    r as 
    (
      select
        id, parent_id, name, 1 as level, null::text as hier, id as root, new_root
      from
        geo
      where
        parent_id is null
      union all
      select
        s.id                           as id,
        r.id                           as parent_id,
        s.name                         as name,
        r.level + 1                    as level,
        case
          when r.level = 1 then null
          when r.level = 2 then r.name
          else r.hier || '#' || r.name
        end                            as hier,
        case
          when r.new_root
          then r.id
          else r.root
        end                            as root,
        s.new_root                     as new_root
      from
        r   as r,
        geo as s
      where
        r.id = s.parent_id
    )
select * from r;

Код


id    parent_id    name                       level    hier                              root new_root
------------------------------------------------------------------------------------------------------
1     null         Планета Земля              1        null                              1    null
2     1            Континент Евразия          2        null                              1    null
3     1            Континент Северная Америка 2        null                              1    null
4     2            Европа                     3        Континент Евразия                 1    null
5     4            Россия                     4        Континент Евразия#Европа          1    true
6     4            Германия                   4        Континент Евразия#Европа          1    null
7     5            Москва                     5        Континент Евразия#Европа#Россия   5    null
8     5            Санкт-Петербург            5        Континент Евразия#Европа#Россия   5    null
9     6            Берлин                     5        Континент Евразия#Европа#Германия 1    null

Автор: polin11 11.6.2019, 04:02

Цитата(Akina @ 10.6.2019, 22:24)

Поле new_root - логическое поле, если значение True, то у всех потомком этого узла должно быть значение root = id этого узла, а не корня иерархии, поэтому для ID=7 и ID=8 значение root равно 5, а для всех остальных 1
Почему "Планета Земля" не включается в hier, потому что такое требование задачи

Добавлено через 5 минут и 2 секунды

Цитата(Zloxa @ 10.6.2019, 23:20)

Цитата(polin11 @ 10.6.2019, 16:21

)

Посоветуйте, как можно улучшить запрос???

А что именно вас не устраивает в том запросе, который вы просите улучшить?

1) рекурсивные запросы для меня пока темный лес
2) хочется отказаться от вложенных запросов в рекурсивной части
3) на больших таблицах, запрос выполняется в таком виде медленно

Автор: Zloxa 11.6.2019, 14:47

Цитата(polin11 @ 11.6.2019, 05:02

)

1) рекурсивные запросы для меня пока темный лес

При этом Вы не озвучили ни одного вопроса по механике работы рекурсивных запросов.
Чем мы можем Вам тут помочь?

Цитата(polin11 @ 11.6.2019, 05:02

)

2) хочется отказаться от вложенных запросов в рекурсивной части

Хочется... казалось бы какая нам *тут* разница чего *там* кому хочется?

Цитата(polin11 @ 11.6.2019, 05:02

)

3) на больших таблицах, запрос выполняется в таком виде медленно

выполняйте на маленьких. smile

Я к тому, что есть некие правила этики, которым, определенно стоит следовать когда Вы обращаетесь за *помощью*.
Сейчас, с моей стороны, Ваш вопрос выглядит как "Я вот тут наовнокодил, нате, гляньте, наведите порядок, исправьте, мне тут *хочется*".
Вы уж простите, но при таком отношении возникает острое желание Вас взаимно обмакнуть.

Однако, следует отдать Вам должное. Помимо неврнятных экзерсисов вы еще как-то попытались описать стоящую перед Вами задачу, подготовили тестовый набор данных, показали ожидаемый результат, подготовили фидл. Это, безусловно, делает Вам огромную честь.

По предложенному мною решению у Вас какие-то комментарии, вопросы, замечания? Хотя бы вежливое "спасибо", нет?

Автор: polin11 12.6.2019, 17:03

Я допустил неточность в описании результата, оказывается у корня иерархии Планета Земля поле root должно быть null, я ваш запрос подправил.
Ваш запрос намного логичнее и понятнее моего первоначального. Большое спасибо за помощь новичку в понимании языка SQL!!!

Код


with recursive
    r as 
    (  select id, parent_id, name, 1 as level, null::text as hier, null::int as root, new_root
      from geo
      where id = 1
      union all
      select  s.id as id, r.id as parent_id, s.name as name, r.level + 1 as level,
        case
          when r.level = 1 then null
          when r.level = 2 then r.name
          else r.hier || '#' || r.name
        end as hier,
        case
          when (r.new_root is true or r.root is null)  then r.id
          else r.root 
        end as root,
        s.new_root as new_root
      from
        r   as r,
        geo as s
      where
        r.id = s.parent_id
    )
select * from r;