Oracle 11g Analytics Functions SUM

Question

I'm using an analytic function to calculate a rolling 24 hour spend amount per customer in my transaction table. The function used to work, however the trx_datetime field was recently changed from date to timestamp(9).

    select sum(th.amount) 
        over(partition by th.customer_id 
        order by th.trx_datetime
        range between 1 preceding and 0 following) as rolling_trx_amt 
from transactions th;

Now when I run the query I get the following error.

ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"

I've searched for hours to find a solution and tried countless conversions on th.trx_datetime but haven't been able to find a way to correct the error. If you know how to get the analytics function order by statement to work with a time stamp please let me know.

Answer 1

You're getting that error because your range is using integers (which works fine with date arithmetic, as that is calculated in numbers of days), whereas timestamp arithmetic uses intervals.

Therefore you need to convert your ranges into intervals, which you can do using numtodsinterval, like so:

select sum(th.amount) 
        over(partition by th.customer_id 
        order by th.trx_datetime
        range between numtodsinterval(1, 'DAY') preceding
                  and numtodsinterval(0, 'DAY') following) as rolling_trx_amt 
from transactions th;

You could also rewrite this as:

select sum(th.amount) 
        over(partition by th.customer_id 
        order by th.trx_datetime
        range between numtodsinterval(1, 'DAY') preceding
                  and current row) as rolling_trx_amt 
from transactions th;

because when you use a windowing clause with range, "current row" equates to "rows with the same value as the current row".