VPF::Группировка итогов по дереву - Форум программистов

turbanoff

Дата 26.5.2011, 20:48 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 57
Регистрация: 6.4.2009

Репутация: нет
Всего: 1

Есть 2 таблицы.

SQL

--Таблица с числами
create table summs
(
   numb number, --группировать для каждого numb свой итог
   iekr_id number NOT NULL,
   AMOUNT number(15,2),
   CONSTRAINT FK_SUMMS_IEKR FOREIGN KEY (IEKR_ID) REFERENCES IEKR(ID)
)
--дерево , по которому надо сформировать итоги
create table iekr
(
   id number,
   parent_id number, --id родителя
   CONSTRAINT FK_IEKR_IEKR FOREIGN KEY (parent_id) REFERENCES iekr(id),
   CONSTRAINT PK_IEKR PRIMARY KEY (id)
)

нужно по таблице summs сформировать суммы с промежуточными итогами
пример SUMMS:

пример IEKR:

нужный результат:

Zloxa

Дата 27.5.2011, 09:46 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 37
Всего: 161

1 скалярным подзапросом

Код


SQL> with summs as (
  2    select 1 numb, 101 iekr_id, 100 amount from dual
  3    union all select 1 numb, 102 iekr_id, 10 amount from dual
  4    union all select 1 numb, 201 iekr_id, 1 amount from dual
  5    union all select 2 numb, 101 iekr_id, 200 amount from dual
  6    union all select 2 numb, 201 iekr_id, 20 amount from dual
  7    union all select 3 numb, 101 iekr_id, 300 amount from dual
  8    union all select 3 numb, 101 iekr_id, 3 amount from dual
  9    union all select 3 numb, 102 iekr_id, 30 amount from dual
 10  )
 11    ,iekr as (
 12    select 0 id, null parent_id from dual
 13    union all select 10 id, 0 parent_id from dual
 14    union all select 20 id, 0 parent_id from dual
 15    union all select 101 id, 10 parent_id from dual
 16    union all select 102 id, 10 parent_id from dual
 17    union all select 201 id, 20 parent_id from dual
 18    )
 19  select
 20         s.numb
 21         ,t.id iekr_id
 22         ,t.parent_id
 23         ,s.amount
 24         ,(select sum(ss.amount)
 25              from iekr tt
 26              left join summs ss partition by (ss.numb) on tt.id = ss.iekr_id
 27              start with tt.id = t.id and ss.numb = s.numb
 28              connect by prior tt.id = tt.parent_id and prior ss.numb = ss.numb
 29           ) scalar_sum
 30  from iekr t
 31  left join summs s partition by (s.numb) on t.id = s.iekr_id
 32  start with t.id = 0
 33  connect by prior t.id = t.parent_id and prior s.numb = s.numb
 34  ;
 
      NUMB    IEKR_ID  PARENT_ID     AMOUNT SCALAR_SUM
---------- ---------- ---------- ---------- ----------
         1          0                              111
         1         10          0                   110
         1        101         10        100        100
         1        102         10         10         10
         1         20          0                     1
         1        201         20          1          1
         2          0                              220
         2         10          0                   200
         2        101         10        200        200
         2        102         10            
         2         20          0                    20
         2        201         20         20         20
         3          0                              333
         3         10          0                   333
         3        101         10        300        303
         3        101         10          3        303
         3        102         10         30         30
         3         20          0            
         3        201         20            
 
19 rows selected

2 моделью

Код


 19  select numb,iekr_id,parent_id, amount, summ_amount
 20  from iekr t
 21  left join summs s partition by (s.numb) on t.id = s.iekr_id
 22  start with t.id = 0
 23  connect by prior t.id = t.parent_id and prior s.numb = s.numb
 24  model dimension by (s.numb,t.id iekr_id, t.parent_id, rownum rn)
 25        measures (level lvl, amount, 0 summ_amount)
 26  (summ_amount[any,any,any,any] order by lvl desc,rn = nvl(sum(nvl(summ_amount,0))[cv(),any,cv(iekr_id),any],0)+nvl(amount[cv(),cv(),cv(),cv()],0)
 27  )
 28  order by rn
 29  ;
 
      NUMB    IEKR_ID  PARENT_ID     AMOUNT SUMM_AMOUNT
---------- ---------- ---------- ---------- -----------
         1          0                               111
         1         10          0                    110
         1        101         10        100         100
         1        102         10         10          10
         1         20          0                      1
         1        201         20          1           1
         2          0                               220
         2         10          0                    200
         2        101         10        200         200
         2        102         10                      0
         2         20          0                     20
         2        201         20         20          20
         3          0                               333
         3         10          0                    333
         3        101         10        300         300
         3        101         10          3           3
         3        102         10         30          30
         3         20          0                      0
         3        201         20                      0
 
19 rows selected

Оба варианта - не айс

Это сообщение отредактировал(а) Zloxa - 27.5.2011, 09:48

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

triclosan

Дата 27.5.2011, 11:34 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 515
Регистрация: 18.8.2006

Репутация: нет
Всего: 12

turbanoff, т.е. развернуть все дерево построчно и просуммировать AMOUNT по каждой ветке?

turbanoff

Дата 27.5.2011, 12:32 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 57
Регистрация: 6.4.2009

Репутация: нет
Всего: 1

triclosan
Да - нужно просуммировать AMOUNT по каждой ветке. Но те ветки, которых нет - они не нужны.
В примере для numb=3: есть только iekr_id =101 и =102, поэтому в итогах нет 20 и 201

1. У SUMMS iekr_id может быть только для конечных(листьев дерева)
2. Для каждого numb - свой итог

Это сообщение отредактировал(а) turbanoff - 27.5.2011, 12:36

triclosan

Дата 27.5.2011, 14:04 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 515
Регистрация: 18.8.2006

Репутация: нет
Всего: 12

с коленки:

Код


select s.numb, t.id, sum(s.amount)
from 
(select *
from iekr ps
CONNECT BY PRIOR ps.id = ps.parent_id
start with ps.id  = 0) t, summs s
s.iekr_id = t.id 
group by s.numb, t.id

под рукой ничего нет, что-то похожее возвращает?

Zloxa

Дата 27.5.2011, 14:08 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 37
Всего: 161

Цитата(triclosan @ 27.5.2011, 14:04

)

под рукой ничего нет, что-то похожее возвращает?

Код


 19  select s.numb, t.id, sum(s.amount)
 20  from
 21  (select *
 22  from iekr ps
 23  CONNECT BY PRIOR ps.id = ps.parent_id
 24  start with ps.id  = 0) t, summs s
 25  where s.iekr_id = t.id
 26  group by s.numb, t.id
 27  ;
 
      NUMB         ID SUM(S.AMOUNT)
---------- ---------- -------------
         1        101           100
         1        102            10
         1        201             1
         2        101           200
         2        201            20
         3        101           303
         3        102            30
 
7 rows selected

Нет размотки по иерархии, нет нкопления. Дервяха тут вообще не к месту, результат деградирует к простому джойну.

Полагаешь я перемудрил? smile

Добавлено через 6 минут и 20 секунд
Мне кажется есть еще способ решить задачу исползуя Recursive Subquery Factoring.
Но его ввели только в 11й версии, елси не в 11.2,а его под рукой у меня нет - попробовать ((

Это сообщение отредактировал(а) Zloxa - 27.5.2011, 14:10

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

triclosan

Дата 27.5.2011, 14:47 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 515
Регистрация: 18.8.2006

Репутация: нет
Всего: 12

Цитата(Zloxa @ 27.5.2011, 14:08

)

Полагаешь я перемудрил?

не, просто попытался решить задачу на уровне своего понимания природы вещей. smile

turbanoff, PL/SQL может заюзать?

turbanoff

Дата 27.5.2011, 16:31 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 57
Регистрация: 6.4.2009

Репутация: нет
Всего: 1

У меня 10 XE.
Да PL/SQL можно. Результат в другую таблицу записать.
В базе строк ~50к и растет. Ну и я уменьшил количество полей для примера
Все, что я пробовал - работало, скажем, недостаточно быстро. Думал, мб если на SQL можно ухитриться - то быстрее буддет

Zloxa

Дата 27.5.2011, 16:40 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 37
Всего: 161

Цитата(turbanoff @ 27.5.2011, 16:31

)

Результат в другую таблицу записать.

если так, то в первом запросе не нужен start with и connect by в основном запросе.

Вы попробовали предложенные мною варианты? мне безумно интересно какой из них оказался менее производительным на объеме.

Цитата(turbanoff @ 27.5.2011, 12:32

)

те ветки, которых нет - они не нужны.

для реализации этого условия, в моих примерах можно добавить фильтр по SUMM_AMOUNT > 0 или SCALAR_SUM >0

Это сообщение отредактировал(а) Zloxa - 27.5.2011, 16:41

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

turbanoff

Дата 27.5.2011, 18:57 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 57
Регистрация: 6.4.2009

Репутация: нет
Всего: 1

Цитата

Вы попробовали предложенные мною варианты? мне безумно интересно какой из них оказался менее производительным на объеме.

1-й за приемлемое время выполнить не удалось. Сейчас 2-й попробую

turbanoff

Дата 30.5.2011, 17:12 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 57
Регистрация: 6.4.2009

Репутация: нет
Всего: 1

2-й тоже. возможно дело в том что в дереве довольно много элементов: 250
а запрос идет по всему дереву для каждого numb

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Oracle \| Следующая тема »