AVG and SUM for INTERVALs
Presumably due to an oversight, Oracle have not yet overloaded the SUM and AVG functions to support the INTERVAL DAY TO SECOND datatype. This is a pity, as it greatly reduces the usefulness of these types for database columns - it's not much use having an "elapsed time" column you can't add up, so many people resort to a plain numeric column representing times in seconds. (Please consider upvoting this suggestion on the Oracle Database Ideas forum.)
Fortunately it's not hard to write your own. Here are my AVG_DSINTERVAL and SUM_DSINTERVAL aggregate/analytic functions.
-- AVG and SUM aggregate functions for INTERVAL DAY TO SECOND values.
-- William Robertson 2004, www.williamrobertson.net
-- Updated 11/2012 to use DSINTERVAL_UNCONSTRAINED type for interval parameters.
--
-- Since introducing the interval types in 9i, Oracle has neglected to overload the
-- aggregate functions SUM, AVG etc for them:
--
-- SQL> select sum(interval '1' second) from dual
-- select sum(interval '1' second) from dual
-- *
-- ERROR at line 1:
-- ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND
--
-- Until they get around to it, you can create your own:
--
-- SQL> select sum_dsinterval(interval '1' second) from dual;
--
-- SUM_DSINTERVAL(INTERVAL'1'SECOND)
-- ---------------------------------------------------------------------------
-- +000000000 00:00:01.000000000
--
-- 1 row selected.
-- Cleardown:
begin
for r in (
select t.type_name from user_types t
where t.type_name in ('DSINTERVAL_AVG', 'DSINTERVAL_SUM')
)
loop
execute immediate 'drop type ' || r.type_name;
loop;
end;
/
-- Each aggregate function requires a corresponding type, providing a standard interface with 4 ODCIAggregate* member functions.
-- Create types for AVG and SUM:
create or replace type dsinterval_avg as object
( total interval day(6) to second
, items integer
, static function ODCIAggregateInitialize
( sctx in out dsinterval_avg )
return number
, member function ODCIAggregateIterate
( self in out dsinterval_avg
, value in interval day to second )
return number
, member function ODCIAggregateMerge
( self in out dsinterval_avg
, ctx2 in dsinterval_avg )
return number
, member function ODCIAggregateTerminate
( self in dsinterval_avg
, returnvalue out dsinterval_unconstrained
, flags in number )
return number
);
/
show errors
create or replace type body dsinterval_avg
as
static function ODCIAggregateInitialize
( sctx in out dsinterval_avg )
return number
is
begin
sctx := new dsinterval_avg(interval '0' second, 0);
return ODCIConst.Success;
end;
member function ODCIAggregateIterate
( self in out dsinterval_avg
, value in interval day to second )
return number
is
begin
self.total := self.total + value;
self.items := self.items + 1;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate
( self in dsinterval_avg
, returnValue out dsinterval_unconstrained
, flags in number )
return number
is
begin
returnvalue := self.total / self.items;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge
( self in out dsinterval_avg
, ctx2 in dsinterval_avg )
return number
is
begin
-- The ODCIAggregateMerge function is used to combine results of
-- parallel queries. Presumably it is applied prior to DCIAggregateTerminate,
-- in which case it should probably combine totals from each thread.
-- Needs testing.
self.total := self.total + ctx2.total;
self.items := self.items + ctx2.items;
return ODCIConst.Success;
end;
end;
/
show errors
-- Corresponding SUM aggregate:
create or replace type dsinterval_sum as object
( total interval day(6) to second
, static function ODCIAggregateInitialize
( sctx in out dsinterval_sum )
return number
, member function ODCIAggregateIterate
( self in out dsinterval_sum
, value in interval day to second )
return number
, member function ODCIAggregateTerminate
( self in dsinterval_sum
, returnValue out dsinterval_unconstrained
, flags in number )
return number
, member function ODCIAggregateMerge
( self in out dsinterval_sum
, ctx2 in dsinterval_sum )
return number
);
/
show errors
create or replace type body dsinterval_sum
as
static function ODCIAggregateInitialize
( sctx in out dsinterval_sum )
return number
is
begin
sctx := new dsinterval_sum(interval '0' second);
return ODCIConst.Success;
end;
member function ODCIAggregateIterate
( self in out dsinterval_sum
, value in interval day to second )
return number
is
begin
self.total := self.total + value;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge
( self in out dsinterval_sum
, ctx2 in dsinterval_sum )
return number
is
begin
-- The ODCIAggregateMerge function is used to combine results of parallel queries prior to ODCIAggregateTerminate.
self.total := self.total + ctx2.total;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate
( self in dsinterval_sum
, returnValue out dsinterval_unconstrained
, flags in number )
return number
is
begin
returnvalue := self.total;
return ODCIConst.Success;
end;
end;
/
show errors
-- Finally, create the functions:
create or replace function sum_dsinterval
( input interval day to second )
return interval day to second
parallel_enable
aggregate using dsinterval_sum;
/
create or replace function avg_dsinterval
( input interval day to second )
return interval day to second
parallel_enable
aggregate using dsinterval_avg;
/
-- Examples:
with tstamp as
( select timestamp '2012-11-12 12:00:00' as t1
, timestamp '2012-11-13 13:01:02' as t2
from dual )
select sum_dsinterval(i) from ( select t2 - t1 as i from tstamp );
-- Avg and Sum of timestamp differences
with times as
( select timestamp '2012-11-12 12:00:00' as t1
, timestamp '2012-11-13 13:01:02' as t2
from dual
union all
select timestamp '2012-11-12 12:34:56' as t1
, timestamp '2012-11-13 14:56:08' as t2
from dual )
select avg_dsinterval(t2 - t1) as average_duration
, sum_dsinterval(t2 - t1) as total_duration
from times;
-- Analytic demo
with demo (item, seq, duration) as
( select 'A', 1, interval '1' minute from dual union all
select 'A', 2, interval '2' minute from dual union all
select 'A', 3, interval '3' minute from dual union all
select 'B', 1, interval '4' minute from dual union all
select 'B', 2, interval '5' minute from dual union all
select 'B', 3, interval '6' minute from dual )
select item, seq
, cast(duration as interval day(1) to second(0)) as duration
, cast(sum_dsinterval(duration) over(partition by item order by seq) as interval day(1) to second(0)) as duration_running_total
from demo;