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. Scalar Support: True Array Support: True Group of Scalars Support: True Group of Arrays Support: False
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 presence of null values in the input will return null.
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 groups and constants.
• One group of arrays.
• 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) ==> null

avg([1,2,3]) ==> 2.0
avg([1,null,3],ignoreNull=False) ==> null
avg([1,2,3],constant(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.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. Scalar Support: False Array Support: True Group of Scalars Support: True Group of Arrays Support: True
Optional Argument Names and Datatypes	N/A
Modifiers	ignore null (default: True): If True, null values are removed from the series. If False, each null value present in the input series will increment the count by 1.
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"]}) ==> {3,3}
count({["a","b","c"],null},ignoreNull=True) ==> {3,null}
count({null,null,null},ignoreNull=True) ==> 0

count({[null],null,[null,1]},ignoreNull=True) ==> {0,null,1}​

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. Scalar Support: False Array Support: True Group of Scalars Support: True Group of Arrays Support: True
Optional Argument Names and Datatypes	N/A
Modifiers	ignore null (default: True): If True, null values are removed from the series. If False, a null value is considered a unique value, and will increment the resulting count by 1.
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"]}) ==> {3,3,3}
countUnique({["a","b","c"],[null,null,null]},ignoreNull=True) ==> {3,0}
countUnique({["a","b","c"],null},ignoreNull=True) ==> {3,null}
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. Scalar Support: True Array Support: True Group of Scalars Support: True Group of Arrays Support: True
Optional Argument Names and Datatypes	N/A
Modifiers	ignore null (default: True): If True, null values are removed from the series. If False, the presence of null values in the input will return null.
Output Datatype(s)	Integer Float String Datetime with Timezone Datetime without Timezone

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 groups of supported types.
• One array
• Two or more scalars.
• A mix of groups and constants.
• One group of arrays.
• A mix of one array and one or more scalars.

Examples

max(1,2,3) ==> 3
max("ABC","efg") ==> "ABC"
max(1,null,2, ignoreNull=True) ==> 2
max(1,null,2, ignoreNull=False) ==> null

max([1,2,3]) ==> 3
max([1,null,3], ignoreNull=True) ==> 3
max([1,null,3], ignoreNull=False) ==> null
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}) ==> 3
max({1,2,3},constant(2)) ==> {2,2,3}
max({1,null,3}, ignoreNull=True) ==> 3
max({1,null,3}, ignoreNull=False) ==> null
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. Scalar Support: True Array Support: True Group of Scalars Support: True Group of Arrays Support: True
Optional Argument Names and Datatypes	N/A
Modifiers	ignore null (default: True): If True, null values are removed from the series. If False, the presence of null values in the input will return null.
Output Datatype(s)	Integer Float String Datetime with Timezone Datetime without Timezone

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 groups of supported types.
• One array
• Two or more scalars.
• A mix of groups and constants.
• One group of arrays.
• A mix of one array and one or more scalars.

Examples

min(1,2,3) ==> 1
min("ABC","efg") ==> "efg"
min(1,null,2, ignoreNull=True) ==> 1
min(1,null,2, ignoreNull=False) ==> null

min([1,2,3]) ==> 1
min([1,null,3], ignoreNull=True) ==> 1
min([1,null,3], ignoreNull=False) ==> null
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}) ==> 1
min({1,2,3},constant(2)) ==> {1,2,2}
min({1,null,3}, ignoreNull=True) ==> 1
min({1,null,3}, ignoreNull=False) ==> null
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 sorts input events from a single input event type based on the event type's sort set, and returns the value from the resulting sorted set expr at the index position.

Usage Information

Category	Details
Number of Arguments	3
Mandatory Argument Names and Datatypes	​set expr: A group of any field of any data type from the input event type. ​index: An integer indicating the index position of the element to return. It uses zero-based numbering. sort set: A group of sortable values (i.e., integer, float, datetime, or string values) from the input event type. Scalar Support: False Array Support: False Group of Scalars Support: True Group of Arrays Support: False
Optional Argument Names and Datatypes	N/A
Modifiers	order: Option to sort between ascending and descending order. ignore null (Default: True): If True, events with null values in the sort expr are removed from the series before the events are sorted. If False, nulls are sorted last in ascending order and first in descending order.
Output Datatype(s)	The function outputs a value with the same data type as the values from set expr.

Examples

nth(set expr = {"a", "b", "c", "z", "x"}, index=constant(1), sort expr={3,4,7,21,6}, order=ascending, ignoreNull=True) ==> "b"

nth(set expr = {"a", "b", "c", "z", "x"}, index=constant(1), sort expr={3,4,7,21,6}, order=descending, ignoreNull=True) ==> "c"

nth(set expr = {"a", "b", "c", "z", "x"}, index=constant(1), sort expr={3,4,null,21,6}, order=descending, ignoreNull=True) ==> "x"

nth(set expr = {"a", "b", "c", "z", "x"}, index=constant(1), sort expr={3,4,null,21,6}, order=descending, ignoreNull=False) ==> "z"

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 group of integers, floats, and scalar values of integers/floats. Scalar Support: True Array Support: True Group of Scalars Support: True Group of Arrays Support: True
Optional Argument Names and Datatypes	N/A
Modifiers	ignore null (default: True): If True, null values are removed from the product. If False, the presence of null values will return null.
Output Datatype(s)	Integer (if all inputs are integers) Float

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 groups and constants.
• One group of arrays.
• A mix of one array and one or more scalars.

Examples

product(2,3,4) ==> 24
product(null,null,null) ==> null

product([2,3,4]) ==> 24
product([2,null,4], ignoreNull=True) => 8
product([2,null,4], ignoreNull=False) => null
product([2,3,4],2) ==> [4,6,8]
product([2,3,4],5,7.0) ==> [70.0,105.0,140.0]

product({2,3,4}) ==> 24
product({1,2,3},constant(2)) ==> {2,4,6}
product({2,3,4},{5,7,8},constant(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. Scalar Support: True Array Support: True Group of Scalars Support: True Group of Arrays Support: False
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 presence of nulls in the input will return null.
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 groups of integers/floats.
• One array.
• Two or more scalars.
• A mix of groups and constants.
• One group of arrays.
• 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) ==> null

stddev([1,2,3]) ==> 0.81649658092773
stddev([1,null,3],ignoreNull=False) ==> null
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}) ==> 0.81649658092773
stddev({1,2,3},constant(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. Scalar Support: True Array Support: True Group of Scalars Support: True Group of Arrays Support: True
Optional Argument Names and Datatypes	N/A
Modifiers	ignore null (Default: True): If True, null values are removed from the series before the sum is computed. If False, the presence of nulls in the input will return null.
Output Datatype(s)	Integer (if all inputs are integers) Float

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 groups of integers/floats.
• One array.
• Two or more scalars.
• A mix of groups and constants.
• One group of arrays.
• A mix of one array and one or more scalars.

Examples

sum(2,3,4) ==> 9
sum(null,null,null, ignoreNull=True) ==> 0
sum(null,null,null, ignoreNull=False) ==> null

sum([2,3,4]) ==> 9
sum([2,null,4], ignoreNull=True) => 6
sum([2,null,4], ignoreNull=False) => null
sum([2,3,4],2) ==> [4,5,6]
sum([2,3,4],5,7.0) ==> [14.0,15.0,16.0]

sum({2,3,4}) ==> 9
sum({1,2,3},constant(2)) ==> {3,4,5}
sum({2,3,4},{5,7,8},constant(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}