ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как вставить несуществующие диапазоны дат
9 сообщений из 9, страница 1 из 1
Как вставить несуществующие диапазоны дат
    #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:23
| Ответить | Цитировать | Написать  
Как вставить несуществующие диапазоны дат
    #39541839
Sneaky Pete
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sneaky Pete
Участник
заранее спасибо... может есть какой то красивый вариант решения...
...
Рейтинг: 0 / 0
25.10.2017, 15:24
| Ответить | Цитировать | Написать  
Как вставить несуществующие диапазоны дат
    #39541845
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Sneaky Pete,

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

ps
lag/lead

....
stax
...
Рейтинг: 0 / 0
25.10.2017, 15:33
| Ответить | Цитировать | Написать  
Как вставить несуществующие диапазоны дат
    #39541853
Sneaky Pete
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sneaky Pete
Участник
Stax, для общего случая.. .когда дырки
...
Рейтинг: 0 / 0
25.10.2017, 15:39
| Ответить | Цитировать | Написать  
Как вставить несуществующие диапазоны дат
    #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, 15:56
| Ответить | Цитировать | Написать  
Как вставить несуществующие диапазоны дат
    #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
25.10.2017, 16:10
| Ответить | Цитировать | Написать  
Как вставить несуществующие диапазоны дат
    #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, 01:37
| Ответить | Цитировать | Написать  
Как вставить несуществующие диапазоны дат
    #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
27.10.2017, 08:36
| Ответить | Цитировать | Написать  
9 сообщений из 9, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как вставить несуществующие диапазоны дат
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39541872&tid=1885031]:
 0ms
get settings:
 6ms
get forum list:
 20ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 174ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 61ms
get tp. blocked users:
 1ms
others: 231ms
total:  515ms
созд. / загр.: 515ms
x
x
Закрыть


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