1

Topic: The rising total (the aggregated totals) on hierarchy with factor application

Good afternoon;
There is a hierarchy:

SELECT
rn;
material;
hier. KOMPONENTE;
path;
Measure;
faktor;
isleaf;
LEVEL _;
KOMPONENTE_HIER;
root
FROM
(
SELECT rownum as rn;
material;
KOMPONENTE,
CONNECT_BY_ROOT komponente as root;
cast (SYS_CONNECT_BY_PATH (komponente, ' / ') as varchar2 (40)) as path;
cast (LPAD (' ', 3 * (LEVEL-1)) || KOMPONENTE as varchar2 (20)) as KOMPONENTE_HIER;
Measure;
CONNECT_BY_ISLEAF isleaf,
level as LEVEL_
from STAMM_HIER
START WITH MATERIAL is NULL
CONNECT BY NOCYCLE PRIOR KOMPONENTE = MATERIAL
order by 1
) hier, STAMM_HIER_FAKTOR fak
where hier.komponente = fak.komponente (+)
 RN MATERIAL KOMPONENTE PATH MEASURE FAKTOR ISLEAF LEVEL_ KOMPONENTE_HIER ROOT
---------- ------------ ------------ ---------------------------------------- ---------- ---------- ---------- ---------- -------------------- ------------
1 12345/12345 20 4 0 1 12345 12345
2 12345 718749/12345/718749 2 5 0 2 718749 12345
3 718749 730014/12345/718749/730014 2 1 0 3 730014 12345
4 730014 809579/12345/718749/730014/809579 5 2 0 4 809579 12345
5 809579 111111/12345/718749/730014/809579/111111 1 1 5 111111 12345
6 809579 222222/12345/718749/730014/809579/222222 1 1 5 222222 12345
7 809579 798808/12345/718749/730014/809579/798808 4 1 5 798808 12345
8 12345 718750/12345/718750 9 3 0 2 718750 12345
9 718750 111111/12345/718750/111111 1 1 3 111111 12345
10 718750 222222/12345/718750/222222 1 1 3 222222 12345
11 718750 809579/12345/718750/809579 5 2 0 3 809579 12345
12 809579 111111/12345/718750/809579/111111 1 1 4 111111 12345
13 809579 222222/12345/718750/809579/222222 1 1 4 222222 12345
14 809579 798808/12345/718750/809579/798808 4 1 4 798808 12345
14 rows selected. 

How to count the rising total on "measure" with factor application (measure * faktor)?
The factor needs to be applied at isleaf = 0. Then the total of expression SUM (measure) * faktor to use further for count of the rising total of following elements. At count of the rising total it is necessary to move from isleaf = 1 to root.
Tried in Excel to reflect a count example.
Also I apply scripts with examples.
In advance thanks!

drop table STAMM_HIER;
create table STAMM_HIER
(ID NUMBER;
MATERIAL varchar2 (12);
KOMPONENTE varchar2 (12);
MEASURE number
);
Insert into STAMM_HIER
(ID, MATERIAL, KOMPONENTE, MEASURE)
Values
(1, ' 12345 ', ' 718749 ', 2);
Insert into STAMM_HIER
(ID, MATERIAL, KOMPONENTE, MEASURE)
Values
(2, ' 718750 ', ' 222222 ', 1);
Insert into STAMM_HIER
(ID, MATERIAL, KOMPONENTE, MEASURE)
Values
(3, ' 730014 ', ' 809579 ', 5);
Insert into STAMM_HIER
(ID, MATERIAL, KOMPONENTE, MEASURE)
Values
(4, ' 718750 ', ' 111111 ', 1);
Insert into STAMM_HIER
(ID, MATERIAL, KOMPONENTE, MEASURE)
Values
(5, ' 809579 ', ' 222222 ', 1);
Insert into STAMM_HIER
(ID, MATERIAL, KOMPONENTE, MEASURE)
Values
(6, ' 718750 ', ' 809579 ', 5);
Insert into STAMM_HIER
(ID, MATERIAL, KOMPONENTE, MEASURE)
Values
(7, ' 12345 ', ' 718750 ', 9);
Insert into STAMM_HIER
(ID, MATERIAL, KOMPONENTE, MEASURE)
Values
(8, ' 809579 ', ' 111111 ', 1);
Insert into STAMM_HIER
(ID, MATERIAL, KOMPONENTE, MEASURE)
Values
(9, ' 718749 ', ' 730014 ', 2);
Insert into STAMM_HIER
(ID, MATERIAL, KOMPONENTE, MEASURE)
Values
(10, ' 809579 ', ' 798808 ', 4);
Insert into STAMM_HIER
(ID, MATERIAL, KOMPONENTE, MEASURE)
Values
(11, NULL, ' 12345 ', 20);
COMMIT;
drop table STAMM_HIER_FAKTOR;
CREATE TABLE STAMM_HIER_FAKTOR
(
KOMPONENTE VARCHAR2 (12 BYTE);
FAKTOR NUMBER
);
CREATE UNIQUE INDEX STAMM_HIER_FAKTOR_PK ON STAMM_HIER_FAKTOR
(KOMPONENTE);
ALTER TABLE STAMM_HIER_FAKTOR ADD (
CONSTRAINT STAMM_HIER_FAKTOR_PK
PRIMARY KEY
(KOMPONENTE)
USING INDEX STAMM_HIER_FAKTOR_PK
ENABLE VALIDATE);
Insert into STAMM_HIER_FAKTOR
(KOMPONENTE, FAKTOR)
Values
(' 730014 ', 1);
Insert into STAMM_HIER_FAKTOR
(KOMPONENTE, FAKTOR)
Values
(' 809579 ', 2);
Insert into STAMM_HIER_FAKTOR
(KOMPONENTE, FAKTOR)
Values
(' 718750 ', 3);
Insert into STAMM_HIER_FAKTOR
(KOMPONENTE, FAKTOR)
Values
(' 12345 ', 4);
Insert into STAMM_HIER_FAKTOR
(KOMPONENTE, FAKTOR)
Values
(' 718749 ', 5);
COMMIT;

2

Re: The rising total (the aggregated totals) on hierarchy with factor application

Resulted hierarchy in more simplified type, that that aggregated the last elements with penultimate since the factor in the last elements is not considered:

set linesize 1000
with hier#
as
(SELECT
hier.material, hier.komponente, hier.path, sum (Measure) as measure, max (fak.faktor) faktor
FROM
(
SELECT
CASE CONNECT_BY_ISLEAF
WHEN 1 THEN
prior material
ELSE material
END as material;
CASE CONNECT_BY_ISLEAF
WHEN 1 THEN
prior KOMPONENTE
ELSE KOMPONENTE
END as komponente;
CASE CONNECT_BY_ISLEAF
WHEN 1 THEN
prior cast (SYS_CONNECT_BY_PATH (komponente, ' / ') as varchar2 (40))
ELSE cast (SYS_CONNECT_BY_PATH (komponente, ' / ') as varchar2 (40))
END as path;
Measure
from STAMM_HIER
START WITH MATERIAL is NULL
CONNECT BY NOCYCLE PRIOR KOMPONENTE = MATERIAL
) hier, STAMM_HIER_FAKTOR fak
where hier.komponente = fak.komponente (+)
group by hier.material, hier.komponente, hier.path
)
SELECT
*
FROM hier# t1
order by 3
 MATERIAL KOMPONENTE PATH MEASURE FAKTOR
------------ ------------ ---------------------------------------- ---------- ----------
12345/12345 20 4
12345 718749/12345/718749 2 5
718749 730014/12345/718749/730014 2 1
730014 809579/12345/718749/730014/809579 11 2
12345 718750/12345/718750 11 3
718750 809579/12345/718750/809579 11 2
6 rows selected. 

The Problem in volume that, I can not receive the total of the previous elements on hierarchy in any way.
P.S. In the present tasks in addition to expression (measure * faktor) are added still pair of constants, therefore carrying out faktor "for a bracket" will not work.
Result in Ehcel:

3

Re: The rising total (the aggregated totals) on hierarchy with factor application

Something of type:

with h as (
select rownum as rn;
material;
t1.komponente;
connect_by_root t1.komponente root;
cast (sys_connect_by_path (t1.komponente, ' / ') || ' / ' as varchar2 (40)) as path;
cast (lpad (' ', 3 * (level-1)) || t1.komponente as varchar2 (20)) as komponente_hier;
measure;
nvl (faktor, 1) faktor;
connect_by_isleaf isleaf;
level as level_
from stamm_hier t1;
stamm_hier_faktor t2
where t2.komponente (+) = t1.komponente
start with material is null
connect by nocycle prior t1.komponente = material
)
select rn;
material;
komponente;
root;
path;
komponente_hier;
measure;
faktor;
faktor_running_sum;
isleaf;
level_
from h
model
dimension by (
path;
level_
)
measures (
rn;
material;
komponente;
root;
komponente_hier;
measure;
faktor;
isleaf;
0 faktor_running_sum
)
rules
(
faktor_running_sum [any, any] order by path desc = faktor [cv (), cv ()] *
(
measure [cv (), cv ()] +
sum (faktor_running_sum) [instr (path, cv ()) = 1, level_ - cv () in (0,1)]
)
)
/
RN MATERIAL KOMPONENTE ROOT PATH KOMPONENTE_HIER MEASURE FAKTOR FAKTOR_RUNNING_SUM ISLEAF LEVEL_
--- -------- ---------- ----- ----------------------------------- -------------------- ------- ---------- ------------------ ------ ------
1 12345 12345/12345/12345 20 4 996 0 1
2 12345 718749 12345/12345/718749/718749 2 5 130 0 2
3 718749 730014 12345/12345/718749/730014/730014 2 1 24 0 3
4 730014 809579 12345/12345/718749/730014/809579/809579 5 2 22 0 4
5 809579111111 12345/12345/718749/730014/809579/111111/111111 1 1 1 1 5
6 809579222222 12345/12345/718749/730014/809579/222222/222222 1 1 1 1 5
7 809579 798808 12345/12345/718749/730014/809579/798808/798808 4 1 4 1 5
8 12345 718750 12345/12345/718750/718750 9 3 99 0 2
9 718750111111 12345/12345/718750/111111/111111 1 1 1 1 3
10 718750222222 12345/12345/718750/222222/222222 1 1 1 1 3
11 718750 809579 12345/12345/718750/809579/809579 5 2 22 0 3
12 809579111111 12345/12345/718750/809579/111111/111111 1 1 1 1 4
13 809579222222 12345/12345/718750/809579/222222/222222 1 1 1 1 4
14 809579 798808 12345/12345/718750/809579/798808/798808 4 1 4 1 4
14 rows selected.
SQL>

SY.

4

Re: The rising total (the aggregated totals) on hierarchy with factor application

SY;
Thanks!!! Cool!!!
I will understand now.

5

Re: The rising total (the aggregated totals) on hierarchy with factor application

Hans Christian Andersen;
Antiquated method with a nested piece of wood.

select h1. *;
(select sum (xmlcast (xmlquery ((measure || sys_connect_by_path (faktor, ' * ')) returning content) as int))
from hier# h0
start with h0.path = h1.path
connect by prior komponente = material) x
from hier# h1
order by 3;
MATERIAL KOMPONENTE PATH MEASURE FAKTOR X
------------ ------------ ------------------------------ ---------- ---------- ----------
12345/12345 20 4 996
12345 718749/12345/718749 2 5 130
718749 730014/12345/718749/730014 2 1 24
730014 809579/12345/718749/730014/809579 11 2 22
12345 718750/12345/718750 11 3 99
718750 809579/12345/718750/809579 11 2 22
6 rows selected.

Still it is possible self join on like if to calculate path with product of factors.

6

Re: The rising total (the aggregated totals) on hierarchy with factor application

Thanks you dbms_photoshop;
But I use decision SY since instead of faktor it is necessary to apply the formula.
For experience, your decision is very useful!
P.S. Thanks SY for the decision. I tested with a small amount of the data - all works.
I will try request with a considerable quantity of elements.

7

Re: The rising total (the aggregated totals) on hierarchy with factor application

dbms_photoshop wrote:

the Antiquated method with a nested piece of wood.

Not too effectively, so-as number  hierarchies to equally number of lines of the table when one hierarchy and the recursive calculation upwards from sheet is necessary to us. MODEL it is possible to replace on recursive subquery factoring.
SY.

8

Re: The rising total (the aggregated totals) on hierarchy with factor application

SY wrote:

it is not too effective

No doubt, it has been written simply to complete the picture.
The model here is better, because considers already counted.
But if we pursue performance I think PL/SQL the decision soils model on orders.

SY wrote:

MODEL it is possible to replace on recursive subquery factoring.

with bypass from below upwards?
Here there is a problem that if for a node some children we come to it some times and, probably, at different levels.
Therefore instead of trivial bypass from leaves to a root it is necessary to dodge a little.

with h as
(select h1. *;
decode ((select count (*) from hier# h2 where h2.material = h1.komponente), 0, 1, 0) leaf;
regexp_count (path, ' / ') orig_lvl;
max (regexp_count (path, ' / ')) over () depth
from hier# h1);
rec (lvl, material, komponente, path, measure, faktor, x, rn) as
(select depth;
material;
komponente;
path;
measure;
faktor;
measure * faktor;
1
from h
where orig_lvl = depth
union all
select r.lvl - 1;
h.material;
h.komponente;
h.path;
h.measure;
h.faktor;
(h.measure + sum (decode (h.komponente, r.material, r.x, 0))
over (partition by h.path)) * h.faktor;
row_number () over (partition by h.path order by null)
from h
join rec r
on (h.komponente = r.material and r.rn = 1)
or (h.leaf = 1 and h.orig_lvl = r.lvl - 1))
select * from rec where rn = 1 order by path;
LVL MATERIAL KOMPONENTE PATH MEASURE FAKTOR X RN
---------- ------------ ------------ ------------------------------ ---------- ---------- ---------- ----------
1 12345/12345 20 4 996 1
2 12345 718749/12345/718749 2 5 130 1
3 718749 730014/12345/718749/730014 2 1 24 1
4 730014 809579/12345/718749/730014/809579 11 2 22 1
2 12345 718750/12345/718750 11 3 99 1
3 718750 809579/12345/718750/809579 11 2 22 1
6 rows selected.

Or there is a variant easier?

9

Re: The rising total (the aggregated totals) on hierarchy with factor application

dbms_photoshop wrote:

With bypass from below upwards?

Yes is not present, I told "MODEL it is possible to replace on recursive subquery factoring", and hierarchy vserhu-downwards  we do not touch.
SY.

10

Re: The rising total (the aggregated totals) on hierarchy with factor application

SY wrote:

it is passed...
Yes is not present, I told "MODEL it is possible to replace on recursive subquery factoring", and hierarchy vserhu-downwards  we do not touch.
SY.

If to look narrowly, I used hier# which the author just as entered hierarchy from a root.
Then on the basis of it there is an aggregation to a root with the help rec with.
All it demands artful connection in the recursive member on OR and to steam of other tricks.
The question was, whether there are ideas for more simple decision. "It is possible to replace expression" well in any way does not explain the approach.