hi all,
Could anyone suggest on best approach for this kind of cumulative sums and counts?
Suppose we have a tree structure data and we want to start from any idea of the tree and get count/sums for the whole branch?
with data as
(
-- one root node
select 1 AS id, NULL as pid, NULL as s FROM dual UNION ALL
-- one root node with 2 node
select 2 AS id, NULL as pid, NULL as s FROM dual UNION ALL
select 3 AS id, 2 as pid, 3 as s FROM dual UNION ALL
select 4 AS id, 2 as pid, 4 as s FROM dual
)
select id, pid, level, s
from data
start with id = 4
connect by prior pid = id;
If I start with id 4, I have one sibling and one parent.
What is the best way to have the whole branch (here id 2 and 4) with proper counts (here id 2 has two nodes count should be 2) and sum of size (here sum for id 2 must be 7 and for leaves, the sum is their own size).
What would be the best approach to do this kind of calculations?
thank you