Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как вставить несуществующие диапазоны дат / 9 сообщений из 9, страница 1 из 1
25.10.2017, 15:23
    #39541837
Sneaky Pete
Sneaky Pete
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как вставить несуществующие диапазоны дат
Привет.
Есть задача получить все диапазоны дат в выборке.

Есть селект

select X.ID,
x.effective_start_date,
x.effective_end_date
from (select 1 as ID , to_date('01.01.2017','DD.MM.YYYY') as effective_start_Date, to_date('03.08.2017','DD.MM.YYYY') as effective_end_date FROM DUAL
UNION ALL
select 1 as ID , to_date('07.08.2017','DD.MM.YYYY') as effective_start_Date, to_date('31.12.2018','DD.MM.YYYY') as effective_end_date FROM DUAL) x
where x.id = 1
and X.effective_start_date <= to_date('31.08.2017','DD.MM.YYYY')
AND X.effective_end_date >= to_date('01.08.2017','DD.MM.YYYY')


Он возвращает логично две строки:

1 1 01.01.2017 03.08.2017
2 1 07.08.2017 31.12.2018

А как сделать красиво так, чтобы вставилась несуществующий как бы диапазон:


1 1 01.01.2017 03.08.2017
X 1 04.08.2017 06.08.2017
2 1 07.08.2017 31.12.2018
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.10.2017, 15:24
    #39541839
Sneaky Pete
Sneaky Pete
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как вставить несуществующие диапазоны дат
заранее спасибо... может есть какой то красивый вариант решения...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.10.2017, 15:33
    #39541845
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как вставить несуществующие диапазоны дат
Sneaky Pete,

для двух записей, или для общего случая (дырки)?

ps
lag/lead

....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.10.2017, 15:39
    #39541853
Sneaky Pete
Sneaky Pete
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как вставить несуществующие диапазоны дат
Stax, для общего случая.. .когда дырки
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.10.2017, 15:56
    #39541872
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как вставить несуществующие диапазоны дат
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
with t as (
select X.ID, x.effective_start_date, x.effective_end_date 
from (        select 1 as ID , to_date('01.01.2017','DD.MM.YYYY') as effective_start_Date, to_date('03.08.2017','DD.MM.YYYY') as effective_end_date FROM DUAL
    UNION ALL select 1 as ID , to_date('07.08.2017','DD.MM.YYYY') as effective_start_Date, to_date('31.12.2018','DD.MM.YYYY') as effective_end_date FROM DUAL) x
where x.id = 1
and X.effective_start_date <= to_date('31.08.2017','DD.MM.YYYY')
AND X.effective_end_date >= to_date('01.08.2017','DD.MM.YYYY')
), 
-----------------------------<cut here>-------------------------------
t_holes as (
select * from(
  select id, effective_end_date+1 effective_start_date, lead(effective_start_date) over(partition by id order by effective_start_date)-1 effective_end_date
  from t
  ) where effective_start_date < effective_end_date
)
select id, effective_start_date, effective_end_date
from t
union all
select id, effective_start_date, effective_end_date
from t_holes
order by effective_start_date
;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.10.2017, 16:10
    #39541882
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как вставить несуществующие диапазоны дат
Sneaky Pete,

Код: 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.
SQL> ed
Wrote file afiedt.buf

  1  with t(b,e) as (
  2  select  1 , 4 from dual union all
  3  select  7 , 8 from dual union all
  4  select  9 ,10 from dual union all
  5  select 11 ,14 from dual union all
  6  select 20 ,25 from dual union all
  7  select 30 ,40 from dual
  8  )
  9  ,tt as (
 10  select t.*
 11   ,decode(lead(b,1,b+1) over (order by b),e+1,cast(null as number),e+1) b2
 12   ,decode(lead(b,1,b+1) over (order by b),e+1,cast(null as number),lead(b,1,b+1) over (order by b)-1) e2
 13  from t
 14  )
 15  select * from tt
 16  UNPIVOT ( ("start","stop") FOR n IN ((b,e) AS '1',(b2,e2) AS '2')) ttt
 17* order by 2
SQL> /

N      start       stop
- ---------- ----------
1          1          4
2          5          6
1          7          8
1          9         10
1         11         14
2         15         19
1         20         25
2         26         29
1         30         40
2         41         30

10 rows selected.



.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.10.2017, 16:10
    #39541884
Sneaky Pete
Sneaky Pete
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как вставить несуществующие диапазоны дат
andrey_anonymous,

а если еще точнее то where effective_start_date < effective_end_date надо заменить на where effective_start_date <= effective_end_date, так как дырка может быть и один день:)

Спасибище!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.10.2017, 01:37
    #39542928
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как вставить несуществующие диапазоны дат
Stax,

Последняя строка у тебя лишняя.

У меня была идея решить без подзапросов, но union all портит всю красоту.

Код: 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.
SQL> with t(b,e) as (
  2  select  1 , 4 from dual union all
  3  select  7 , 8 from dual union all
  4  select  9 ,10 from dual union all
  5  select 11 ,14 from dual union all
  6  select 20 ,25 from dual union all
  7  select 30 ,40 from dual
  8  )
  9  select mr.*, decode(cls, 'Y', ee + 1, b) strt, decode(cls, 'Y', b - 1, e) end
 10  from (select * from t union all select 1e10, 1e10 from dual)
 11  match_recognize
 12  ( order by b
 13    measures classifier() cls, last(cont.e) ee
 14    all rows per match with unmatched rows
 15    after match skip to last y
 16    pattern (strt x* y)
 17    subset cont = (strt, x)
 18    define x as x.b = prev(x.e) + 1
 19  ) mr
 20  where b <> 1e10
 21  order by b;

         B CLS           EE          E       STRT        END
---------- ----- ---------- ---------- ---------- ----------
         1 STRT           4          4          1          4
         7 Y              4          8          5          6
         7 STRT           8          8          7          8
         9 X             10         10          9         10
        11 X             14         14         11         14
        20 Y             14         25         15         19
        20 STRT          25         25         20         25
        30 Y             25         40         26         29
        30 STRT          40         40         30         40

9 rows selected.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.10.2017, 08:36
    #39542967
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как вставить несуществующие диапазоны дат
dbms_photoshopStax,

Последняя строка у тебя лишняя.

вопрос
оракл lead будет раз считать, или три раза?


спасибо,
замодифицировался
Код: 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.
with t(b,e) as (
  select  1 , 4 from dual union all
  select  7 , 8 from dual union all
  select  9 ,10 from dual union all
  select 11 ,14 from dual union all
  select 20 ,25 from dual union all
  select 30 ,40 from dual
 )
,tt as (
  select t.*
   ,decode(lead(b,1,e+1) over (order by b),e+1,cast(null as number),e+1) b2
   ,decode(lead(b,1,e+1) over (order by b),e+1,cast(null as number),lead(b,1,e+1) over (order by b)-1) e2
  from t
  )
 select * from tt
 UNPIVOT ( ("start","stop") FOR n IN ((b,e) AS '1',(b2,e2) AS '2')) ttt
order by 2
/

N      start       stop
- ---------- ----------
1          1          4
2          5          6
1          7          8
1          9         10
1         11         14
2         15         19
1         20         25
2         26         29
1         30         40

9 rows selected.


.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как вставить несуществующие диапазоны дат / 9 сообщений из 9, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 338ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1885031]:
 0ms
get settings:
 7ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 51ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 51ms
get tp. blocked users:
 2ms
others: 194ms
total:  338ms
x
x
Закрыть


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