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

Откуда:
Сообщений: 1686
есть запрос типа

with a as(
select 1 v1,  '01.06.09' tmp_date, 1 line, 10 value from dual union
select 1 v1,  '02.06.09' tmp_date, 1 line, 12 value from dual union
select 1 v1,  '02.06.09' tmp_date, 2 line, 22 value from dual union
select 1 v1,  '03.06.09' tmp_date, 1 line, 10 value from dual union
select 1 v1,  '04.06.09' tmp_date, 1 line, 12 value from dual union
select 1 v1,  '04.06.09' tmp_date, 2 line, 22 value from dual union
select 1 v1,  '05.06.09' tmp_date, 1 line, 10 value from dual union
select 1 v1,  '06.06.09' tmp_date, 1 line, 12 value from dual union
select 1 v1,  '06.06.09' tmp_date, 2 line, 22 value from dual union
select 1 v1,  '06.06.09' tmp_date, 3 line, 33 value from dual union
select 1 v1,  '07.06.09' tmp_date, 1 line, 10 value from dual union
select 1 v1,  '08.06.09' tmp_date, 1 line, 12 value from dual union
select 2 v1,  '01.06.09' tmp_date, 1 line, 10 value from dual union
select 2 v1,  '02.06.09' tmp_date, 1 line, 12 value from dual union
select 2 v1,  '03.06.09' tmp_date, 1 line, 10 value from dual union
select 2 v1,  '03.06.09' tmp_date, 2 line, 22 value from dual union
select 2 v1,  '04.06.09' tmp_date, 1 line, 12 value from dual union
select 2 v1,  '05.06.09' tmp_date, 1 line, 10 value from dual union
select 2 v1,  '05.06.09' tmp_date, 2 line, 22 value from dual union
select 2 v1,  '06.06.09' tmp_date, 1 line, 12 value from dual union
select 2 v1,  '07.06.09' tmp_date, 1 line, 10 value from dual union
select 2 v1,  '08.06.09' tmp_date, 1 line, 12 value from dual)
select 
 case when grouping(v1)=1
      then 'итого'
      when grouping(v1)=0 and grouping(tmp_date)=1
      then 'итого по '||to_char(v1)
      else to_char(v1)
 end vv1
, case when grouping(v1)=0 and grouping(tmp_date)=0 and grouping(line) = 1
  then 'итого по '||tmp_date
 else tmp_date
 end tmp_date1
,line
,tmp_date
,sum(value)
from a
group by rollup(v1,tmp_date, line)

нужно

1. не выводить записи до '03.06.09' и после '06.06.09'
2. вместо этого добавить 2 колонки - остаток на начало, остаток на конец
2.1 для строк где grouping(v1)=0 and grouping(tmp_date)=0 они должны быть null
2.2 для строк где grouping(v1)=0 and grouping(tmp_date)=1
- остаток на начало=сумма всех value по v1 до даты '03.06.09'
- остаток на конец=сумма всех value по v1 до даты '07.06.09'
2.3 для строк где grouping(v1)=0 and grouping(tmp_date)=1
- остаток на начало=сумма всех value до даты '03.06.09'
- остаток на конец=сумма всех value до даты '07.06.09'

подскажите в какую сторону копать...
7 июл 09, 11:10    [7384840]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом (rollup)  [new]
suPPLer
Member

Откуда: Харків, Україна
Сообщений: 7794
Блог
grok,

копать в сторону:
1. Завернуть в подзапрос, во внешний запрос добавить в WHERE ограничение по дате.
2. CASE + SUM() OVER(...)
7 июл 09, 11:27    [7384955]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом (rollup)  [new]
grok
Member

Откуда:
Сообщений: 1686
suPPLer
grok,

копать в сторону:
1. Завернуть в подзапрос, во внешний запрос добавить в WHERE ограничение по дате.
2. CASE + SUM() OVER(...)


сначала сделать rollup, потом ограничить по дате или наоборот ?
7 июл 09, 13:29    [7385848]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом (rollup)  [new]
suPPLer
Member

Откуда: Харків, Україна
Сообщений: 7794
Блог
grok,

чем условия для 2.2 и 2.3 отличаются?
7 июл 09, 13:35    [7385919]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом (rollup)  [new]
grok
Member

Откуда:
Сообщений: 1686
suPPLer
grok,

чем условия для 2.2 и 2.3 отличаются?


чорт....

правильно читать:

2.2 для строк где grouping(v1)=0 and grouping(tmp_date)=1
- остаток на начало=сумма всех value по v1 до даты '03.06.09'
- остаток на конец=сумма всех value по v1 до даты '07.06.09'
2.3 для строк где grouping(v1)=1
- остаток на начало=сумма всех value до даты '03.06.09'
- остаток на конец=сумма всех value до даты '07.06.09'
7 июл 09, 13:41    [7385980]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом (rollup)  [new]
suPPLer
Member

Откуда: Харків, Україна
Сообщений: 7794
Блог
grok,

ну и желаемый результат покажите, пожалуйста.
7 июл 09, 14:32    [7386404]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом (rollup)  [new]
suPPLer
Member

Откуда: Харків, Україна
Сообщений: 7794
Блог
Что-то такое?

+
with a0 as(
select 1 v1,  '01.06.09' tmp_date, 1 line, 10 value from dual union
select 1 v1,  '02.06.09' tmp_date, 1 line, 12 value from dual union
select 1 v1,  '02.06.09' tmp_date, 2 line, 22 value from dual union
select 1 v1,  '03.06.09' tmp_date, 1 line, 10 value from dual union
select 1 v1,  '04.06.09' tmp_date, 1 line, 12 value from dual union
select 1 v1,  '04.06.09' tmp_date, 2 line, 22 value from dual union
select 1 v1,  '05.06.09' tmp_date, 1 line, 10 value from dual union
select 1 v1,  '06.06.09' tmp_date, 1 line, 12 value from dual union
select 1 v1,  '06.06.09' tmp_date, 2 line, 22 value from dual union
select 1 v1,  '06.06.09' tmp_date, 3 line, 33 value from dual union
select 1 v1,  '07.06.09' tmp_date, 1 line, 10 value from dual union
select 1 v1,  '08.06.09' tmp_date, 1 line, 12 value from dual union
select 2 v1,  '01.06.09' tmp_date, 1 line, 10 value from dual union
select 2 v1,  '02.06.09' tmp_date, 1 line, 12 value from dual union
select 2 v1,  '03.06.09' tmp_date, 1 line, 10 value from dual union
select 2 v1,  '03.06.09' tmp_date, 2 line, 22 value from dual union
select 2 v1,  '04.06.09' tmp_date, 1 line, 12 value from dual union
select 2 v1,  '05.06.09' tmp_date, 1 line, 10 value from dual union
select 2 v1,  '05.06.09' tmp_date, 2 line, 22 value from dual union
select 2 v1,  '06.06.09' tmp_date, 1 line, 12 value from dual union
select 2 v1,  '07.06.09' tmp_date, 1 line, 10 value from dual union
select 2 v1,  '08.06.09' tmp_date, 1 line, 12 value from dual)
   , a as (select v1, tmp_date, line, value
                , sum(case when to_date(tmp_date, 'dd.mm.yyyy') < to_date('03.06.09', 'dd.mm.yyyy') then value else 0 end) over(partition by v1) start_saldo_v1
                , sum(case when to_date(tmp_date, 'dd.mm.yyyy') < to_date('07.06.09', 'dd.mm.yyyy') then value else 0 end) over(partition by v1) end_saldo_v1
                , sum(case when to_date(tmp_date, 'dd.mm.yyyy') < to_date('03.06.09', 'dd.mm.yyyy') then value else 0 end) over() start_saldo
                , sum(case when to_date(tmp_date, 'dd.mm.yyyy') < to_date('07.06.09', 'dd.mm.yyyy') then value else 0 end) over() end_saldo
             from a0    )
select 
 case when grouping(v1)=1
      then 'итого'
      when grouping(v1)=0 and grouping(tmp_date)=1
      then 'итого по '||to_char(v1)
      else to_char(v1)
 end vv1
, case when grouping(v1)=0 and grouping(tmp_date)=0 and grouping(line) = 1
  then 'итого по '||tmp_date
 else tmp_date
 end tmp_date1
,line
,tmp_date
,sum(value)
, case when grouping(tmp_date) = 1 and grouping(v1) = 0 then max(start_saldo_v1)
       when grouping(v1) = 1 then max(start_saldo)
   end start_saldo
, case when grouping(tmp_date) = 1 and grouping(v1) = 0 then max(end_saldo_v1)
       when grouping(v1) = 1 then max(end_saldo)
   end end_saldo
from a
where to_date(tmp_date, 'dd.mm.yyyy') between to_date('03.06.09', 'dd.mm.yyyy') and to_date('06.06.09', 'dd.mm.yyyy')
group by rollup(v1,tmp_date, line);

-------------------------------------------------------
When I say "RTFM" or "STFF" or "STFW",
the third letter means "Following" or "Fine"...
7 июл 09, 14:55    [7386577]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом (rollup)  [new]
grok
Member

Откуда:
Сообщений: 1686
suPPLer
grok,

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


ожидаемый результат

v1tmp_datelineb_sumsume_sum
1 '03.06.09' 110
1итого по '03.06.09' 441054
1 '04.06.09' 112
1 '04.06.09' 222
1итого по '04.06.09' 543488
1 '05.06.09' 110
1итого по '05.06.09' 881098
1 '06.06.09' 112
1 '06.06.09' 222
1 '06.06.09' 333
1итого по '06.06.09' 9867165
итого по 1440165
2 '03.06.09' 110
2 '03.06.09' 222
2итого по '03.06.09' 423274
2 '04.06.09' 112
2итого по '04.06.09' 741286
2 '05.06.09' 110
2 '05.06.09' 222
2итого по '05.06.09' 8632118
2 '06.06.09' 112
2итого по '06.06.09' 11812130
итого по 2420130
итого86209295
7 июл 09, 14:59    [7386600]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом (rollup)  [new]
grok
Member

Откуда:
Сообщений: 1686
grok
suPPLer
grok,

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


ожидаемый результат

v1tmp_datelineb_sumsume_sum
1 '03.06.09' 110
1итого по '03.06.09' 441054
1 '04.06.09' 112
1 '04.06.09' 222
1итого по '04.06.09' 543488
1 '05.06.09' 110
1итого по '05.06.09' 881098
1 '06.06.09' 112
1 '06.06.09' 222
1 '06.06.09' 333
1итого по '06.06.09' 9867165
итого по 1440165
2 '03.06.09' 110
2 '03.06.09' 222
2итого по '03.06.09' 423274
2 '04.06.09' 112
2итого по '04.06.09' 741286
2 '05.06.09' 110
2 '05.06.09' 222
2итого по '05.06.09' 8632118
2 '06.06.09' 112
2итого по '06.06.09' 11812130
итого по 2420130
итого86209295


чорт, это не правильно
7 июл 09, 15:02    [7386625]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом (rollup)  [new]
grok
Member

Откуда:
Сообщений: 1686
grok

чорт, это не правильно


вот, правильно вроде

v1tmp_datelineb_sumsume_sum
1 '03.06.09' 110
1итого по '03.06.09' 441054
1 '04.06.09' 112
1 '04.06.09' 222
1итого по '04.06.09' 543488
1 '05.06.09' 110
1итого по '05.06.09' 881098
1 '06.06.09' 112
1 '06.06.09' 222
1 '06.06.09' 333
1итого по '06.06.09' 9867165
итого по 144121165
2 '03.06.09' 110
2 '03.06.09' 222
2итого по '03.06.09' 423274
2 '04.06.09' 112
2итого по '04.06.09' 741286
2 '05.06.09' 110
2 '05.06.09' 222
2итого по '05.06.09' 8632118
2 '06.06.09' 112
2итого по '06.06.09' 11812130
итого по 24288130
итого86209295
7 июл 09, 15:05    [7386648]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом (rollup)  [new]
suPPLer
Member

Откуда: Харків, Україна
Сообщений: 7794
Блог
grok
grok

чорт, это не правильно


вот, правильно вроде

v1tmp_datelineb_sumsume_sum
итого по 24288130


Почему сальдо на начало по v1=2 это 42, а не 22?
7 июл 09, 15:13    [7386707]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом (rollup)  [new]
grok
Member

Откуда:
Сообщений: 1686
suPPLer
grok
grok

чорт, это не правильно


вот, правильно вроде

v1tmp_datelineb_sumsume_sum
итого по 24288130


Почему сальдо на начало по v1=2 это 42, а не 22?


suPPLer, спасибо вам большое, у вас всё правильно
это я опять накосячил

1 '01.06.09' 110
1 '02.06.09' 112
1 '02.06.09' 222
1 '03.06.09' 110
1итого по '03.06.09' 441054
1 '04.06.09' 112
1 '04.06.09' 222
1итого по '04.06.09' 543488
1 '05.06.09' 110
1итого по '05.06.09' 881098
1 '06.06.09' 112
1 '06.06.09' 222
1 '06.06.09' 333
1итого по '06.06.09' 9867165
1 '07.06.09' 110
1итого по '07.06.09' 16510175
1 '08.06.09' 112
1итого по '08.06.09' 17512187
итого по 144121165
2 '01.06.09' 110
2 '02.06.09' 112
2 '03.06.09' 110
2 '03.06.09' 222
2итого по '03.06.09' 223254
2 '04.06.09' 112
2итого по '04.06.09' 541266
2 '05.06.09' 110
2 '05.06.09' 222
2итого по '05.06.09' 663298
2 '06.06.09' 112
2итого по '06.06.09' 9812110
2 '07.06.09' 110
2 '08.06.09' 112
итого по 22288110
итого66209275


общие цифры совпадают, только нужно еще на уровне "итого по [tmp_date]"
7 июл 09, 15:20    [7386771]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом (rollup)  [new]
suPPLer
Member

Откуда: Харків, Україна
Сообщений: 7794
Блог
+ В лоб
with a0 as(
select 1 v1,  '01.06.09' tmp_date, 1 line, 10 value from dual union
select 1 v1,  '02.06.09' tmp_date, 1 line, 12 value from dual union
select 1 v1,  '02.06.09' tmp_date, 2 line, 22 value from dual union
select 1 v1,  '03.06.09' tmp_date, 1 line, 10 value from dual union
select 1 v1,  '04.06.09' tmp_date, 1 line, 12 value from dual union
select 1 v1,  '04.06.09' tmp_date, 2 line, 22 value from dual union
select 1 v1,  '05.06.09' tmp_date, 1 line, 10 value from dual union
select 1 v1,  '06.06.09' tmp_date, 1 line, 12 value from dual union
select 1 v1,  '06.06.09' tmp_date, 2 line, 22 value from dual union
select 1 v1,  '06.06.09' tmp_date, 3 line, 33 value from dual union
select 1 v1,  '07.06.09' tmp_date, 1 line, 10 value from dual union
select 1 v1,  '08.06.09' tmp_date, 1 line, 12 value from dual union
select 2 v1,  '01.06.09' tmp_date, 1 line, 10 value from dual union
select 2 v1,  '02.06.09' tmp_date, 1 line, 12 value from dual union
select 2 v1,  '03.06.09' tmp_date, 1 line, 10 value from dual union
select 2 v1,  '03.06.09' tmp_date, 2 line, 22 value from dual union
select 2 v1,  '04.06.09' tmp_date, 1 line, 12 value from dual union
select 2 v1,  '05.06.09' tmp_date, 1 line, 10 value from dual union
select 2 v1,  '05.06.09' tmp_date, 2 line, 22 value from dual union
select 2 v1,  '06.06.09' tmp_date, 1 line, 12 value from dual union
select 2 v1,  '07.06.09' tmp_date, 1 line, 10 value from dual union
select 2 v1,  '08.06.09' tmp_date, 1 line, 12 value from dual)
   , a as (select v1, tmp_date, line, value
                , sum(case when to_date(tmp_date, 'dd.mm.yyyy') < to_date('03.06.09', 'dd.mm.yyyy') then value else 0 end) over(partition by v1) start_saldo_v1
                , sum(case when to_date(tmp_date, 'dd.mm.yyyy') < to_date('03.06.09', 'dd.mm.yyyy') then value else 0 end) over() start_saldo
                , sum(case when to_date(tmp_date, 'dd.mm.yyyy') < to_date('07.06.09', 'dd.mm.yyyy') then value else 0 end) over(partition by v1) end_saldo_v1
                , sum(case when to_date(tmp_date, 'dd.mm.yyyy') < to_date('07.06.09', 'dd.mm.yyyy') then value else 0 end) over() end_saldo
                , sum(case when to_date(tmp_date, 'dd.mm.yyyy') < to_date('07.06.09', 'dd.mm.yyyy') then value else 0 end) over(partition by v1 order by tmp_date) end_saldo_tmp
             from a0 
              )    
select
 case when grouping(v1)=1
      then 'итого'
      when grouping(v1)=0 and grouping(tmp_date)=1
      then 'итого по '||to_char(v1)
      else to_char(v1)
 end vv1
, case when grouping(v1)=0 and grouping(tmp_date)=0 and grouping(line) = 1
  then 'итого по '||tmp_date
 else tmp_date
 end tmp_date1
,line
,tmp_date
, case when grouping(v1) = 1 then max(start_saldo)
       when grouping(tmp_date) = 1 then max(start_saldo_v1)
       when grouping(line) = 1 then max(end_saldo_tmp) - sum(value)
   end start_saldo
,sum(value)
, case when grouping(tmp_date) = 1 and grouping(v1) = 0 then max(end_saldo_v1)
       when grouping(v1) = 1 then max(end_saldo)
       when grouping(line) = 1 then max(end_saldo_tmp)
   end end_saldo
from a
where to_date(tmp_date, 'dd.mm.yyyy') between to_date('03.06.09', 'dd.mm.yyyy') and to_date('06.06.09', 'dd.mm.yyyy')
group by rollup(v1,tmp_date, line);
7 июл 09, 15:55    [7387157]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом (rollup)  [new]
grok
Member

Откуда:
Сообщений: 1686
suPPLer
+
+ В лоб
with a0 as(
select 1 v1,  '01.06.09' tmp_date, 1 line, 10 value from dual union
select 1 v1,  '02.06.09' tmp_date, 1 line, 12 value from dual union
select 1 v1,  '02.06.09' tmp_date, 2 line, 22 value from dual union
select 1 v1,  '03.06.09' tmp_date, 1 line, 10 value from dual union
select 1 v1,  '04.06.09' tmp_date, 1 line, 12 value from dual union
select 1 v1,  '04.06.09' tmp_date, 2 line, 22 value from dual union
select 1 v1,  '05.06.09' tmp_date, 1 line, 10 value from dual union
select 1 v1,  '06.06.09' tmp_date, 1 line, 12 value from dual union
select 1 v1,  '06.06.09' tmp_date, 2 line, 22 value from dual union
select 1 v1,  '06.06.09' tmp_date, 3 line, 33 value from dual union
select 1 v1,  '07.06.09' tmp_date, 1 line, 10 value from dual union
select 1 v1,  '08.06.09' tmp_date, 1 line, 12 value from dual union
select 2 v1,  '01.06.09' tmp_date, 1 line, 10 value from dual union
select 2 v1,  '02.06.09' tmp_date, 1 line, 12 value from dual union
select 2 v1,  '03.06.09' tmp_date, 1 line, 10 value from dual union
select 2 v1,  '03.06.09' tmp_date, 2 line, 22 value from dual union
select 2 v1,  '04.06.09' tmp_date, 1 line, 12 value from dual union
select 2 v1,  '05.06.09' tmp_date, 1 line, 10 value from dual union
select 2 v1,  '05.06.09' tmp_date, 2 line, 22 value from dual union
select 2 v1,  '06.06.09' tmp_date, 1 line, 12 value from dual union
select 2 v1,  '07.06.09' tmp_date, 1 line, 10 value from dual union
select 2 v1,  '08.06.09' tmp_date, 1 line, 12 value from dual)
   , a as (select v1, tmp_date, line, value
                , sum(case when to_date(tmp_date, 'dd.mm.yyyy') < to_date('03.06.09', 'dd.mm.yyyy') then value else 0 end) over(partition by v1) start_saldo_v1
                , sum(case when to_date(tmp_date, 'dd.mm.yyyy') < to_date('03.06.09', 'dd.mm.yyyy') then value else 0 end) over() start_saldo
                , sum(case when to_date(tmp_date, 'dd.mm.yyyy') < to_date('07.06.09', 'dd.mm.yyyy') then value else 0 end) over(partition by v1) end_saldo_v1
                , sum(case when to_date(tmp_date, 'dd.mm.yyyy') < to_date('07.06.09', 'dd.mm.yyyy') then value else 0 end) over() end_saldo
                , sum(case when to_date(tmp_date, 'dd.mm.yyyy') < to_date('07.06.09', 'dd.mm.yyyy') then value else 0 end) over(partition by v1 order by tmp_date) end_saldo_tmp
             from a0 
              )    
select
 case when grouping(v1)=1
      then 'итого'
      when grouping(v1)=0 and grouping(tmp_date)=1
      then 'итого по '||to_char(v1)
      else to_char(v1)
 end vv1
, case when grouping(v1)=0 and grouping(tmp_date)=0 and grouping(line) = 1
  then 'итого по '||tmp_date
 else tmp_date
 end tmp_date1
,line
,tmp_date
, case when grouping(v1) = 1 then max(start_saldo)
       when grouping(tmp_date) = 1 then max(start_saldo_v1)
       when grouping(line) = 1 then max(end_saldo_tmp) - sum(value)
   end start_saldo
,sum(value)
, case when grouping(tmp_date) = 1 and grouping(v1) = 0 then max(end_saldo_v1)
       when grouping(v1) = 1 then max(end_saldo)
       when grouping(line) = 1 then max(end_saldo_tmp)
   end end_saldo
from a
where to_date(tmp_date, 'dd.mm.yyyy') between to_date('03.06.09', 'dd.mm.yyyy') and to_date('06.06.09', 'dd.mm.yyyy')
group by rollup(v1,tmp_date, line);


результат правильный

спасибо еще раз
7 июл 09, 16:46    [7387652]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить