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

Откуда:
Сообщений: 16
Всем привет!Нужна Ваша помощь! есть таблица с полями dt date,acc_from number,acc_to number,amt number, на основе которой формируется другая таблица с временными интервалами.
Поправьте, пожалуйста запрос, чтобы он при начальной дате "01-01-1970", конечной датой считал следующую начальную дату периода у которой сумма будет не 0. А у которых сумма 0 - начальная дата "01-01-1970", а конечная "31-12-3000"

create table table_end(acc,bgn_dt, end_dt, amt) as
  (SELECT acc,bgn_dt, end_dt,s
    from(SELECT acc,bgn_dt, end_dt,s,lag(s,1) over(partition by acc order by acc)q
   from (SELECT acc, dt bgn_dt, lead(dt,1, '31-12-3000') over(partition by acc order by dt) end_dt, sum(amt) over(partition by acc order by dt)s
    FROM ((select acc_from acc, dt, -amt amt from table_start) 
                  union all
           SELECT acc_to, dt, amt from table_start)
           ))
           where  bgn_dt <> end_dt and ((s != 0 and q  = 0)or s != 0)
  union all 
  (select acc, bgn_dt,end_dt,amt 
  from (select distinct acc_to acc, to_date('01-01-1970') bgn_dt, first_value(dt) over(partition by acc_to order by dt) end_dt, 0 amt
    from (select distinct acc_to, dt, amt from table_start
                   union all
          SELECT distinct acc_from, dt, -(amt)  from table_start)
    where exists (select *
                  from (select lag(dt, 1,null) over(partition by acc_to order by dt) lg
                        from  table_start)
                        where lg is null))) );
18 июн 19, 13:44    [21910627]     Ответить | Цитировать Сообщить модератору
 Re: Временные интервалы.  [new]
Elic
Member

Откуда:
Сообщений: 29990
vgpframed
Поправьте, пожалуйста запрос,
А ты тестовые данные предоставил?
18 июн 19, 14:03    [21910649]     Ответить | Цитировать Сообщить модератору
 Re: Временные интервалы.  [new]
vgpframed
Member

Откуда:
Сообщений: 16
Elic,
CREATE TABLE table_start(
  dt DATE,
  acc_from number,
  acc_to number,
  amt number);

insert into table_start values(TO_DATE('01.02.2018','dd.mm.yyyy'),140,200,100);
insert into table_start values(TO_DATE('02.02.2018','dd.mm.yyyy'),200,140,100);
insert into table_start values(TO_DATE('02.02.2018','dd.mm.yyyy'),100,140,100);
insert into table_start values(TO_DATE('03.02.2018','dd.mm.yyyy'),100,140,200);
insert into table_start values(TO_DATE('03.02.2018','dd.mm.yyyy'),140,200,200);
insert into table_start values(TO_DATE('01.02.2018','dd.mm.yyyy'),300,400,100);
insert into table_start values(TO_DATE('01.02.2018','dd.mm.yyyy'),400,300,100);
insert into table_start values(TO_DATE('02.02.2018','dd.mm.yyyy'),300,400,200);
insert into table_start values(TO_DATE('02.02.2018','dd.mm.yyyy'),400,300,200);
insert into table_start values(TO_DATE('05.05.2019','dd.mm.yyyy'),1,2,100);
insert into table_start values(TO_DATE('05.05.2019','dd.mm.yyyy'),2,1,100);
insert into table_start values(TO_DATE('06.06.2019','dd.mm.yyyy'),2,1,150);
18 июн 19, 14:05    [21910652]     Ответить | Цитировать Сообщить модератору
 Re: Временные интервалы.  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18388
select * from v$version
18 июн 19, 14:18    [21910678]     Ответить | Цитировать Сообщить модератору
 Re: Временные интервалы.  [new]
vgpframed
Member

Откуда:
Сообщений: 16
andrey_anonymous,
11g.
18 июн 19, 14:20    [21910683]     Ответить | Цитировать Сообщить модератору
 Re: Временные интервалы.  [new]
Elic
Member

Откуда:
Сообщений: 29990
vgpframed
11g.
Чудак, исполняй указания дословно.
18 июн 19, 14:23    [21910686]     Ответить | Цитировать Сообщить модератору
 Re: Временные интервалы.  [new]
vgpframed
Member

Откуда:
Сообщений: 16
Elic,
4 TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
2 PL/SQL Release 11.2.0.1.0 - Production
1 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
5 NLSRTL Version 11.2.0.1.0 - Production
3 CORE 11.2.0.1.0 Production
18 июн 19, 14:24    [21910689]     Ответить | Цитировать Сообщить модератору
 Re: Временные интервалы.  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2795
vgpframed,

исходну табличку (данные) и саму задачку можно было сформулиловать с учетом
select acc_from acc, dt, -amt amt from table_start
union all
SELECT acc_to, dt, amt from table_start

.....
stax
18 июн 19, 14:27    [21910690]     Ответить | Цитировать Сообщить модератору
 Re: Временные интервалы.  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18388
Что-то больно мудреный код, я утомилсо распутывать.
Попробуйте так:
with t_interim as(select acc_from acc, dt, -amt amt from table_start
          union all SELECT acc_to, dt, amt from table_start)
select acc, dt dt_from, lead(dt-1,1,date'3000-12-31') over(partition by acc order by dt) dt_till, amt
  from (  select acc, dt, sum(amt) amt from t_interim group by acc, dt having sum(amt) <> 0
          union all select acc, date'1970-01-01', 0 from t_interim group by acc)
18 июн 19, 14:50    [21910710]     Ответить | Цитировать Сообщить модератору
 Re: Временные интервалы.  [new]
vgpframed
Member

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

Да Вы - гений! Спасибо Вам огромное.
18 июн 19, 15:08    [21910725]     Ответить | Цитировать Сообщить модератору
 Re: Временные интервалы.  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18388
vgpframed
гений

Эт врядли.

Если логика верная, то, чтобы не лепить лишние union-ы, можно попробовать чуть переформулировать:
with t_interim as(select acc_from acc, dt, -amt amt from table_start
          union all SELECT acc_to, dt, amt from table_start)
select acc, dt dt_from
     , lead(dt-1,1,date'3000-12-31') over(partition by acc order by dt) dt_till
     , amt
  from ( select acc, case(grouping(dt)) when 0 then dt else date'1970-01-01' end dt
              , case(grouping(dt)) when 0 then sum(amt) else 0 end amt
          from t_interim 
         group by acc, rollup(dt) having grouping(dt) = 1 or sum(amt) <> 0
       )
18 июн 19, 15:26    [21910733]     Ответить | Цитировать Сообщить модератору
 Re: Временные интервалы.  [new]
vgpframed
Member

Откуда:
Сообщений: 16
andrey_anonymous,
только момент... сумма должна считаться только постоянная. Ответ должен быть что-то вроде этого.
1 01.01.1970 05.05.2019 0
1 06.06.2019 31.12.3000 150
2 01.01.1970 05.05.2019 0
2 06.06.2019 31.12.3000 -150
100 01.01.1970 02.02.2018 0
100 02.02.2018 03.02.2018 -100
100 03.02.2018 31.12.3000 -300
140 01.01.1970 01.02.2018 0
140 01.02.2018 02.02.2018 -100
140 02.02.2018 03.02.2018 100
140 03.02.2018 31.12.3000 100
200 01.01.1970 01.02.2018 0
200 01.02.2018 02.02.2018 100
200 03.02.2018 31.12.3000 200
300 01.01.1970 31.12.3000 0
400 01.01.1970 31.12.3000 0
18 июн 19, 15:42    [21910749]     Ответить | Цитировать Сообщить модератору
 Re: Временные интервалы.  [new]
vgpframed
Member

Откуда:
Сообщений: 16
vgpframed
andrey_anonymous,
только момент... сумма должна считаться только постоянная. Ответ должен быть что-то вроде этого.
1 01.01.1970 05.05.2019 0
1 06.06.2019 31.12.3000 150
2 01.01.1970 05.05.2019 0
2 06.06.2019 31.12.3000 -150
100 01.01.1970 02.02.2018 0
100 02.02.2018 03.02.2018 -100
100 03.02.2018 31.12.3000 -300
140 01.01.1970 01.02.2018 0
140 01.02.2018 02.02.2018 -100
140 02.02.2018 03.02.2018 100
140 03.02.2018 31.12.3000 100
200 01.01.1970 01.02.2018 0
200 01.02.2018 02.02.2018 100
200 02.02.2018 03.02.2018 0
200 03.02.2018 31.12.3000 200
300 01.01.1970 31.12.3000 0
400 01.01.1970 31.12.3000 0

точнее так
18 июн 19, 15:45    [21910755]     Ответить | Цитировать Сообщить модератору
 Re: Временные интервалы.  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18388
vgpframed
andrey_anonymous,
только момент... сумма должна считаться только постоянная.

Не очень понял что значит "постоянная", но в примере вроде как нарастающим итогом, я про него забыл.
Поправьте:
andrey_anonymous
with t_interim as(select acc_from acc, dt, -amt amt from table_start
          union all SELECT acc_to, dt, amt from table_start)
select acc, dt dt_from
     , lead(dt-1,1,date'3000-12-31') over(partition by acc order by dt) dt_till
--     , amt
     , sum(amt) over(partition by acc order by dt) amt
  from ( select acc, case(grouping(dt)) when 0 then dt else date'1970-01-01' end dt
              , case(grouping(dt)) when 0 then sum(amt) else 0 end amt
          from t_interim 
         group by acc, rollup(dt) having grouping(dt) = 1 or sum(amt) <> 0
       )
18 июн 19, 15:51    [21910758]     Ответить | Цитировать Сообщить модератору
 Re: Временные интервалы.  [new]
vgpframed
Member

Откуда:
Сообщений: 16
andrey_anonymous,
Идеально, спасибо)))
18 июн 19, 15:55    [21910764]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить