ArangoDB v3.9 reached End of Life (EOL) and is no longer supported.

# 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
``````

## COSINE_SIMILARITY()

Introduced in: v3.9.0

`COSINE_SIMILARITY(x, y) → num`

Return the cosine similarity between x and y.

To calculate the distance, see L1_DISTANCE() and L2_DISTANCE().

• x (array): first input array
• y (array): second input array
• returns num (number|array): the cosine similarity value. If one of the inputs is a nested (2D) array, then an array is returned. The length of each 2D array row should be equal to the length of second input array in that case.

In case of invalid input values the function returns null and produces a warning.

``````COSINE_SIMILARITY([0,1], [1,0]) // 0
COSINE_SIMILARITY([[0,1,0,1],[1,0,0,1],[1,1,1,0],[0,0,0,1]], [1,1,1,1]) // [0.707, 0.707, 0.866, 0.5]
COSINE_SIMILARITY([-1,0], [1,0]) // -1
``````

## DECAY_GAUSS()

Introduced in: v3.9.0

`DECAY_GAUSS(value, origin, scale, offset, decay) → score`

Calculate the score for one or multiple values with a Gaussian function that decays depending on the distance of a numeric value from a user-given origin.

• value (number|array): the input value or an array with input values
• origin (number): the point of origin used for calculating the distance
• scale (number): defines the distance from `origin` + `offset` at which the computed score will equal the `decay` parameter
• offset (number): the decay function will be evaluated for distance values greater than the defined offset
• decay (number): the decay parameter defines how input values are scored at the distance given by the `scale` parameter
• returns score (number|array): a single score or an array of scores depending on the type of the input `value`
``````DECAY_GAUSS(41, 40, 5, 5, 0.5) // 1
DECAY_GAUSS([20, 41], 40, 5, 5, 0.5) // [0.0019531250000000017, 1.0]
DECAY_GAUSS(49.9889, 49.987, 0.001, 0.001, 0.2) // 0.2715403018822964
``````

## DECAY_EXP()

Introduced in: v3.9.0

`DECAY_EXP(value, origin, scale, offset, decay) → num, array`

Calculate the score for one or multiple values with an exponential function that decays depending on the distance of a numeric value from a user-given origin.

• value (number|array): the input value or an array with input values
• origin (number): the point of origin used for calculating the distance
• scale (number): defines the distance from `origin` + `offset` at which the computed score will equal the `decay` parameter
• offset (number): the decay function will be evaluated for distance values greater than the defined offset
• decay (number): the decay parameter defines how input values are scored at the distance given by the `scale` parameter
• returns score (number|array): a single score or an array of scores depending on the type of the input `value`
``````DECAY_EXP(41, 40, 5, 5, 0.7) // 1
DECAY_EXP(2, 0, 10, 0, 0.2)  // 0.7247796636776955
DECAY_EXP(49.9889, 50, 0.001, 0.001, 0.2) // 8.717720806626885e-08
``````

## DECAY_LINEAR()

Introduced in: v3.9.0

`DECAY_LINEAR(value, origin, scale, offset, decay) → score`

Calculate the score for one or multiple values with a linear function that decays depending on the distance of a numeric value from a user-given origin.

• value (number|array): the input value or an array with input values
• origin (number): the point of origin used for calculating the distance
• scale (number): defines the distance from `origin` + `offset` at which the computed score will equal the `decay` parameter
• offset (number): the decay function will be evaluated for distance values greater than the defined offset
• decay (number): the decay parameter defines how input values are scored at the distance given by the `scale` parameter
• returns score (number|array): a single score or an array of scores depending on the type of the input `value`
``````DECAY_LINEAR(41, 40, 5, 5, 0.5)   // 1
DECAY_LINEAR(9.8, 0, 10, 0, 0.2)  // 0.21599999999999994
DECAY_LINEAR(5..7, 0, 10, 0, 0.2) // [0.6, 0.52, 0.44]
``````

## 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) // 4
LOG10(10) // 1
LOG10(0) // null
``````

## L1_DISTANCE()

Introduced in: v3.9.0

`L1_DISTANCE(x, y) → num`

Return the Manhattan distance between x and y.

To calculate the similarity, see COSINE_SIMILARITY().

• x (array): first input array
• y (array): second input array
• returns num (number|array): the L1 distance value. If one of the inputs is a nested (2D) array, then an array is returned. The length of each inner array should be equal to the length of second input array in that case.

In case of invalid input values the function returns null and produces a warning.

``````L1_DISTANCE([-1,-1], [2,2]) // 6
L1_DISTANCE([[1,2,3],[-1,-2,-3],[3,4,5],[-5,2,1]], [1,1,1]) // [3,9,9,7]
L1_DISTANCE([1.5], [3]) // 1.5
``````

## L2_DISTANCE()

Introduced in: v3.9.0

`L2_DISTANCE(x,y) → num`

Return the Euclidean distance between x and y.

To calculate the similarity, see COSINE_SIMILARITY().

• x (array): first input array
• y (array): second input array
• returns num (number|array): the L2 distance value. If one of the inputs is a nested (2D) array, then an array is returned. The length of each inner array should be equal to the length of second input array in that case.

In case of invalid input values the function returns null and produces a warning.

``````L2_DISTANCE([1,1], [5,2]) // 4.1231056256176606
L2_DISTANCE([[1,2,3], [4,5,6], [7,8,9]], [3,2,1]) // [2.8284271247461903, 5.916079783099616, 10.770329614269007]
L2_DISTANCE([0,1], [1,0]) // 1.4142135623730951
``````

## 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
``````

## PRODUCT()

Introduced in: v3.7.2

`PRODUCT(numArray) → product`

Return the product of the values in array.

• numArray (array): an array of numbers, null values are ignored
• returns product (number): the product of all values in numArray. If the array is empty or only null values are contained in the array, 1 will be returned.
``````PRODUCT( [1, 2, 3, 4] ) // 24
PRODUCT( [null, -5, 6] ) // -30
PRODUCT( [ ] ) // 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. The start and stop arguments are truncated to integers unless a step argument is provided.

Also see the range operator for ranges with integer bounds and a step size of 1.

• 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, 2 ]
RANGE(1.5, 2.5, 1) // [ 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().