Aggregation (DB)

Performs aggregate calculations on a data set by specifying a group-by configuration and an aggregate expression.

Information at a Glance

Note: You can also use this operator in a workflow that uses TIBCO® Data Virtualization and Apache Spark 3.2 or later.

Parameter

 Description
Category Transform
Data source type DB
Send output to other operators Yes
Data processing tool n/a

Note: The Aggregation (DB) operator is for database data only. For Hadoop data, use the Aggregation (HD) operator.

The Aggregation operator also supports the ability to define window fields.

Input

A data set from the preceding operator.

Bad or Missing Values

Null values are not allowed and result in an error.

Note:

Before using this operator, you can filter out null values or use the Null Value Replacement (DB) operator to replace them with a compatible value.

Configuration

Parameter Description
Notes Notes or helpful information about this operator's parameter settings. When you enter content in the Notes field, a yellow asterisk appears on the operator.
Aggregates Specify the Group By and Aggregators fields.

To display the Define Column Aggregations dialog, click Define Aggregations.

Window Field List Specifies window columns, associated window function expressions, and the window OVER specifications for the aggregate data set.

A window column is calculated from aggregate data by assessing the values for all other data source columns first, then walking through the results and, based on the groups of records, calculating a new value for each group. This is helpful, for example, when sorting data by percent of the total or ranking data groups.

To display the Window Column Configuration dialog, click Window Field List. For more information, see Window Column Configuration dialog.

Output Type
  • TABLE outputs a database table. Specifying TABLE enables Storage Parameters.
  • VIEW outputs a database view.
Output Schema The schema for the output table or view.
Output Table Specify the table path and name where the output of the results is generated. By default, this is a unique table name based on your user ID, workflow ID, and operator.
Storage Parameters Advanced database settings for the operator output. Available only for TABLE output.

See Storage Parameters dialog for more information.

Drop If Exists Specifies whether to overwrite an existing table.
  • Yes - If a table with the name exists, it is dropped before storing the results.
  • No - If a table with the name exists, the results window shows an error message.

Output

Visual Output
The data rows of the output table/view displayed (up to 2000 rows of the data).
Data Output
A new data set.