Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Агрегация сумм в иерархических запросах  [new]
chameleon82
Member

Откуда: Russia, Новосибирск
Сообщений: 252
Всех приветствую, возник такой вопрос, необходимо сагрегировать суммы в иерархическом запросе. Имеется некая иерархия: "Блоки/Контрагенты/Статьи/Счета", иерархия настроена определенным образом как этого хочет видеть заказчик.
Соответственно, при присоединении данных к иерархии суммы в рублях у меня находятся в самых нижних уровнях иерархии. Задача - получить агрегированные суммы по иерархии.
Например, я делаю селект:
SELECT lpad(' ',(level-1)*5)||description , amount
FROM XX_BDR_HIER_V v
CONNECT BY PRIOR flex_value = parent_flex_value
START WITH parent_flex_value is null 

и получаю нечто вроде:

Блок инвестиционные расходы		0
     Технические расходы		0
         Статья 1			2000
         Статья 2			3000
     Прочие расходы			1000

А хотелось бы
Блок инвестиционные расходы		6000
     Технические расходы		5000
         Статья 1			2000
         Статья 2			3000
     Прочие расходы			1000

Притом, гонять иерархию на расчет каждой суммы (сумма иерархии от парент-нода текущей строки в иерархии) не хотелось бы - увеличение времени работы сложного запроса, практика показала, что на больших данных могут еще и нехорошие ошибки вываливаться.
Также не пойдет метод джоина с группировкой по каждой строке иерархии по полям Блок/контрагент/статьи/счета, т.к. могут быть сложные правила соединения (например, по определенным дополнительным условиям данные попадают в другой блок), а потом заворачивание на иерархию.

Самым простым я тут вижу написание функции по типу sys_connect_by_path, но суммирующую суммы с дочерних нодов (допускается с суммой текущего нода для простоты). Но чтото не могу сообразить как это сделать. Помогите разобраться :)
Пс: Oracle 9i
7 июл 10, 08:22    [9061618]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
-2-
Member

Откуда:
Сообщений: 15330
chameleon82,

with t as (
   select 1 id, null parent_id, 'Top' description, 0 amount from dual union all
   select 2 id, 1 parent_id, 'Top-One' description, 0 amount from dual union all
   select 3 id, 2 parent_id, 'One-one' description, 2000 amount from dual union all
   select 4 id, 2 parent_id, 'One-two' description, 3000 amount from dual union all
   select 5 id, 1 parent_id, 'Top-Two' description, 1000 amount from dual 
)
select *
from t
start with parent_id is null
connect by prior id = parent_id
model 
dimension by (id, parent_id, rownum r)
measures(amount)
rules (
   amount[any, any, any] order by r desc = nvl(amount[cv(), cv(), cv()],0) + nvl(sum(amount)[any, cv(id), any],0)
)
+апгрейд до 10g.
7 июл 10, 09:19    [9061746]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
Добрый Э - Эх
Guest
Вложенный деревянный запрос:
-- Тестовые данные:
with XX_BDR_HIER_V as (
   select 1 flex_value, null parent_flex_value, 'Top' description, 0 amount from dual union all
   select 2 flex_value, 1 parent_flex_value, 'Top-One' description, 0 amount from dual union all
   select 3 flex_value, 2 parent_flex_value, 'One-one' description, 2000 amount from dual union all
   select 4 flex_value, 2 parent_flex_value, 'One-two' description, 3000 amount from dual union all
   select 5 flex_value, 1 parent_flex_value, 'Top-Two' description, 1000 amount from dual 
)
--
--
-- Основной запрос:
SELECT lpad(' ',(level-1)*5)||description as description, amount,
       (
         select sum(amount) 
           from XX_BDR_HIER_V z
          start with z.flex_value = v.flex_value
        connect by prior flex_value = parent_flex_value
       ) as total_amount
  FROM XX_BDR_HIER_V v
CONNECT BY PRIOR flex_value = parent_flex_value
 START WITH parent_flex_value is null 


Query finished, retrieving results...

   DESCRIPTION        AMOUNT     TOTAL_AMOUNT
-----------------     ------     ------------
Top                        0             6000
     Top-One               0             5000
          One-one       2000             2000
          One-two       3000             3000
     Top-Two            1000             1000

5 row(s) retrieved
7 июл 10, 09:35    [9061813]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
chameleon82
Member

Откуда: Russia, Новосибирск
Сообщений: 252
-2-, к сожалению апгрейд исключен. Продакшен сервер с офф.поддержкой, на 10ку нет денег.

2Добрый Э-эх. Я изначально решил задачу точно таким же методом, но, к сожалению, время работы его заметно увеличилось, при больших объемах вываливается too old snapshot или еще какая-нибудь нехорошая вещь. Материализовать подзапрос также не предоставляется возможным изза вероятности выпадения ошибки нехватки temp.
Поэтому нужно какое-нибудь элегантное решение. Есть конечно вариант через промежуточную temp таблицу, но очень хотелось бы без нее.
7 июл 10, 10:41    [9062407]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
ALocky
Member

Откуда: Понаехал из Подмосковья
Сообщений: 747
chameleon82,

Почему бы не хранить нужные тебе суммы в той же таблице? Один раз столбец просчитаешь, дальше выборка будет мгновенная.
7 июл 10, 10:46    [9062452]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
-2-
Member

Откуда:
Сообщений: 15330
chameleon82,

Если уровень вложенности ограничен udf_evaluate(sys_connect_by_path(amount, '+'))
7 июл 10, 10:54    [9062517]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
chameleon82
Member

Откуда: Russia, Новосибирск
Сообщений: 252
Alocky,
Иерархия - это наборы значений, там свои поля добавлять нельзя - да и не нужно это - т.к. система многопользовательская.
Реальные данные, которые соединяются с этой иерархией, сгруппированные данные порядка мильона строк. Фактически эти сгруппированные данные - это еще один более нижний уровень, т.к. пользователь может запросить и детализацию (в разных блоках группировка по разным критериям может быть).

Хотя есть вариант, на который вы меня навели: сделать временную таблицу, в нее сгрузить и иерархию, и сгруппированные данные со ссылкой на парент_ид, потом все это завернуть в отчет. Однако это не решает моей задачи. Пересчет каждого нода все равно надо делать :) Хотя в данном случае snapshoot too old практически сводится к минимальным шансам.
7 июл 10, 10:56    [9062533]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
ALocky
Member

Откуда: Понаехал из Подмосковья
Сообщений: 747
chameleon82

Иерархия - это наборы значений, там свои поля добавлять нельзя - да и не нужно это - т.к. система многопользовательская.

Что-то связи не увидел

chameleon82
Пересчет каждого нода все равно надо делать

Конечно надо, кто ж спорит. Решать это можно по-разному, тут от задачи зависит. Общих рекомендаций, думаю, не существует.
7 июл 10, 11:02    [9062584]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
chameleon82
Member

Откуда: Russia, Новосибирск
Сообщений: 252
-2-
chameleon82,

Если уровень вложенности ограничен udf_evaluate(sys_connect_by_path(amount, '+'))


Нет функции udf_evaluate, google не помогает :( Что это за функция? Если суммы собираются с нижних уровней к верхним, то в качестве решения пойдет. Уровень вложенности не более 5-6ти, так что в этом плане так же пойдет.
7 июл 10, 11:05    [9062615]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
chameleon82
Member

Откуда: Russia, Новосибирск
Сообщений: 252
ALocky
chameleon82

Иерархия - это наборы значений, там свои поля добавлять нельзя - да и не нужно это - т.к. система многопользовательская.

Что-то связи не увидел


OeBS, иерархию можно получить в виде вьюшки. Если повесить поле сумма на какой-нибудь атрибут, то что произойдет, если два пользователя запустят отчет одновременно? Fail.
В общем я хотел бы решение, похожее на приведенное -2-, возможно с помощью самописной аналитической функции.
7 июл 10, 11:09    [9062662]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
-2-
Member

Откуда:
Сообщений: 15330
chameleon82
Нет функции udf_evaluate, google не помогает :( Что это за функция? Если суммы собираются с нижних уровней к верхним, то в качестве решения пойдет. Уровень вложенности не более 5-6ти, так что в этом плане так же пойдет.
Что за функция, можно было догадаться из названия, но собирает она действительно сверху вниз - не подходит.
7 июл 10, 11:15    [9062722]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
chameleon82
Member

Откуда: Russia, Новосибирск
Сообщений: 252
-2-
Что за функция, можно было догадаться из названия, но собирает она действительно сверху вниз - не подходит.

По названию то я догадался ), но в нашей 9i ее нет (
7 июл 10, 11:18    [9062744]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54383
здесь писал лет 5 назад

используем принцип в сотнях отчетов
7 июл 10, 11:25    [9062799]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
chameleon82,

без двойной деревяшки - это так:
select b.empno, b.ename, a.s1, a.s2 from
(select a.empno ,sum(a.sal) s1, sum(a.comm) s2
  from scott.emp a
      ,(select empno
          from scott.emp
    start with mgr is not null
   connect by prior mgr = empno) b
group by a.empno
) a, scott.emp b
where a.empno=b.empno
order by 2;
7 июл 10, 11:32    [9062874]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
-2-
Member

Откуда:
Сообщений: 15330
Не знаю, будет ли быстрее пять-шесть over(partition by ...)
with t as ( --id - уникальны
   select 1 id, null parent_id, 'Top' description, 10 amount from dual union all
   select 2 id, 1 parent_id, 'Top-One' description, 100 amount from dual union all
   select 3 id, 2 parent_id, 'One-one' description, 2000 amount from dual union all
   select 4 id, 2 parent_id, 'One-two' description, 3000 amount from dual union all
   select 5 id, 1 parent_id, 'Top-Two' description, 1000 amount from dual union all
   select 4 id, 2 parent_id, 'One-three' description, 300 amount from dual 
)
select id, description, amount, 
   case l 
      when 1 then sum(amount) over(partition by p1)
      when 2 then sum(amount) over(partition by p2)
      when 3 then sum(amount) over(partition by p3)
      when 4 then sum(amount) over(partition by p4)
      when 5 then sum(amount) over(partition by p5)
   end ha
from (
select level l, id, parent_id, description, amount, 
sys_connect_by_path(id,'/') p,
substR(sys_connect_by_path(id,'/'), 1, 1*2) p1,
substR(sys_connect_by_path(id,'/'), 1, 2*2) p2,
substR(sys_connect_by_path(id,'/'), 1, 3*2) p3,
substR(sys_connect_by_path(id,'/'), 1, 4*2) p4,
substR(sys_connect_by_path(id,'/'), 1, 5*2) p5
from t
start with parent_id is null
connect by prior id = parent_id
) tt;
7 июл 10, 11:33    [9062883]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
-2-
Member

Откуда:
Сообщений: 15330
-2-,

sys_connect_by_path(id,'/') -> sys_connect_by_path('*','/')
7 июл 10, 11:34    [9062892]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
-2-
Member

Откуда:
Сообщений: 15330
-2-,

черт... в общем, выровнять id на фиксированную длину
7 июл 10, 11:35    [9062906]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
chameleon82
Member

Откуда: Russia, Новосибирск
Сообщений: 252
В общем придумал такую вещь :)
select lpad(' ',(level-1)*5)||t.job hier
	 , sal
     , (select extractvalue(a.column_value, '/*/*/*') from table(xmlsequence(dbms_xmlgen.getXMLtype('select '||t.summ||' column_value from dual a')))a)
	   sal_summ   
from (
select ( '0'||sys_connect_by_path(sal,'+') ) summ
    , e.* from scott.emp e
start with mgr is not null
connect by prior mgr = empno
) t
start with mgr is null
connect by mgr = prior empno
order siblings by job
Теперь осталось это прикрутить как-нибудь к реальным данным. Все же мне кажется, в любом случае придется использовать global temporary table. Но похоже, гадких ошибок уже не будет.
7 июл 10, 13:00    [9063718]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
chameleon82
Member

Откуда: Russia, Новосибирск
Сообщений: 252
^^^
Кстати, в этом виде все равно просчитывается для каждого нода :( а используются ли повторно вычисленные значения по иерархии - не знаю как проверить? Если да - то замечательно, если нет, то видимо все равно тормоза будут. Пс: в реальной иерархии порядка 50-100 нодов, с детализацией может быть до 1000.
7 июл 10, 13:10    [9063834]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
-2-
Member

Откуда:
Сообщений: 15330
Без анала и моделей:
with t as ( 
   select 1 id, null parent_id, 'Top' description, 10 amount from dual union all
   select 2 id, 1 parent_id, 'Top-One' description, 100 amount from dual union all
   select 3 id, 2 parent_id, 'One-one' description, 2000 amount from dual union all
   select 4 id, 2 parent_id, 'One-two' description, 3000 amount from dual union all
   select 5 id, 1 parent_id, 'Top-Two' description, 1000 amount from dual union all
   select 6 id, 2 parent_id, 'One-three' description, 300 amount from dual 
)
select 
   min(id) keep(dense_rank first order by l) id,
   min(description) keep(dense_rank first order by l) description,
   sum(amount)
from (
     select level l, id, parent_id, description, amount, 
     sys_connect_by_path(id,'/') p,
     std.subword(sys_connect_by_path(id,'/'), 1) p1,
     std.subword(sys_connect_by_path(id,'/'), 2) p2,
     std.subword(sys_connect_by_path(id,'/'), 3) p3,
     std.subword(sys_connect_by_path(id,'/'), 4) p4,
     std.subword(sys_connect_by_path(id,'/'), 5) p5 --максимальная глубина хиерархии
   from t
   start with parent_id is null
   connect by prior id = parent_id
) tt
group by p1, rollup(p2,p3,p4,p5)
having grouping_id(p2, p3, p4, p5) = power(2, 5-min(l))-1
;
7 июл 10, 13:51    [9064322]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
chameleon82
Member

Откуда: Russia, Новосибирск
Сообщений: 252
Решение интересное
Однако std.subword - опять же с 10ки? или что за пакет? заменил на substr, вроде работает :)
7 июл 10, 14:02    [9064400]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
-2-
Member

Откуда:
Сообщений: 15330
chameleon82
Однако std.subword - опять же с 10ки?
Нет еще с семерки тяну на каждую БД, где пасусь...
7 июл 10, 14:08    [9064436]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
-2-
Member

Откуда:
Сообщений: 15330
От having можно попробовать избавиться через grouping sets, но не допер как.
7 июл 10, 14:10    [9064458]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
chameleon82
Member

Откуда: Russia, Новосибирск
Сообщений: 252
-2-
От having можно попробовать избавиться через grouping sets, но не допер как.


Указать необходимые в выводе сочетания строк
7 июл 10, 15:09    [9065005]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация сумм в иерархических запросах  [new]
LG
Member

Откуда:
Сообщений: 352
-2-,

-2-
Без анала и моделей:

Результат не верный :)
7 июл 10, 16:25    [9065764]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Oracle Ответить