TurboSQL Guide

CREATE AGGREGATE Statement

Previous  Top  Next

Syntax

CREATE AGGREAGATE aggregate_name([:parameter_name data_type]...) RETURNS data_type AS EXTERNAL NAME [namespace_name.class_name],assembly_name

Description

namespace_name.class_name refers to a public class in the assembly. This class must have a default constructor and implement three methods:

[C#]
public <class_name>()
public void Init()
public void Accumulate(<data_type>)
public <data_type> Terminate()

The data_type must fit the parameters of the TurboSQL function (see "Exchanging Parameters with .NET  Assemblies").

When a user-defined aggregate is used in a SQL statement, e.g. SELECT MAX2(Salary) FROM Employees, the execution engine creates an instance of the CLR aggregation class. At the beginning of each group it calls the Init function. After this, it calls the Accumulate function for each row in the group in the order defined by the GROUP BY clause. After all rows of the group have been accumulated, the execution engine calls the Terminate function to retrieve the result. Any resources allocated can be disposed of in the Terminate function.

Samples

The C# code for an aggregate that computes the second order maximum looks like this.

namespace MathRoutines {
 
       public class SecondOrderMax {
 
               public SecondOrderMax() { }
 
               public void Init() {
                       max = null;
                       max2 = null;
               }
 
               public void Accumulate(double? x) {
                       if (max == null || x > max) {
                               if (max != null)
                                       max2 = max;
                               max = x;
                       } else if (max2 == null || x > max2)
                               max2 = x;
               }
 
               public double? Terminate() {
                       return max2;
               }
 
               private double? max;
               private double? max2;
       }
}

And this is how you import the aggregate into TurboSQL.

CREATE AGGREGATE Max2(:x DOUBLE) RETURNS DOUBLE AS
EXTERNAL NAME [MathRoutines.SecondOrderMax],MathRoutines

Compatibility Information

This statement is only available in TurboDB Managed.