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)

Answer 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