Aggregation Functions
Aggregation functions provide mechanisms for working with sets of values.
The examples for each function use the following notation:
- Square brackets (
[]) indicate arrays. - Curly braces (
{}) indicate groups. - Arrows (
==>) separate inputs and outputs. Inputs are shown on the left side of the arrow. Outputs are shown on the right side of the arrow.
Average
The Average function returns the average of the input variables.
Usage Information
| Category | Details |
|---|---|
| Number of Arguments | 1+ |
| Mandatory Argument Names and Datatypes | arg: An integer or float.
|
| Optional Argument Names and Datatypes | N/A |
| Modifiers | ignore null (Default: True): If True, null values are removed from the series before the average is computed. If False, the average is computed including nulls, with nulls given a 0 value. |
| Output Datatype(s) | Float |
note
Integers and floats can both be inputs into the same function.
Average takes any of the following:
- One or more sets of supported types.
- One array
- Two or more scalars.
- A mix of two or more scalars and sets.
- A mix of one array and one or more scalars.
Examples
avg(1,2,3) ==> 2.0
avg(1,null,2,ignoreNull=True) ==> 1.5
avg(1,null,2,ignoreNull=False) ==> 1.0
avg([1,2,3]) ==> 2.0
avg([1,null,3],ignoreNull=False) ==> 1.0
avg([1,2,3],2) ==> [1.5,2.0,2.5]
avg([2,3,10],5,7.0) ==> [2.0,3.0,7.0]
avg([1,2,3],{2,3,4}) ==> {[1,2,2],[1,2,3],[1,2,3]}
avg({1,2,3}) ==> 2.0
avg({1,2,3},2) ==> {1.5,2.0,2.5}
avg({1,2,3},{4,1,6}) ==> {2.5,1.5,4.5}
avg({[1,2,3],[3,4,5],[5,6,7]}) ==> {2.0,4.0,6.0}
Count
The Count function returns the total number of elements in the input array, or the total number of values for a given field in all the events in the input element.
Usage Information
| Category | Details |
|---|---|
| Number of Arguments | 1 |
| Mandatory Argument Names and Datatypes | series: An array or set of any datatype.
|
| Optional Argument Names and Datatypes | N/A |
| Modifiers | ignore null (default: True): If True, null values are removed from the series. If False, null values are included. |
| Output Datatype(s) | Integer |
Examples
count(["a","b","c"]) ==> 3
count(["a","b",null],ignoreNull=False) ==> 3
count(null) ==> null
count({"a","b","c"}) ==> 3
count({"a","b",null},ignoreNull=True) ==> 2
count({["a","b","c"],["1","2","3"]}) ==> 2
count({["a","b","c"],null},ignoreNull=True) ==> 1
count({null,null,null},ignoreNull=True) ==> 0
Count Unique
The Count Unique function counts the total number of unique elements in an input array, or the total number of unique events of the input element.
Usage Information
| Category | Details |
|---|---|
| Number of Arguments | 1 |
| Mandatory Argument Names and Datatypes | series: An array or set of any datatype.
|
| Optional Argument Names and Datatypes | N/A |
| Modifiers | ignore null (default: True): If True, null values are removed from the series. If False, null values are included. |
| Output Datatype(s) | Integer |
Examples
countUnique(["a","b","c","a","b"]) ==> 3
countUnique(["a","b",null],ignoreNull=False) ==> 3
countUnique(null) ==> null
countUnique({"a","b","c","a","b"}) ==> 3
countUnique({"a","b",null},ignoreNull=True) ==> 2
countUnique({["a","b","c"],["1","2","3"],["1","2","3"]}) ==> 2
countUnique({["a","b","c"],[null,null,null]},ignoreNull=True) ==> 2
countUnique({["a","b","c"],null},ignoreNull=True) ==> 1
countUnique({null,null,null},ignoreNull=True) ==> 0
Max
The Max function returns the maximum value from the input series of variables.
Usage Information
| Category | Details |
|---|---|
| Number of Arguments | 1+ |
| Mandatory Argument Names and Datatypes | arg: An integer, float, string, datetime with timezone, or datetime without timezone.
|
| Optional Argument Names and Datatypes | N/A |
| Modifiers | N/A |
| Output Datatype(s) |
|
note
Integers and floats can both be inputs into the same function. All input data must share the same datatype.
Max takes any of the following:
- One or more sets of supported types.
- One array
- Two or more scalars.
- A mix of two or more scalars and sets.
- A mix of one array and one or more scalars.
Examples
max(1,2,3) ==> 3
max("ABC","efg") ==> "ABC"
max(1,null,2) ==> 2
max([1,2,3]) ==> 3
max([1,null,3]) ==> 3
max([1,2,3],2) ==> [2,2,3]
max([2,3,10],5,7.0) ==> [7.0,7.0,10.0]
max([1,2,3],{2,3,4}) ==> {[2,2,3],[3,3,3],[4,4,4]}
max({1,2,3}) ==> 3
max({1,2,3},2) ==> {2,2,3}
max({1,2,3},{4,1,6}) ==> {4,2,6}
max({[1,2,3],[3,4,5],[5,6,7]}) ==> {3,5,7}
Min
The Min function returns the minimum value from the input series of variables.
Usage Information
| Category | Details |
|---|---|
| Number of Arguments | 1+ |
| Mandatory Argument Names and Datatypes | arg: An integer, float, string, datetime with timezone, or datetime without timezone.
|
| Optional Argument Names and Datatypes | N/A |
| Modifiers | N/A |
| Output Datatype(s) |
|
note
Integers and floats can both be inputs into the same function. All input data must share the same datatype.
Min takes any of the following:
- One or more sets of supported types.
- One array
- Two or more scalars.
- A mix of two or more scalars and sets.
- A mix of one array and one or more scalars.
Examples
min(1,2,3) ==> 1
min("ABC","efg") ==> "efg"
min(1,null,2) ==> 1
min([1,2,3]) ==> 1
min([1,null,3]) ==> 1
min([1,2,3],2) ==> [1,2,2]
min(([2,3,10],5,7.0)) ==> [2.0,3.0,7.0]
min([1,2,3],|2,3,4|) ==> {[1,2,2],[1,2,3],[1,2,3]}
min({1,2,3}) ==> 1
min({1,2,3},2) ==> {1,2,2}
min({1,2,3},{4,1,6}) ==> {1,1,3}
min({[1,2,3],[3,4,5],[5,6,7]}) ==> {1,3,3}
Nth
The Nth function returns the value of the nth element in the input series.
Usage Information
| Category | Details |
|---|---|
| Number of Arguments | 2 |
| Mandatory Argument Names and Datatypes |
|
| Optional Argument Names and Datatypes | N/A |
| Modifiers | N/A |
| Output Datatype(s) | The function outputs a scalar value of any datatype (the same datatype as the input array subtype/set). |
Examples
nth([1,2,3,4,5],2) ==> 3
nth({1,2,3,4,5},2) ==> 3
nth({1,4,6,34},30) ==> null
Product
The Product function returns the product of the entered series of numerical values.
Usage Information
| Category | Details |
|---|---|
| Number of Arguments | 1+ |
| Mandatory Argument Names and Datatypes | arg: An array or set of integers, floats, and scalar values of integers/floats.
|
| Optional Argument Names and Datatypes | N/A |
| Modifiers | N/A |
| Output Datatype(s) |
|
note
Integers and floats can both be inputs for the same function. The output datatype will always be a floating point number if both integers and floats are received as input.
Product can take any of the following:
- One or more sets of integers/floats.
- One array.
- Two or more scalars.
- A mix of two or more scalars and sets.
- A mix of one array and one or more scalars.
Nulls are ignored, and default to a value of 1.
Examples
product(2,3,4) ==> 24
product(null,null,null) ==> null
product([2,3,4]) ==> 24
product([2,null,4]) => 8
product([2,3,4],2) ==> [4,6,8]
product([2,3,4],5,7.0) ==> [70.0,105.0,140.0]
product([1,2,3],{2,3,4}) ==> {[2,4,6],[3,6,9],[4,8,12]}
product({2,3,4}) ==> 24
product({1,2,3},2) ==> {2,4,6}
product({2,3,4},{5,7,8},9) ==> {90,189,288}
product({[1,2,3],[3,4,5],[5,6,7]}) ==> {6,60,210}
Std-dev
The Std-dev function returns the standard deviation of the input series of variables.
Usage Information
| Category | Details |
|---|---|
| Number of Arguments | 1+ |
| Mandatory Argument Names and Datatypes | arg: An integer or float.
|
| Optional Argument Names and Datatypes | N/A |
| Modifiers | ignore null (Default: True): If True, null values are removed from the series before the standard deviation is computed. If False, the standard deviation is computed with nulls given a value of 0. |
| Output Datatype(s) | Float |
note
Integers and floats can both be inputs for the same function. All input data must share the same datatype.
Std-dev can take any of the following:
- One or more sets of integers/floats.
- One array.
- Two or more scalars.
- A mix of two or more scalars and sets.
- A mix of one array and one or more scalars.
Examples
stddev(1,2,3) ==> 0.81649658092773
stddev(1,null,2,ignoreNull=True) ==> 0.5
stddev(1,null,2,ignoreNull=False) ==> 0.81649658
stddev([1,2,3]) ==> 0.81649658092773
stddev([1,null,3],ignoreNull=False) ==> 1.0
stddev([1,2,3],2) ==> [0.5,0.0,0.5]
stddev(([2,3,10],5,7.0) ==> [2.0548047,1.6329932,2.0548047]
stddev([1,2,3],{2,3,4}) ==> {[0.5,0.0,0.5],[1.0,0.5,0.0],[1.5,1.0,0.5]}
stddev({1,2,3}) ==> 0.81649658092773
stddev({1,2,3},2) ==> {0.5,0.0,0.5}
stddev({1,2,3},{4,1,6}) ==> {1.5,0.5,1.5}
stddev({[1,2,3],[3,4,5],[5,6,7]}) ==> {0.81649658,0.81649658,0.81649658}
Sum
The Sum function returns the sum of the entered series of numerical values.
Usage Information
| Category | Details |
|---|---|
| Number of Arguments | 1+ |
| Mandatory Argument Names and Datatypes | arg: An array or set of integers, floats, and scalar values of integers/floats.
|
| Optional Argument Names and Datatypes | N/A |
| Modifiers | N/A |
| Output Datatype(s) |
|
note
Integers and floats can both be inputs for the same function. The output datatype will always be a floating point number if both integers and floats are received as input.
sum can take any of the following:
- One or more sets of integers/floats.
- One array.
- Two or more scalars.
- A mix of two or more scalars and sets.
- A mix of one array and one or more scalars.
Nulls are ignored, and default to a value of 0.
Examples
sum(2,3,4) ==> 9
sum(null,null,null) ==> null
sum([2,3,4]) ==> 9
sum([2,null,4]) => 6
sum([2,3,4],2) ==> [4,5,6]
sum([2,3,4],5,7.0) ==> [14.0,15.0,16.0]
sum([1,2,3],{2,3,4}) ==> {[3,4,5],[4,5,6],[5,6,7]}
sum({2,3,4}) ==> 9
sum({1,2,3},2) ==> {3,4,5}
sum({2,3,4},{5,7,8},9) ==> {16,19,21}
sum({[1,2,3],[3,4,5],[5,6,7]}) ==> {6,12,18}
sum({[2,3],[3,4,5,6],[5,6,7]}) ==> {5,18,18}