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 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. 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, 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. 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, 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

Join

The Join function returns a string containing all input elements joined together by the specified delimiter.

Usage Information

Category	Details
Number of Arguments	1+
Mandatory Argument Names and Datatypes	arg: Any datatype. Scalar Support: True Array Support: True Group of Scalars Support: True Group of Arrays Support: True
Optional Argument Names and Datatypes	delimiter: The string to use as the delimiter. (By default, it joins the elements with no space between them.)
Modifiers	ignore null (default: True): If True, null values are not included in the output string. If False, null values are included as part of the output string.
Output Datatype(s)	String

note

All input data must share the same datatype.

Join takes any of the following:

• One or more sets of supported types.
• One array.
• Two or more scalar.
• A mix of two or more scalars and sets.
• A mix of one array and one or more scalar.

Examples

join(1,2,3, delimiter="|") ==> "1|2|3"
join(1,2,null,4, ignoreNull=True) ==> "124"
join(1,2,null,4, ignoreNull=False) ==> null

join([1,2,3]) ==> "123"

join({1,2,3}, delimiter=",") ==> "1,2,3"
join(1,{2,3,4}, delimiter=',') ==> {"1,2","1,3","1,4"}
join({1,2,3},{4,5,6}, delimiter="|") ==> {"1|4","2|5","3|6"}

join({[1,2,3],[4,5,6]}, delimiter=",") ==> "[1,2,3],[4,5,6]"

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, or datetime. Scalar Support: True Array Support: True Group of Scalars Support: True Group of Arrays Support: False
Optional Argument Names and Datatypes	N/A
Modifiers	N/A
Output Datatype(s)	Integer Float String Datetime

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, or datetime. Scalar Support: True Array Support: True Group of Scalars Support: True Group of Arrays Support: False
Optional Argument Names and Datatypes	N/A
Modifiers	N/A
Output Datatype(s)	Integer Float String Datetime

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	series: An array or set of any datatype. nth: An integer indicating the index position of the element to return. It uses zero-based numbering. Scalar Support: TBD Array Support: True Group of Scalars Support: True Group of Arrays Support: False
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. Scalar Support: True Array Support: True Group of Scalars Support: True Group of Arrays Support: True
Optional Argument Names and Datatypes	N/A
Modifiers	N/A
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 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. 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 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. Scalar Support: True Array Support: True Group of Scalars Support: True Group of Arrays Support: True
Optional Argument Names and Datatypes	N/A
Modifiers	N/A
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 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}