10.1 Numeric functions

Numeric functions expect numbers: a null, a string or a missing field is an error (`abs` expects a number, found Null). Guard optional values with coalesce(x?, 0) or if(x? == null, …, …). The aggregates sum, avg, median, percentile and stddev are the exception: they skip null items (min and max do not).

abs

abs(x) → the absolute value. An integer stays an integer.

abs(-3)        → 3
abs(-2.5)      → 2.5
abs(price - 100)

sqrt

sqrt(x) → the square root, as a float. A negative input is an error (`sqrt` expects a non-negative number).

sqrt(16)       → 4.0

pow

pow(x, y) → x to the power y; an integer when the result is exact.

pow(2, 10)     → 1024
pow(2, 0.5)    → 1.4142135623730951

round

round(x [, digits [, mode]]) → x rounded.

A value written as a decimal half (2.675) counts as one, even though its binary float is slightly below. An integer input is returned unchanged.

round(13.333, 2)               → 13.33
round(2.675, 2)                → 2.68
round(2.5)                     → 3
round(-2.5)                    → -3
round(0.125, 2, "half_even")   → 0.12

Errors. digits outside 0..15; a mode other than the two above.

trunc

trunc(x [, digits]) → x rounded toward zero, same rules as round.

trunc(-2.7)        → -2
trunc(3.14159, 2)  → 3.14

floor

floor(x) → the largest integer not above x.

floor(12.5)    → 12
floor(-2.5)    → -3

ceil

ceil(x) → the smallest integer not below x.

ceil(2.1)      → 3

sign

sign(x) → -1, 0 or 1.

sign(-3)       → -1

ln

ln(x) → the natural logarithm, as a float; x must be positive.

ln(1)          → 0.0

log10

log10(x) → the base-10 logarithm, as a float; x must be positive.

log10(1000)    → 3.0

log

log(x, base) → the logarithm of x in base, as a float.

log(8, 2)      → 3.0

exp

exp(x) → e to the power x, as a float.

exp(0)         → 1.0

min

min(a, b, …) → the smallest argument. With one array argument, the smallest item; null for an empty array. Values must be comparable (numbers with numbers, strings with strings): a null item is an error (`min` cannot compare Null and Number…), whereas the min measure of group skips null.

min(3, 7, 1)        → 1
min([3, 1, 2])      → 1
min("b", "a")       → "a"

max

max(a, b, …) → the largest argument, or the largest item of one array argument; same rules as min.

max(age, 30)
max(scores)

sum

sum(a, b, …) → the sum. With one array argument, the sum of its items. null items are skipped; the result stays an integer while every term is; 0 for an empty array.

sum(1, 2, 3)          → 6
sum([])               → 0
sum([1, null, 2.5])   → 3.5
sum(scores)

avg

avg(a, b, …) → the mean, or the mean of one array argument’s items; null items skipped; null when there is nothing to average.

avg(1, 2)      → 1.5
avg([])        → null

median

median(a, b, …) → the middle value (the mean of the two middle values for an even count), as a float; one array argument works too; null items skipped; null when empty.

median([3, 1, 2])      → 2.0
median([1, 2, 3, 4])   → 2.5

percentile

percentile(array, fraction) → the value at fraction (0 to 1) of the sorted numbers, linear between the two closest values (a spreadsheet’s inclusive percentile); null when empty.

percentile([3, 1, 2], 0.5)     → 2.0
percentile(scores, 0.9)

stddev

stddev(a, b, …) → the sample standard deviation; one array argument works too; null with fewer than two values (stddev([1]) → null).

stddev([3, 1, 2])      → 1.0

div

div(a, b [, fallback]) → a / b, or fallback when b is 0 (null without a fallback). The safe division: / by zero is an error.

div(7, 2)          → 3.5
div(1, 0)          → null
div(1, 0, -1)      → -1
div(margin, revenue, 0)

Operators. + - * / % follow 4.5: integers stay integers while they fit, / always gives a float (7 / 2 → 3.5), % is the remainder (7 % 3 → 1).