Mathematical Functions and Operators

Mathematical Operators

Mathematical Functions

abs(x) -> same as input

Returns the absolute value of x.

cbrt(x) -> double()

Returns the cube root of x.

ceil(x) -> same as input

This is an alias for ceiling().

ceiling(x) -> same as input

Returns x rounded up to the nearest integer.

cosine_similarity(x, y) -> double()

Returns the cosine similarity between the sparse vectors x and y:

1. SELECT cosine_similarity(MAP(ARRAY['a'], ARRAY[1.0]), MAP(ARRAY['a'], ARRAY[2.0])); -- 1.0

cosine_similarity(x, y) -> double()

Returns the cosine similarity between the arrays x and y. If the input arrays have different sizes or if the input arrays contain a null, the function throws user error:

1. SELECT cosine_similarity(ARRAY[1.2], ARRAY[2.0]); -- 1.0

l2_squared(array(real), array(real)) -> real()

Returns the squared Euclidean distance between the vectors represented as array(real). If the input arrays have different sizes or if the input arrays contain a null, the function throws user error:

1. SELECT l2_squared(ARRAY[1.0], ARRAY[2.0]); -- 1.0

l2_squared(array(double), array(double)) -> double()

Returns the squared Euclidean distance between the vectors represented as array(double). If the input arrays have different sizes or if the input arrays contain a null, the function throws user error:

1. SELECT l2_squared(ARRAY[1.0], ARRAY[2.0]); -- 1.0

dot_product(array(real), array(real)) -> real()

Returns the dot product of two vectors represented as array(real). If the input arrays have different sizes or if the input arrays contain a null, the function throws user error:

1. SELECT dot_product(ARRAY[1.0, 2.0], ARRAY[3.0, 4.0]); -- 11.0

dot_product(array(double), array(double)) -> double()

Returns the dot product of two vectors represented as array(double). If the input arrays have different sizes or if the input arrays contain a null, the function throws user error:

1. SELECT dot_product(ARRAY[1.0, 2.0], ARRAY[3.0, 4.0]); -- 11.0

degrees(x) -> double()

Converts angle x in radians to degrees.

e() -> double()

Returns the constant Euler’s number.

exp(x) -> double()

Returns Euler’s number raised to the power of x.

floor(x) -> same as input

Returns x rounded down to the nearest integer.

from_base(string, radix) -> bigint()

Returns the value of string interpreted as a base-radix number.

ln(x) -> double()

Returns the natural logarithm of x.

log2(x) -> double()

Returns the base 2 logarithm of x.

log10(x) -> double()

Returns the base 10 logarithm of x.

mod(n, m) -> same as input

Returns the modulus (remainder) of n divided by m.

pi() -> double()

Returns the constant Pi.

pow(x, p) -> double()

This is an alias for power().

power(x, p) -> double()

Returns x raised to the power of p.

radians(x) -> double()

Converts angle x in degrees to radians.

rand() -> double()

This is an alias for random().

random() -> double()

Returns a pseudo-random value in the range 0.0 <= x < 1.0.

random(n) -> same as input

Returns a pseudo-random number between 0 and n (exclusive).

secure_rand() -> double()

This is an alias for secure_random().

secure_random() -> double()

Returns a cryptographically secure random value in the range 0.0 <= x < 1.0.

secure_random(lower, upper) -> same as input

Returns a cryptographically secure random value in the range lower <= x < upper, where lower < upper.

round(x) -> same as input

Returns x rounded to the nearest integer.

round(x, d) -> same as input

Returns x rounded to d decimal places.

sign(x) -> same as input

Returns the signum function of x, that is:

• 0 if the argument is 0,
• 1 if the argument is greater than 0,
• -1 if the argument is less than 0.

For double arguments, the function additionally returns:

• NaN if the argument is NaN,
• 1 if the argument is +Infinity,
• -1 if the argument is -Infinity.

sqrt(x) -> double()

Returns the square root of x.

to_base(x, radix) -> varchar()

Returns the base-radix representation of x.

truncate(x) -> double()

Returns x rounded to integer by dropping digits after decimal point.

truncate(x, n) -> double()

Returns x truncated to n decimal places. n can be negative to truncate n digits left of the decimal point.

Example: truncate(REAL '12.333', -1) -> result is 10.0 truncate(REAL '12.333', 0) -> result is 12.0 truncate(REAL '12.333', 1) -> result is 12.3

width_bucket(x, bound1, bound2, n) -> bigint()

Returns the bin number of x in an equi-width histogram with the specified bound1 and bound2 bounds and n number of buckets.

width_bucket(x, bins) -> bigint()

Returns the bin number of x according to the bins specified by the array bins. The bins parameter must be an array of doubles, should not contain null or non-finite elements, and is assumed to be in sorted ascending order.

Note: The function returns an error if it encounters a null or non-finite element in bins, but due to the binary search algorithm some such elements might go unnoticed and the function will return a result.

factorial(x) -> bigint()

Returns the factorial of x.

Probability Functions: cdf

beta_cdf(a, b, value) -> double()

Compute the Beta cdf with given a, b parameters: P(N < value; a, b). The a, b parameters must be positive real numbers and value must be a real value (all of type DOUBLE). The value must lie on the interval [0, 1].

binomial_cdf(numberOfTrials, successProbability, value) -> double()

Compute the Binomial cdf with given numberOfTrials and successProbability (for a single trial): P(N < value). The successProbability must be real value in [0, 1], numberOfTrials and value must be positive integers with numberOfTrials greater or equal to value.

cauchy_cdf(median, scale, value) -> double()

Compute the Cauchy cdf with given parameters median and scale (gamma): P(N; median, scale). The scale parameter must be a positive double. The value parameter must be a double on the interval [0, 1].

chi_squared_cdf(df, value) -> double()

Compute the Chi-square cdf with given df (degrees of freedom) parameter: P(N < value; df). The df parameter must be a positive real number, and value must be a non-negative real value (both of type DOUBLE).

f_cdf(df1, df2, value) -> double()

Compute the F cdf with given df1 (numerator degrees of freedom) and df2 (denominator degrees of freedom) parameters: P(N < value; df1, df2). The numerator and denominator df parameters must be positive real numbers. The value must be a non-negative real number.

gamma_cdf(shape, scale, value) -> double()

Compute the Gamma cdf with given shape and scale parameters: P(N < value; shape, scale). The shape and scale parameters must be positive real numbers. The value must be a non-negative real number.

laplace_cdf(mean, scale, value) -> double()

Compute the Laplace cdf with given mean and scale parameters: P(N < value; mean, scale). The mean and value must be real values and the scale parameter must be a positive value (all of type DOUBLE).

normal_cdf(mean, sd, value) -> double()

Compute the Normal cdf with given mean and standard deviation (sd): P(N < value; mean, sd). The mean and value must be real values and the standard deviation must be a real and positive value (all of type DOUBLE).

poisson_cdf(lambda, value) -> double()

Compute the Poisson cdf with given lambda (mean) parameter: P(N <= value; lambda). The lambda parameter must be a positive real number (of type DOUBLE) and value must be a non-negative integer.

weibull_cdf(a, b, value) -> double()

Compute the Weibull cdf with given parameters a, b: P(N <= value). The a and b parameters must be positive doubles and value must also be a double.

Probability Functions: inverse_cdf

inverse_beta_cdf(a, b, p) -> double()

Compute the inverse of the Beta cdf with given a, b parameters for the cumulative probability (p): P(N < n). The a, b parameters must be positive real values (all of type DOUBLE). The probability p must lie on the interval [0, 1].

inverse_binomial_cdf(numberOfTrials, successProbability, p) -> int()

Compute the inverse of the Binomial cdf with given numberOfTrials and successProbability (of a single trial) the cumulative probability (p): P(N <= n). The successProbability and p must be real values in [0, 1] and the numberOfTrials must be a positive integer.

inverse_cauchy_cdf(median, scale, p) -> double()

Compute the inverse of the Cauchy cdf with given parameters median and scale (gamma) for the probability p. The scale parameter must be a positive double. The probability p must be a double on the interval [0, 1].

inverse_chi_squared_cdf(df, p) -> double()

Compute the inverse of the Chi-square cdf with given df (degrees of freedom) parameter for the cumulative probability (p): P(N < n). The df parameter must be positive real values. The probability p must lie on the interval [0, 1].

inverse_gamma_cdf(shape, scale, p) -> double()

Compute the inverse of the Gamma cdf with given shape and scale parameters for the cumulative probability (p): P(N < n). The shape and scale parameters must be positive real values. The probability p must lie on the interval [0, 1].

inverse_f_cdf(df1, df2, p) -> double()

Compute the inverse of the F cdf with a given df1 (numerator degrees of freedom) and df2 (denominator degrees of freedom) parameters for the cumulative probability (p): P(N < n). The numerator and denominator df parameters must be positive real numbers. The probability p must lie on the interval [0, 1].

inverse_laplace_cdf(mean, scale, p) -> double()

Compute the inverse of the Laplace cdf with given mean and scale parameters for the cumulative probability (p): P(N < n). The mean must be a real value and the scale must be a positive real value (both of type DOUBLE). The probability p must lie on the interval [0, 1].

inverse_normal_cdf(mean, sd, p) -> double()

Compute the inverse of the Normal cdf with given mean and standard deviation (sd) for the cumulative probability (p): P(N < n). The mean must be a real value and the standard deviation must be a real and positive value (both of type DOUBLE). The probability p must lie on the interval (0, 1).

inverse_poisson_cdf(lambda, p) -> integer()

Compute the inverse of the Poisson cdf with given lambda (mean) parameter for the cumulative probability (p). It returns the value of n so that: P(N <= n; lambda) = p. The lambda parameter must be a positive real number (of type DOUBLE). The probability p must lie on the interval [0, 1).

inverse_weibull_cdf(a, b, p) -> double()

Compute the inverse of the Weibull cdf with given parameters a, b for the probability p. The a, b parameters must be positive double values. The probability p must be a double on the interval [0, 1].

Statistical Functions

wilson_interval_lower(successes, trials, z) -> double()

Returns the lower bound of the Wilson score interval of a Bernoulli trial process at a confidence specified by the z-score z.

wilson_interval_upper(successes, trials, z) -> double()

Returns the upper bound of the Wilson score interval of a Bernoulli trial process at a confidence specified by the z-score z.

Trigonometric Functions

All trigonometric function arguments are expressed in radians. See unit conversion functions degrees() and radians().

acos(x) -> double()

Returns the arc cosine of x.

asin(x) -> double()

Returns the arc sine of x.

atan(x) -> double()

Returns the arc tangent of x.

atan2(y, x) -> double()

Returns the arc tangent of y / x.

cos(x) -> double()

Returns the cosine of x.

cosh(x) -> double()

Returns the hyperbolic cosine of x.

sin(x) -> double()

Returns the sine of x.

tan(x) -> double()

Returns the tangent of x.

tanh(x) -> double()

Returns the hyperbolic tangent of x.

Floating Point Functions

infinity() -> double()

Returns the constant representing positive infinity.

is_finite(x) -> boolean()

Determine if x is finite.

is_infinite(x) -> boolean()

Determine if x is infinite.

is_nan(x) -> boolean()

Determine if x is not-a-number.

nan() -> double()

Returns the constant representing not-a-number.