Модераторы: skyboy
  

Поиск:

Ответ в темуСоздание новой темы Создание опроса
> Update с использованием рекурсивного запроса 
:(
    Опции темы
polin11
Дата 10.6.2019, 15:21 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Шустрый
*


Профиль
Группа: Участник
Сообщений: 122
Регистрация: 6.6.2015

Репутация: нет
Всего: нет



Использую 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


Посоветуйте, как можно улучшить запрос???
PM MAIL   Вверх
Akina
Дата 10.6.2019, 16:59 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Советчик
****


Профиль
Группа: Модератор
Сообщений: 20570
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 453



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

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


--------------------
 О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума.

PM MAIL WWW ICQ Jabber   Вверх
polin11
Дата 10.6.2019, 17:42 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Шустрый
*


Профиль
Группа: Участник
Сообщений: 122
Регистрация: 6.6.2015

Репутация: нет
Всего: нет



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

Это сообщение отредактировал(а) polin11 - 10.6.2019, 17:45

Присоединённый файл ( Кол-во скачиваний: 12 )
Присоединённый файл  ИТОГ.png 26,68 Kb
PM MAIL   Вверх
Akina
Дата 10.6.2019, 22:24 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Советчик
****


Профиль
Группа: Модератор
Сообщений: 20570
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 453



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

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

Что за поле new_root?


--------------------
 О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума.

PM MAIL WWW ICQ Jabber   Вверх
Zloxa
Дата 10.6.2019, 23:20 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Чо?
****


Профиль
Группа: Завсегдатай
Сообщений: 3470
Регистрация: 12.9.2008

Репутация: 53
Всего: 161



Цитата(polin11 @  10.6.2019,  16:21 Найти цитируемый пост)
Посоветуйте, как можно улучшить запрос??? 

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


--------------------
Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile
PM   Вверх
Zloxa
Дата 10.6.2019, 23:52 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Чо?
****


Профиль
Группа: Завсегдатай
Сообщений: 3470
Регистрация: 12.9.2008

Репутация: 53
Всего: 161



Код

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


Это сообщение отредактировал(а) Zloxa - 11.6.2019, 00:07


--------------------
Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile
PM   Вверх
polin11
Дата 11.6.2019, 04:02 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Шустрый
*


Профиль
Группа: Участник
Сообщений: 122
Регистрация: 6.6.2015

Репутация: нет
Всего: нет



Цитата(Akina @ 10.6.2019,  22:24)
Почему для ID=7 и ID=8 значение root равно 5, а для всех остальных 1?

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

Что за поле new_root?

Поле 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) на больших таблицах, запрос выполняется в таком виде медленно

PM MAIL   Вверх
Zloxa
Дата 11.6.2019, 14:47 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Чо?
****


Профиль
Группа: Завсегдатай
Сообщений: 3470
Регистрация: 12.9.2008

Репутация: 53
Всего: 161



Цитата(polin11 @  11.6.2019,  05:02 Найти цитируемый пост)
1) рекурсивные запросы для меня пока темный лес

При этом Вы не озвучили ни одного вопроса по механике работы рекурсивных запросов.
Чем мы можем Вам тут помочь?
Цитата(polin11 @  11.6.2019,  05:02 Найти цитируемый пост)
2) хочется отказаться от вложенных запросов в рекурсивной части

Хочется... казалось бы какая нам *тут* разница чего *там* кому хочется?
Цитата(polin11 @  11.6.2019,  05:02 Найти цитируемый пост)
3) на больших таблицах, запрос выполняется в таком виде медленно

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

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

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

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


--------------------
Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile
PM   Вверх
polin11
Дата 12.6.2019, 17:03 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Шустрый
*


Профиль
Группа: Участник
Сообщений: 122
Регистрация: 6.6.2015

Репутация: нет
Всего: нет



Я допустил неточность в описании результата, оказывается у корня иерархии Планета Земля поле 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;

PM MAIL   Вверх
  
Ответ в темуСоздание новой темы Создание опроса
1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема | Составление SQL-запросов | Следующая тема »


 




[ Время генерации скрипта: 0.1403 ]   [ Использовано запросов: 21 ]   [ GZIP включён ]


Реклама на сайте     Информационное спонсорство

 
По вопросам размещения рекламы пишите на vladimir(sobaka)vingrad.ru
Отказ от ответственности     Powered by Invision Power Board(R) 1.3 © 2003  IPS, Inc.