ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Нарастающий итог (агрегированные суммы) по иерархии
15 сообщений из 15, страница 1 из 1
Нарастающий итог (агрегированные суммы) по иерархии
    #39509464
Hans Christian Andersen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hans Christian Andersen
Гость
Добрый день,

имеется иерархия:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT  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,
       level as LEVEL_,
       CONNECT_BY_ISLEAF isleaf,
       material,
       KOMPONENTE
 from STAMM_HIER
     START WITH MATERIAL is NULL
     CONNECT BY NOCYCLE  PRIOR KOMPONENTE =  MATERIAL;



Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
ROOT         PATH                                     KOMPONENTE_HIER         MEASURE     LEVEL_     ISLEAF MATERIAL     KOMPONENTE  
------------ ---------------------------------------- -------------------- ---------- ---------- ---------- ------------ ------------
12345        /12345                                   12345                                    1          0              12345       
12345        /12345/718749                               718749                     2          2          0 12345        718749      
12345        /12345/718749/730014                           730014                  2          3          0 718749       730014      
12345        /12345/718749/730014/809579                       809579               5          4          0 730014       809579      
12345        /12345/718749/730014/809579/111111                   111111            1          5          1 809579       111111      
12345        /12345/718749/730014/809579/222222                   222222            1          5          1 809579       222222      
12345        /12345/718749/730014/809579/798808                   798808            4          5          1 809579       798808      
12345        /12345/718750                               718750                     9          2          0 12345        718750      
12345        /12345/718750/111111                           111111                  1          3          1 718750       111111      
12345        /12345/718750/222222                           222222                  1          3          1 718750       222222      
12345        /12345/718750/809579                           809579                  5          3          0 718750       809579      
12345        /12345/718750/809579/111111                       111111               1          4          1 809579       111111      
12345        /12345/718750/809579/222222                       222222               1          4          1 809579       222222      
12345        /12345/718750/809579/798808                       798808               4          4          1 809579       798808      

14 rows selected.

Как подсчитать нарастающий итог по "measure".
Прилагается картинка с иерархией, где зеленым цветом отображен нарастающий итог.
Заранее спасибо!

Данные здесь:

Код: 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.
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', NULL);
COMMIT;
...
Рейтинг: 0 / 0
23.08.2017, 17:05
| Ответить | Цитировать | Написать  
Нарастающий итог (агрегированные суммы) по иерархии
    #39509469
Vint
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vint
Участник
Hans Christian Andersen,
перед тем как чукча будет следующий раз писать, хоть потрудись посмотреть на этой странице похожие темы.
студенты совсем отупели.
...
Рейтинг: 0 / 0
23.08.2017, 17:09
| Ответить | Цитировать | Написать  
Нарастающий итог (агрегированные суммы) по иерархии
    #39509487
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Vint,

я так понимаю, обратная задачка к 20741682 , от листа к корню


.....
stax
...
Рейтинг: 0 / 0
23.08.2017, 17:33
| Ответить | Цитировать | Написать  
Нарастающий итог (агрегированные суммы) по иерархии
    #39509491
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Hans Christian Andersen,

почему 12345=37?

.....
stax
...
Рейтинг: 0 / 0
23.08.2017, 17:36
| Ответить | Цитировать | Написать  
Нарастающий итог (агрегированные суммы) по иерархии
    #39509494
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Hans Christian Andersen,

поиск типа childs_sum

.....
stax
...
Рейтинг: 0 / 0
23.08.2017, 17:38
| Ответить | Цитировать | Написать  
Нарастающий итог (агрегированные суммы) по иерархии
    #39509500
плюс
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
плюс
Гость
StaxHans Christian Andersen,

почему 12345=37?

.....
stax

две "дочки" 15 и 22
...
Рейтинг: 0 / 0
23.08.2017, 17:43
| Ответить | Цитировать | Написать  
Нарастающий итог (агрегированные суммы) по иерархии
    #39509527
Hans Christian Andersen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hans Christian Andersen
Гость
Проблема в пересекающихся ветках:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
SELECT  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,
       level as LEVEL_,
       CONNECT_BY_ISLEAF isleaf,
       material,
       KOMPONENTE,
       (
         select sum(innen.measure)
         from STAMM_HIER innen
         start with innen.KOMPONENTE = aus.KOMPONENTE
         connect by NOCYCLE prior komponente =  material   
       ) sum_measure
 from STAMM_HIER aus
     START WITH MATERIAL is NULL
     CONNECT BY NOCYCLE PRIOR KOMPONENTE =  MATERIAL;




Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
ROOT         PATH                                     KOMPONENTE_HIER         MEASURE     LEVEL_     ISLEAF MATERIAL     KOMPONENTE   SUM_MEASURE
------------ ---------------------------------------- -------------------- ---------- ---------- ---------- ------------ ------------ -----------
12345        /12345                                   12345                                    1          0              12345                 37
12345        /12345/718749                               718749                     2          2          0 12345        718749                15
12345        /12345/718749/730014                           730014                  2          3          0 718749       730014                13
12345        /12345/718749/730014/809579                       809579               5          4          0 730014       809579                22
12345        /12345/718749/730014/809579/111111                   111111            1          5          1 809579       111111                 2
12345        /12345/718749/730014/809579/222222                   222222            1          5          1 809579       222222                 2
12345        /12345/718749/730014/809579/798808                   798808            4          5          1 809579       798808                 4
12345        /12345/718750                               718750                     9          2          0 12345        718750                22
12345        /12345/718750/111111                           111111                  1          3          1 718750       111111                 2
12345        /12345/718750/222222                           222222                  1          3          1 718750       222222                 2
12345        /12345/718750/809579                           809579                  5          3          0 718750       809579                22
12345        /12345/718750/809579/111111                       111111               1          4          1 809579       111111                 2
12345        /12345/718750/809579/222222                       222222               1          4          1 809579       222222                 2
12345        /12345/718750/809579/798808                       798808               4          4          1 809579       798808                 4

14 rows selected.

Хоть и результат головного элемента (Root) верен (12345 --> 37), нарастающий итог в пересекающихся элементах ошибочный.
...
Рейтинг: 0 / 0
23.08.2017, 18:45
| Ответить | Цитировать | Написать  
Нарастающий итог (агрегированные суммы) по иерархии
    #39509570
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Код: 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.
WITH T AS (
           SELECT  CONNECT_BY_ROOT komponente ROOT,
                   CAST(SYS_CONNECT_BY_PATH(komponente,'/') AS VARCHAR2(40)) PATH,
                   CAST(LPAD(' ',3*(LEVEL-1)) || KOMPONENTE AS VARCHAR2(20)) KOMPONENTE_HIER,
                   MEASURE,
                   LEVEL LEVEL_,
                   CONNECT_BY_ISLEAF ISLEAF,
                   MATERIAL,
                   KOMPONENTE,
                   ROWNUM RN
             FROM  STAMM_HIER
             START WITH MATERIAL is NULL
             CONNECT BY NOCYCLE  PRIOR KOMPONENTE =  MATERIAL
          )
SELECT  ROOT,
        PATH,
        KOMPONENTE_HIER,
        (
         SELECT  SUM(T2.MEASURE)
           FROM  T T2
           WHERE INSTR(T2.PATH,T1.PATH) = 1
        ) MEASURE,
        LEVEL_,
        ISLEAF,
        MATERIAL,
        KOMPONENTE
  FROM  T T1
/

ROOT         PATH                                KOMPONENTE_HIER         MEASURE     LEVEL_     ISLEAF MATERIAL     KOMPONENTE
------------ ----------------------------------- -------------------- ---------- ---------- ---------- ------------ ------------
12345        /12345                              12345                        37          1          0              12345
12345        /12345/718749                          718749                    15          2          0 12345        718749
12345        /12345/718749/730014                      730014                 13          3          0 718749       730014
12345        /12345/718749/730014/809579                  809579              11          4          0 730014       809579
12345        /12345/718749/730014/809579/222222              222222            1          5          1 809579       222222
12345        /12345/718749/730014/809579/111111              111111            1          5          1 809579       111111
12345        /12345/718749/730014/809579/798808              798808            4          5          1 809579       798808
12345        /12345/718750                          718750                    22          2          0 12345        718750
12345        /12345/718750/222222                      222222                  1          3          1 718750       222222
12345        /12345/718750/111111                      111111                  1          3          1 718750       111111
12345        /12345/718750/809579                      809579                 11          3          0 718750       809579
12345        /12345/718750/809579/222222                  222222               1          4          1 809579       222222
12345        /12345/718750/809579/111111                  111111               1          4          1 809579       111111
12345        /12345/718750/809579/798808                  798808               4          4          1 809579       798808

14 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
23.08.2017, 20:44
| Ответить | Цитировать | Написать  
Нарастающий итог (агрегированные суммы) по иерархии
    #39509774
Hans Christian Andersen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hans Christian Andersen
Гость
SY,

спасибо большое. Решение оказалось очень простое.
...
Рейтинг: 0 / 0
24.08.2017, 11:42
| Ответить | Цитировать | Написать  
Нарастающий итог (агрегированные суммы) по иерархии
    #39510096
MaximaXXL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MaximaXXL
Участник
Hans Christian AndersenПроблема в пересекающихся ветках:

.............

Проблема в соединении селектов:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
SELECT  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,
       level as LEVEL_,
       CONNECT_BY_ISLEAF isleaf,
       material,
       KOMPONENTE,
       (
         select sum(innen.measure)
         from STAMM_HIER innen
         start with innen.id = aus.id
         connect by NOCYCLE prior komponente =  material   
       ) sum_measure
 from STAMM_HIER aus
     START WITH MATERIAL is NULL
     CONNECT BY NOCYCLE PRIOR KOMPONENTE =  MATERIAL;
...
Рейтинг: 0 / 0
24.08.2017, 18:20
| Ответить | Цитировать | Написать  
Нарастающий итог (агрегированные суммы) по иерархии
    #39513512
Hans Christian Andersen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hans Christian Andersen
Гость
SY,

а как можно оптимировать запрос, если имеется 5 measure?
...
Рейтинг: 0 / 0
31.08.2017, 15:32
| Ответить | Цитировать | Написать  
Нарастающий итог (агрегированные суммы) по иерархии
    #39513514
MaximaXXL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MaximaXXL
Участник
Hans Christian Andersen,

Мой последний не пробовал?
...
Рейтинг: 0 / 0
31.08.2017, 15:34
| Ответить | Цитировать | Написать  
Нарастающий итог (агрегированные суммы) по иерархии
    #39513553
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Hans Christian AndersenSY,

а как можно оптимировать запрос, если имеется 5 measure?
суммы || в строку, и разобрать обратно

.....
stax
...
Рейтинг: 0 / 0
31.08.2017, 16:33
| Ответить | Цитировать | Написать  
Нарастающий итог (агрегированные суммы) по иерархии
    #39513574
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Hans Christian Andersen,

не знаю будет ли намного быстрее,
в подзапросе старт по ровиду

Код: 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.
  1  select aus.*,
  2         (
  3           select sum(innen.measure)
  4           from STAMM_HIER innen
  5           start with innen.rowid = aus.rowid
  6           connect by NOCYCLE prior komponente =  material
  7         ) sum_measure
  8* from STAMM_HIER aus
SQL> /

        ID MATERIAL     KOMPONENTE      MEASURE SUM_MEASURE
---------- ------------ ------------ ---------- -----------
         1 12345        718749                2          15
         2 718750       222222                1           1
         3 730014       809579                5          11
         4 718750       111111                1           1
         5 809579       222222                1           1
         6 718750       809579                5          11
         7 12345        718750                9          22
         8 809579       111111                1           1
         9 718749       730014                2          13
        10 809579       798808                4           4
        11              12345                            37

11 rows selected.




.....
stax
...
Рейтинг: 0 / 0
31.08.2017, 16:58
| Ответить | Цитировать | Написать  
Нарастающий итог (агрегированные суммы) по иерархии
    #39513581
Hans Christian Andersen
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hans Christian Andersen
Гость
Stax,

спасибо, попробую!
...
Рейтинг: 0 / 0
31.08.2017, 17:04
| Ответить | Цитировать | Написать  
15 сообщений из 15, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Нарастающий итог (агрегированные суммы) по иерархии
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39513512&tid=1885332]:
 0ms
get settings:
 6ms
get forum list:
 13ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 144ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 54ms
get tp. blocked users:
 1ms
others: 197ms
total:  429ms
созд. / загр.: 429ms
x
x
Закрыть


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