11 User-Defined Aggregate Functions

Example 11-1 How User-Defined Aggregate Functions Work

Consider the aggregate function AVG() in the following statement:

SELECT AVG(T.Sales)
FROM AnnualSales T
GROUP BY T.State;

To perform this computation, the aggregate function AVG() goes through steps like these:

1. Initializes the computation by initializing the aggregation context—the rows over which aggregation is performed:

   runningSum = 0; runningCount = 0;
   
   

2. Iteratively processes each successive input value and updates the context:

   runningSum += inputval; runningCount++;
   
   

3. [Optional] Merge by combining the two aggregation contexts and return a single context. This operation combines the results of aggregation over subsets in order to obtain the aggregate over the entire set. This extra step can be required during either serial or parallel evaluation of an aggregate. If needed, it is performed before the Terminate step:

   runningSum = runningSum1 + runningSum2;
   runningCount = runningCount1 + runningCount2
   
   

   This step is described in greater detail in section "Evaluating User-Defined Aggregates in Parallel".

4. Terminates by computing the result; uses the context to return the resultant aggregate value:

   return (runningSum/runningCount);
   
   

If AVG() were a user-defined function, the object type that embodies it would implement a method for a corresponding ODCIAggregate routine for each of these steps. The variables runningSum and runningCount, which determine the state of the aggregation in the example, would be attributes of that object type.

Example 11-2 How to Implement the ODCIAggregate Interface

The ODCIAggregate routines are implemented as methods within an object type SpatialUnionRoutines. The actual implementation could be in any Oracle-supported language for type methods, such as PL/SQL, C, C++ or Java.

CREATE TYPE SpatialUnionRoutines(
   STATIC FUNCTION ODCIAggregateInitialize( ... ) ...,
   MEMBER FUNCTION ODCIAggregateIterate(...) ... ,
   MEMBER FUNCTION ODCIAggregateMerge(...) ...,
   MEMBER FUNCTION ODCIAggregateTerminate(...)
);

CREATE TYPE BODY SpatialUnionRoutines IS 
...
END;

Example 11-3 How to Define a User-Defined Aggregate Function

This function definition creates the SpatialUnion() aggregate function by specifying its signature and the object type that implements the ODCIAggregate interface:

CREATE FUNCTION SpatialUnion(x Geometry) RETURN Geometry 
AGGREGATE USING SpatialUnionRoutines;

Example 11-4 How to Use SELECT Statement with User-Defined Aggregate Functions

The following query can be used to compute state boundaries by aggregating the geometries of all counties belonging to the same state:

SELECT SpatialUnion(geometry)
FROM counties
GROUP BY state

Example 11-5 How to Use HAVING Clause with User-Defined Aggregate Functions

User-defined aggregates can be used in the HAVING clause to eliminate groups from the output based on the results of the aggregate function. Here, MyUDAG() is a user-defined aggregate:

SELECT groupcol, MyUDAG(col)
FROM tab
GROUP BY groupcol
HAVING MyUDAG(col) > 100
ORDER BY MyUDAG(col);

Example 11-6 How to Use other Query Options with User-Defined Aggregate Functions

User-defined aggregates can take DISTINCT or ALL (default) options on the input parameter. DISTINCT causes duplicate values to be ignored while computing an aggregate. The SELECT statement that contains a user-defined aggregate can also include GROUP BY extensions such as ROLLUP, CUBE and grouping sets:

SELECT ..., MyUDAG(col)
FROM tab
GROUP BY ROLLUP(gcol1, gcol2);

The ODCIAggregateMerge interface is invoked to compute super aggregate values in such rollup operations.

Example 11-7 How to Parallel-Enable a User-Defined Aggregate Function

CREATE FUNCTION MyUDAG(...) RETURN ...
PARALLEL_ENABLE AGGREGATE USING MyAggrRoutines;

Example 11-10 How to Create and Use a User-Defined Aggregate Function

SecondMax() returns the second-largest value in a set of numbers.

1. Implement the type SecondMaxImpl to contain the ODCIAggregate routines:

   create type SecondMaxImpl as object
   (
     max NUMBER, -- highest value seen so far 
     secmax NUMBER, -- second highest value seen so far
     static function ODCIAggregateInitialize(sctx IN OUT SecondMaxImpl) 
       return number,
     member function ODCIAggregateIterate(self IN OUT SecondMaxImpl, 
       value IN number) return number,
     member function ODCIAggregateTerminate(self IN SecondMaxImpl, 
       returnValue OUT number, flags IN number) return number,
     member function ODCIAggregateMerge(self IN OUT SecondMaxImpl, 
       ctx2 IN SecondMaxImpl) return number
   );
   /
   
   

2. Implement the type body for SecondMaxImpl:

   create or replace type body SecondMaxImpl is 
   static function ODCIAggregateInitialize(sctx IN OUT SecondMaxImpl) 
   return number is 
   begin
     sctx := SecondMaxImpl(0, 0);
     return ODCIConst.Success;
   end;
   
   member function ODCIAggregateIterate(self IN OUT SecondMaxImpl, value IN number) return number is
   begin
     if value > self.max then
       self.secmax := self.max;
       self.max := value;
     elsif value > self.secmax then
       self.secmax := value;
     end if;
     return ODCIConst.Success;
   end;
   
   member function ODCIAggregateTerminate(self IN SecondMaxImpl, 
       returnValue OUT number, flags IN number) return number is
   begin
     returnValue := self.secmax;
     return ODCIConst.Success;
   end;
   
   member function ODCIAggregateMerge(self IN OUT SecondMaxImpl, ctx2 IN SecondMaxImpl) return number is
   begin
     if ctx2.max > self.max then
       if ctx2.secmax > self.secmax then 
         self.secmax := ctx2.secmax;
       else
         self.secmax := self.max;
       end if;
       self.max := ctx2.max;
     elsif ctx2.max > self.secmax then
       self.secmax := ctx2.max;
     end if;
     return ODCIConst.Success;
   end;
   end;
   /
   
   

3. Create the user-defined aggregate:

   CREATE FUNCTION SecondMax (input NUMBER) RETURN NUMBER 
   PARALLEL_ENABLE AGGREGATE USING SecondMaxImpl;
   
   

4. Use SecondMax():

   SELECT SecondMax(salary), department_id
      FROM employees
      GROUP BY department_id
      HAVING SecondMax(salary) > 9000;