| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: connect by and aggregate functions
Here's one solution:
v734> create table hdata (
2 item number primary key, 3 target number references hdata, 4 value number);
Table created.
v734> insert into hdata values (0,null,0);
v734> insert into hdata values (1,0,5);
v734> insert into hdata values (2,1,7);
v734> insert into hdata values (3,0,12);
v734> insert into hdata values (4,1,9);
v734> insert into hdata values (5,3,2);
v734> commit;
v734> select * from hdata;
ITEM TARGET VALUE
---------- ---------- ----------
0 0
1 0 5
2 1 7
3 0 12
4 1 9
5 3 2
6 rows selected.
v734> column num noprint
v734> select v1.num, v1.lvl "Level",
2 substr(lpad(' ',2*(v1.lvl-1))||v1.item,1,10) "Item",
3 v1.target "Target",
4 v1.value "Value",
5 sum(c.value) "Sum",
6 avg(c.value) "Avg"
7 from (select level lvl, item, target, value, rownum num
8 from hdata a
9 connect by prior item = target
10 start with target = 0) v1,
11 hdata c
12 where c.item in (select item from hdata d
13 connect by prior item = target
14 start with d.item=v1.item)
15 group by v1.num, v1.lvl, v1.item, v1.target, v1.value
16 /
Level Item Target Value Sum Avg ---------- ---------- ---------- ---------- ---------- ----------
1 1 0 5 21 7
2 2 1 7 7 7
2 4 1 9 9 9
1 3 0 12 14 7
2 5 3 2 2 2
5 rows selected.
-- Have a nice day Michel Mike Linksvayer <ml_at_gondwanaland.com> a écrit dans le message : 90h01l$2oit$1_at_nntp1.ba.best.com...Received on Mon Dec 11 2000 - 10:58:36 CST
> Say I have some hierarchical data like
>
> SQL> select * from hdata;
>
> ITEM TARGET VALUE
> ---------- ---------- ----------
> 0 0
> 1 0 5
> 2 1 7
> 3 0 12
> 4 1 9
> 5 3 2
>
> The first is a dummy row to allow getting all roots with
> 'start with item = 0' as 'start with item = null' doesn't work.
>
> I can reproduce a tree with a query like
>
> column item format a10;
> select lpad(' ',level)||item item, target, value
> from hdata connect by prior item = target
> start with target = 0;
> ITEM TARGET VALUE sum avg
> ---------- ---------- ---------- imaginary imaginary
> 1 0 5 21 7
> 2 1 7 7 7
> 4 1 9 9 9
> 3 0 12 16 8
> 5 3 4 4 4
>
>
> I'd like to be able to to obtain sums, averages and the like for
> all nodes in a subtree, as in the imaginary results above. I'd
> also like to be able to sort nodes within a subtree by some
> value calculated from a node and its children.
>
> I could do this by executing the above query, reading it into
> my own data structures and doing the tree aggregate calculations
> and sorting there. Doesn't seem very efficient.
>
> Any hints or recommendations on how to proceed?
>
> The 'value' above is actually a score from an intermedia text search,
> so it won't help to precalculate anything, as all values will change
> with the text query.
>
> Thanks...
> Mike
> --
> See From: and Organization: above.
 |
 |