TableAggregation Transform

image_pdfimage_print

This transform aggregates the source data according a set of key columns. For each value column an aggregation function has to be specified for example the sum of a value for all rows with identical key columns.

Aggregation Functions

The following aggregation functions are available for transforms TableAggregation, TableNormalization, and TableDenormalzation:

sum

Sum

min

Minimum

max

Maximum

avg

Average

first

The first data entry for one target position

last

The last data entry for one target position

count

Number of data entries written on one target position

count_distinct

Number of distinct data entries written on one target position

group_concat

Concatenates all data entries with separator “,”

concat_distinct

Concatenates all distinct data entries with separator “,” (i.e. remove duplicate values)

selectivity

Estimates the selectivity (0-100) of a value. The value is defined as (100 * distinctCount / rowCount).

var_samp

The sample variance (square of the sample standard deviation)

stddev_samp

The sample standard deviation

var_pop

The population variance (square of the population standard deviation)

stddev_pop

The population standard deviation

none

No aggregation occurs; the last data set is written (overwriting).

Advanced Settings

Column Include Pattern:

Regular expression for column names to be included as output columns of the transform.

Column Exclude Pattern:

Regular expression for column names to be excluded as output columns of the transform.

Measure Include Pattern:

Regular expression for measure names to be included as output columns of the transform.

Measure Exclude Pattern:

Regular expression for measure names to be excluded as output columns of the transform.

Measure Aggregation:

The aggregation function used for all measures which are added dynamically via the Measure include or exclude pattern.

Keep Source Order:

If set, the order of output rows respects the order in the source with first appearance of a particular key. If not set the order is arbitrary but the performance might be better.
Special Case: Distinct Rows

The distinct rows of a source, i.e. removing of all duplicate rows, can be achieved with this transform by setting “Column include pattern” to “.” (i.e. the regular expression for accept all) and no measures. In order to maintain the order of rows the option “keep source order” has to be set.

 

 

image_pdfimage_print