Aggregation Functions
This page lists the aggregation functions available in the GSQL query language. These functions take a single SET/BAG, SetAccum/BagAccum, or ListAccum as the parameter and return an aggregated value from all members of the set or bag.
|
The |
avg()
Syntax
avg( [DISTINCT] setExp )
Description
Returns the average of all elements in a set or bag. The function can only take set/bag expressions whose members are numeric types.
Return type
A numeric type. If all members of the set/bag expression are integers, the return value will also be rounded down to be an integer.
Parameters
| Parameter | Description | Data type |
|---|---|---|
|
An expression that evaluates to a |
|
Example
avg([5, 4, 1, 0, 0, 0]) -> 1
avg([3, 2, 1]) -> 2count()
Syntax
count( [DISTINCT] setExp )
Description
Returns the size of the set or bag.
Return type
INT
Parameters
| Parameter | Description | Data type |
|---|---|---|
|
An expression that evaluates to a |
|
Example
count([1, 2, 3]) => 3
count([1, 1, 2, 2) => 4max()
Syntax
max( [DISTINCT] setExp )
Description
Returns the member with the maximum value in a set or bag. The function can only take set/bag expressions whose members are numeric types.
Return type
A numeric type.
Parameters
| Parameter | Description | Data type |
|---|---|---|
|
An expression that evaluates to a |
|
Example
max([1, 2, -3, 4]) => 4
max([1, 1, 3, 3]) => 3min()
Syntax
min( [DISTINCT] setExp )
Description
Returns the member with the minimum value in a set or bag. The function can only take set/bag expressions whose members are numeric types.
Return type
A numeric type.
Parameters
| Parameter | Description | Data type |
|---|---|---|
|
An expression that evaluates to a |
|
Example
min([1, 2, -3, 4]) => -3
min([1, 1, 3, 3]) => 1stdev()
Syntax
stdev( [DISTINCT] setExp )
Description
Returns the standard deviation of all members in a set or bag, assuming the input is a sample.
This function uses N - 1 as the denominator, and should be used when taking a sample of the population for an unbiased estimate. When the standard variation of the entire population is being calculated, stdevp should be used.
The function can only take set/bag expressions whose members are numeric types.
Return type
double
Parameters
| Parameter | Description | Data type |
|---|---|---|
|
An expression that evaluates to a |
|
Example
stdev([1]) => 0
stdev([1, 2, 3, 4, 5]) => 1.5811388300842stdevp()
Syntax
stdevp( [DISTINCT] setExp )
Description
Returns the standard deviation of all members in a set or bag, assuming the input is the population.
This function uses N as the denominator, and should be used when calculating the standard deviation for an entire population. When the standard variation of only a sample of the population is being calculated, stDev should be used.
The function can only take set/bag expressions whose members are numeric types.
Return type
double
Parameters
| Parameter | Description | Data type |
|---|---|---|
|
An expression that evaluates to a |
|
Example
stdevp([1]) => 0
stdevp([1, 2, 3, 4, 5]) => 1.4142135623731sum()
Syntax
sum( [DISTINCT] setExp )
Description
Returns the sum of all members in a set or bag. The function can only take set/bag expressions whose members are numeric types.
Return type
A numeric type.
Parameters
| Parameter | Description | Data type |
|---|---|---|
|
An expression that evaluates to a |
|
Example
sum([1, 2, -3, 4]) => 4
sum([1, 1, 3, 3]) => 8