Mathematical Functions and Operators
Mathematical Operators
Operator | Description |
---|---|
| Addition |
| Subtraction |
| Multiplication |
| Division (integer division performs truncation) |
| Modulus (remainder) |
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
:
SELECT cosine_similarity(MAP(ARRAY['a'], ARRAY[1.0]), MAP(ARRAY['a'], ARRAY[2.0])); -- 1.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 and is assumed to be in sorted ascending order.
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.