生成日期和时间序列 [英] Generate Sequence of dates and time

问题描述

是否可以在下面更改此查询，以便与"OPEN"相同而不是最后的"INTERVALENDTIME".除非当然是新的一天，否则它将是"OPEN"

Is it possible to change this query below so that instead of the "OPEN" being the same it would be the LAST "INTERVALENDTIME". Unless of course it is a new day then it would be "OPEN"

例如，以下是当前版本中的几行日期:

For example here are a few lines of date from the current version:

  DT            OPEN                CLOSE          NAME    INTERVAL END
8/4/2015    8/4/2015 9:00:00 AM 8/4/2015 2:00:00 PM Bob 8/4/2015 1:00:00 PM
8/4/2015    8/4/2015 9:00:00 AM 8/4/2015 2:00:00 PM Bob 8/4/2015 2:00:00 PM
8/5/2015    8/5/2015 9:00:00 AM 8/5/2015 2:00:00 PM Bob 8/5/2015 10:00:00 AM
8/5/2015    8/5/2015 9:00:00 AM 8/5/2015 2:00:00 PM Bob 8/5/2015 11:00:00 AM
8/5/2015    8/5/2015 9:00:00 AM 8/5/2015 2:00:00 PM Bob 8/5/2015 12:00:00 PM

这就是结果

  DT            OPEN                CLOSE          NAME    INTERVAL END
8/4/2015   8/4/2015 12:00:00 PM 8/4/2015 2:00:00 PM Bob 8/4/2015 1:00:00 PM
8/4/2015    8/4/2015 1:00:00 PM 8/4/2015 2:00:00 PM Bob 8/4/2015 2:00:00 PM
8/5/2015    8/5/2015 9:00:00 AM 8/5/2015 2:00:00 PM Bob 8/5/2015 10:00:00 AM
8/5/2015   8/5/2015 10:00:00 AM 8/5/2015 2:00:00 PM Bob 8/5/2015 11:00:00 AM
8/5/2015   8/5/2015 11:00:00 AM 8/5/2015 2:00:00 PM Bob 8/5/2015 12:00:00 PM

这是查询

with par as (
  select date '2015-08-07' enddate, 4 LookBackDays, 3600 inteval, 'Bob' Name, 
         '09:00' open, '14:00' close from dual),
t1 as (
  select to_char(enddate-level+1, 'yyyy-mm-dd') dt, name, open, close from par
    connect by level <= LookBackDays + 1 ),
t2 as (
  select to_char(to_date(open, 'hh24:mi') + (level) * inteval / (24*60*60), 'hh24:mi') tm
    from par
    connect by to_date(open, 'hh24:mi') + level * inteval / (24*60*60) 
               <= to_date(close, 'hh24:mi') )
select to_date(dt, 'yyyy-mm-dd') dt, 
       to_date(dt||' '||open, 'yyyy-mm-dd hh24:mi') open, 
       to_date(dt||' '||close, 'yyyy-mm-dd hh24:mi') close, name, 
       to_date(dt||' '||tm, 'yyyy-mm-dd hh24:mi') IntervalEnd
  from t1 cross join t2 order by dt, tm

谢谢.

推荐答案

我认为您想通过dt使用lag函数和分区... 希望这会有所帮助.

I think you want to use the lag function and partition by by dt... Hope this helps.

with par as (
  select date '2015-08-07' enddate, 4 LookBackDays, 3600 inteval, 'Bob' Name, 
         '09:00' open, '14:00' close from dual),
t1 as (
  select to_char(enddate-level+1, 'yyyy-mm-dd') dt, name, open, close from par
    connect by level <= LookBackDays + 1 ),
t2 as (
  select to_char(to_date(open, 'hh24:mi') + (level) * inteval / (24*60*60), 'hh24:mi') tm
    from par
    connect by to_date(open, 'hh24:mi') + level * inteval / (24*60*60) 
               <= to_date(close, 'hh24:mi') )
select to_date(dt, 'yyyy-mm-dd') dt, 
       lag(to_date(dt||' '||tm, 'yyyy-mm-dd hh24:mi'), 1,to_date(dt||' '||open, 'yyyy-mm-dd hh24:mi'))over(partition by to_date(dt||' '||open, 'yyyy-mm-dd hh24:mi') order by tm) open,  
       to_date(dt||' '||close, 'yyyy-mm-dd hh24:mi') close, name, 
       to_date(dt||' '||tm, 'yyyy-mm-dd hh24:mi') IntervalEnd
from t1 cross join t2 order by dt, tm

这篇关于生成日期和时间序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！