Oracle SQL hierarchical query data accumulation

Question

I need to accumulate data in hierarchical query like this:

select 
id, 
prior id as parent_id,
count * prior count  --need to be accumulated through whole hierarchy like sys_connect_by_path 
from table
start with id = 1
connect by prior id = parent_id

Except, i can only access prior row, so it will not work properly. And i cannot use alias defined in current SELECT clause for prior row:

select 
id, 
prior id as parent_id,
count,
(count * prior whole_count) as count_product --not allowed to do this :(
from table
start with id = 1
connect by prior id = parent_id

What i'm missing? SYS_CONNECT_BY_PATH doing exactly what i need (except concatenation instead of multiplication), so it should be possible. I can do this using WITH clause, but for some reason it is extremely slow, like half a second for CONNECT_BY vs ~20 for WITH.

Expected output:

ID	PARENT_ID	COUNT	COUNT_PRODUCT
1	null	1	1
2	1	2	2
3	2	3	6
4	3	4	24
5	4	5	120
6	5	6	720

Fiddle using WITH clause (from MT0)

Please edit the question and include a minimal reproducible example with: the CREATE TABLE and INSERT statements for your sample data; an English (not code or pseudo-code) explanation of the logic that you are trying to implement; and the expected output for your sample data. At the moment, it is difficult to understand what count * prior count is trying to achieve and whether it is COUNT(*) or the product of successive "count" values over the hierarchy (in which case what is a "count")? — MT0
– MT0, Commented Sep 30, 2024 at 13:36

MT0 · Accepted Answer · 2024-09-30 13:46:34Z

2

If you are trying to get the product of successive count values through the hierarchy then use a recursive sub-query factoring clause:

WITH rsqfc (id, count, count_product) AS (
  SELECT id, count, count FROM table_name WHERE parent_id IS NULL
UNION ALL
  SELECT t.id, t.count, t.count * r.count_product
  FROM   rsqfc r
         INNER JOIN table_name t
         ON (r.id = t.parent_id)
)
SELECT *
FROM   rsqfc;

Which, for the sample data:

CREATE TABLE table_name (
  id        NUMBER(10,0) PRIMARY KEY,
  parent_id REFERENCES table_name (id),
  count     NUMBER(10,0)
)

INSERT INTO table_name (id, parent_id, count)
SELECT 1, NULL, 1 FROM DUAL UNION ALL
SELECT 2, 1, 2 FROM DUAL UNION ALL
SELECT 3, 1, 3 FROM DUAL UNION ALL
SELECT 4, 2, 4 FROM DUAL UNION ALL
SELECT 5, 3, 5 FROM DUAL UNION ALL
SELECT 6, 5, 6 FROM DUAL;

Outputs:

ID	COUNT	COUNT_PRODUCT
1	1	1
2	2	2
3	3	3
4	4	8
5	5	15
6	6	90

fiddle

answered Sep 30, 2024 at 13:46

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Efgrafich Over a year ago

Thank you for your answer, but for some reason recursive query using WITH clause works incredibly slow for the same dataset. Maybe there is a way to write aggregate function analogous to SYS_CONNECT_BY_PATH, only with different operation?

MT0 · Accepted Answer · 2024-10-01 08:26:43Z

1

Assuming that your data is a tree structure (without loops) and there is a single path from each descendant element back to the root of the tree then you can descend the tree using a hierarchical query and then find all the previous values by using a correlated hierarchical query in the reverse direction and aggregate their natural logs to calculate the product:

SELECT id, parent_id, count,
       ( SELECT ROUND(EXP(SUM(LN(count))), 10)
         FROM   table_name s
         START WITH s.ROWID = t.ROWID
         CONNECT BY PRIOR parent_id = id ) AS count_product
FROM   table_name t
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id

Which, for the sample data:

CREATE TABLE table_name (
  id        NUMBER(10,0) PRIMARY KEY,
  parent_id REFERENCES table_name (id),
  count     NUMBER(10,0)
)

INSERT INTO table_name (id, parent_id, count)
SELECT 1, NULL, 1 FROM DUAL UNION ALL
SELECT 2, 1, 2 FROM DUAL UNION ALL
SELECT 3, 1, 3 FROM DUAL UNION ALL
SELECT 4, 2, 4 FROM DUAL UNION ALL
SELECT 5, 3, 5 FROM DUAL UNION ALL
SELECT 6, 5, 6 FROM DUAL;

Outputs:

ID	PARENT_ID	COUNT	COUNT_PRODUCT
1	null	1	1
2	1	2	2
4	2	4	8
3	1	3	3
5	3	5	15
6	5	6	90

fiddle

answered Oct 1, 2024 at 8:26

MT0

173k12 gold badges70 silver badges136 bronze badges

1 Comment

Efgrafich Over a year ago

Looks very excessive, but I probably won't get a better answer, thank you.

p3consulting · Accepted Answer · 2024-10-01 11:49:34Z

1

Not that I find it particularly smart but if you insist using CONNECT BY and SYS_CONNECT_BY_PATH:

select id, parent_id, xmlquery(('1 ' || formula) returning content).getNumberVal() as res 
from (
    select 
    id, 
    parent_id,
    cnt,
    sys_connect_by_path(cnt,' * ') as formula
    from data
    start with id = 1
    connect by prior id = parent_id
)
;

answered Oct 1, 2024 at 11:49

p3consulting

5,4212 gold badges14 silver badges15 bronze badges

1 Comment

Efgrafich Over a year ago

Interesting workaround, thank you. I dont insist on using 'SYS_CONNECT_BY_PATH', it is just an example of required behaviour.

Collectives™ on Stack Overflow

Oracle SQL hierarchical query data accumulation

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related