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

Откуда:
Сообщений: 40
Здравствуйте. Решаю задачу:
Eсть таблица категорий товара.
create table category (catid number primary key enable, -- ключ
par_catid number references category, -- ссылка на родительскую категорию
name varchar2(300), -- наименование категории
descr varchar2(2000), -- описание
cdate date -- дата создания
);
Есть таблица товаров.
create table goods (gid number primary key enable, -- ключ
cat number references category, -- ссылка на категорию
name varchar2(300), -- наименование товара
descr varchar2(2000), -- описание
unit number references cargo, -- единица измерения
loc number references place, -- место хранения
resp number references corp, -- ответственный за сопровождение
);
Есть таблица движения товаров.
create table moves (gd number references goods, -- товар
mdate date, -- дата операции
inout varchar2(1) default '+', -- приход/расход
amount number, -- количество
price number -- цена
);
Требуется оформить вывод в таком виде
_______________
Категория уровня1||Суммарная стоимость прихода||Суммарная стоимость расхода
...
Категория уровняN||Суммарная стоимость прихода||Суммарная стоимость расхода
Товар 1 категории уровняN|Суммарное количество прихода|Суммарная стоимость прихода|Суммарное количество расхода|Суммарная стоимость расхода| Остаток товара
...
Товар m категории уровняN|Суммарное количество прихода|Суммарная стоимость прихода|Суммарное количество расхода|Суммарная стоимость расхода| Остаток товара

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

Как сделать вывод в таком виде разобрался:
select
LPAD(' ',(level-1)*2) || name as name_cat ,catid,par_catid,level
from category 
connect by prior catid=par_catid
start with catid in (select catid
from category
where par_catid is null)
ORDER SIBLINGS BY name


Помогите добавить: Суммирование стоимости и количества прихода и расхода; вывод продуктов по своим категориям.
17 мар 13, 19:29    [14059856]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
alekssey
Member

Откуда:
Сообщений: 40
Неужели ни у кого нет мыслей по моей задаче?
18 мар 13, 20:31    [14064409]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
Sacramento
Member [заблокирован]

Откуда: from Paris with love
Сообщений: 525
alekssey
Неужели ни у кого нет мыслей по моей задаче?

ты понимаеш, если и есть, то а ля "еще одно"

А чтобы появились такие что решить тебе задачу, то тебе эту самую задачу не помешало бы привести в надлежащий вид, привести исходную выборку и конечный результат. Кому интересно делать hard-parse твоего текста кроме тебя самого? А так, кто-то лучше пойдет ужын готовить, кто-то девушке позвонит, я вот лучше досмотрю полуфинал турнира в Indian Wells :)

ЗЫ

start with catid in (select catid
from category
where par_catid is null)


чем тебе не нравиццо просто
start with par_catid is null
18 мар 13, 23:08    [14064772]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
alekssey
Неужели ни у кого нет мыслей по моей задаче?

поиском пользуйтесь.
например, сумма дерева
19 мар 13, 12:44    [14066677]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
alekssey
Member

Откуда:
Сообщений: 40
orawish
alekssey
Неужели ни у кого нет мыслей по моей задаче?

поиском пользуйтесь.
например, сумма дерева


Поиском пользовался, похожее есть но не совсем. Там везде разбираются примеры с иерархией в одной таблице, с этим бы я и сам справился, но у меня их 3, в этом то и вся сложность.

автор
А чтобы появились такие что решить тебе задачу, то тебе эту самую задачу не помешало бы привести в надлежащий вид, привести исходную выборку и конечный результат


Условие задачи в том виде, в котором оно было предоставлено мне.
20 мар 13, 11:36    [14071241]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
ten
Member

Откуда: Екатеринбург
Сообщений: 1672
alekssey,
Так ведь и у тебя иерархия только в одной таблице. Из остальных берутся только суммы.
20 мар 13, 12:09    [14071433]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
alekssey
Member

Откуда:
Сообщений: 40
проблема в том что я не знаю как подцепить товар только на последней ступени иерархии
20 мар 13, 13:55    [14072061]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
ten
Member

Откуда: Екатеринбург
Сообщений: 1672
alekssey,
По уже приведенной ссылке все это есть.
20 мар 13, 14:40    [14072303]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
alekssey
проблема в том что я не знаю как подцепить товар только на последней ступени иерархии

даже если не можете это определить по значениям, можно по
  connect_by_isleaf 
20 мар 13, 14:42    [14072316]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
alekssey
Member

Откуда:
Сообщений: 40
Вообщем немного сдвинулся с места, написал вот что:
select
LPAD(' ',(level-1)*2) || c.name,
 Prihod,Rashod
from category c left join

(select ctg.catid ,  sum(decode(inout,'+',m.price * m.amount)) as Prihod ,
                sum(decode(inout,'-',m.price * m.amount)) as Rashod

    from category ctg, goods g, moves m
    where  ctg.catid=g.cat and m.gd=g.gid
    group by ctg.catid) 


x on c.catid = x.catid


connect by prior c.catid=c.par_catid
start with c.par_catid is null


Считает нормально, но только на самом последнем уровне иерархии. Вопрос как проставить значения на уровнях выше?
Категория уровня1
Категория2
Категория2_1
Категория3
Категория4 2718 243
атегория1_1
Категория2_2
Категория3_1 1137 1498
21 мар 13, 11:36    [14076279]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8131
alekssey
Неужели ни у кого нет мыслей по моей задаче?
Полезная ссылка
21 мар 13, 11:57    [14076418]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
ten
Member

Откуда: Екатеринбург
Сообщений: 1672
alekssey
Вообщем немного сдвинулся с места, написал вот что

Чукча не читатель?
Если даже не обращать внимание, что обращаться к category дважды нет никакого смысла, то что делает этот запрос? Просто строит иерархию и для каждого элемента считает сумму. Тебе же нужна сумма по дереву, а не сумма для каждого элемента дерева по отдельности.
21 мар 13, 12:35    [14076660]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
alekssey
Member

Откуда:
Сообщений: 40
Вот этот запрос что дает:
SELECT pt.ID, first_value(id) over (PARTITION BY ROWNUM-LEVEL ORDER BY ROWNUM) fv     
			FROM PROBA_TREE pt    
			CONNECT BY PRIOR pt.ID_PARENT=pt.ID
21 мар 13, 13:17    [14076943]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
alekssey
Member

Откуда:
Сообщений: 40
И вообще, если все такие умные, ссылками забрасываете, я только начинаю работу с ораклом. Растолкуйте тогда запрос:
вот полностью готовый РАБОТАЮЩИЙ запрос для 2 таблицбез подзапросов в цикле
SELECT    
pt.name, pt.id, pt_sum.summa, pt.lv    
FROM    
	(    
	SELECT pt.ID, SUM(pts.summa) summa    
	FROM    
		(	SELECT pt.ID, first_value(id) over (PARTITION BY ROWNUM-LEVEL ORDER BY ROWNUM) fv     
			FROM PROBA_TREE pt    
			CONNECT BY PRIOR pt.ID_PARENT=pt.ID    
		)pt,    
		PROBA_TREE_SUM pts    
	WHERE pt.fv=pts.id_tree    
	GROUP BY pt.id    
	) pt_sum,    
	(SELECT pt.ID, pt.ID_parent, LPAD(' ',2 * (LEVEL - 1)) ||pt.name name, ROWNUM rn, LEVEL lv    
			FROM PROBA_TREE pt    
			CONNECT BY PRIOR pt.ID=pt.ID_PARENT    
	) pt    
WHERE pt.id=pt_sum.id
ORDER BY pt.rn
Таблицы
CREATE TABLE PROBA_TREE
(
  ID         NUMBER(5)                          NOT NULL,
  ID_PARENT  NUMBER(5),
  NAME       VARCHAR2(30)
);

CREATE TABLE PROBA_TREE_SUM
(
  ID_TREE  NUMBER(3)                            NOT NULL,
  SUMMA    NUMBER(10,2)
);

INSERT INTO PROBA_TREE ( ID, ID_PARENT, NAME ) VALUES ( 
1, NULL, 'Главная'); 
INSERT INTO PROBA_TREE ( ID, ID_PARENT, NAME ) VALUES ( 
2, 1, 'второй уровень 2'); 
INSERT INTO PROBA_TREE ( ID, ID_PARENT, NAME ) VALUES ( 
3, 1, 'второй уровень 3'); 
INSERT INTO PROBA_TREE ( ID, ID_PARENT, NAME ) VALUES ( 
4, 2, 'третий уровень 1'); 
INSERT INTO PROBA_TREE_SUM ( ID, ID_TREE, SUMMA ) VALUES ( 
1, 4, 4); 
INSERT INTO PROBA_TREE_SUM ( ID, ID_TREE, SUMMA ) VALUES ( 
2, 3, 3); 
INSERT INTO PROBA_TREE_SUM ( ID, ID_TREE, SUMMA ) VALUES ( 
3, 2, 2); 
COMMIT;
21 мар 13, 13:20    [14076969]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
ten
Member

Откуда: Екатеринбург
Сообщений: 1672
alekssey,
Я думал дежавю, но нет :). Плагиат, однако, нехорошо.
По теме, если непонятно, то, что написал Андрей, то вот это-то должно быть понятно. 9061813
21 мар 13, 15:34    [14077872]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
alekssey
Member

Откуда:
Сообщений: 40
С этим разобрался, по дереву все суммируется:
SELECT 
pt.name, pt.catid, pt_sum.Prihod, pt_sum.Rashod, pt.lv 
FROM 
( 
  SELECT pt.catID, sum(decode(inout,'+',m.price * m.amount)) as Prihod, 
                 sum(decode(inout,'-',m.price * m.amount)) as Rashod

  FROM 
        ( SELECT pt.catID,  CONNECT_BY_ROOT pt.catid fv 
        FROM category pt 
        CONNECT BY PRIOR pt.PAR_catid=pt.catID)pt, 
  goods pts,moves m 
  WHERE pt.fv=pts.cat and pts.gid=m.gd 
  GROUP BY pt.catid 
  ) pt_sum, 
(SELECT pt.catID, pt.par_catID, LPAD(' ',2 * (LEVEL - 1)) ||pt.name name, ROWNUM rn, LEVEL lv 
FROM category pt 
CONNECT BY PRIOR pt.catID=pt.PAR_catid
start with pt.catid in (select catid from category where par_catid is null)
) pt 
WHERE pt.catid=pt_sum.catid
ORDER BY pt.rn


Вопрос, как к нему добавить вывод продуктов, только в самых нижних уровнях, запрос такого вида:
select ctg.catid,g.gid,g.name, sum(decode(inout,'+',m.amount)) as Kol_Prihod,
   sum(decode(inout,'+',m.price * m.amount)) as Stoim_Prihod,
    sum(decode(inout,'-',m.amount)) as Kol_Rashod,
     sum(decode(inout,'-',m.price * m.amount)) as StoimRashod,
     (sum(decode(inout,'+',m.amount))-sum(decode(inout,'-',m.amount))) as ostatok

    from category ctg, goods g, moves m
    where  ctg.catid=g.cat and m.gd=g.gid
    group by ctg.catid,g.gid,g.name
21 мар 13, 16:30    [14078324]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
ten
Member

Откуда: Екатеринбург
Сообщений: 1672
alekssey,
Приведи пример тестовых данных и желаемый результат. Наподобие
+
-- Данные
with t as (select 1 as id, 'x' as val from dual
          union all
          select 2, 'y' from dual)
select * from t

-- Ожидаемый результат
id val
1  x
2  y
21 мар 13, 16:48    [14078414]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
alekssey
Member

Откуда:
Сообщений: 40
ten, все условие в первом посте.
21 мар 13, 17:09    [14078546]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
ten
Member

Откуда: Екатеринбург
Сообщений: 1672
alekssey,
Нет у тебя там ничего, кроме описания таблиц, а тебе уже не первый раз предлагают привести исходные данные и требуемый результат. Советую еще раз внимательно перечитать топик и не лениться, а сходить по ссылкам, кроме всего прочего поймешь, как нужно оформлять исходные данные.
P.S. Ну а если тебе это не нужно, то остальным то и подавно.
22 мар 13, 07:56    [14079955]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
ten
Member

Откуда: Екатеринбург
Сообщений: 1672
alekssey,
Например, вот описание задачи.
22 мар 13, 07:59    [14079956]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
mas_abd
Member

Откуда:
Сообщений: 31
Добавлю описание задачи:
для примера есть две таблицы (остальные таблицы опущу, там проблем нет
a as (----перечень категорий
  select 1 catid, null par_catid, 'Продукты' description from dual union
  select 2 catid, 1 par_catid, 'Масложиркомбинат' description from dual union
  select 3 catid, 2 par_catid, 'Молоко' description from dual union
  select 4 catid, 2 par_catid, 'Майонез' description from dual union
  select 5 catid, 4 par_catid, 'Высокой жирности' description from dual union
  select 6 catid, 4 par_catid, 'Низкой жирности' description from dual union
  select 7 catid, 1 par_catid, 'Напитки' description from dual union
  select 8 catid, 7 par_catid, 'Сок' description from dual union
  select 9 catid, 7 par_catid, 'Газ.вода' description from dual union
),
b as (---перечень товаров
  select 1 id, 3 catid, 'Молочник' description, 100 summa from dual union
  select 2 id, 3 catid, 'Буренка' description, 100 summa from dual union
  select 3 id, 3 catid, 'Столичное' description, 100 summa from dual union
  select 4 id, 4 catid, 'Провансаль' description, 50 summa from dual union
  select 5 id, 4 catid, 'Салатный' description, 50 summa from dual union
  select 6 id, 8 catid, 'Любимый' description, 75 summa from dual union
  select 7 id, 8 catid, 'Добрый' description, 75 summa from dual union
  select 8 id, 9 catid, 'Буратино' description, 25 summa from dual union
  select 9 id, 9 catid, 'Колокольчик' description, 25 summa from dual union
)


Как вывести данные в виде:
Продукты                           1000
   Масложиркомбинат                800
      Молоко                       300
         Молочник                  100
         Буренка                   100
         Столичное                 100
      Майонез                      100 
         Высокой жирности          50
            Провансаль             50
         Низкой жирности           50
            Салатный               50
   Напитки                         200
      Сок                          150
         Любимый                   75
         Добрый                    75
      Газ.вода                     50
        Буратино                   25
         Колокольчик               25

Как вывести в таком варианте:
Продукты                           1000
   Масложиркомбинат                800
      Молоко                       300
      Майонез                      100 
         Высокой жирности          50
         Низкой жирности           50
   Напитки                         200
      Сок                          150
      Газ.вода                     50

нашел по форуму с учетом замечаний по вышестоящим постам. Но не могу сообразить, как помимо категорий с подсчитанными итогами выводить и перечень товаров с ценами((
ПОдсказка с connect_by_isleaf - как ее применить? Она подсказывает самые нижние уровни, но как дополнительно привязать данные с наименованием в иерархическом дереве из другой таблицы?

Подскажите, пожалуйста, уважаемые гуру! Если жаль времени - ткните носом куда копать? За любую помощь - спасибо...
11 июл 13, 18:00    [14554316]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
orawish
Member

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

на 11.2 можно дерево строить рекурсивным факторингом
ну а (для любой версии сервера) - сначала union, и дерево строить по нему
11 июл 13, 18:30    [14554467]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
mas_abd
Member

Откуда:
Сообщений: 31
orawish
mas_abd,
на 11.2 можно дерево строить рекурсивным факторингом

Пишу код под 11g. Если не трудно - можно небольшой пример, чтобы уловить суть и использовать его для решения своей задачи?
Спасибо.
11 июл 13, 18:37    [14554491]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет всех суммарные значений для каждой категории с учетом иерархии  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
mas_abd
orawish
mas_abd,
на 11.2 можно дерево строить рекурсивным факторингом

Пишу код под 11g. Если не трудно - можно небольшой пример, чтобы уловить суть и использовать его для решения своей задачи?
Спасибо.

ну, как - то так.
на входе две таблицы - набор данных на выходе может обрабатываться, как единое дерево
with t( name, id, par_id, i )
as (select dname, deptno, cast(null as number), 0
      from scott.dept
 union all
    select ename, empno, id, i+1
      from scott.emp a
          ,t
     where (i = 0 and job = 'MANAGER' and id = deptno)
        or (i > 0 and id = mgr)
) select * from t;
11 июл 13, 19:37    [14554661]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить