# Numeric functions

AQL offers some numeric functions for calculations. The following functions are supported:

## ABS()

`ABS(value) → unsignedValue`

Return the absolute part of value.

• value (number): any number, positive or negative
• returns unsignedValue (number): the number without + or - sign
``````ABS(-5) // 5
ABS(+5) // 5
ABS(3.5) // 3.5
``````

## ACOS()

`ACOS(value) → num`

Return the arccosine of value.

• value (number): the input value
• returns num (number|null): the arccosine of value, or null if value is outside the valid range -1 and 1 (inclusive)
``````ACOS(-1) // 3.141592653589793
ACOS(0) // 1.5707963267948966
ACOS(1) // 0
ACOS(2) // null
``````

## ASIN()

`ASIN(value) → num`

Return the arcsine of value.

• value (number): the input value
• returns num (number|null): the arcsine of value, or null if value is outside the valid range -1 and 1 (inclusive)
``````ASIN(1) // 1.5707963267948966
ASIN(0) // 0
ASIN(-1) // -1.5707963267948966
ASIN(2) // null
``````

## ATAN()

`ATAN(value) → num`

Return the arctangent of value.

• value (number): the input value
• returns num (number): the arctangent of value
``````ATAN(-1) // -0.7853981633974483
ATAN(0) // 0
ATAN(10) // 1.4711276743037347
``````

## ATAN2()

`ATAN2(y, x) → num`

Return the arctangent of the quotient of y and x.

``````ATAN2(0, 0) // 0
ATAN2(1, 0) // 1.5707963267948966
ATAN2(1, 1) // 0.7853981633974483
ATAN2(-10, 20) // -0.4636476090008061
``````

## AVERAGE()

`AVERAGE(numArray) → mean`

Return the average (arithmetic mean) of the values in array.

• numArray (array): an array of numbers, null values are ignored
• returns mean (number|null): the average value of numArray. If the array is empty or contains null values only, null will be returned.
``````AVERAGE( [5, 2, 9, 2] ) // 4.5
AVERAGE( [ -3, -5, 2 ] ) // -2
AVERAGE( [ 999, 80, 4, 4, 4, 3, 3, 3 ] ) // 137.5
``````

## AVG()

This is an alias for AVERAGE().

## CEIL()

`CEIL(value) → roundedValue`

Return the integer closest but not less than value.

To round downward, see FLOOR().
To round to the nearest integer value, see ROUND().

• value (number): any number
• returns roundedValue (number): the value rounded to the ceiling
``````CEIL(2.49) // 3
CEIL(2.50) // 3
CEIL(-2.50) // -2
CEIL(-2.51) // -2
``````

## COS()

`COS(value) → num`

Return the cosine of value.

• value (number): the input value
• returns num (number): the cosine of value
``````COS(1) // 0.5403023058681398
COS(0) // 1
COS(-3.141592653589783) // -1
``````

## DEGREES()

`DEGREES(rad) → num`

Return the angle converted from radians to degrees.

• rad (number): the input value
• returns num (number): the angle in degrees
``````DEGREES(0.7853981633974483) // 45
DEGREES(0) // 0
DEGREES(3.141592653589793) // 180
``````

## EXP()

`EXP(value) → num`

Return Euler's constant (2.71828...) raised to the power of value.

• value (number): the input value
• returns num (number): Euler's constant raised to the power of value
``````EXP(1) // 2.718281828459045
EXP(10) // 22026.46579480671
EXP(0) // 1
``````

## EXP2()

`EXP2(value) → num`

Return 2 raised to the power of value.

• value (number): the input value
• returns num (number): 2 raised to the power of value
``````EXP2(16) // 65536
EXP2(1) // 2
EXP2(0) // 1
``````

## FLOOR()

`FLOOR(value) → roundedValue`

Return the integer closest but not greater than value.

To round upward, see CEIL().
To round to the nearest integer value, see ROUND().

• value (number): any number
• returns roundedValue (number): the value rounded downward
``````FLOOR(2.49) // 2
FLOOR(2.50) // 2
FLOOR(-2.50) // -3
FLOOR(-2.51) // -3
``````

## LOG()

`LOG(value) → num`

Return the natural logarithm of value. The base is Euler's constant (2.71828...).

• value (number): the input value
• returns num (number|null): the natural logarithm of value, or null if value is equal or less than 0
``````LOG(2.718281828459045) // 1
LOG(10) // 2.302585092994046
LOG(0) // null
``````

## LOG2()

`LOG2(value) → num`

Return the base 2 logarithm of value.

• value (number): the input value
• returns num (number|null): the base 2 logarithm of value, or null if value is equal or less than 0
``````LOG2(1024) // 10
LOG2(8) // 3
LOG2(0) // null
``````

## LOG10()

`LOG10(value) → num`

Return the base 10 logarithm of value.

• value (number): the input value
• returns num (number): the base 10 logarithm of value, or null if value is equal or less than 0
``````LOG10(10000) // 10
LOG10(10) // 1
LOG10(0) // null
``````

## MAX()

`MAX(anyArray) → max`

Return the greatest element of anyArray. The array is not limited to numbers. Also see type and value order.

• anyArray (array): an array of numbers, null values are ignored
• returns max (any|null): the element with the greatest value. If the array is empty or contains null values only, the function will return null.
``````MAX( [5, 9, -2, null, 1] ) // 9
MAX( [ null, null ] ) // null
``````

## MEDIAN()

`MEDIAN(numArray) → median`

Return the median value of the values in array.

The array is sorted and the element in the middle is returned. If the array has an even length of elements, the two center-most elements are interpolated by calculating the average value (arithmetic mean).

• numArray (array): an array of numbers, null values are ignored
• returns median (number|null): the median of numArray. If the array is empty or contains null values only, the function will return null.
``````MEDIAN( [ 1, 2, 3] ) // 2
MEDIAN( [ 1, 2, 3, 4 ] ) // 2.5
MEDIAN( [ 4, 2, 3, 1 ] ) // 2.5
MEDIAN( [ 999, 80, 4, 4, 4, 3, 3, 3 ] ) // 4
``````

## MIN()

`MIN(anyArray) → min`

Return the smallest element of anyArray. The array is not limited to numbers. Also see type and value order.

• anyArray (array): an array of numbers, null values are ignored
• returns min (any|null): the element with the smallest value. If the array is empty or contains null values only, the function will return null.
``````MIN( [5, 9, -2, null, 1] ) // -2
MIN( [ null, null ] ) // null
``````

## PERCENTILE()

`PERCENTILE(numArray, n, method) → percentile`

Return the nth percentile of the values in numArray.

• numArray (array): an array of numbers, null values are ignored
• n (number): must be between 0 (excluded) and 100 (included)
• method (string, optional): "rank" (default) or "interpolation"
• returns percentile (number|null): the nth percentile, or null if the array is empty or only null values are contained in it or the percentile cannot be calculated
``````PERCENTILE( [1, 2, 3, 4], 50 ) // 2
PERCENTILE( [1, 2, 3, 4], 50, "rank" ) // 2
PERCENTILE( [1, 2, 3, 4], 50, "interpolation" ) // 2.5
``````

## PI()

`PI() → pi`

Return pi.

• returns pi (number): the first few significant digits of pi (3.141592653589793)
``````PI() // 3.141592653589793
``````

## POW()

`POW(base, exp) → num`

Return the base to the exponent exp.

• base (number): the base value
• exp (number): the exponent value
• returns num (number): the exponentiated value
``````POW( 2, 4 ) // 16
POW( 5, -1 ) // 0.2
POW( 5, 0 ) // 1
``````

`RADIANS(deg) → num`

Return the angle converted from degrees to radians.

• deg (number): the input value
• returns num (number): the angle in radians
``````RADIANS(180) // 3.141592653589793
``````

## RAND()

`RAND() → randomNumber`

Return a pseudo-random number between 0 and 1.

• returns randomNumber (number): a number greater than 0 and less than 1
``````RAND() // 0.3503170117504508
RAND() // 0.6138226173882478
``````

Complex example:

``````LET coinFlips = (
FOR i IN 1..100000
RETURN RAND() > 0.5 ? "heads" : "tails"
)
RETURN MERGE(
FOR flip IN coinFlips
COLLECT f = flip WITH COUNT INTO count
RETURN { [f]: count }
)
``````

Result:

``````[
{
"tails": 50098
}
]
``````

## RANGE()

`RANGE(start, stop, step) → numArray`

Return an array of numbers in the specified range, optionally with increments other than 1.

For integer ranges, use the range operator instead for better performance.

• start (number): the value to start the range at (inclusive)
• stop (number): the value to end the range with (inclusive)
• step (number, optional): how much to increment in every step, the default is 1.0
• returns numArray (array): all numbers in the range as array
``````RANGE(1, 4) // [ 1, 2, 3, 4 ]
RANGE(1, 4, 2) // [ 1, 3 ]
RANGE(1, 4, 3) // [ 1, 4 ]
RANGE(1.5, 2.5) // [ 1.5, 2.5 ]
RANGE(1.5, 2.5, 0.5) // [ 1.5, 2, 2.5 ]
RANGE(-0.75, 1.1, 0.5) // [ -0.75, -0.25, 0.25, 0.75 ]
``````

## ROUND()

`ROUND(value) → roundedValue`

Return the integer closest to value.

• value (number): any number
• returns roundedValue (number): the value rounded to the closest integer
``````ROUND(2.49) // 2
ROUND(2.50) // 3
ROUND(-2.50) // -2
ROUND(-2.51) // -3
``````

Rounding towards zero, also known as trunc() in C/C++, can be achieved with a combination of the ternary operator, CEIL() and FLOOR():

``````value >= 0 ? FLOOR(value) : CEIL(value)
``````

## SIN()

`SIN(value) → num`

Return the sine of value.

• value (number): the input value
• returns num (number): the sine of value
``````SIN(3.141592653589783 / 2) // 1
SIN(0) // 0
SIN(-3.141592653589783 / 2) // -1
``````

## SQRT()

`SQRT(value) → squareRoot`

Return the square root of value.

• value (number): a number
• returns squareRoot (number): the square root of value
``````SQRT(9) // 3
SQRT(2) // 1.4142135623730951
``````

Other roots can be calculated with POW() like `POW(value, 1/n)`:

``````// 4th root of 8*8*8*8 = 4096
POW(4096, 1/4) // 8

// cube root of 3*3*3 = 27
POW(27, 1/3) // 3

// square root of 3*3 = 9
POW(9, 1/2) // 3
``````

## STDDEV_POPULATION()

`STDDEV_POPULATION(numArray) → num`

Return the population standard deviation of the values in array.

• numArray (array): an array of numbers, null values are ignored
• returns num (number|null): the population standard deviation of numArray. If the array is empty or only null values are contained in the array, null will be returned.
``````STDDEV_POPULATION( [ 1, 3, 6, 5, 2 ] ) // 1.854723699099141
``````

## STDDEV_SAMPLE()

`STDDEV_SAMPLE(numArray) → num`

Return the sample standard deviation of the values in array.

• numArray (array): an array of numbers, null values are ignored
• returns num (number|null): the sample standard deviation of numArray. If the array is empty or only null values are contained in the array, null will be returned.
``````STDDEV_SAMPLE( [ 1, 3, 6, 5, 2 ] ) // 2.0736441353327724
``````

## STDDEV()

This is an alias for STDDEV_POPULATION().

## SUM()

`SUM(numArray) → sum`

Return the sum of the values in array.

• numArray (array): an array of numbers, null values are ignored
• returns sum (number): the total of all values in numArray. If the array is empty or only null values are contained in the array, 0 will be returned.
``````SUM( [1, 2, 3, 4] ) // 10
SUM( [null, -5, 6] ) // 1
SUM( [ ] ) // 0
``````

## TAN()

`TAN(value) → num`

Return the tangent of value.

• value (number): the input value
• returns num (number): the tangent of value
``````TAN(10) // 0.6483608274590866
TAN(5) // -3.380515006246586
TAN(0) // 0
``````

## VARIANCE_POPULATION()

`VARIANCE_POPULATION(numArray) → num`

Return the population variance of the values in array.

• numArray (array): an array of numbers, null values are ignored
• returns num (number|null): the population variance of numArray. If the array is empty or only null values are contained in the array, null will be returned.
``````VARIANCE_POPULATION( [ 1, 3, 6, 5, 2 ] ) // 3.4400000000000004
``````

## VARIANCE_SAMPLE()

`VARIANCE_SAMPLE(array) → num`

Return the sample variance of the values in array.

• numArray (array): an array of numbers, null values are ignored
• returns num (number|null): the sample variance of numArray. If the array is empty or only null values are contained in the array, null will be returned.
``````VARIANCE_SAMPLE( [ 1, 3, 6, 5, 2 ] ) // 4.300000000000001
``````

## VARIANCE()

This is an alias for VARIANCE_POPULATION().