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

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

есть запрос

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

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

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

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

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

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

в результате получил две строки с ID = 66045. одна с суммой ноль. другая с нулевой суммой. если вернуть условие в root - сумма нулевая
...
Рейтинг: 0 / 0
05.07.2015, 19:23
| Ответить | Цитировать | Написать  
CTE рекурсия
    #39000081
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
Эти крохотные данные можно было и сюда затолкать:
 
Код: 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
05.07.2015, 22:49
| Ответить | Цитировать | Написать  
CTE рекурсия
    #39000106
sergq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sergq
Участник
Таблоид,

вот как только условие 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, 00:33
| Ответить | Цитировать | Написать  
CTE рекурсия
    #39000117
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
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, 01:47
| Ответить | Цитировать | Написать  
CTE рекурсия
    #39000281
sergq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sergq
Участник
Таблоид,

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

 



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, 10:38
| Ответить | Цитировать | Написать  
CTE рекурсия
    #39000328
sergq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sergq
Участник
Таблоид,

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

а должно быть

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



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

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

 

Код: 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
06.07.2015, 12:15
| Ответить | Цитировать | Написать  
14 сообщений из 14, страница 1 из 1
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / CTE рекурсия
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=40&msg=38999940&tid=1562736]:
 0ms
get settings:
 9ms
get forum list:
 12ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 43ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 50ms
get tp. blocked users:
 2ms
others: 276ms
total:  408ms
созд. / загр.: 408ms
x
x
Закрыть


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