ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / разделить на непересекающиеся интервалы дат отрезки с ссумированием суммы в пересечениях
14 сообщений из 14, страница 1 из 1
разделить на непересекающиеся интервалы дат отрезки с ссумированием суммы в пересечениях
    #39666412
Romael
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Romael
Участник
Добрый день!

Есть идеи, как решить следующую задачу на SQL.

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
with p as 
    (
        select 1  id, 77 attr, to_date('28.10.13') date_from, to_date('10.03.14') date_to, 10  balance from dual union all 
        select 2  id, 77 attr, to_date('29.12.14') date_from, to_date('06.03.15') date_to, 20  balance from dual union all 
        select 3  id, 77 attr, to_date('07.03.15') date_from, to_date('14.03.15') date_to, 30  balance from dual union all 
        select 4  id, 77 attr, to_date('08.03.15') date_from, to_date('10.03.15') date_to, 70  balance from dual union all
        select 5  id, 77 attr, to_date('31.12.15') date_from, to_date('07.01.16') date_to, 100 balance from dual union all
        select 6  id, 77 attr, to_date('09.01.16') date_from, to_date('13.03.17') date_to, 60  balance from dual
    )
select * 
from p
order by p.date_from;



Результат:
ATTR DATE_FROM DATE_TO BALANCE77 28.10.2013 10.03.2014 1077 29.12.2014 06.03.2015 2077 07.03.2015 07.03.2015 3077 08.03.2015 10.03.2015 100 -- на пересечении баланс сложился 77 11.03.2015 14.03.2015 30 -- затем остался равен балансу с строкой id = 377 31.12.2015 07.01.2016 10077 09.01.2016 13.03.2017 60
...
Рейтинг: 0 / 0
27.06.2018, 11:25
| Ответить | Цитировать | Написать  
разделить на непересекающиеся интервалы дат отрезки с ссумированием суммы в пересечениях
    #39666429
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
Romael,

unpivot+sum over+lead over
...
Рейтинг: 0 / 0
27.06.2018, 11:51
| Ответить | Цитировать | Написать  
разделить на непересекающиеся интервалы дат отрезки с ссумированием суммы в пересечениях
    #39666443
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
чуток допилить

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
  1  with p as
  2      (
  3          select 1  id, 77 attr, to_date('28.10.13') date_from, to_date('10.03.14') date_to, 10  balance from dual union all
  4          select 2  id, 77 attr, to_date('29.12.14') date_from, to_date('06.03.15') date_to, 20  balance from dual union all
  5          select 3  id, 77 attr, to_date('07.03.15') date_from, to_date('14.03.15') date_to, 30  balance from dual union all
  6          select 4  id, 77 attr, to_date('08.03.15') date_from, to_date('10.03.15') date_to, 70  balance from dual union all
  7          select 5  id, 77 attr, to_date('31.12.15') date_from, to_date('07.01.16') date_to, 100 balance from dual union all
  8          select 6  id, 77 attr, to_date('09.01.16') date_from, to_date('13.03.17') date_to, 60  balance from dual
  9      )
 10  ,pp as (
 11  select attr,decode(s,1,date_from,date_to) d,sum(balance*s) b
 12  from p,(select 1 s from dual union all select -1 from dual) d
 13  group by attr,decode(s,1,date_from,date_to)
 14  )
 15  ,ppp as (
 16  select pp.*,sum(b) over (partition by  attr order by d) b_sum
 17  from pp)
 18  select attr,d d_from,lead(d) over (partition by attr order by d) d_to,b_sum
 19  from ppp
 20* order by 1,2
SQL> /

      ATTR D_FROM   D_TO          B_SUM
---------- -------- -------- ----------
        77 28.10.13 10.03.14         10
        77 10.03.14 29.12.14          0
        77 29.12.14 06.03.15         20
        77 06.03.15 07.03.15          0
        77 07.03.15 08.03.15         30
        77 08.03.15 10.03.15        100
        77 10.03.15 14.03.15         30
        77 14.03.15 31.12.15          0
        77 31.12.15 07.01.16        100
        77 07.01.16 09.01.16          0
        77 09.01.16 13.03.17         60
        77 13.03.17                   0

12 rows selected.

SQL>




.....
stax
...
Рейтинг: 0 / 0
27.06.2018, 12:13
| Ответить | Цитировать | Написать  
разделить на непересекающиеся интервалы дат отрезки с ссумированием суммы в пересечениях
    #39666449
Romael
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Romael
Участник
-2-Romael,

unpivot+sum over+lead over

точно unpivot?
...
Рейтинг: 0 / 0
27.06.2018, 12:24
| Ответить | Цитировать | Написать  
разделить на непересекающиеся интервалы дат отрезки с ссумированием суммы в пересечениях
    #39666456
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Romael-2-Romael,

unpivot+sum over+lead over

точно unpivot?

pp as ( ...

.....
stax
...
Рейтинг: 0 / 0
27.06.2018, 12:30
| Ответить | Цитировать | Написать  
разделить на непересекающиеся интервалы дат отрезки с ссумированием суммы в пересечениях
    #39666489
Romael
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Romael
Участник
StaxRomaelпропущено...


точно unpivot?

pp as ( ...

.....
stax
это просто как бы размножение строки...
...
Рейтинг: 0 / 0
27.06.2018, 12:58
| Ответить | Цитировать | Написать  
разделить на непересекающиеся интервалы дат отрезки с ссумированием суммы в пересечениях
    #39666496
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
RomaelStaxпропущено...


pp as ( ...

.....
stax
это просто как бы размножение строки...
інтервальчики надо разбить/обьеденить,
для етого обычно start/stop в одну колонку (unpivot)
алгоритмы наверное разные есть, но мне так проще

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
with p as
    (
        select 1  id, 77 attr, to_date('28.10.13') date_from, to_date('10.03.14') date_to, 10  balance from dual union all
        select 2  id, 77 attr, to_date('29.12.14') date_from, to_date('06.03.15') date_to, 20  balance from dual union all
        select 3  id, 77 attr, to_date('07.03.15') date_from, to_date('14.03.15') date_to, 30  balance from dual union all
        select 4  id, 77 attr, to_date('08.03.15') date_from, to_date('10.03.15') date_to, 70  balance from dual union all
        select 5  id, 77 attr, to_date('31.12.15') date_from, to_date('07.01.16') date_to, 100 balance from dual union all
        select 50 id, 77 attr, to_date('31.12.15') date_from, to_date('05.01.16') date_to, 100 balance from dual union all
        select 6  id, 77 attr, to_date('09.01.16') date_from, to_date('13.03.17') date_to, 60  balance from dual
    )
/*
,pp as (
select attr,decode(s,1,date_from,date_to+1) d,sum(balance*s) b
from p,(select 1 s from dual union all select -1 from dual) d
group by attr,decode(s,1,date_from,date_to+1)
)
,ppp as (
select pp.*,sum(b) over (partition by  attr order by d) b_sum
from pp)
*/
,p2 as (
select DISTINCT 
  attr
 ,decode(s,1,date_from,date_to+1) d,
  sum(balance*s) over (partition by  attr order by decode(s,1,date_from,date_to+1)) b_sum
from p,(select 1 s from dual union all select -1 from dual) d
)
select 
  attr
 ,d d_from
 ,lead(d-1) over (partition by attr order by d) d_to
 ,b_sum
from p2
order by 1,2

SQL> /

      ATTR D_FROM   D_TO          B_SUM
---------- -------- -------- ----------
        77 28.10.13 10.03.14         10
        77 11.03.14 28.12.14          0
        77 29.12.14 06.03.15         20
        77 07.03.15 07.03.15         30
        77 08.03.15 10.03.15        100
        77 11.03.15 14.03.15         30
        77 15.03.15 30.12.15          0
        77 31.12.15 05.01.16        200
        77 06.01.16 07.01.16        100
        77 08.01.16 08.01.16          0
        77 09.01.16 13.03.17         60
        77 14.03.17                   0

12 rows selected.

SQL>



.....
stax
...
Рейтинг: 0 / 0
27.06.2018, 13:06
| Ответить | Цитировать | Написать  
разделить на непересекающиеся интервалы дат отрезки с ссумированием суммы в пересечениях
    #39666560
Romael
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Romael
Участник
Stax,
спасибо, то что нужно! Подпилил, получил искомые данные.

Вот, итоговый запрос:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
with p as
    (
        select 1  id, 77 attr, to_date('28.10.13') date_from, to_date('10.03.14') date_to, 10  balance from dual union all
        select 2  id, 77 attr, to_date('29.12.14') date_from, to_date('06.03.15') date_to, 20  balance from dual union all
        select 3  id, 77 attr, to_date('07.03.15') date_from, to_date('14.03.15') date_to, 30  balance from dual union all
        select 4  id, 77 attr, to_date('08.03.15') date_from, to_date('10.03.15') date_to, 70  balance from dual union all
        select 5  id, 77 attr, to_date('31.12.15') date_from, to_date('07.01.16') date_to, 100 balance from dual union all
        select 6  id, 77 attr, to_date('09.01.16') date_from, to_date('13.03.17') date_to, 60  balance from dual
    ),
    p2 as 
    (
        -- размнаживаем строки
        select 
            p.attr,
            decode(s, 1, date_from, date_to) ascending_date,
            sum(balance * s) tmp_balance
        from p,
            (
                select 1 s from dual 
                union all 
                select -1 from dual
            ) d
        group by 
            attr,
            decode(d.s, 1, p.date_from, p.date_to)
    ),
    p3 as 
    (
        -- вычисляем баланс
        select 
            p2.*,
            sum(p2.tmp_balance) over (partition by p2.attr order by p2.ascending_date) calc_balance
        from 
            p2
    ),
    p4 as 
    (
        select 
            p.attr,
            p.ascending_date as date_from,
            lead(p.ascending_date) over (partition by attr order by p.ascending_date) date_to,
            p.tmp_balance,
            p.calc_balance,
            lead(p.tmp_balance) over (partition by attr order by p.ascending_date) next_tmp_balance
            --lag(p.tmp_balance) over (partition by attr order by p.ascending_date) prev_tmp_balance
        from 
            p3 p
    )
select 
    p.attr,
    case 
        when p.tmp_balance < 0 and p.next_tmp_balance < 0 then p.date_from + 1
        else p.date_from
    end calc_date_from,
    case 
        when p.tmp_balance > 0 and p.next_tmp_balance > 0 then p.date_to-1
        else p.date_to
    end calc_date_to,
    p.calc_balance
from 
    p4 p
where 
    p.calc_balance > 0;
...
Рейтинг: 0 / 0
27.06.2018, 14:37
| Ответить | Цитировать | Написать  
разделить на непересекающиеся интервалы дат отрезки с ссумированием суммы в пересечениях
    #39666564
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Romael,

імхо, проще из 21524977 убрать пустые интервалы (сумма 0)

.....
stax
...
Рейтинг: 0 / 0
27.06.2018, 14:41
| Ответить | Цитировать | Написать  
разделить на непересекающиеся интервалы дат отрезки с ссумированием суммы в пересечениях
    #39666597
Romael
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Romael
Участник
StaxRomael,

імхо, проще из 21524977 убрать пустые интервалы (сумма 0)

.....
stax

по-моему, там суммы поехали...
...
Рейтинг: 0 / 0
27.06.2018, 15:10
| Ответить | Цитировать | Написать  
разделить на непересекающиеся интервалы дат отрезки с ссумированием суммы в пересечениях
    #39666642
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
RomaelStaxRomael,

імхо, проще из 21524977 убрать пустые интервалы (сумма 0)

.....
stax

по-моему, там суммы поехали...

не понял какие поехали, имхо, вроде правильно (кроме где 0)
но Вам виднее

.....
stax
...
Рейтинг: 0 / 0
27.06.2018, 15:50
| Ответить | Цитировать | Написать  
разделить на непересекающиеся интервалы дат отрезки с ссумированием суммы в пересечениях
    #39805690
medium
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
medium
Участник
Мне кажется, что решение некорректно, что подтверждается примером:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
WITH p AS
 (SELECT 1 id
        ,77 attr
        ,to_date('01.01.13', 'dd.mm.RR') date_from
        ,to_date('19.01.13', 'dd.mm.RR') date_to
        ,10 balance
    FROM dual
  UNION ALL
  SELECT 2 id
        ,77 attr
        ,to_date('03.01.13', 'dd.mm.RR') date_from
        ,to_date('05.01.13', 'dd.mm.RR') date_to
        ,20 balance
    FROM dual
  UNION ALL
  SELECT 3 id
        ,77 attr
        ,to_date('07.01.13', 'dd.mm.RR') date_from
        ,to_date('10.01.13', 'dd.mm.RR') date_to
        ,30 balance
    FROM dual
  
  ),
p2 AS
 (
  -- размнаживаем строки
  SELECT p.attr
         ,decode(s, 1, date_from, date_to) ascending_date
         ,SUM(balance * s) tmp_balance
    FROM p
         ,(SELECT 1 s FROM dual UNION ALL SELECT -1 FROM dual) d
   GROUP BY attr
            ,decode(d.s, 1, p.date_from, p.date_to)),
p3 AS
 (
  -- вычисляем баланс
  SELECT p2.*
         ,SUM(p2.tmp_balance) over(PARTITION BY p2.attr ORDER BY p2.ascending_date) calc_balance
    FROM p2),
p4 AS
 (SELECT p.attr
        ,p.ascending_date AS date_from
        ,lead(p.ascending_date) over(PARTITION BY attr ORDER BY p.ascending_date) date_to
        ,p.tmp_balance
        ,p.calc_balance
        ,lead(p.tmp_balance) over(PARTITION BY attr ORDER BY p.ascending_date) next_tmp_balance
  --lag(p.tmp_balance) over (partition by attr order by p.ascending_date) prev_tmp_balance
    FROM p3 p)
SELECT p.attr
      ,CASE
         WHEN p.tmp_balance < 0
              AND p.next_tmp_balance < 0 THEN
          p.date_from + 1
         ELSE
          p.date_from
       END calc_date_from
      ,CASE
         WHEN p.tmp_balance > 0
              AND p.next_tmp_balance > 0 THEN
          p.date_to - 1
         ELSE
          p.date_to
       END calc_date_to
      ,p.calc_balance
  FROM p4 p
 WHERE p.calc_balance > 0;




Получен результат:

Код: plsql
1.
2.
3.
4.
5.
"1";"77";"01.01.2013";"02.01.2013";"10"
"2";"77";"03.01.2013";"05.01.2013";"30"
"3";"77";"05.01.2013";"07.01.2013";"10"
"4";"77";"07.01.2013";"10.01.2013";"40"
"5";"77";"11.01.2013";"19.01.2013";"10"


В результате потерян интервал с "06.01.2013";"06.01.2013";"???"
...
Рейтинг: 0 / 0
24.04.2019, 12:16
| Ответить | Цитировать | Написать  
разделить на непересекающиеся интервалы дат отрезки с ссумированием суммы в пересечениях
    #39805708
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
mediumВ результате потерян интервал с "06.01.2013";"06.01.2013";"???"
інтервал длиной 0

21524977

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SQL> /

      ATTR D_FROM   D_TO          B_SUM
---------- -------- -------- ----------
        77 01.01.13 02.01.13         10
        77 03.01.13 05.01.13         30
        77 06.01.13 06.01.13         10
        77 07.01.13 10.01.13         40
        77 11.01.13 19.01.13         10
        77 20.01.13                   0

6 rows selected.


....
stax
...
Рейтинг: 0 / 0
24.04.2019, 12:44
| Ответить | Цитировать | Написать  
разделить на непересекающиеся интервалы дат отрезки с ссумированием суммы в пересечениях
    #39805715
medium
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
medium
Участник
STAX, спасибо, помогло, невнимательно читал топик.
...
Рейтинг: 0 / 0
24.04.2019, 12:58
| Ответить | Цитировать | Написать  
14 сообщений из 14, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / разделить на непересекающиеся интервалы дат отрезки с ссумированием суммы в пересечениях
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&fpage=78&tid=1882552]:
 0ms
get settings:
 9ms
get forum list:
 12ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 31ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 86ms
get tp. blocked users:
 2ms
others: 15ms
total:  170ms
созд. / загр.: 170ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]