ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Выстрел в мозг
11 сообщений из 11, страница 1 из 1
Выстрел в мозг
    #39520399
HOME_X
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
HOME_X
Участник
Доброго дня господа !
Переопределение удельного веса в группе/подгруппе при отсутствие показателя

Имею исходные данные
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

Дерево визуально
0 -- (корень)
--8
--7
--9
--3
--4
--5
--11
--12
--6
--10
--1
--2


Отсутствие показателя это EXES=0 (поле другой таблицы Left join)
при это веса группы в которой находиться это показатель перераспределяются
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

Реальный пример
ID PARENT RATE EXES RESULT
1 10 40 1 100
2 10 60 0

В случае если все показатели одной группы EXES = 0 - такой группы нет (родителя тоже нет)
ее вес перераспределяется по аналогичному принципу на родительские группы

Реальный пример
ID PARENT RATE EXES
7 8 100 0
8 0 10 0 - дочери равны нулю - родитель обнулен

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


Если родитель имеет EXES=0, но его дочери имеют хотя бы один из составляющих EXES=1
удельный вес группы включается в расчет

Реальный пример
ID PARENT RATE EXES
4 9 80 0

Но дочер. елемены существуют
ID PARENT RATE EXES
5 4 30 0
6 4 70 1 - есть один составляющий

В окончательный расчет должны получить удельные веса САМЫЙ НИЗШЕЙ дочерней
группы !!!! учитывая веса всех родительских групп !!!! итоговой сумме равны 100
(если родитель - является дочерью другой группы величина не включена)

Результат
Исх.вес Перерасчет 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


Возможно ли создать эдакое МОЗГОВПРАВСТВО средствами Oracle SQL
Кол-во вложений неизвестно и задается пользователем

Подчиненность может быть определена иерар. структ. кодом
Типа Родитель = 1
Дочь = 11
Дочь = 12
След. дочь = 121


Заранее благодарен !
...
Рейтинг: 0 / 0
13.09.2017, 13:00
| Ответить | Цитировать | Написать  
Выстрел в мозг
    #39520405
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
[quot HOME_X]Доброго дня господа !							
Переопределение удельного веса в группе/подгруппе  при отсутствие показателя							
							
Имею исходные данные 							
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				
							
Дерево  визуально 								
0	-- (корень)							
     --8							
	   --7						
     --9							
	   --3						
	   --4						
		--5					
		     --11				
		     --12				
		--6					
    --10							
	   --1						
	   --2						
								
								
Отсутствие показателя это EXES=0 (поле другой таблицы Left join)								
при это веса группы в которой находиться это показатель перераспределяются 								
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		
								
Реальный пример 								
ID	PARENT	RATE	  EXES  RESULT				
1	10	        40	    1	      100				
2	10	        60	    0					
								
В случае если все показатели одной группы EXES = 0  - такой группы нет (родителя тоже нет)						
ее вес перераспределяется  по аналогичному принципу на родительские группы								
								
Реальный пример 								
ID	PARENT	RATE	  EXES					
7	8	        100	    0					
8	0	          10	    0	 - дочери равны нулю - родитель обнулен 				
								
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
								
								
Если родитель имеет EXES=0, но его дочери имеют хотя бы один из составляющих EXES=1								
удельный вес группы включается в расчет								
								
Реальный пример 								
ID	PARENT	RATE	  EXES					
4	9	        80	  0					

Но дочер. елемены существуют 								
ID	PARENT	RATE	  EXES					
5	4	        30	  0					
6	4	        70	  1	 - есть один составляющий				
								
В окончательный расчет  должны получить удельные веса САМЫЙ НИЗШЕЙ дочерней  								
группы  !!!!  учитывая веса всех родительских групп  !!!!   итоговой сумме равны 100 								
(если родитель - является дочерью другой группы величина не включена)								
								
Результат 								
			       Исх.вес	Перерасчет                            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
								
								
Возможно ли создать эдакое МОЗГОВПРАВСТВО средствами Oracle SQL								
Кол-во вложений неизвестно и задается пользователем								

Подчиненность может быть определена иерар. структ. кодом 
Типа Родитель = 1
               Дочь = 11
               Дочь = 12
                  След. дочь = 121
			
				
Заранее благодарен ![/quot]

сегодня не только лишь все могут читать такую кашу. точнее, читать могут все, но никто не будет этого делать.
...
Рейтинг: 0 / 0
13.09.2017, 13:04
| Ответить | Цитировать | Написать  
Выстрел в мозг
    #39520410
HOME_X
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
HOME_X
Участник
HOME_X,

Деревья скрокозябрыло ....
Скрин прилагаю
...
Рейтинг: 0 / 0
13.09.2017, 13:10
| Ответить | Цитировать | Написать  
Выстрел в мозг
    #39520415
HOME_X
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
HOME_X
Участник
Добрый Э - Эх,

Спасибо за дубль ...
...
Рейтинг: 0 / 0
13.09.2017, 13:13
| Ответить | Цитировать | Написать  
Выстрел в мозг
    #39520484
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
HOME_X,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
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.


?

Вместо модели можно по вкусу
- аналитика
- подзапрос/соединение
- pattern matching
- rec with

Читай тему Агрегация сумм в иерархических запросах , только тебе надо не агрегат суммы, а max + greatest, чтоб получить признак EXES_CALC для родительских записей.

PS. Имеется небольшой прогресс в формулировании постановки, видно старался.
В следующий раз еще потрудись приводить данные в виде with + union all и над названием темы надо было больше поработать.
...
Рейтинг: 0 / 0
13.09.2017, 14:42
| Ответить | Цитировать | Написать  
Выстрел в мозг
    #39520558
HOME_X
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
HOME_X
Участник
dbms_photoshop,

Огромное спасибо за то что ПРОЧИТАЛИ и ВНИКЛИ !!!!!!!!!! (за решение отдельно)

Попытался решить так - еще "допиливаю/упрощаю"
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
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
...
Рейтинг: 0 / 0
13.09.2017, 16:26
| Ответить | Цитировать | Написать  
Выстрел в мозг
    #39520613
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
HOME_Xdbms_photoshop,

Огромное спасибо за то что ПРОЧИТАЛИ и ВНИКЛИ !!!!!!!!!! (за решение отдельно)

Попытался решить так - еще "допиливаю/упрощаю"В решение позволю себе уже не вникать, но двойной connect by и оба раз без start with выглядит крайне сомнительно.

Если цель еще взвесить листья с учетом взвешенных узлов первого уровня.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
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.
...
Рейтинг: 0 / 0
13.09.2017, 17:28
| Ответить | Цитировать | Написать  
Выстрел в мозг
    #39520775
HOME_X
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
HOME_X
Участник
dbms_photoshopно двойной connect by и оба раз


Согласен - рукожопство - уточняю
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
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



Вроде не очень громоздко получилось ...
Спасибо за советы
...
Рейтинг: 0 / 0
13.09.2017, 22:37
| Ответить | Цитировать | Написать  
Выстрел в мозг
    #39521014
MaximaXXL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MaximaXXL
Участник
Глянув на селект заметил (Выделено красным в спойлере)
 
HOME_X
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
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




и как предложение
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
                                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
...
Рейтинг: 0 / 0
14.09.2017, 12:07
| Ответить | Цитировать | Написать  
Выстрел в мозг
    #39521233
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
HOME_XВроде не очень громоздко получилосьДа, вполне читаемо.

При большом желании можно обойтись построением дерева из корня и натянуть сверху модель.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
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.
...
Рейтинг: 0 / 0
14.09.2017, 16:11
| Ответить | Цитировать | Написать  
Выстрел в мозг
    #39521244
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
bug nullif
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
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
...
Рейтинг: 0 / 0
14.09.2017, 16:26
| Ответить | Цитировать | Написать  
11 сообщений из 11, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Выстрел в мозг
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&gotonew=1&tid=1885251]:
 0ms
get settings:
 12ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 166ms
get topic data:
 14ms
get first new msg:
 8ms
get forum data:
 4ms
get page messages:
 70ms
get tp. blocked users:
 2ms
others: 280ms
total:  576ms
созд. / загр.: 576ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]