Tom Tom - 4 months ago 10
SQL Question

MAX analytic function with date window preserving time

I'm trying to get the max date over a window of previous rows. Of note is the actual time-portion of the date.

The actual table contains data for plenty more dates, but the query resides in a function which takes a date as an argument to limit the data to one day.

-- ┌──────┬───────┬──────────────────────────────────────────────────────┐
-- │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;


However, I'm not getting any result for the 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 │ - │
-- └──────────────────┴──────────────────┴────────────┘


Though as soon as I add a date for another day it'll start "working"

-- ┌──────────────────┬──────────────────┬──────────────────┐
-- │ 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 │
-- └──────────────────┴──────────────────┴──────────────────┘


Just to illustrate that though - I don't need partitioning, I just want this to work on my data for one day.

But it seems as though the windowing clause is behaving differently because of the date-datatype. I don't want that - I just want it to work on the actual previous row according to the sorting, not on
(date - 1)
. Is it because it is a date, and how could I deal with this?

Answer

If you are using ORDER BY start_dt asc range BETWEEN UNBOUNDED PRECEDING 1 PRECEDING Oracle calculate logical offset from current row. Example. For first row from your example, range is 08/07/2016 09:00 and 07/07/2016 09:00 because (start_dt - 1) = 08/07/2016 09:00 -1 = 07/07/2016 09:00. . Change range to rows

Comments