User-defined aggregates

A user-defined aggregate (UDA) is user-supplied code that implements the various phases of aggregate evaluation, such as initialization, accumulation, and merging, on the system.

UDAs provide new types of aggregation functions that are not currently available with the built-in aggregates such as count(), sum(), avg(), max(), or min(). UDAs are able to take multiple arguments, but they are also scalar and produce one output value. You can use UDAs in a SQL statement anywhere that you could use a built-in aggregate as either grand, grouped, or windowed aggregates.

You can control whether a UDA is allowed in grouped aggregate query or a window (analytical) aggregate query, or either type, when you define the UDA. The restriction is a performance optimization; by restricting an aggregate to grouped aggregates only, for example, the Netezza Performance Server does not allow users to include the aggregate in an analytic (windowed) aggregate query. This can be the intended design of the UDA itself, or it can be a performance optimization to control memory impacts on the Netezza Performance Server system. If an aggregate is defined as ANY type, then it can be used in either aggregation types. For more information about window and grouped aggregates and the performance implications of window aggregates, see the IBM® Netezza® Database User’s Guide.