我正在尝试通过前几行的窗口获取最长日期。值得注意的是日期的实际时间部分。

实际表包含更多日期的数据,但查询驻留在一个函数中,该函数以日期为参数将数据限制为一天。

+---------------------------------------------------------------------+
¦FROM  ¦ TO    ¦ GRAPH                                                ¦
+------+-------+------------------------------------------------------¦
¦09:00 ¦ 11:00 ¦  [--------]                                          ¦
¦10:00 ¦ 10:30 ¦     [-]                                              ¦
¦10:45 ¦ 12:00 ¦         [-------]                                    ¦
¦13:00 ¦ 14:30 ¦                      [--------]                      ¦
¦14:00 ¦ 15:00 ¦                            [-----]                   ¦
¦15:30 ¦ 16:30 ¦                                      [----]          ¦
¦16:30 ¦ 17:30 ¦                                           [-----]    ¦
+---------------------------------------------------------------------+


-- show the time portion when querying, for convenience
alter session set nls_date_format = 'DD/MM/YYYY HH24:MI';

-- create an inline view with some data, perform select on it
with iv_dates (start_dt, end_dt) as (
select to_date('08/07/2016 09:00','DD/MM/YYYY HH24:MI'), to_date('08/07/2016 11:00','DD/MM/YYYY HH24:MI') from dual
union all
select to_date('08/07/2016 10:00','DD/MM/YYYY HH24:MI'), to_date('08/07/2016 10:30','DD/MM/YYYY HH24:MI') from dual
union all
select to_date('08/07/2016 10:30','DD/MM/YYYY HH24:MI'), to_date('08/07/2016 12:00','DD/MM/YYYY HH24:MI') from dual
union all
select to_date('08/07/2016 13:00','DD/MM/YYYY HH24:MI'), to_date('08/07/2016 14:30','DD/MM/YYYY HH24:MI') from dual
union all
select to_date('08/07/2016 14:00','DD/MM/YYYY HH24:MI'), to_date('08/07/2016 15:00','DD/MM/YYYY HH24:MI') from dual
union all
select to_date('08/07/2016 15:30','DD/MM/YYYY HH24:MI'), to_date('08/07/2016 16:30','DD/MM/YYYY HH24:MI') from dual
union all
select to_date('08/07/2016 16:30','DD/MM/YYYY HH24:MI'), to_date('08/07/2016 17:30','DD/MM/YYYY HH24:MI') from dual
)
 SELECT start_dt
      , end_dt
      , MAX (end_dt) OVER ( ORDER BY start_dt asc
                            RANGE BETWEEN UNBOUNDED PRECEDING
                            AND     1     PRECEDING
                          )
                          max_end_dt
 FROM iv_dates;


但是,对于max end_dt我没有任何结果。

+--------------------------------------------------+
¦ START_DT         ¦ END_DT           ¦ MAX_END_DT ¦
+------------------+------------------+------------¦
¦ 08/07/2016 09:00 ¦ 08/07/2016 11:00 ¦ -          ¦
¦ 08/07/2016 10:00 ¦ 08/07/2016 10:30 ¦ -          ¦
¦ 08/07/2016 10:30 ¦ 08/07/2016 12:00 ¦ -          ¦
¦ 08/07/2016 13:00 ¦ 08/07/2016 14:30 ¦ -          ¦
¦ 08/07/2016 14:00 ¦ 08/07/2016 15:00 ¦ -          ¦
¦ 08/07/2016 15:30 ¦ 08/07/2016 16:30 ¦ -          ¦
¦ 08/07/2016 16:30 ¦ 08/07/2016 17:30 ¦ -          ¦
+--------------------------------------------------+


尽管我为另一天添加日期后就会开始“运行”

+--------------------------------------------------------+
¦ START_DT         ¦ END_DT           ¦ MAX_END_DT       ¦
+------------------+------------------+------------------¦
¦ 08/07/2016 09:00 ¦ 08/07/2016 11:00 ¦ -                ¦
¦ 08/07/2016 10:00 ¦ 08/07/2016 10:30 ¦ -                ¦
¦ 08/07/2016 10:30 ¦ 08/07/2016 12:00 ¦ -                ¦
¦ 08/07/2016 13:00 ¦ 08/07/2016 14:30 ¦ -                ¦
¦ 08/07/2016 14:00 ¦ 08/07/2016 15:00 ¦ -                ¦
¦ 08/07/2016 15:30 ¦ 08/07/2016 16:30 ¦ -                ¦
¦ 08/07/2016 16:30 ¦ 08/07/2016 17:30 ¦ -                ¦
¦ 09/07/2016 09:00 ¦ 09/07/2016 11:00 ¦ 08/07/2016 11:00 ¦
¦ 09/07/2016 10:00 ¦ 09/07/2016 10:30 ¦ 08/07/2016 11:00 ¦
¦ 09/07/2016 10:30 ¦ 09/07/2016 12:00 ¦ 08/07/2016 12:00 ¦
+--------------------------------------------------------+


只是为了说明这一点-我不需要分区,我只希望它能在一天之内处理我的数据。

但是,似乎由于日期数据类型,windowing子句的行为有所不同。我不需要-我只希望它根据排序在实际的前一行上工作,而不是在(date - 1)上工作。是因为这是一个约会,我该如何处理?

最佳答案

如果使用的是ORDER BY start_dt asc range BETWEEN UNBOUNDED PRECEDING 1 PRECEDING,Oracle将计算当前行的逻辑偏移量。
例。
对于示例的第一行,范围为08/07/2016 09:0007/07/2016 09:00
因为(start_dt-1)= 08/07/2016 09:00 -1 = 07/07/2016 09:00

将范围更改为行

08-05 00:46
查看更多