Numeric and math functions

Applies to VAST Cluster 5.5.

Sign and rounding, division, powers and logarithms, trigonometry, special values, random numbers, bitwise functions and number formatting.

Each function has its own page: syntax, what it does, NULLs and edge cases, examples with their output, how it runs in VAST, how to make it faster, limitations and errors, and similar functions. Names that the VAST Query Engine doesn't have are in all functions A-Z.

Sign and rounding

Function	What it does
abs (also `@`)	Return the absolute value of a number, keeping its type; the @ operator is the same function.
ceil (also `ceiling`)	Round a number up to the nearest integer value; ceiling is the same function.
even	Round a number away from zero to the next even integer, as a DOUBLE.
floor	Round a number down to the nearest integer value.
round	Round a number to a given number of decimal places, with halves rounded away from zero.
sign	Return -1, 0 or 1 for a negative, zero or positive number, as a TINYINT.
trunc	Drop the fractional part of a number, rounding towards zero; integers keep their type.

Arithmetic

Function	What it does
divide	Divide with divide(a, b): integer division on integers, like //, and NULL instead of infinity for a zero divisor.
factorial (also `n!`)	Compute n! with factorial(n) or the postfix ! operator; the result is a 128-bit integer, up to 33!.
gcd (also `greatest_common_divisor`)	Find the greatest common divisor of two integers with gcd(); greatest_common_divisor is the same function.
lcm (also `least_common_multiple`)	Return the least common multiple of two integers; least_common_multiple is the same function.
fdiv	Divide two numbers and round the quotient down to a whole number, as a DOUBLE.
fmod	Return the remainder of a division rounded down, with the sign of the divisor, as a DOUBLE.

Powers and logarithms

Function	What it does
cbrt	Return the cube root of a number, also of a negative number, as a DOUBLE.
exp	Return e raised to the power of a number, as a DOUBLE.
ln	Return the natural logarithm of a number as a DOUBLE; zero and negative numbers fail the query.
log	Return the base-10 logarithm of a number, or its logarithm to any base with log(b, x), as a DOUBLE.
log10	Return the base-10 logarithm of a number as a DOUBLE; zero and negative numbers fail the query.
pow (also `power`, `**`, `^`)	Raise a number to a power; power and the ** and ^ operators do the same and return a DOUBLE.
sqrt	Return the square root of a number as a DOUBLE; a negative number fails the query.
gamma	Compute the gamma function of a number, as a DOUBLE: (n - 1)! for a positive whole number n, extended to fractions.
lgamma	Compute the natural logarithm of the absolute value of the gamma function, for arguments too large for gamma itself.
log2 (also `log(2, x)`)	Take the base-2 logarithm of a number, as a DOUBLE; zero and negative numbers are errors.

Inverse trigonometry

Function	What it does
asin	Compute the arcsine of a number from -1 to 1: the angle, in radians, whose sine it is.
acos	Return the arc cosine, in radians, of a number from -1 to 1, for example the angle that a cosine similarity stands for.
atan	Return the arc tangent, in radians, of any number, for example the angle of a slope.
atan2	Return the angle, in radians from -pi to pi, of the point (x, y), with the right quadrant for every sign of x and y.

Trigonometry

Function	What it does
cos	Compute the cosine of an angle in radians, as a DOUBLE; infinite angles are errors.
cot	Compute the cotangent of an angle in radians, as a DOUBLE; an angle of 0 is an error.
sin	Compute the sine of an angle in radians, as a DOUBLE; infinite angles are errors.
tan	Compute the tangent of an angle in radians, as a DOUBLE; infinite angles are errors.

Hyperbolic functions

Function	What it does
acosh	Return the inverse hyperbolic cosine of a number of 1 or more; a value below 1 gives NaN, not an error.
asinh	Return the inverse hyperbolic sine of any number, a log-like scale that also works for zero and negative values.
cosh	Return the hyperbolic cosine of a number; it's at least 1, and large values overflow to infinity without an error.
sinh	Return the hyperbolic sine of a number; large values overflow to infinity without an error.
tanh	Return the hyperbolic tangent of a number: a value from -1 to 1, often used to squash a score into that range.
atanh	Compute the inverse hyperbolic tangent of a number from -1 to 1, for example for the Fisher z-transform of a correlation.

Angles and constants

Function	What it does
degrees	Convert an angle from radians to degrees; the result is a DOUBLE.
pi	Return the constant pi as a DOUBLE, for areas, circumferences and angle conversions.
radians	Convert an angle from degrees to radians, for example before sin() or cos(); the result is a DOUBLE.

Floating-point values

Function	What it does
isfinite	Test whether a FLOAT or DOUBLE value is an ordinary number, neither infinite nor NaN; it also takes dates and timestamps.
isinf	Test whether a FLOAT or DOUBLE value is positive or negative infinity; it also takes dates and timestamps.
isnan	Test whether a FLOAT or DOUBLE value is NaN (not a number), and how NaN behaves in comparisons and aggregates.
nextafter	Return the next DOUBLE or FLOAT value after x in the direction of y, to step by the smallest amount a floating-point value can change.

Number formatting

Function	What it does
bin (also `to_binary`)	Write an integer, or the bytes of a string, as text of 0s and 1s with bin(); to_binary is the same function.
bar	Draw a text bar of block characters whose length shows where a number lies in a range.
format_bytes (also `formatReadableSize`)	Write a number of bytes for people to read, in binary units of 1,024 (KiB, MiB, GiB); formatReadableSize is the same function.
formatReadableDecimalSize	Write a number of bytes for people to read, in decimal units of 1,000 (kB, MB, GB).
to_base	Write a non-negative integer as text in any base from 2 to 36, optionally zero-padded.

Random numbers

Function	What it does
random	Return a random DOUBLE from 0 up to, not including, 1, with a new value on each row.
setseed	Pass a seed from -1 to 1 for random(); setseed always returns NULL, and a seed outside the range is an error.

Bits

Function	What it does
xor	Set the bits that are set in exactly one of two integers with xor(); there's no XOR operator.

Binning

Function	What it does
equi_width_bins	Split a range of numbers, dates or timestamps into bins of equal width and return their upper bounds as a list.

Intervals

Function	What it does
to_centuries	Turn an integer number of centuries into an INTERVAL of 100-year units, to add to dates and timestamps.

Rounding

Function	What it does
round_even (also `roundbankers`)	Round a number to n decimal places with halves going to the even digit (banker's rounding); roundbankers is the same function.