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

Откуда:
Сообщений: 133
добрый день! подскажите пожалуйста с промежуточными итогами, не могу получить нужный результат.
данные
with query1 as 
(
     select '123' schet, '1111101' INN, 'Рога и копыта' nameorg, 12.12 SummAll, 123.12 Summ1, 4.04 Proc, 'Филиал1' fil from dual
     union
     select '124' , '1111102' , 'Рога и копыта1' , 1.01 , 1.02 , 6.23 , 'Филиал2'  from dual
     union
     select '125' , '1111103' , 'Рога и копыта3' , 2.02 , 2.02 , 2.02 , 'Филиал1'  from dual
     union
     select '126' , '1111103' , 'Рога и копыта33' , 3.03 , 3.03 , 3.03 , 'Филиал3'  from dual
     union
     select '127' , '1111105' , 'Рога и копыта11' , 4.04 , 4.04 , 4.24 , 'Филиал1'  from dual
     union
     select '128' , '1111105' , 'Рога и копыта12'  , 5.05 , 5.05 , 5.25 , 'Филиал2'  from dual
     union
     select '129' , '1111107' , 'Рога и копыта111' , 6.06 , 6.06 , 6.26 , 'Филиал1'  from dual
     union
     select '130' , '1111107' , 'Рога и копыта122' , 7.07 , 7.07 , 7.27 , 'Филиал2'  from dual
     union
     select '131' , '1111107' , 'Рога и копыта133' , 8.08 , 8.08 , 8.28 , 'Филиал3'  from dual

)

select  nameorg, INN, schet, fil, sum(SummAll) SummAll, sum(Summ1) Summ1, sum(Proc) Proc
from query1
group by rollup (nameorg, INN, schet, fil)


необходимо сгруппировать и получить промежуточные итоги в виде результата

               
           nameorg            INN             schet        fil                     SummAll      Summ1        Proc
1	Рога и копыта	      1111101        '123'	   'Филиал1'               12,12        123,12        4,04
2	Итого		                                                           12,12        123,12        4,04
3	Рога и копыта1	      1111102	 '124'        'Филиал2'                    1,01        1,02        6,23
4	Итого               		                                           1,01        1,02        6,23
5	Рога и копыта3	      1111103	 '125'        'Филиал1'                    2,02        2,02        2,02
6	Рога и копыта33	      1111103	 '126'        'Филиал3'                    3,03        3,03        3,03
7	Итого             		                                           5,05        5,05        5,05
8	Рога и копыта11	      1111105	 '127'        'Филиал1'                    4,04        4,04        4,04
9	Рога и копыта12	      1111105	 '128'        'Филиал2'                    5,05        5,05        5,05
10	итого             		                                           9,09        9,09        9,09
11	Рога и копыта111      1111107	 '129'        'Филиал1'                     6,06        6,06        6,26
12	Рога и копыта122      1111107	 '130'        'Филиал2'                     7,07        7,07        7,27
13	Рога и копыта133      1111107	 '131'        'Филиал3'                     8,08        8,08        8,28
14	итого             		                                          21,21      21,21        21,21
27 мар 17, 16:59    [20337907]     Ответить | Цитировать Сообщить модератору
 Re: Промежуточные итоги rollup  [new]
Ну как-то так
Guest
maxwait,

select  nvl(nameorg,'ИТОГО'), INN, schet, fil, sum(SummAll) SummAll, sum(Summ1) Summ1, sum(Proc) Proc
from query1
group by rollup (nameorg), INN, schet, fil
27 мар 17, 17:04    [20337920]     Ответить | Цитировать Сообщить модератору
 Re: Промежуточные итоги rollup  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 2170
maxwait,

GROUPING SETS
Оно?
27 мар 17, 17:04    [20337923]     Ответить | Цитировать Сообщить модератору
 Re: Промежуточные итоги rollup  [new]
maxwait
Member

Откуда:
Сообщений: 133
Ну как-то так, не совсем то
вот если делаю так
with query1 as 
(
     select '123' schet, '1111101' INN, 'Рога и копыта' nameorg, 12.12 SummAll, 123.12 Summ1, 4.04 Proc, 'Филиал1' fil from dual
     union
     select '124' , '1111102' , 'Рога и копыта1' , 1.01 , 1.02 , 6.23 , 'Филиал2'  from dual
     union
     select '125' , '1111103' , 'Рога и копыта3' , 2.02 , 2.02 , 2.02 , 'Филиал1'  from dual
     union
     select '126' , '1111103' , 'Рога и копыта33' , 3.03 , 3.03 , 3.03 , 'Филиал3'  from dual
     union
     select '127' , '1111105' , 'Рога и копыта11' , 4.04 , 4.04 , 4.24 , 'Филиал1'  from dual
     union
     select '128' , '1111105' , 'Рога и копыта12'  , 5.05 , 5.05 , 5.25 , 'Филиал2'  from dual
     union
     select '129' , '1111107' , 'Рога и копыта111' , 6.06 , 6.06 , 6.26 , 'Филиал1'  from dual
     union
     select '130' , '1111107' , 'Рога и копыта122' , 7.07 , 7.07 , 7.27 , 'Филиал2'  from dual
     union
     select '131' , '1111107' , 'Рога и копыта133' , 8.08 , 8.08 , 8.28 , 'Филиал3'  from dual

)

select  INN, nameorg, sum(SummAll) SummAll, sum(Summ1) Summ1, sum(Proc) Proc
from query1
group by rollup (INN, nameorg) 


INN     NAMEORG             SUMMALL      SUMM1       PROC
------- ---------------- ---------- ---------- ----------
1111101 Рога и копыта         12,12     123,12       4,04
1111101                       12,12     123,12       4,04
1111102 Рога и копыта1         1,01       1,02       6,23
1111102                        1,01       1,02       6,23
1111103 Рога и копыта3         2,02       2,02       2,02
1111103 Рога и копыта33        3,03       3,03       3,03
1111103                        5,05       5,05       5,05
1111105 Рога и копыта11        4,04       4,04       4,24
1111105 Рога и копыта12        5,05       5,05       5,25
1111105                        9,09       9,09       9,49
1111107 Рога и копыта111       6,06       6,06       6,26
1111107 Рога и копыта122       7,07       7,07       7,27
1111107 Рога и копыта133       8,08       8,08       8,28
1111107                       21,21      21,21      21,81
                              48,48     159,49      46,62



то более менее, что-то похоже на правду, но мне еще нужно добавить к результату schet и fil, и если я добавляю, то у меня фигня получается.

select INN, nameorg, schet, fil, sum(SummAll) SummAll, sum(Summ1) Summ1, sum(Proc) Proc
from query1
group by rollup (nameorg), INN, schet, fil
27 мар 17, 19:38    [20338278]     Ответить | Цитировать Сообщить модератору
 Re: Промежуточные итоги rollup  [new]
maxwait
Member

Откуда:
Сообщений: 133
я понимаю что это говнокод и нифига не правильно, но не знаю как по другому сделать
select nameorg
       --, schet  
       , case when schet = LAG(schet) OVER (ORDER BY schet) then '---' else schet end schet
       --, case when fil = LAG(fil) OVER (ORDER BY fil) then '---' else fil end
       , INN
       , SummAll
       , Summ1
       , Proc
from( 
    select  nvl(nameorg,'Итого:') nameorg
        , max(schet) schet
        , max(fil) fil
        , INN
        , sum(SummAll) SummAll
        , sum(Summ1) Summ1
        , sum(Proc) Proc
    from query1
    group by rollup (nameorg),INN
)


Результат уже похож на то что мне нужно

NAMEORG          SCHET INN        SUMMALL      SUMM1       PROC
---------------- ----- ------- ---------- ---------- ----------
Рога и копыта    123   1111101      12,12     123,12       4,04
Итого:           ---   1111101      12,12     123,12       4,04
Рога и копыта1   124   1111102       1,01       1,02       6,23
Итого:           ---   1111102       1,01       1,02       6,23
Рога и копыта3   125   1111103       2,02       2,02       2,02
Рога и копыта33  126   1111103       3,03       3,03       3,03
Итого:           ---   1111103       5,05       5,05       5,05
Рога и копыта11  127   1111105       4,04       4,04       4,24
Рога и копыта12  128   1111105       5,05       5,05       5,25
Итого:           ---   1111105       9,09       9,09       9,49
Рога и копыта111 129   1111107       6,06       6,06       6,26
Рога и копыта122 130   1111107       7,07       7,07       7,27
Рога и копыта133 131   1111107       8,08       8,08       8,28
Итого:           ---   1111107      21,21      21,21      21,81
27 мар 17, 20:07    [20338338]     Ответить | Цитировать Сообщить модератору
 Re: Промежуточные итоги rollup  [new]
maxwait
Member

Откуда:
Сообщений: 133
Еще хуже , но результат тот который мне нужен
select nameorg
, case when nameorg = 'Итого:' then '---' else schet end schet
, case when nameorg = 'Итого:' then '---' else fil end fil
, INN
, SummAll
, Summ1
, Proc
from(
select nvl(nameorg,'Итого:') nameorg
, max(schet) schet
, max(fil) fil
, INN
, sum(SummAll) SummAll
, sum(Summ1) Summ1
, sum(Proc) Proc
from query1
group by rollup (nameorg),INN
)
подскажите пожалуйста, как по правильному сделать...
27 мар 17, 20:14    [20338354]     Ответить | Цитировать Сообщить модератору
 Re: Промежуточные итоги rollup  [new]
AnSi_Sr
Member

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

nvl/grouping function
28 мар 17, 06:01    [20339277]     Ответить | Цитировать Сообщить модератору
 Re: Промежуточные итоги rollup  [new]
flu4u
Member

Откуда: Москва <-120км-> Ярославль
Сообщений: 120
select INN, 
       decode(grouping_id(INN,nameorg),1,'Всего по ИНН '||INN||':',3,'Итого:',nameorg) nameorg, 
       schet, 
       fil,
       sum(SummAll) SummAll, 
       sum(Summ1) Summ1, 
       sum(Proc) Proc,
       decode(grouping_id(INN,nameorg),3,1,0) seq_1,
       decode(grouping_id(INN,nameorg),1,1,0) seq_2
from query1
group by rollup(INN, (nameorg, schet, fil))
order by seq_1,INN,seq_2,nameorg
28 мар 17, 08:11    [20339360]     Ответить | Цитировать Сообщить модератору
 Re: Промежуточные итоги rollup  [new]
maxwait
Member

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

Премного благодарен, то что нужно!
28 мар 17, 08:51    [20339428]     Ответить | Цитировать Сообщить модератору
 Re: Промежуточные итоги rollup  [new]
maxwait
Member

Откуда:
Сообщений: 133
можно еще вопрос, а можно применить having к промежуточным итогам, вывести только те записи где sum(Proc)<5
28 мар 17, 09:53    [20339706]     Ответить | Цитировать Сообщить модератору
 Re: Промежуточные итоги rollup  [new]
maxwait
Member

Откуда:
Сообщений: 133
Подскажите пожалуйста, как применить условие для промежуточного итога rollup. Вывести те организации у которых суммарный % sum(Proc) <7,
with query1 as 
(
     select '123' schet, '1111101' INN, 'Рога и копыта' nameorg, 12.12 SummAll, 123.12 Summ1, 4.04 Proc, 'Филиал1' fil from dual
     union
     select '124' , '1111102' , 'Рога и копыта1' , 1.01 , 1.02 , 6.23 , 'Филиал2'  from dual
     union
     select '125' , '1111103' , 'Рога и копыта3' , 2.02 , 2.02 , 2.02 , 'Филиал1'  from dual
     union
     select '126' , '1111103' , 'Рога и копыта33' , 3.03 , 3.03 , 3.03 , 'Филиал3'  from dual
     union
     select '127' , '1111105' , 'Рога и копыта11' , 4.04 , 4.04 , 4.24 , 'Филиал1'  from dual
     union
     select '128' , '1111105' , 'Рога и копыта12'  , 5.05 , 5.05 , 5.25 , 'Филиал2'  from dual
     union
     select '129' , '1111107' , 'Рога и копыта111' , 6.06 , 6.06 , 6.26 , 'Филиал1'  from dual
     union
     select '130' , '1111107' , 'Рога и копыта122' , 7.07 , 7.07 , 7.27 , 'Филиал2'  from dual
     union
     select '131' , '1111107' , 'Рога и копыта133' , 8.08 , 8.08 , 8.28 , 'Филиал3'  from dual

)
select * 
from (
    select INN, 
           decode(grouping_id(INN,nameorg),1,'Всего по ИНН '||INN||':',3,'Итого:',nameorg) nameorg, 
           schet, 
           fil,
           sum(SummAll) SummAll, 
           sum(Summ1) Summ1, 
           sum(Proc) Proc,
           decode(grouping_id(INN,nameorg),3,1,0) seq_1,
           decode(grouping_id(INN,nameorg),1,1,0) seq_2
    from query1
    group by rollup(INN, (nameorg, schet, fil))
    order by seq_1,INN,seq_2,nameorg
)


т.е. должны получить результат
1	1111101	Рога и копыта	123	Филиал1	12,12	123,12	4,04	0	0
2	1111101	Всего по ИНН 1111101:			12,12	123,12	4,04	0	1
3	1111102	Рога и копыта1	124	Филиал2	1,01	1,02	6,23	0	0
4	1111102	Всего по ИНН 1111102:			1,01	1,02	6,23	0	1
5	1111103	Рога и копыта3	125	Филиал1	2,02	2,02	2,02	0	0
6	1111103	Рога и копыта33	126	Филиал3	3,03	3,03	3,03	0	0
7	1111103	Всего по ИНН 1111103:			5,05	5,05	5,05	0	1
28 мар 17, 11:13    [20340296]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить