Re: Writing aggregate functions in stored functions?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 28 Jul 2001 06:38:28 -0700
Message-ID: <9juf8k01el9@drn.newsguy.com>

In article <9jtd31$bm6$1_at_news.chatlink.com>, "Jerzy says...
>
>Thanks for the info. Yes, coding my own aggregate function is exactly
>what I'm looking for. I chose an example mixing MEDIAN with MIN/MAX
>(builtins) to show that I would like to use time in the same context.
>
>I may have to look at 9i sooner than I was planning.
>

well, in 9i you could code a median but you don't have to, you can use a continous or discrete percentile to get it (new analytic functions)

scott_at_ORA9I.WORLD> select ename, sal from emp;

ENAME SAL
---------- ----------

SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300

14 rows selected.

(here is a pure sql way to get it, not excessively efficient, just as a demonstration)

scott_at_ORA9I.WORLD> 
scott_at_ORA9I.WORLD> 
scott_at_ORA9I.WORLD> SELECT deptno, AVG(DISTINCT sal)
  2    FROM     (SELECT cp1.deptno, CP1.sal
  3             FROM emp CP1, emp CP2
  4            where cp1.deptno = cp2.deptno
  5            GROUP BY cp1.deptno, CP1.sal
  6           HAVING SUM(DECODE(CP1.sal, CP2.sal, 1, 0)) >=
  7                          ABS(SUM(SIGN(CP1.sal - CP2.sal))))

  8 group by deptno
  9 /

    DEPTNO AVG(DISTINCTSAL)

---------- ----------------
        10             2450
        20             2975
        30             1375

scott_at_ORA9I.WORLD>
scott_at_ORA9I.WORLD> SELECT deptno,

  2         PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sal DESC) med_cont,
  3         PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY sal DESC) med_disc

  4 FROM emp
  5 GROUP BY deptno
  6 /

    DEPTNO MED_CONT MED_DISC
---------- ---------- ----------

        10       2450       2450
        20       2975       2975
        30       1375       1500

And if you wanted to code your own, it would resemble something like the following. In 9i they added a new datatype "interval" (a measure of elapsed time in days and seconds or years and days). The SUM() aggregate doesn't work on it -- so we can write our own sum for it. To write an aggregate, we create a type that implements an interface that does an initialize, iterater, terminate (give back the answer) and merge (for parallel query):

create or replace type day_to_second_sum_type as object (

   total interval day to second,

   static function

        ODCIAggregateInitialize(sctx IN OUT day_to_second_sum_type )
        return number,

   member function
        ODCIAggregateIterate(self IN OUT day_to_second_sum_type ,
                             value IN interval day to second )
        return number,

   member function
        ODCIAggregateTerminate(self IN day_to_second_sum_type,
                               returnValue OUT  interval day to second,
                               flags IN number)
        return number,

   member function
        ODCIAggregateMerge(self IN OUT day_to_second_sum_type,
                           ctx2 IN day_to_second_sum_type)
        return number

);
/
show erro

create or replace type body day_to_second_sum_type is

static function ODCIAggregateInitialize(sctx IN OUT day_to_second_sum_type) return number
is
begin

    sctx := day_to_second_sum_type( numtodsinterval( 0, 'SECOND' ) );     return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT day_to_second_sum_type,

                                     value IN interval day to second )

return number
is
begin

    self.total := self.total + value;
    return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN day_to_second_sum_type,

                                       returnValue OUT interval day to second,
                                       flags IN number)

return number
is
begin

    returnValue := self.total;
    return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT day_to_second_sum_type,

                                   ctx2 IN day_to_second_sum_type)

return number
is
begin

    self.total := self.total + ctx2.total;     return ODCIConst.Success;
end;

end;
/

then, we create an aggregate function that points to this interface:

CREATE or replace
FUNCTION ds_sum(input interval day to second ) RETURN interval day to second
PARALLEL_ENABLE AGGREGATE USING day_to_second_sum_type; /

and we can call it:

drop table t;

create table t ( x interval day(5) to second );

insert into t
select numtodsinterval( rownum, 'hour' )   from all_objects
 where rownum < 25;

select ds_sum( x ) from t;

>Jerzy
>
>"John Russell" <johnrussell10_at_home.com> wrote in message
>news:chg2mtk0a03qd4845nd6em83icotklil7j_at_4ax.com...
>> 9i lets you code your own aggregate functions. Not sure if that's what
>> you're asking, since the ones you cite (other than median) are
>> built-ins.
>>
>> If you want to get MIN etc. for subsets of the data, you can use the
>> OVER( ) clause. The syntax inside OVER( ) varies, but with a simple
>> GROUP BY it would be something like "MIN(sal) OVER( PARTITION BY
>> DEPT_NO )".
>>
>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sat Jul 28 2001 - 08:38:28 CDT