The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

Thanks for the question, Bill.

Asked: May 01, 2007 - 4:46 pm UTC

Last updated: October 25, 2010 - 8:46 am UTC

Version: 9.0.1

Viewed 10K+ times! This question is

You Asked

I would like to create user-defined aggregate/window functions that allow multiple parameters such as weighted-averages, weighted-standard-deviations, and various other statistical calculations for statistical process control charts, trend analysis, etc.

As far as I can tell from documentation, the ODCIAggregateIterate and ODCIAggregateDelete functions only support a single "value" as parameter for aggregation.

Granted, I presume the "value" parameter could be either a collection object or a type object with multiple properties, but this does not seem to me a satisfying solution.

For example, from the following table...

CREATE TABLE TBL ( 
  DAY DATE,
  VAL NUMBER, 
  WGT NUMBER );

...I'd like to be able SQL with following syntax...

SELECT day, val,
  AVG_WGT(val, wgt) over (order by day rows :N-1 preceding) Nday_wtd_stdev,
  STDEV_WGT(val, wgt) over (order by day rows :N-1 preceding) Nday_wtd_stdev
FROM tbl;

In this example, weighted-average could have been substituted with SUM( wgt * val ) / SUM( wgt ), but for weighted-standard-deviation (and others I have in mind) there are no substitutions using existing aggregate functions that can be made.

I only need to know how to pass more than one parameter to the ODCIAggregateIterate and ODCIAggregateDelete functions and maintain simple straight forward syntax for users of these functions.

I know how to do the internal math for these functions, and can do so without having to internally or externally maintain any potentially large aggregation context. For weighted-standard-deviation example (windowed or not), it can be managed internally by only 4 numerical values.

Thanks for any help you can provide.

and Tom said...

correct, that is the documented restriction.

one parameter for aggregates, that is all.

you can, as noted, use an object type like this:

ops$tkyte%ORA10GR2> create or replace type parms as table of varchar2(4000)
  2  /

Type created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace type string_agg_type as object
  2  (
  3     total varchar2(4000),
  4
  5     static function
  6          ODCIAggregateInitialize(sctx IN OUT string_agg_type )
  7          return number,
  8
  9     member function
 10          ODCIAggregateIterate(self IN OUT string_agg_type ,
 11                               value IN parms )
 12          return number,
 13
 14     member function
 15          ODCIAggregateTerminate(self IN string_agg_type,
 16                                 returnValue OUT  varchar2,
 17                                 flags IN number)
 18          return number,
 19
 20     member function
 21          ODCIAggregateMerge(self IN OUT string_agg_type,
 22                             ctx2 IN string_agg_type)
 23          return number
 24  );
 25  /

Type created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace type body string_agg_type
  2  is
  3
  4  static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
  5  return number
  6  is
  7  begin
  8      sctx := string_agg_type( null );
  9      return ODCIConst.Success;
 10  end;
 11
 12  member function ODCIAggregateIterate(self IN OUT string_agg_type,
 13                                       value IN parms )
 14  return number
 15  is
 16          l_delimiter varchar2(30) := ',';
 17  begin
 18          if (value.count = 2)
 19          then
 20                  l_delimiter := value(2);
 21          end if;
 22      self.total := self.total || l_delimiter || value(1);
 23      return ODCIConst.Success;
 24  end;
 25
 26  member function ODCIAggregateTerminate(self IN string_agg_type,
 27                                         returnValue OUT varchar2,
 28                                         flags IN number)
 29  return number
 30  is
 31  begin
 32      returnValue := ltrim(self.total,',');
 33      return ODCIConst.Success;
 34  end;
 35
 36  member function ODCIAggregateMerge(self IN OUT string_agg_type,
 37                                     ctx2 IN string_agg_type)
 38  return number
 39  is
 40  begin
 41      self.total := self.total || ctx2.total;
 42      return ODCIConst.Success;
 43  end;
 44
 45
 46  end;
 47  /

Type body created.

ops$tkyte%ORA10GR2> show err
No errors.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> CREATE or replace
  2  FUNCTION stragg(input parms )
  3  RETURN varchar2
  4  PARALLEL_ENABLE AGGREGATE USING string_agg_type;
  5  /

Function created.

ops$tkyte%ORA10GR2> column sa format a40
ops$tkyte%ORA10GR2> select deptno, stragg( parms(ename) ) sa
  2    from scott.emp
  3   group by deptno
  4  /

    DEPTNO SA
---------- ----------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

ops$tkyte%ORA10GR2> select deptno, stragg( parms(ename,'|') ) sa
  2    from scott.emp
  3   group by deptno
  4  /

    DEPTNO SA
---------- ----------------------------------------
        10 |CLARK|KING|MILLER
        20 |SMITH|FORD|ADAMS|SCOTT|JONES
        30 |ALLEN|BLAKE|MARTIN|TURNER|JAMES|WARD

Rating

(3 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Munish Chaturvedi, October 03, 2008 - 12:56 am UTC

Hi Tom,

Above information is really a great help. But i want to go a step further and get sorted values in output. I noticed that even if values are sorted before passing to stragg fucntion the output is not sorted.

SQL Query mentioned in your above post:

ops$tkyte%ORA10GR2> select deptno, stragg( parms(ename,'|') ) sa
  2    from scott.emp
  3   group by deptno
  4  /

************************
Current output with above query. 
************************
    DEPTNO SA
---------- ----------------------------------------
        10 |CLARK|KING|MILLER
        20 |SMITH|FORD|ADAMS|SCOTT|JONES
        30 |ALLEN|BLAKE|MARTIN|TURNER|JAMES|WARD

*************************
Desired Output.
************************* 
 DEPTNO SA
---------- ----------------------------------------
  10 |CLARK|KING|MILLER
  20 |ADAMS|FORD|JONES|SCOTT|SMITH
  30 |ALLEN|BLAKE|JAMES|MARTIN|TURNER|WARD

Concatenation should happen after sorting values.

How can we implement this.

Many Thanks,
Munish

October 03, 2008 - 8:09 am UTC

read the two reviews starting at:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15637744429336#16545124501853

for approaches to that.

solution for weighted stddev

Korina Tolboom, October 18, 2010 - 4:28 am UTC

This solutions uses an algorithm so a double loop isn't needed:
http://en.wikipedia.org/wiki/Algorithms_for_calculating_variance

CREATE OR REPLACE type rec_parms as object (average number, weight number)
/

create or replace type agg_t as object (

sum_avg_x_gew number,
sum_avg2_x_gew number,
sum_weight number,

static function ODCIAggregateInitialize(sctx in out agg_t)
return number,

member function ODCIAggregateIterate (self in out agg_t,
value in rec_parms )
return number,

member function ODCIAggregateTerminate (self in agg_t ,
return_value out number,
flags in number )
return number,

member function ODCIAggregateMerge(self in out agg_t,
ctx2 in agg_t )
return number
);
/

create or replace type body agg_t is

static function ODCIAggregateInitialize(sctx in out agg_t)
return number is
begin
sctx := agg_t(0,0,0);
return ODCIConst.Success;
end;

member function ODCIAggregateIterate(
self in out agg_t, value in rec_parms)
return number is
begin
sum_avg_x_gew := sum_avg_x_gew + value.average * value.weight;
sum_avg2_x_gew := sum_avg2_x_gew + value.average * value.average * value.weight;
sum_weight := sum_weight + value.weight;
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self in agg_t,
return_value out number, flags in number) return number is
l_mean number := 0;
l_variance number := 0;
begin
l_mean := sum_avg_x_gew / sum_weight;
l_variance := (sum_avg2_x_gew - (sum_avg_x_gew * l_mean))/sum_weight;
return_value := power(l_variance,0.5);
return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self in out agg_t,
ctx2 in agg_t) return number is
begin
sum_avg_x_gew := ctx2.sum_avg_x_gew;
sum_avg2_x_gew := ctx2.sum_avg2_x_gew;
sum_weight := ctx2.sum_weight;
return ODCIConst.Success;
end;
end;
/

create or replace function stddev_w (input rec_parms) return number
parallel_enable aggregate using agg_t;
/

create table agg_test (
col1 number,
average number,
weight number
);

insert into agg_test values (1,10,10);
insert into agg_test values (1,100,1);

column weigthedstddev format 999999999999.99999

select
col1, stddev_w(rec_parms(average,weight)) weightedstddev from agg_test
group by
col1;

To clear everything:
drop table agg_test;
drop function stddev_w;
drop type agg_t;
drop type rec_parms;

October 25, 2010 - 8:46 am UTC

thanks!

one bug resolved

korina tolboom, March 17, 2011 - 7:13 am UTC

body agg_t sometimes produced an error:
this part is added: "< 1e-10 "

create or replace type body agg_t is

static function ODCIAggregateInitialize(sctx in out agg_t)
return number is
begin
sctx := agg_t(0,0,0);
return ODCIConst.Success;
end;

member function ODCIAggregateIterate(
self in out agg_t, value in rec_parms)
return number is
begin
sum_gem_x_gew := sum_gem_x_gew + nvl(value.gemiddelde,0) * nvl(value.gewicht,0);
sum_gem2_x_gew := sum_gem2_x_gew + nvl(value.gemiddelde,0) * nvl(value.gemiddelde,0) * nvl(value.gewicht,0);
sum_gewicht := sum_gewicht + nvl(value.gewicht,0);
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self in agg_t,
return_value out number, flags in number) return number is
l_mean number := 0;
l_variance number := 0;
begin
l_mean := sum_gem_x_gew / sum_gewicht;
l_variance := (sum_gem2_x_gew - (sum_gem_x_gew * l_mean))/sum_gewicht;
if abs(l_variance) < 1e-10 then
l_variance:=0;
end if;
return_value := power(l_variance,0.5);
return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self in out agg_t,
ctx2 in agg_t) return number is
begin
sum_gem_x_gew := ctx2.sum_gem_x_gew;
sum_gem2_x_gew := ctx2.sum_gem2_x_gew;
sum_gewicht := ctx2.sum_gewicht;
return ODCIConst.Success;
end;
end;
/

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

solution for weighted stddev

one bug resolved

More to Explore

Analytics

User-Defined Aggregate with More than One Value Parameter

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

solution for weighted stddev

one bug resolved

More to Explore

Analytics