Need to find average processing time between all timestamp records in Oracle SQL

Question

I am fairly new in SQL/Oracle, and have come across two queries I needed to put together:

1.) Find the time in Days, HH:MM:SS it takes to process one record AND 2.) Find the average processing time based on all timestammps for the selected results

I solved number 1 (maybe not the best way) and am left with the number 2 query giving me headaches. I will try to explain with data ...

So, to solve number 1, I wrote the following query which produced many results, an excerpt of which is shown below the query..

SELECT to_number( to_char(to_date('1','J') + (t_modified - (t_created - (0/24))), 'J') - 1)  days,
to_char(to_date('00:00:00','HH24:MI:SS') +   
(t_modified - (t_created - (0/24))), 'HH24:MI:SS') TimeInQueue, message_no, id, t_created, t_modified
from TABLE   
where message_no in (MESSAGENUMBER) and status = 1 and t_modified > sysdate-2 order by t_created;

This query produced this: I am new to the forum so I cannot post the image in my problem, so here is a link: http://imagesup.net/?di=313891989943

Now, where I am actually stuck is this ... In the T_MODIFIED field, for the given timespan of let's say sysdate -1 I need to find the average between ALL records' T_MODIFIED values. So the end goal here is to find the average time it is taking to process these records for the entire range selected (sysdate-1, -2 or what have you). So I need to take all of those JAN 8 dates in the T_MODIFIED column and find one average time between them all. I really hope this makes sense. If there is any question, I will try to specify better.

Thanks!

Answer 1

Doing time intervals, and especially doing aggregations (sums, averages) across many intervals or interval groups is not straightforward in Oracle. The AVG function doesn't work on timestamp intervals, it expects numbers. So, we'll need to create our own aggregation object and function that will do this.

First, the object spec:

CREATE OR REPLACE TYPE AvgInterval 
AS OBJECT (
runningSum INTERVAL DAY(9) TO SECOND(9),
runningCnt number,

STATIC FUNCTION ODCIAggregateInitialize
  ( actx IN OUT AvgInterval
  ) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateIterate
  ( self  IN OUT AvgInterval,
    val   IN       DSINTERVAL_UNCONSTRAINED
  ) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateTerminate
  ( self             IN   AvgInterval,
    returnValue  OUT DSINTERVAL_UNCONSTRAINED,
    flags           IN   NUMBER
  ) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateMerge
  (self  IN OUT AvgInterval,
   ctx2 IN      AvgInterval
  ) RETURN NUMBER
);

And the object body:

CREATE OR REPLACE TYPE BODY AvgInterval AS
STATIC FUNCTION ODCIAggregateInitialize
  ( actx IN OUT AvgInterval
  ) RETURN NUMBER IS 
  BEGIN
    IF actx IS NULL THEN
      actx := AvgInterval (INTERVAL '0 0:0:0.0' DAY TO SECOND, 0);
    ELSE
      actx.runningSum := INTERVAL '0 0:0:0.0' DAY TO SECOND;
      actx.runningCnt := 0;
    END IF;
    RETURN ODCIConst.Success;
  END;

MEMBER FUNCTION ODCIAggregateIterate
  ( self  IN OUT AvgInterval,
    val   IN     DSINTERVAL_UNCONSTRAINED
  ) RETURN NUMBER IS
  BEGIN
    self.runningSum := self.runningSum + val;
    self.runningCnt := self.runningCnt + 1;
    RETURN ODCIConst.Success;
  END;

MEMBER FUNCTION ODCIAggregateTerminate
  ( self        IN  AvgInterval,
    ReturnValue OUT DSINTERVAL_UNCONSTRAINED,
    flags       IN  NUMBER
  ) RETURN NUMBER IS
  BEGIN
    if (runningCnt <> 0) then
        returnValue := (self.runningSum/runningCnt);
    else
        returnValue := self.runningSum;
    end if;
    RETURN ODCIConst.Success;
  END;

MEMBER FUNCTION ODCIAggregateMerge
  (self IN OUT AvgInterval,
   ctx2 IN     AvgInterval
  ) RETURN NUMBER IS
  BEGIN
    self.runningSum := self.runningSum + ctx2.runningSum;
    self.runningCnt := self.runningCnt + ctx2.runningCnt;
    RETURN ODCIConst.Success;
  END;

END;

Finally, the function that uses the above object:

CREATE OR REPLACE FUNCTION avg_interval( x DSINTERVAL_UNCONSTRAINED) 
RETURN DSINTERVAL_UNCONSTRAINED  PARALLEL_ENABLE
AGGREGATE USING AvgInterval;

Now, we can use it like this:

with x as (
    select systimestamp - 1/24 as created_date, systimestamp as modified_date from dual
    union
    select systimestamp - 2/24 as created_date, systimestamp as modified_date from dual
    union
    select systimestamp - 3/24 as created_date, systimestamp as modified_date from dual
)
select avg_interval(modified_date - created_date)
from x;

Output:

+00 02:00:00.562669

We can also aggregate over groups with this:

with x as (
    select 'FL' as state, to_dsinterval('0 00:56:30') as duration from dual
    union
    select 'FL' as state, to_dsinterval('0 02:08:40') as duration from dual
    union
    select 'GA' as state, to_dsinterval('0 01:01:00') as duration from dual
)
select state, avg_interval(duration)
from x
group by state;

Output:

FL  +00 01:32:35.000000
GA  +00 01:01:00.000000