Show TOC

Aggregates (Greenplum)Locate this document in the navigation structure

PowerDesigner models user-defined Greenplum aggregate functions as extended objects with a stereotype of <<Aggregate>>.

Creating an Aggregate

You can create an aggregate in any of the following ways:

  • Select Start of the navigation path Model Next navigation step Aggregates End of the navigation path to access the List of Aggregates, and click the Add a Row tool.

  • Right-click the model or package in the Browser, and select Start of the navigation path New Next navigation step Aggregate End of the navigation path.

Aggregate Properties

You can modify an object's properties from its property sheet. To open an aggregate property sheet, double-click its Browser entry in the Aggregates folder.

The following extended attributes are available on the General tab:

Name

Description

Name

Specifies the name of the aggregate function.

Scripting name: AggregateName

Input data types

Specifies the input data type on which this aggregate function operates. To create a zero-argument aggregate function, write * in place of the list of input data types. An example of such an aggregate is count(*).

Scripting name: InputTypeList

Owner

Specifies the owner of the aggregate.

Scripting name: Owner

Ordered

Specifies that the created aggregate function is ordered.

Scripting name: Ordered

The following extensions are available on the Greenplum tab:

Name

Description

Final function

Specifies the name of the final function called to compute the aggregate's result after all input rows have been traversed. The function must take a single argument of type state_data_type. The return data type of the aggregate is defined as the return type of this function. If a final function is not specified, then the ending state value is used as the aggregate's result, and the return type is state_data_type.

Scripting name: FinalFunc

Initial condition

Specifies the initial setting for the state value. This must be a string constant in the form accepted for the data type state_data_type. If not specified, the state value starts out null.

Scripting name: InitCond

Preliminary aggregation function

Specifies the name of a preliminary aggregation function. This is a function of two arguments, both of type state_data_type. It must return a value of state_data_type. A preliminary function takes two transition state values and returns a new transition state value representing the combined aggregation. In Greenplum, if the result of the aggregate function is computed in a segmented fashion, the preliminary aggregation function is invoked on the individual internal states in order to combine them into an ending internal state.

Scripting name: PreFunc

State transition function

Specifies the name of the state transition function to be called for each input row. For an N-argument aggregate function, the function must take N+1 arguments, the first being of type state_data_type and the rest matching the declared input data type(s) of the aggregate. The function must return a value of type state_data_type. This function takes the current state value and the current input data value(s), and returns the next state value.

Scripting name: SFunc

Sort operator

Specifies the associated sort operator for a MIN- or MAX-like aggregate. This is just an operator name (possibly schema-qualified). The operator is assumed to have the same input data types as the aggregate (which must be a single-argument aggregate).

Scripting name: SortOp

State data type

Specifies the data type for the aggregate's state value.

Scripting name: SType