Concepts to Know : Expression Language : DataFlow Core Expression Language : Aggregate Functions
 
Share this page                  
Aggregate Functions
Using the Group Operator to Compute Aggregations and Using the RowsToColumns Operator to Convert Rows to Columns (Pivot) make use of a special class of functions called aggregations. Aggregations can only be used in certain cases depending on the operator and support a slight variation of the usual expression language.
The syntax for the SQL-like, textual form for defining aggregate functions is straightforward:
function_name(input [, input] [, parameter] ) [as target_field_name]
• The input to an aggregation can be either a field name or an expression (see Expression Language).
• The corr and covar aggregations take a second input; all others take only one.
• Some aggregations take an optional parameter, as noted in the following table. These parameters must be given as a constant Boolean or integer value and may be omitted.
• The as clause is optional. If as is specified, the target field name must be given.
• To specify multiple aggregations, separate them with a comma.
• Field names may be surrounded with back ticks. This is required if they contain non-alphanumeric characters.
The following table provides the aggregrations that are supported by the Group operator.
Name
Description
Supported Type
Additional Parameters
avg
Calculates the average (mean)
All numeric types
 
corr
Determines the correlation between two input fields
All numeric types
 
count
Counts the number of rows within a group
All types
sample - specifies if the field data is from a sample or full population.
covar
Calculates the covariance of two input fields
All numeric types
 
geoAvg
Calculates the geometric average
All numeric types
 
harmAvg
Calculates the harmonic average
All numeric types
 
kurtosis
Calculates the kurtosis measure
All numeric types
sample - specifies if the field data is from a sample or full population.
max
Determines the maximum value
All types
 
min
Determines the minimum value
All types
 
moment
Calculates the central moment
All numeric types
k - specifies the moment to calculate
skewness
Measures the skewness of the field data
All numeric types
sample - specifies if the field data is from a sample or full population.
stddev
Calculates the standard deviation
All numeric types
sample - specifies if the field data is from a sample or full population.
sum
Summation of the field data
All numeric types
 
sumSquares
Calculates the sum of squares
All numeric types
 
var
Calculates the variance of the field data
All numeric types
sample - specifies if the field data is from a sample or full population
Although the input to an aggregate function can be defined using various other functions within the expression language, the relationship is one way.
Aggregate expressions cannot be used within other expressions. The aggregate expressions supported by the Using the Group Operator to Compute Aggregations and Using the RowsToColumns Operator to Convert Rows to Columns (Pivot) can be found in Available Functions.