<img src="02cr_aud32.gif" height="49" width="100"> CREATE AGGREGATE

Informix Guide to SQL: Syntax
SQL Statements

CREATE AGGREGATE

Use the CREATE AGGREGATE statement to create a new aggregate function. User-defined aggregates extend the functionality of the database server because they can perform any kind of aggregate computation that the user wants to implement.

Syntax

Element Purpose Restrictions Syntax

aggregate Name of the new aggregate The name cannot be the same as the name of any built-in aggregate or the name of any `UDR`. Identifier, p. 4-205

comb_func Function that merges one partial result into the other and returns the updated partial result You must specify the combine function both for parallel queries and for sequential queries. Database Object Name, p. 4-50

final_func Function that converts a partial result into the result type If the final function is omitted, the database server returns the final result of the iterator function. Database Object Name, p. 4-50

init_func Function that initializes the data structures required for the aggregate computation The initialization function must be able to handle null arguments. Database Object Name, p. 4-50

iter_func Function that merges a single value with a partial result and returns the updated partial result You must specify a value for the iterator function. If the initialization function is omitted, the iterator function must be able to handle null arguments. Database Object Name, p. 4-50

Element	Purpose	Restrictions	Syntax
aggregate	Name of the new aggregate	The name cannot be the same as the name of any built-in aggregate or the name of any `UDR`.	Identifier, p. 4-205
comb_func	Function that merges one partial result into the other and returns the updated partial result	You must specify the combine function both for parallel queries and for sequential queries.	Database Object Name, p. 4-50
final_func	Function that converts a partial result into the result type	If the final function is omitted, the database server returns the final result of the iterator function.	Database Object Name, p. 4-50
init_func	Function that initializes the data structures required for the aggregate computation	The initialization function must be able to handle null arguments.	Database Object Name, p. 4-50
iter_func	Function that merges a single value with a partial result and returns the updated partial result	You must specify a value for the iterator function. If the initialization function is omitted, the iterator function must be able to handle null arguments.	Database Object Name, p. 4-50

Usage

You can specify the INIT, ITER, COMBINE, FINAL, and HANDLESNULLS modifiers in any order.

Important: You must specify the ITER and COMBINE modifiers in a CREATE AGGREGATE statement. You do not have to specify the INIT, FINAL, and HANDLESNULLS modifiers in a CREATE AGGREGATE statement.

The ITER, COMBINE, FINAL, and INIT modifiers specify the support functions for a user-defined aggregate. These support functions do not have to exist at the time you create the user-defined aggregate.

If you omit the HANDLESNULLS modifier, rows with null aggregate argument values do not contribute to the aggregate computation. If you include the HANDLESNULLS modifier, you must declare all the support functions to handle null values as well.

Extending the Functionality of Aggregates

The database server provides two ways to extend the functionality of aggregates. You use the CREATE AGGREGATE statement only for the second method.

Extensions of built-in aggregates

A built-in aggregate is an aggregate that the database server provides, such as COUNT, SUM, or AVG. These aggregates work only with built-in data types. You can extend these aggregates to work with extended data types. To extend a built-in aggregate, you must create user-defined routines that overload the binary operators for that aggregate. For further information on extending built-in aggregates, see the Extending Informix Dynamic Server 2000 manual.

Creation of user-defined aggregates

A user-defined aggregate is an aggregate that you define to perform an aggregate computation that is not provided by the database server. You can use user-defined aggregates with built-in data types, extended data types, or both. To create a user-defined aggregate, you use the CREATE AGGREGATE statement. In this statement, you name the new aggregate and specify the support functions that compute the aggregate result. These support functions perform initialization, sequential aggregation, combination of results, and type conversion

Example of Creating a User-Defined Aggregate

In the following example, you create a user-defined aggregate named average:

CREATE AGGREGATE average

	WITH (

		INIT = average_init,

		ITER = average_iter,

		COMBINE = average_combine,

		FINAL = average_final

		)

Before you use the average aggregate in a query, you must also use CREATE FUNCTION statements to create the support functions specified in the CREATE AGGREGATE statement. The following table gives an example of the task that each support function might perform for average.


Keyword	Support Function	Purpose
`INIT`	average_init	Allocates and initializes an extended data type that stores the current sum and the current row count.
ITER	average_iter	For each row, adds the value of the expression to the current sum and increments the current row count by one.
COMBINE	average_combine	Adds the current sum and the current row count of one partial result to the other and returns the updated result.
FINAL	average_final	Returns the ratio of the current sum to the current row count and converts this ratio to the result type.

Parallel Execution

The database server can break up an aggregate computation into several pieces and compute them in parallel. The database server using the INIT and ITER support functions to compute each piece sequentially. Then the database server uses the COMBINE function to combine the partial results from all the pieces into a single result value. Whether an aggregate is parallel is an optimization decision that is transparent to the user.

Related Information

Related statements: DROP AGGREGATE

For information about how to invoke a user-defined aggregate, see the discussion of user-defined aggregates in the Expression segment.

For a description of the sysaggregates system catalog table that holds information about user-defined aggregates, see the Informix Guide to SQL: Reference.