ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Обход дерева
12 сообщений из 12, страница 1 из 1
Обход дерева
    #39805515
m7m
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
m7m
Участник
Наставьте на путь истины

Есть таблица с деревянной структурой
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
CREATE TABLE TMP_TEST (
    CODE   INTEGER NOT NULL, 
    OWNER  INTEGER,               
    NAME   VARCHAR(64),         
    VAL    INTEGER                   
);
ALTER TABLE TMP_TEST ADD CONSTRAINT PK_TMP_TEST PRIMARY KEY (CODE);
CREATE INDEX TMP_TEST_IDX1 ON TMP_TEST (OWNER);


По некоторому заданному узлу надо получить всех его детей с "полными именами" и суммами поля Val

ну как-то вот так
Данные
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
CODE	OWNER	NAME	VAL
1	0	Д1	1
2	0	Д2	2
3	0	Д3	3
4	2	У1	4
5	2	У2	6
6	4	К1	7
7	4	К2	8
8	5	Е1	9
9	5	Е2	10

Надо получить для узла 2 (code=2) вот такой результат
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
CODE	OWNER	NAME       	VAL	SUM_VAL
2	0	Д2	        2	46
4	2	Д2/У1       	4	19
5	2	Д2/У2       	6	25
6	4	Д2/У1/К1	7	7
7	4	Д2/У1/К2	8	8
8	5	Д2/У2/Е1	9	9
9	5	Д2/У2/Е2	10	10

получил я такой результат следующим запросом
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
with recursive Tree_1
as (select S.Code, S.Owner, cast(S.Name as varchar(1048)) as Name, S.Val
      from Tmp_Test S
      where Code = 2
    union all
    select S.Code, S.Owner, T.Name || '/' || S.Name, S.Val
      from Tmp_Test S
        join Tree_1 T on T.Code = S.Owner),
Tree_2
as (select S.Code Owner, S.Code, S.Val
      from Tmp_Test S
    union all
    select P.Owner, S.Code, S.Val
      from Tmp_Test S
        join Tree_2 P on P.Code = S.Owner)

select A.Code, A.Owner, A.Name, A.Val, B.Sum_Val
  from Tree_1 A join(select Owner, sum(Val) Sum_Val
                       from Tree_2
                       group by Owner) B on B.Owner = A.Code


результат то меня устраивает, однако запрос этот мне совсем не нравится, ощущение такое что я что-то недопонимаю и что можно сделать проще :(
...
Рейтинг: 0 / 0
23.04.2019, 18:17
| Ответить | Цитировать | Написать  
Обход дерева
    #39805520
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
m7m,

что именно не нравится? В трёшке можно упростить если оконными функциями воспользоваться. Внутри рекурсивной части CTE всё равно агрегаты считать нельзя
...
Рейтинг: 0 / 0
23.04.2019, 18:31
| Ответить | Цитировать | Написать  
Обход дерева
    #39805523
Гаджимурадов Рустам
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гаджимурадов Рустам
Участник
m7m> VAL SUM_VAL 2 0 A2 2 464

Я в детали твоих чисел и запроса не вникал, но попробуй что-то
упростить за счет агрегатов - не только Sum, но и List(..., '/').
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
23.04.2019, 18:48
| Ответить | Цитировать | Написать  
Обход дерева
    #39805530
m7m
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
m7m
Участник
Симонов Денисm7m,

что именно не нравится? В трёшке можно упростить если оконными функциями воспользоваться. Внутри рекурсивной части CTE всё равно агрегаты считать нельзя
Не нравится что обход дерева идет фактически два раза, хочется за один проход все сделать
Не нравится что в Tree_2 идет расчет сумм по всей таблице, а хотелось-бы только для той части которая соответствует заданному узлу (сообразить как это сделать не смог)
зы. FB 2.5
...
Рейтинг: 0 / 0
23.04.2019, 19:04
| Ответить | Цитировать | Написать  
Обход дерева
    #39805537
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
m7m,

а зачем ты второе раз древо строишь, первым два раза нельзя воспользоваться?
...
Рейтинг: 0 / 0
23.04.2019, 19:58
| Ответить | Цитировать | Написать  
Обход дерева
    #39805546
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
m7m,

ну можно типа упростить да такого, но не уверен что это будет лучше

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
with recursive Tree
as (select S.code as P_code, S.Code, S.Owner, cast(S.Name as varchar(1048)) as Name, S.Val
      from Tmp_Test S
    union all
    select T.P_code, S.Code, S.Owner, T.Name || '/' || S.Name, S.Val
      from Tmp_Test S
        join Tree T on T.Code = S.Owner)
select A.Code, A.Owner, A.Name, A.Val, B.Sum_Val
  from Tree A join(select P_code, sum(Val) Sum_Val
                       from Tree Tree_2
                       group by P_code) B on B.P_code = A.Code
where a.P_code = 2
...
Рейтинг: 0 / 0
23.04.2019, 20:15
| Ответить | Цитировать | Написать  
Обход дерева
    #39805554
Фотография PEAKTOP
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PEAKTOP
Участник
m7m,

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

решение: хранить полный путь в таблице.
1) создаём дополнительное поле PATH BLOB TEXT
2) на триггер BEFORE INSERT OR UPDATE рассчитываем рекурсивным запросом путь и пишем в поле PATH.
3) у нас всегда готов полный путь к ноду дерева.

просто несколько миллисекунд при вставке/обновлении записи в деревянную таблицу пользователь не заметит.
а при обходе больших деревьев (например, при подготовке визуализации деревянной таблицы-справочника) расчет пути "на лету" будет дико тупить.

---
задача решена при условии, что на размер базы и место на диске заказчика мы тупо забиваем. пусть покупают нормальные винты.
...
Рейтинг: 0 / 0
23.04.2019, 21:12
| Ответить | Цитировать | Написать  
Обход дерева
    #39805558
vvvait
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vvvait
Гость
PEAKTOP, а теперь сменим родителя узлу с кучей дочерних узлов....
...
Рейтинг: 0 / 0
23.04.2019, 21:39
| Ответить | Цитировать | Написать  
Обход дерева
    #39805561
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
vvvaitа теперь сменим родителя узлу с кучей дочерних узлов....

Чисто чтобы слоники бегали или у тебя есть практическая задача для этой операции?
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
23.04.2019, 22:09
| Ответить | Цитировать | Написать  
Обход дерева
    #39805591
vvvait
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vvvait
Гость
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
with recursive
Tree_1 as (
  select S.Code, S.Owner, cast(S.Name as varchar(1048)) as Name, S.Val
  from Tmp_Test S
  where Code = 2
  union all
  select S.Code, S.Owner, T.Name || '/' || S.Name, S.Val
  from Tmp_Test S
  join Tree_1 T on T.Code = S.Owner)
select AA.Code, AA.Owner, AA.Name, AA.Val, (select sum(Val) from Tree_1 where Name starting AA.Name)
from Tree_1 AA
order by AA.Code



не факт, что будет работать быстрее, но по крайней мере не идет полным перебором по всему дереву
...
Рейтинг: 0 / 0
24.04.2019, 02:31
| Ответить | Цитировать | Написать  
Обход дерева
    #39805593
vvvait
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vvvait
Гость
если такой запрос нужен постоянно, то можно на временной таблице сделать
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
CREATE global temporary TABLE TMP_TEST_TEMP (
    CODE   INTEGER NOT NULL, 
    OWNER  INTEGER,               
    NAME   VARCHAR(1000),
    VAL    INTEGER,
    SUM_VAL INTEGER
) on commit delete rows;

create or alter trigger TMP_TEST_TEM_AIU for TMP_TEST_TEMP
after insert or update
as
begin
  if (inserting)
     then RDB$SET_CONTEXT('USER_TRANSACTION', 'VAL', new.VAL);
  update TMP_TEST_TEMP set SUM_VAL = SUM_VAL + iif(inserting, new.VAL, cast(RDB$GET_CONTEXT('USER_TRANSACTION', 'VAL') as integer)) where CODE = new.OWNER;
end

ALTER TABLE TMP_TEST_TEMP ADD CONSTRAINT PK_TMP_TEST_TEMP PRIMARY KEY (CODE);

insert into TMP_TEST_TEMP(CODE, OWNER, NAME, VAL, SUM_VAL)
with recursive
Tree_1 as (
  select S.Code, S.Owner, cast(S.Name as varchar(1048)) as Name, S.Val
  from Tmp_Test S
  where Code = 2
  union all
  select S.Code, S.Owner, T.Name || '/' || S.Name, S.Val
  from Tmp_Test S
  join Tree_1 T on T.Code = S.Owner)
select AA.Code, AA.Owner, AA.Name, AA.Val, AA.VAl
from Tree_1 AA
...
Рейтинг: 0 / 0
24.04.2019, 03:17
| Ответить | Цитировать | Написать  
Обход дерева
    #39805616
m7m
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
m7m
Участник
Всем огромное спасибо!!!

Симонов Дениса зачем ты второе раз древо строишь, первым два раза нельзя воспользоваться?
Ну мягко говоря вчера у меня голова совсем не работала, да и сегодня еще не полный порядок
Симонов Денисну можно типа упростить да такого, но не уверен что это будет лучше выглядит гораздо лучше чем мой исходный, однако дерево строится для всей таблицы, что мне не очень нравится причина ->в таблице иного независимых "деревьев"
PEAKTOPсам подход теоретически неправильный, на больших деревьях будет нагружать сервер.
решение: хранить полный путь в таблице.
а при обходе больших деревьев (например, при подготовке визуализации деревянной таблицы-справочника) расчет пути "на лету" будет дико тупить.
я бы не был столь категоричен, по крайней мере мне этот вариант не подходит, и проблема у меня не в том чтобы "рассчитать полный путь"
Dimitry Sibiryakovvvvaitа теперь сменим родителя узлу с кучей дочерних узлов....

Чисто чтобы слоники бегали или у тебя есть практическая задача для этой операции? Если про смену родителя у узла, то есть, но причина отказа от хранения полного пути у меня не в этом (ну просто по крайней мере до этого момента полный путь меня не интересовал)
vvvaitесли такой запрос нужен постоянно, то можно на временной таблице сделать
В такой реализации точно не буду, у меня мозг не переварит
Код: sql
1.
insert into (...) with recursive ... select..


да еще с триггером after insert or update в котором сидит update
------------------------------------------------------------------------------

 
Резюме: все пациенты врут (с)Если бы задача была именно такая(один в один) как я написал
то я бы остановился на варианте
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
with recursive Tree
as (select S.Code as P_Code, S.Code, S.Owner, cast(S.Name as varchar(1048)) as Name, S.Val
      from Tmp_Test S
      where S.Code = :CODE
    union all
    select T.P_Code, S.Code, S.Owner, T.Name || '/' || S.Name, S.Val
      from Tmp_Test S
        join Tree T on T.Code = S.Owner),
Tree_2
as (select S.Code as P_Code, S.Code, S.Owner, S.Val
      from Tree S
    union all
    select T.P_Code, S.Code, S.Owner, S.Val
      from Tree S
        join Tree_2 T on T.Code = S.Owner)

select A.Code, A.Owner, A.Name, A.Val, B.Sum_Val
  from Tree A join(select P_Code, sum(Val) Sum_Val
                     from Tree_2
                     group by P_Code) B on B.P_Code = A.Code


Отличается от моего первоначального варианта только тем что второе дерево строится на основании первого

Ну а на самом деле
поля Val и в помине нет в исходной таблице, и это не одно значение а несколько, и их вычисление достаточно затратно то будет наверное где-то вот так

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
for  with recursive Tree
as (select S.Code as P_Code, S.Code, S.Owner, cast(S.Name as varchar(1048)) as Name
      from Tmp_Test S
      where S.Code = :CODE
    union all
    select T.P_Code, S.Code, S.Owner, T.Name || '/' || S.Name
      from Tmp_Test S
        join Tree T on T.Code = S.Owner)
select A.Code, A.Owner, A.Name, A.Val, B.Sum_Val
  from Tree A 
into ....
do begin -- Цикл по дереву
    Расчет и запись во временную таблицу
end -- Цикл по дереву
for with recursive
Tree_2
as (select S.Code as P_Code, S.Code, S.Owner,  S.Val
      from tmp_table S
    union all
    select T.P_Code, S.Code, S.Owner, S.Val
      from tmp_table S
        join Tree_2 T on T.Code = S.Owner)

select A.Code, A.Owner, A.Name, A.Val, B.Sum_Val
  from tmp_table A join(select P_Code, sum(Val) Sum_Val
                     from Tree_2
                     group by P_Code) B on B.P_Code = A.Code
do suspend;




И последнее (пожалуй самое важное) для чего все это затевается
Все банально, очередной велосипед с отчетами.
Проблема с выборками и группировками,
"естественные" выборки и группировки как-бы продуманы и заложены
однако периодически возникает требование выдать отчет по некоторой группе объектов
ну которые совершенно не можем формализовать (по причине отсутствия в базе нужных для этого характеристик объектов, ну не были нужны они (эти характеристики) для решаемой задачи, да и не будут они нужны потом, через неделю - две такая выборка будет не актуальна)
Вот и родилась идея - держите деревянный справочник, вносите туда что хотите и как хотите, а при выдачи отчета укажите требуемую группу и получите данные по требуемым объектам с нужной вам группировкой и итогами и будет вам счастье и мне тоже (если доведу до ума)

Еще раз всем спасибо, и простите за столь длинный пост, наболело :(

...
Рейтинг: 0 / 0
24.04.2019, 08:21
| Ответить | Цитировать | Написать  
12 сообщений из 12, страница 1 из 1
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Обход дерева
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=40&gotonew=1&tid=1560728]:
 0ms
get settings:
 11ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 83ms
get topic data:
 10ms
get first new msg:
 7ms
get forum data:
 2ms
get page messages:
 53ms
get tp. blocked users:
 1ms
others: 312ms
total:  497ms
созд. / загр.: 497ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]