Overview

OceanBase Database supports user-defined aggregate functions for complex data.

OceanBase Database supports predefined aggregate functions such as MAX, MIN, and SUM for multi-row operations. These predefined aggregate functions can only be used with scalar data and cannot be used with complex data types such as multimedia data, opaque types, and LOB. To address these limitations, custom aggregate functions can be used.

User-defined aggregate functions can be used in SQL DML statements, just like built-in aggregate functions, and can also be used with scalar data.

User-defined aggregate functions are an extension feature that can be created using a set of ODCIAggregate routines from the ODCIAggregate interface.

Each user-defined aggregate function can use up to four ODCIAggregate routines or steps: initialization, iteration, merging, and termination.

• Initialization is performed by the ODCIAggregateInitialize() routine. OceanBase Database calls this routine to initialize the user-defined aggregate function and returns the initialized aggregate context as an object type instance.

• Iteration is executed by the ODCIAggregateIterate() routine. This routine can be called multiple times. Each call receives one or more new values and the current aggregate context. The routine processes the new values and returns the updated aggregate context. NULL values are ignored during aggregation and are not passed to the routine.

• Merging is performed by the ODCIAggregateMerge() routine. This routine can be called to merge two aggregate contexts. The routine takes two contexts as input, combines them into a single aggregate context, and returns it.

• Termination of the aggregation is done using the ODCIAggregateTerminate() routine. This routine takes the aggregate context as input and returns the aggregated result value.