Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / CTE рекурсия / 14 сообщений из 14, страница 1 из 1
04.07.2015, 23:10
    #38999715
sergq
sergq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
CTE рекурсия
Здравствуйте.

слегка запутался в рекурсии

есть запрос

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
    WITH RECURSIVE SUBSUM_TEMP AS (
      SELECT K.id AS ID, K.PARENT AS PARENT, coalesce( FT.summ,0) AS SUMM
      FROM products K
      left join products_tree on products_tree.c = k.products_tree_id
     left  JOIN products_data FT ON FT.products_id = K.id
--      where k.parent = 66023
      UNION ALL

      SELECT K.id, K.PARENT, SS.SUMM
      FROM products K
      left join products_tree on products_tree.c = k.products_tree_id
      INNER JOIN SUBSUM_TEMP SS ON SS.parent = K.id
    ), SUBSUM AS (
      SELECT ID, SUM(SUMM) AS SUMM
      FROM SUBSUM_TEMP
      GROUP BY 1
    )
    SELECT ID, SUMM FROM SUBSUM;




выводит все дерево и соответствующие суммы по ветвям.

все хорошо.


Но как показать например данные определенного уровня.
если поставить такое условие - where k.parent = 66023 то он все равно раскручивает дерево и вверх и вниз.

есть вариант запроса с execute block и временной таблицей. но условие накладывается после рекурсии. те чтобы мне получить данные определенного уровня ( 5-15 строк) вычитывается все дерево.

спасибо
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.07.2015, 16:58
    #38999940
sergq
sergq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
CTE рекурсия
никто не знает?)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.07.2015, 17:06
    #38999945
Таблоид
Таблоид
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
CTE рекурсия
sergq,

есть подозрение, что вместо "where k.parent = 66023" надо в root-части ставить "where k.id = 66023".
Но будет лучше, если вы предоставите DDL таблиц и пример данных - здесь мало энтузиастов, готовых выдумывать эту инфу за вас.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.07.2015, 17:52
    #38999960
sergq
sergq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
CTE рекурсия
Таблоид,

если раскомментировать where k.parent = 66023 в root части, то если у строки есть дети - не считает сумму по этой строке.

данные приложил.

еще раз запрос
 

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
    WITH RECURSIVE SUBSUM_TEMP AS (
      SELECT K.id AS ID, K.PARENT AS PARENT,
      coalesce(k.counter,0) counter,0 level,k.name,k.code,k.isgroup
      FROM test_tree_z k


      where k.parent = 66023
      UNION ALL

      SELECT K.id, K.PARENT,ss.counter,1 level,k.name,k.code,k.isgroup
      FROM test_tree_z K
      INNER JOIN SUBSUM_TEMP SS ON SS.parent = K.id
    )
    , SUBSUM AS (
      SELECT ID, name,code,level,isgroup,parent,sum(counter) PREVCOUNT FROM SUBSUM_TEMP GROUP BY 1,2,3,4,5,6 )
    SELECT ID,name,code,level,PREVCOUNT,isgroup,parent FROM SUBSUM;




для строки с ID = 66045 не считается сумма. и дерево разворачивается вне зависимости от условия в root
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.07.2015, 17:54
    #38999961
Таблоид
Таблоид
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
CTE рекурсия
sergqданные приложил.Повторите попытку, плз, их нет.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.07.2015, 17:55
    #38999962
sergq
sergq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
CTE рекурсия
пардон. файл с данными тут http://wdfiles.ru/57z
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.07.2015, 19:10
    #39000000
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
CTE рекурсия
sergq
для строки с ID = 66045 не считается сумма. и дерево разворачивается вне зависимости от условия в root

конечно не считается. Ты же её для потомков считаешь. А не для самой этой строки
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.07.2015, 19:23
    #39000003
sergq
sergq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
CTE рекурсия
Симонов Денис,

тогда вообще не понимаю )

выполнил запрос без фильтра в root.

в результате получил две строки с ID = 66045. одна с суммой ноль. другая с нулевой суммой. если вернуть условие в root - сумма нулевая
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.07.2015, 22:49
    #39000081
Таблоид
Таблоид
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
CTE рекурсия
Эти крохотные данные можно было и сюда затолкать:
 
Код: plaintext
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.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
recreate table test (
    id       integer,
    parent   integer,
    counter  integer,
    isgroup  integer
);

insert into test (id, parent, counter, isgroup) values (65921, 229148, 0 , 1);
insert into test (id, parent, counter, isgroup) values (65926, 65921, 0,  1);
insert into test (id, parent, counter, isgroup) values (65927, 65926, 0,  1);
insert into test (id, parent, counter, isgroup) values (65928, 65927, 0,  1);
insert into test (id, parent, counter, isgroup) values (65929, 65928, 0,  1);
insert into test (id, parent, counter, isgroup) values (66023, 65929, 0,  1);
insert into test (id, parent, counter, isgroup) values (66024, 66023, 0,  1);
insert into test (id, parent, counter, isgroup) values (66027, 66023, 388,  0);
insert into test (id, parent, counter, isgroup) values (66028, 66023, 398,  0);
insert into test (id, parent, counter, isgroup) values (66029, 66023, 198,  0);
insert into test (id, parent, counter, isgroup) values (66030, 66023, 0,  0);
insert into test (id, parent, counter, isgroup) values (66031, 66023, 0,  0);
insert into test (id, parent, counter, isgroup) values (66032, 66023, 545,  0);
insert into test (id, parent, counter, isgroup) values (66033, 66023, 6,  0);
insert into test (id, parent, counter, isgroup) values (66034, 66023, 6,  0);
insert into test (id, parent, counter, isgroup) values (66035, 66023, 0,  0);
insert into test (id, parent, counter, isgroup) values (66036, 66023, 12,  0);
insert into test (id, parent, counter, isgroup) values (66037, 66023, 0,  0);
insert into test (id, parent, counter, isgroup) values (66038, 66023, 0,  0);
insert into test (id, parent, counter, isgroup) values (66039, 66023, 0,  0);
insert into test (id, parent, counter, isgroup) values (66040, 66023, 0,  0);
insert into test (id, parent, counter, isgroup) values (66041, 66023, 0,  0);
insert into test (id, parent, counter, isgroup) values (66042, 66023, 0,  0);
insert into test (id, parent, counter, isgroup) values (66043, 66023, 0,  0);
insert into test (id, parent, counter, isgroup) values (66044, 66023, 0,  0);
insert into test (id, parent, counter, isgroup) values (66045, 66023, 0,  1);
insert into test (id, parent, counter, isgroup) values (66052, 66023, 0,  1);
insert into test (id, parent, counter, isgroup) values (66056, 66023, 0,  1);
insert into test (id, parent, counter, isgroup) values (229148, 0, 0,  1);
insert into test (id, parent, counter, isgroup) values (66046, 66045, 0,  0);
insert into test (id, parent, counter, isgroup) values (66047, 66045, 10000,  0);
insert into test (id, parent, counter, isgroup) values (66048, 66045, 0,  0);
insert into test (id, parent, counter, isgroup) values (66049, 66045, 0,  0);
insert into test (id, parent, counter, isgroup) values (66050, 66045, 0,  0);
insert into test (id, parent, counter, isgroup) values (66051, 66045, 0,  0);
commit;
И насколько я понимаю вот это:авторкак показать например данные определенного уровня- вам нужно фильтрануть в "раскрученном" дереве записи, относящиеся к некоторому УРОВНЮ (поле в запросе - `lvl`):
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
with recursive
rtree as (
    select k.id as id, k.parent as parent, coalesce(k.counter,0) counter, k.isgroup, 0 as lvl
    from test k
    where k.parent = 0

    union all
    
    select k.id, k.parent,k.counter,k.isgroup, r.lvl+1 as lvl
    from test k
    join rtree r on k.parent = r.id
)
select lvl,parent,isgroup,sum(counter) sum_count
from rtree
-- where lvl = 7 (или parent = пресловутому 66023 - не знаю, что там по задаче надо)
group by lvl,parent,isgroup
Результат этого запроса будет таким:LVLPARENTISGROUPSUM_COUNT001012291481026592110365926104659271056592810665929107660230155376602310866045010000

Если этот запрос надо вкрячить в параметризованную ХП, то во where-условии у вас должен быть входной параметр ("уровень" или что-то там еще).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.07.2015, 00:33
    #39000106
sergq
sergq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
CTE рекурсия
Таблоид,

вот как только условие parent = 66023 добавляю - исчезает строка с SUM_COUNT = 10000


делал на примере http://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=713220&msg=7954817

там все правильно считает. но там предварительно во временную таблицу добавляется вообще все дерево.
фильтрация по нужному паренту тоже только в FOR SELECT.
и общее время выполнения запроса в районе 900-1000 милисекунд. много.
В дереве всего 10000 нод
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.07.2015, 01:47
    #39000117
Таблоид
Таблоид
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
CTE рекурсия
sergqвот как только условие parent = 66023 добавляю - исчезает строка с SUM_COUNT = 10000Меня терзают смутные сомнения, что на самом деле вам надо получить для заданного ID сумму по всем записям, для которых этот ID является прямым или опосредованным предком. С группировкой по полю isGroup.
Если это так, то формируйте на каждой итерации рекурсии наращиваемую строку, являющуюся путём к текущему узлу от самого верха. Далее (после рекурсии) добавляйте where-условие со своем "66023" и затем - группировку с суммой.
Т.е. примерно так:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
with recursive
rtree as (
    select k.id as id, k.parent as parent, coalesce(k.counter,0) counter, k.isgroup, 0 as lvl
    --,cast(','||k.parent||',' as varchar(8192)) as path1
    ,cast(','||k.id||',' as varchar(8192)) as path2
    from test k
    where k.parent = 0

    union all
    
    select k.id, k.parent,k.counter,k.isgroup, r.lvl+1 as lvl
    --,r.path1 || k.parent ||',' as path1
    ,r.path2 || k.id ||',' as path2
    from test k
    join rtree r on k.parent = r.id
)
-- select * from rtree order by lvl, parent, id

select isgroup,sum(counter) sum_count
from rtree
where path2 containing ',66023,'
group by isgroup;
Result:ISGROUPSUM_COUNT01155310
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.07.2015, 10:38
    #39000281
sergq
sergq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
CTE рекурсия
Таблоид,

если быть точным, то на этих данных

 



CREATE TABLE TEST_TREE_Z (
ID INTEGER,
PARENT INTEGER,
COUNTER INTEGER,
NAME CHAR(1000),
CODE CHAR(1000),
ISGROUP INTEGER
);


INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (65921, 229148, 0, 1);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (65926, 65921, 0, 1);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (65927, 65926, 0, 1);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (65928, 65927, 0, 1);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (65929, 65928, 0, 1);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66023, 65929, 0, 1);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66024, 66023, 0, 1);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66027, 66023, 388, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66028, 66023, 398, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66029, 66023, 198, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66030, 66023, 0, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66031, 66023, 0, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66032, 66023, 545, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66033, 66023, 6, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66034, 66023, 6, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66035, 66023, 0, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66036, 66023, 12, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66037, 66023, 0, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66038, 66023, 0, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66039, 66023, 0, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66040, 66023, 0, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66041, 66023, 0, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66042, 66023, 0, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66043, 66023, 0, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66044, 66023, 0, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66045, 66023, 0, 1);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66052, 66023, 0, 1);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66056, 66023, 0, 1);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (229148, 0, 0, 1);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66046, 66045, 0, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66047, 66045, 10000, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66048, 66045, 0, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66049, 66045, 0, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66050, 66045, 0, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66051, 66045, 0, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66025, 66024, 373, 0);
INSERT INTO TEST_TREE_Z (ID, PARENT, COUNTER, ISGROUP)
VALUES (66026, 66024, 140, 0);

COMMIT WORK;



надо получить такой результат
idisgroupcounter6602415136602703886602803986602901986603000660310066032054566033066603406660350066036012660370066038006603900660400066041006604200660430066044006604511000066052106605610

те все ноды указанного уровня. вне зависимости от значения counter. Если нода является группой - посчитать сумму ее детей
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.07.2015, 11:18
    #39000328
sergq
sergq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
CTE рекурсия
Таблоид,

в последнем запросе ид 66026,66025,66045 идут отдельно

а должно быть

66024 = 66026+66025
66045 = 66047
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.07.2015, 12:15
    #39000393
sergq
sergq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
CTE рекурсия
Таблоид,



вроде как получилось )

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

 

Код: 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.
29.
30.
select   id,parent,counter,isgroup,lvl,coalesce (subsumm,0)+counter fullsumm from
(
    select k0.id as id, k0.parent as parent, coalesce(k0.counter,0) counter, k0.isgroup, 0 as lvl
    ,cast( k0.parent|| ','||k0.id||',' as varchar(8192)) as path2
   ,

(with recursive
rtree as (
    select k.id as id, k.parent as parent, coalesce(k.counter,0) counter, k.isgroup, 0 as lvl
    ,cast( k.parent|| ','||k.id||',' as varchar(8192)) as path2
    from test_tree_z k
    where k.parent = k0.id

    union all
    
    select k.id, k.parent,k.counter,k.isgroup, r.lvl+1 as lvl
    ,r.path2 || k.id ||',' as path2
    from test_tree_z k

    join rtree r on k.parent = r.id
)

select sum(counter) sum_count
from rtree
 ) subsumm


    from test_tree_z k0

    where k0.parent = 66023)


...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / CTE рекурсия / 14 сообщений из 14, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 398ms
Закрыть
start [/forum/topic.php?fid=40&mobile=1&tid=1562736]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 33ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 43ms
get tp. blocked users:
 1ms
others: 279ms
total:  398ms
x
x
Закрыть


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