Oracle Analytics Date Range

Question

While researching Oracle Analytics, I came across this query:

select an_id, 
    a_date, 
    min(a_date) over (
        partition by an_id, trunc(a_date) 
        order by a_date
        range between (2.5/24) preceding and (2.5/24) following
    ) mn,
    max(a_date) over (
        partition by an_id, trunc(a_date) 
        order by a_date
        range between (2.5/24) preceding and (2.5/24) following
    ) mx
from a_table

I believe this finds the min and max dates for a given an_id and a_date within a 2.5 hour period.

My question is why does this comparison between a_date (a date) and 2.5/24 (a number) work and how can it be modified for ranges of days, months, or years?

Answer 1

The date type allows arithmetic where a unit of 1 is a day, so SYSDATE + 1 is tomorrow.

For example, try select to_char(sysdate + 1/24, 'DD-MON-YY HH:MM:SS') from dual -> 1 hour from now.

Answer 2

Here are the docs that talk about that specific analytic construct to give some foundational info and add to what jwilson mentioned.

http://download.oracle.com/docs/cd/B19306%5F01/server.102/b14200/functions001.htm#i97640

Answer 3

It's probably clearer to use intervals to specify window ranges:

http://download.oracle.com/docs/cd/B19306%5F01/server.102/b14200/functions104.htm#i89943

SELECT last_name,
       hire_date,
       salary,
       SUM(salary) OVER (ORDER BY hire_date 
                         RANGE    NUMTOYMINTERVAL(1,'year') PRECEDING) AS t_sal 
FROM   employees;