1

Topic: Shot in a brain

Kind day of the Lord!
Relative density redefinition in group/subgroup at absence of an index
I have the initial data
ID PARENT RATE EXES
1 10 40 1
2 10 60 0
3 9 20 1
4 9 80 0
5 4 30 0
6 4 70 1
7 8 100 0
8 0 10 0
9 0 70 0
10 0 20 0
11 5 45 1
12 5 55 0
Tree visually
0 - (Root)
--8
--7
--9
--3
--4
--5
--11
--12
--6
--10
--1
--2
Absence of an index it EXES=0 (a field of other table Left join)
Thus of group weight in which to be it an index are reallocated
ID RATE EXES FORMULA RESULT
1 10 1 =10*1 / (10*1 + 20*0 +70 *1) *100 12.5
2 20 0 =20*0 / (10*1 + 20*0 +70 *1) *100 0
3 70 1 =70*1 / (10*1 + 20*0 +70 *1) *100 87.5
Real example
ID PARENT RATE EXES RESULT
1 10 40 1 100
2 10 60 0
In a case if all indexes of one group EXES = 0 - such group is not present (the parent too is not present)
Its weight is reallocated by a similar principle on parent groups
Real example
ID PARENT RATE EXES
7 8 100 0
8 0 10 0 - Daughters are equal to zero - the parent is nullified
ID PARENT RATE EXES FORMULA RESULT
8 0 10 0 =10*0 / (10*0 + 70*1 +20 *1) *100 0
9 0 70 1 =70*1 / (10*0 + 70*1 +20 *1) *100 77.7778
10 0 20 1 =20*1 / (10*0 + 70*1 +20 *1) *100 22.2222
If the parent has EXES=0, but his daughters have at least one of components EXES=1
Relative density of group turnes on in calculation
Real example
ID PARENT RATE EXES
4 9 80 0
But .  exist
ID PARENT RATE EXES
5 4 30 0
6 4 70 1 - There is one making
In final settlement should receive specific weight MOST the LOWEST child
Groups !!!! Considering weight of all parent groups!!!! to a total amount are equal 100
(If the parent - is the daughter of other group value is not included)
Result
Ref. weight Recomputation FORMULA RESULT
0 100.00
--8 10 0 0 0
--7 100 0 0 0
--9 70 77.78 0 0
--3 20 20 =20/100*77.78/100*100 15.556
--4 80 80 0 0
--5 30 30 0 0
--11 45 100 =100/100*30/100*80/100*77.78/100*100 18.6672
--12 55 0 0 0
--6 70 70 =70/100*80/100*77.78/100*100 43.5568
--10 20 22.22 0 0
--1 40 100 =100/100*22.22/100*100 22.22
--2 60 0 0 0
Whether probably to create such  Oracle SQL means
Kol-in nestings it is not known and it is set by the user
Subordination can be defined . . The code
Type the Parent = 1
The daughter = 11
The daughter = 12
Trace. The daughter = 121
Thankful in advance!

2

Re: Shot in a brain

[quote=HOME_X] Kind day of the Lord!
Relative density redefinition in group/subgroup at absence of an index
I have the initial data
ID PARENT RATE EXES
1 10 40 1
2 10 60 0
3 9 20 1
4 9 80 0
5 4 30 0
6 4 70 1
7 8 100 0
8 0 10 0
9 0 70 0
10 0 20 0
11 5 45 1
12 5 55 0
Tree visually
0 - (Root)
--8
--7
--9
--3
--4
--5
--11
--12
--6
--10
--1
--2
Absence of an index it EXES=0 (a field of other table Left join)
Thus of group weight in which to be it an index are reallocated
ID RATE EXES FORMULA RESULT
1 10 1 =10*1 / (10*1 + 20*0 +70 *1) *100 12.5
2 20 0 =20*0 / (10*1 + 20*0 +70 *1) *100 0
3 70 1 =70*1 / (10*1 + 20*0 +70 *1) *100 87.5
Real example
ID PARENT RATE EXES RESULT
1 10 40 1 100
2 10 60 0
In a case if all indexes of one group EXES = 0 - such group is not present (the parent too is not present)
Its weight is reallocated by a similar principle on parent groups
Real example
ID PARENT RATE EXES
7 8 100 0
8 0 10 0 - Daughters are equal to zero - the parent is nullified
ID PARENT RATE EXES FORMULA RESULT
8 0 10 0 =10*0 / (10*0 + 70*1 +20 *1) *100 0
9 0 70 1 =70*1 / (10*0 + 70*1 +20 *1) *100 77.7778
10 0 20 1 =20*1 / (10*0 + 70*1 +20 *1) *100 22.2222
If the parent has EXES=0, but his daughters have at least one of components EXES=1
Relative density of group turnes on in calculation
Real example
ID PARENT RATE EXES
4 9 80 0
But .  exist
ID PARENT RATE EXES
5 4 30 0
6 4 70 1 - There is one making
In final settlement should receive specific weight MOST the LOWEST child
Groups [b]!!!! Considering weight of all parent groups!!!! [/b] to a total amount are equal 100
(If the parent - is the daughter of other group value is not included)
Result
Ref. weight Recomputation FORMULA RESULT
0 100.00
--8 10 0 0 0
--7 100 0 0 0
--9 70 77.78 0 0
--3 20 20 =20/100*77.78/100*100 15.556
--4 80 80 0 0
--5 30 30 0 0
--11 45 100 =100/100*30/100*80/100*77.78/100*100 18.6672
--12 55 0 0 0
--6 70 70 =70/100*80/100*77.78/100*100 43.5568
--10 20 22.22 0 0
--1 40 100 =100/100*22.22/100*100 22.22
--2 60 0 0 0
Whether probably to create such  Oracle SQL means
Kol-in nestings it is not known and it is set by the user
Subordination can be defined . . The code
Type the Parent = 1
The daughter = 11
The daughter = 12
Trace. The daughter = 121
Thankful in advance! [/quote]

today not only only all can read such porridge. More precisely, can read everything, but nobody of it will do.

3

Re: Shot in a brain

HOME_X;
Trees ....
Screen I apply

4

Re: Shot in a brain

Kind  - Eh;
Thanks for a double...

5

Re: Shot in a brain

HOME_X;

SQL> with t (ID, PARENT, RATE, EXES) as
2 (select 1, 10, 40, 1 from dual
3 union all select 2, 10, 60, 0 from dual
4 union all select 3, 9, 20, 1 from dual
5 union all select 4, 9, 80, 0 from dual
6 union all select 5, 4, 30, 0 from dual
7 union all select 6, 4, 70, 1 from dual
8 union all select 7, 8, 100, 0 from dual
9 union all select 8, 0, 10, 0 from dual
10 union all select 9, 0, 70, 0 from dual
11 union all select 10, 0, 20, 0 from dual
12 union all select 11, 5, 45, 1 from dual
13 union all select 12, 5, 55, 0 from dual)
14, t0 as
15 (
16 select *
17 from (select level l, t.*
18 from t
19 start with parent = 0
20 connect by prior id = parent)
21 model
22 dimension by (id, parent, rownum rn)
23 measures (l, rate, exes, 0 exes_calc)
24 (
25 - calculating exes for ancestors
26 exes_calc [any, any, any] order by rn desc =
27 greatest (exes [cv (id), cv (parent), cv (rn)], nvl (max (exes) [any, cv (id), any], 0))
28)
29)
30 - weighting
31 select t0. *, nvl (decode (exes_calc, 1, rate) * sum (rate) over (partition by parent) /
32 sum (decode (exes_calc, 1, rate)) over (partition by parent), 0) x
33 from t0
34 order by rn;
ID PARENT RN L RATE EXES EXES_CALC X
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
8 0 1 1 10 0 0 0
7 8 2 2 100 0 0 0
9 0 3 1 70 0 1 77.7777778
3 9 4 2 20 1 1 20
4 9 5 2 80 0 1 80
5 4 6 3 30 0 1 30
11 5 7 4 45 1 1 100
12 5 8 4 55 0 0 0
6 4 9 3 70 1 1 70
10 0 10 1 20 0 1 22.2222222
1 10 11 2 40 1 1 100
2 10 12 2 60 0 0 0
12 rows selected.

?
Instead of model it is possible to taste
- Analytics
- Subquery/connection
- pattern matching
- rec with
Read a subject Aggregation of the totals in hierarchical requests , only it is necessary to you not the total aggregate, and max + greatest to receive sign EXES _CALC for parent records.
PS. There is a small progress in a setting formulation, it is visible tried.
Next time still work to cite the data in a type with + union all and over a subject title it was necessary to work more.

6

Re: Shot in a brain

dbms_photoshop;
That READ many thanks for that and INVESTIGATED!!!!!!!!!! (For the decision separately)
Tried to solve so - still I "dopilivaju/simplify"

with LIST as (
select 1 NM, 10 PARENT, 40 RATE, 1 PLAN from DUAL union all
select 2 NM, 10 PARENT, 60 RATE, 0 PLAN from DUAL union all
select 3 NM, 9 PARENT, 20 RATE, 1 PLAN from DUAL union all
select 4 NM, 9 PARENT, 80 RATE, 0 PLAN from DUAL union all
select 5 NM, 4 PARENT, 30 RATE, 0 PLAN from DUAL union all
select 6 NM, 4 PARENT, 70 RATE, 1 PLAN from DUAL union all
select 7 NM, 8 PARENT, 100 RATE, 0 PLAN from DUAL union all
select 8 NM, 0 PARENT, 10 RATE, 0 PLAN from DUAL union all
select 9 NM, 0 PARENT, 70 RATE, 0 PLAN from DUAL union all
select 10 NM, 0 PARENT, 20 RATE, 0 PLAN from DUAL union all
select 11 NM, 5 PARENT, 45 RATE, 1 PLAN from DUAL union all
select 12 NM, 5 PARENT, 55 RATE, 0 PLAN from DUAL
)
select F.*
from (
select E. *;
Case when E.KEYS=1 and E.PLAN=1 then
Exp (Sum (Ln (Case when VES_ALL*KEYS1/100=0 then 1 else VES_ALL*KEYS1/100 end
)
) over (partition by ROOT)
)*100
else 0 end TOTAL
from (
select CONNECT_BY_ROOT D.NM ROOT;
LEVEL LL;
D. *;
Max (D.KEYS) over (partition by CONNECT_BY_ROOT D.NM) KEYS1
from (
select C.NM;
C.PARENT;
C.RATE;
C.PLAN;
Case when C.KEYS=1 then 1 else 0 end KEYS;
Sum (C.RATE) over (partition by C.PARENT) VES_BEG,
Sum (C.RATE*C.PLAN) over (partition by C.PARENT) VES_END;
Round (Nvl (C.RATE*C.PLAN/Nullif (Sum (C.RATE*C.PLAN) over (partition by PARENT), 0), 0) *100,4) VES_ALL
from (select B.NM;
B.PARENT;
B.RATE;
B.LV;
Max (B.PLAN) over (partition by NM) PLAN;
Max (B.LV) over (partition by NM) KEYS
from (select
A.NM;
A.PARENT;
A.RATE;
Max (A.PLAN) over (partition by CONNECT_BY_ROOT A.NM) PLAN;
LEVEL LV
from LIST A
connect by Prior A.PARENT=A.NM
) B
) C
where C.LV=1
order by C.NM
) D
connect by Prior D.PARENT=D.NM
order by CONNECT_BY_ROOT D.NM, LEVEL
) E
) F
where F.LL=1

7

Re: Shot in a brain

HOME_X wrote:

dbms_photoshop;
That READ many thanks for that and INVESTIGATED!!!!!!!!!! (For the decision separately)
Tried to solve so - still I "dopilivaju/simplify"

In the decision I will dare not to investigate any more, but double connect by and both times without start with looks extremely doubtfully.:-Q
If the purpose still to weigh leaves taking into account the weighed nodes of the first level.

SQL> with t (ID, PARENT, RATE, EXES) as
2 (select 1, 10, 40, 1 from dual
3 union all select 2, 10, 60, 0 from dual
4 union all select 3, 9, 20, 1 from dual
5 union all select 4, 9, 80, 0 from dual
6 union all select 5, 4, 30, 0 from dual
7 union all select 6, 4, 70, 1 from dual
8 union all select 7, 8, 100, 0 from dual
9 union all select 8, 0, 10, 0 from dual
10 union all select 9, 0, 70, 0 from dual
11 union all select 10, 0, 20, 0 from dual
12 union all select 11, 5, 45, 1 from dual
13 union all select 12, 5, 55, 0 from dual)
14, t0 as
15 (
16 select *
17 from (select level l, t. *, connect_by_isleaf leaf, connect_by_root id root_id
18 from t
19 start with parent = 0
20 connect by prior id = parent)
21 model
22 dimension by (id, parent, rownum rn)
23 measures (l, leaf, root_id, rate, exes, 0 exes_calc)
24 (
25 - calculating exes for ancestors
26 exes_calc [any, any, any] order by rn desc =
27 greatest (exes [cv (id), cv (parent), cv (rn)], nvl (max (exes) [any, cv (id), any], 0))
28)
29)
30 - weighting + leaves weighting
31 select t1. *, xmlcast (xmlquery (decode (connect_by_isleaf * exes, 1, ' 1 ' || sys_connect_by_path (round (x, 4), ' * '), ' 0 ')
32 returning content) as number) / power (100, level - 1) result
33 from (select t0. *, nvl (decode (exes_calc, 1, rate) * sum (rate) over (partition by parent) /
34 sum (decode (exes_calc, 1, rate)) over (partition by parent), 0) x
35 from t0) t1
36 start with parent = 0
37 connect by prior id = parent
38 order by rn;
ID PARENT RN L LEAF ROOT_ID RATE EXES EXES_CALC X RESULT
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
8 0 1 1 0 8 10 0 0 0 0
7 8 2 2 1 8 100 0 0 0 0
9 0 3 1 0 9 70 0 1 77.7777778 0
3 9 4 2 1 9 20 1 1 20 15.55556
4 9 5 2 0 9 80 0 1 80 0
5 4 6 3 0 9 30 0 1 30 0
11 5 7 4 1 9 45 1 1 100 18.666672
12 5 8 4 1 9 55 0 0 0 0
6 4 9 3 1 9 70 1 1 70 43.555568
10 0 10 1 0 10 20 0 1 22.2222222 0
1 10 11 2 1 10 40 1 1 100 22.2222
2 10 12 2 1 10 60 0 0 0 0
12 rows selected.

8

Re: Shot in a brain

dbms_photoshop wrote:

but double connect by and both times

It agree -  - I specify

with LIST (ID, PARENT, RATE, PLAN) as
(
select 1,10, 0.40,1 from DUAL union all
select 2,10, 0.60,0 from DUAL union all
select 3, 9, 0.20,1 from DUAL union all
select 4, 9, 0.80,0 from DUAL union all
select 5, 4, 0.30,0 from DUAL union all
select 6, 4, 0.70,1 from DUAL union all
select 7, 8, 1.00,0 from DUAL union all
select 8, 0, 0.10,0 from DUAL union all
select 9, 0, 0.70,0 from DUAL union all
select 10, 0, 0.20,0 from DUAL union all
select 11, 5, 0.45,1 from DUAL union all
select 12, 5, 0.55,0 from DUAL
)
select F.ID;
F.PARENT;
F.RATE;
F.PLAN;
F.CALC
from (
select E. *;
Round (Nvl (Exp (Sum (Ln (Nullif (E.VES_ALL*E.PLAN, 0)
)
) over (partition by E.ROOT)
)
,0
)
,4
) CALC
from (
select D. *;
Max (VES_CALC) over (partition by D.ID) VES_ALL
from (
select C. *;
Round (Nvl (Case when C.LEV=1 then C.RATE*C.EXEC end/
Nullif (Sum (Case when C.LEV=1 then C.RATE*C.EXEC end) over (partition by C.PARENT), 0)
,0
)
,6
) VES_CALC
from (
select B.ROOT;
B.LEV;
B.ID;
B.PARENT;
B.RATE;
Max (B.PLAN) over (partition by ROOT) PLAN;
Max (B.EXEC) over (partition by ID) EXEC
from (
select
CONNECT_BY_ROOT A.ID ROOT;
LEVEL LEV;
A.ID;
A.PARENT;
A.RATE;
A.PLAN;
Max (A.PLAN) over (partition by CONNECT_BY_ROOT A.ID) EXEC
from LIST A
connect by Prior A.PARENT=A.ID
) B
) C
) D
) E
) F
where F.LEV=1
order by ROOT, LEV

Like it is not so bulky it turned out...
Thanks for councils

9

Re: Shot in a brain

Looking on  noted (It is selected red in a spoiler)
[spoiler]

HOME_X wrote:

with LIST (ID, PARENT, RATE, PLAN) as
(
select 1,10, 0.40,1 from DUAL union all
select 2,10, 0.60,0 from DUAL union all
select 3, 9, 0.20,1 from DUAL union all
select 4, 9, 0.80,0 from DUAL union all
select 5, 4, 0.30,0 from DUAL union all
select 6, 4, 0.70,1 from DUAL union all
select 7, 8, 1.00,0 from DUAL union all
select 8, 0, 0.10,0 from DUAL union all
select 9, 0, 0.70,0 from DUAL union all
select 10, 0, 0.20,0 from DUAL union all
select 11, 5, 0.45,1 from DUAL union all
select 12, 5, 0.55,0 from DUAL
)
select F.ID;
F.PARENT;
F.RATE;
F.PLAN;
F.CALC
from (
select E. *;
Round (Nvl (Exp (Sum (Ln (Nullif (E.VES_ALL*E.PLAN, 0)
)
) over (partition by E.ROOT)
)
,0
)
,4
) CALC
from (
select D. *;
Max (VES_CALC) over (partition by D.ID) VES_ALL
from (
select C. *;
Round (Nvl (Case when C.LEV=1 then C.RATE*C.EXEC end/
Nullif (Sum (Case when C.LEV=1 then C.RATE*C.EXEC end) over (partition by C.PARENT), 0)
,0
)
,6
) VES_CALC
from (
select B.ROOT;
B.LEV;
B.ID;
B.PARENT;
B.RATE;
Max (B.PLAN) over (partition by>>> ROOT <<<) PLAN;
Max (B.EXEC) over (partition by ID) EXEC
from (
select
>>> CONNECT_BY_ROOT A.ID <<<ROOT;
LEVEL LEV;
A.ID;
A.PARENT;
A.RATE;
A.PLAN;
Max (A.PLAN) over (partition by>>> CONNECT_BY_ROOT A.ID <<<) EXEC
from LIST A
connect by Prior A.PARENT=A.ID
) B
) C
) D
) E
) F
where F.LEV=1
order by ROOT, LEV

[/spoiler]
And as a sentence

select B.ROOT;
B.LEV;
B.ID;
B.PARENT;
B.RATE;
--Max (B.PLAN) over (partition by ROOT) PLAN;
B.EXEC PLAN;
Max (B.EXEC) over (partition by ID) EXEC
from (
select
CONNECT_BY_ROOT A.ID ROOT;
LEVEL LEV;
A.ID;
A.PARENT;
A.RATE;
--A.PLAN;
Max (A.PLAN) over (partition by CONNECT_BY_ROOT A.ID) EXEC
from LIST A
connect by Prior A.PARENT=A.ID
) B

10

Re: Shot in a brain

HOME_X wrote:

Like it is not so bulky it turned out

Yes, quite readably.
At the big desire it is possible to manage creation of a tree from a root and to stretch on top model.:-D

SQL> with t (ID, PARENT, RATE, EXES) as
2 (select 1, 10, 40, 1 from dual
3 union all select 2, 10, 60, 0 from dual
4 union all select 3, 9, 20, 1 from dual
5 union all select 4, 9, 80, 0 from dual
6 union all select 5, 4, 30, 0 from dual
7 union all select 6, 4, 70, 1 from dual
8 union all select 7, 8, 100, 0 from dual
9 union all select 8, 0, 10, 0 from dual
10 union all select 9, 0, 70, 0 from dual
11 union all select 10, 0, 20, 0 from dual
12 union all select 11, 5, 45, 1 from dual
13 union all select 12, 5, 55, 0 from dual)
14 select *
15 from (select level l, t. *, connect_by_isleaf leaf, rownum rn, sys_connect_by_path (id, ' # ') path
16 from t
17 start with parent = 0
18 connect by prior id = parent)
19 model
20 dimension by (id, parent, path)
21 measures (rn, l, leaf, rate, exes, 0 exes_calc, 0 x, 0 result)
22 (
23 - calculating exes for ancestors
24 exes_calc [any, any, any] order by path desc =
25 greatest (exes [cv (id), cv (parent), cv (path)], nvl (max (exes) [any, cv (id), any], 0))
26 - weighting
27, x [any, any, any] =
28 case when sum (decode (exes_calc, 1, rate, 0)) [any, cv (parent), any] = 0
29 then 0
30 else decode (exes_calc [cv (), cv (), cv ()], 1, rate [cv (), cv (), cv ()], 0) /
31 sum (decode (exes_calc, 1, rate, 0)) [any, cv (parent), any]
32 end
33 - leaves weighting
34, result [any, any, any] =
35 case when leaf [cv (), cv (), cv ()] * exes [cv (), cv (), cv ()] = 1
36 then exp (sum (ln (nullif (x, 0))) [any, any, cv (path) like path || ' % ']) *
37 sum (rate) [any, 0, any]
38 else 0
39 end
40)
41 order by rn;
ID PARENT PATH RN L LEAF RATE EXES EXES_CALC X RESULT
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
8 0 #8 1 1 0 10 0 0 0 0
7 8 #8#7 2 2 1 100 0 0 0 0
9 0 #9 3 1 0 70 0 1.777777778 0
3 9 #9#3 4 2 1 20 1 1.2 15.5555556
4 9 #9#4 5 2 0 80 0 1.8 0
5 4 #9#4#5 6 3 0 30 0 1.3 0
11 5 #9#4#5#11 7 4 1 45 1 1 1 18.6666667
12 5 #9#4#5#12 8 4 1 55 0 0 0 0
6 4 #9#4#6 9 3 1 70 1 1.7 43.5555556
10 0 #10 10 1 0 20 0 1.222222222 0
1 10 #10#1 11 2 1 40 1 1 1 22.2222222
2 10 #10#2 12 2 1 60 0 0 0 0
12 rows selected.

11

Re: Shot in a brain

bug nullif

SQL> with t as
2 (select rownum id from dual connect by level <= 3)
3 select *
4 from t
5 model
6 dimension by (id)
7 measures (id x, 0 x1, 0 x2, 0 x3)
8 (
9 x1 [any] = sum (x) [id <=cv (id)];
10 x2 [any] = nullif (sum (x) [id <=cv (id)], 6);
11 x3 [any] = decode (sum (x) [id <=cv (id)], 6, null, sum (x) [id <=cv (id)])
12);
ID X X1 X2 X3
---------- ---------- ---------- ---------- ----------
1 1 1 2 1
2 2 3 3
3 3 6 12