Math Functions
abs
Returns the absolute value of x.
Examples:
select abs(-1) as value; -- value '1.0'
Supported Signatures
function abs(tinyint) returns tinyint
function abs(smallint) returns smallint
function abs(bigint) returns bigint
function abs(double) returns double
function abs(decimal(p, s)) returns decimal(p, s)
function abs(real) returns real
acos
Returns the arc cosine of x.
Examples:
select acos(0.5) as value; -- value '1.0471975511965976'
Supported Signatures
function acos(double) returns double
asin
Returns the arc sine of x.
Examples:
select asin(1) as value; -- value '1.5707963267948966'
Supported Signatures
function asin(double) returns double
atan
Returns the arc tangent of x.
Examples:
select atan(0) as value; -- value '0.0'
Supported Signatures
function atan(double) returns double
atan2
Returns the arc tangent of y / x.
Examples:
select atan2(1, 0.5) as value; -- value '1.1071487177940904'
Supported Signatures
function atan2(double, double) returns double
beta_cdf
Compute the Beta cdf with given a, b parameters. P(N < v; a, b). The a, b parameters must be positive real numbers and value v must be a real value. The value v must lie on the interval [0, 1].
Supported Signatures
function beta_cdf(double, double, double) returns double
cbrt
Returns the cube root of x.
Examples:
SELECT cbrt(328509) as value; -- value '69.0'
Supported Signatures
function cbrt(double) returns double
ceil
This is an alias for ceiling().
Examples:
SELECT ceil(1.3) AS value; -- value '2.0'
Supported Signatures
function ceil(bigint) returns bigint
function ceil(decimal(p, s)) returns decimal(rp, 0)
function ceil(double) returns double
function ceil(integer) returns integer
function ceil(real) returns real
function ceil(smallint) returns smallint
function ceil(tinyint) returns tinyint
ceiling
Returns x rounded up to the nearest integer.
Supported Signatures
function ceiling(bigint) returns bigint
function ceiling(decimal(p, s)) returns decimal(rp, 0)
function ceiling(double) returns double
function ceiling(integer) returns integer
function ceiling(real) returns real
function ceiling(smallint) returns smallint
function ceiling(tinyint) returns tinyint
cos
Returns the cosine of x.
Examples:
select cos(1.0) as value; -- value '0.5403023058681398'
Supported Signatures
function cos(double) returns double
cosh
Returns the hyperbolic cosine of the given value.
Examples:
SELECT cosh(0) AS value; -- value '1.0'
Supported Signatures
function cosh(double) returns double
cosine_similarity
Returns the cosine similarity between the sparse vectors x and y.
Supported Signatures
function cosine_similarity(map<varchar, double>, map<varchar, double>) returns double
degrees
Converts angle x in radians to degrees.
Examples:
SELECT degrees(3.14159) AS value; -- value '179.9998547447946'
Supported Signatures
function degrees(double) returns double
e
Returns the constant Euler’s number.
Supported Signatures
function e() returns double
exp
Returns Euler’s number raised to the power of x.
Examples:
select exp(1.0) as value; -- value '2.718281828459045'
Supported Signatures
function exp(double) returns double
floor
Returns x rounded down to the nearest integer.
Supported Signatures
function floor(bigint) returns bigint
function floor(decimal(p, s)) returns decimal(rp, 0)
function floor(double) returns double
function floor(integer) returns integer
function floor(real) returns real
function floor(smallint) returns smallint
function floor(tinyint) returns tinyint
from_base
Returns the value of string interpreted as a base-radix number.
Supported Signatures
function from_base(varchar, bigint) returns bigint
infinity
Returns the constant representing positive infinity.
Supported Signatures
function infinity() returns double
inverse_beta_cdf
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. The probability p must lie on the interval [0, 1].
Supported Signatures
function inverse_beta_cdf(double, double, double) returns double
inverse_normal_cdf
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. The probability p must lie on the interval (0, 1).
Supported Signatures
function inverse_normal_cdf(double, double, double) returns double
is_finite
Determine if x is finite.
Supported Signatures
function is_finite(double) returns boolean
is_infinite
Determine if x is infinite.
Supported Signatures
function is_infinite(double) returns boolean
is_nan
Determine if x is not-a-number.
Examples:
select is_nan(1.1) as value; -- value 'false'
Supported Signatures
function is_nan(double) returns boolean
function is_nan(real) returns boolean
ln
Returns the natural logarithm of x.
Examples:
select ln(2.1) as value; -- value '0.7419373447293773'
Supported Signatures
function ln(double) returns double
log
Returns the base b logarithm of x.
Examples:
SELECT log(2.19, 1) AS value; -- value '0.0'
Supported Signatures
function log(double, double) returns double
log10
Returns the base 10 logarithm of x.
Examples:
select log10(10) as value; -- value '1.0'
Supported Signatures
function log10(double) returns double
log2
Returns the base 2 logarithm of x.
Examples:
select log2(2) as value; -- value '1.0'
Supported Signatures
function log2(double) returns double
mod
Returns the modulus (remainder) of n divided by m.
Supported Signatures
function mod(bigint, bigint) returns bigint
function mod(decimal(a_precision, a_scale), decimal(b_precision, b_scale)) returns decimal(r_precision, r_scale)
function mod(double, double) returns double
function mod(integer, integer) returns integer
function mod(real, real) returns real
function mod(smallint, smallint) returns smallint
function mod(tinyint, tinyint) returns tinyint
nan
Returns the constant representing not-a-number.
Supported Signatures
function nan() returns double
normal_cdf
Compute the Normal cdf with given mean and standard deviation (sd). P(N < v; mean, sd). The mean and value v must be real values and the standard deviation must be a real and positive value.
Supported Signatures
function normal_cdf(double, double, double) returns double
pi
Returns the constant Pi.
Examples:
SELECT PI() -- value '3.141592653589793'
Supported Signatures
function pi() returns double
pow
This is an alias for power().
Supported Signatures
function pow(double, double) returns double
power
Returns x raised to the power of p.
Examples:
select power(2, 8) as value; -- value '256'
Supported Signatures
function power(double, double) returns double
radians
Converts angle x in degrees to radians.
Examples:
SELECT radians(360) AS value; -- value '6.283185307179586'
Supported Signatures
function radians(double) returns double
rand
This is an alias for random().
Examples:
SELECT rand() AS value; -- value '0.20138668902074985'
Supported Signatures
function rand(bigint) returns bigint
function rand(bigint, bigint) returns bigint
function rand() returns double
function rand(integer) returns integer
function rand(integer, integer) returns integer
function rand(smallint) returns smallint
function rand(smallint, smallint) returns smallint
function rand(tinyint) returns tinyint
function rand(tinyint, tinyint) returns tinyint
Note: rand() is volatile, i.e. might return a different value for the same input.
random
Returns a pseudo-random value in the range 0.0 <= x < 1.0.
Supported Signatures
function random(bigint) returns bigint
function random(bigint, bigint) returns bigint
function random() returns double
function random(integer) returns integer
function random(integer, integer) returns integer
function random(smallint) returns smallint
function random(smallint, smallint) returns smallint
function random(tinyint) returns tinyint
function random(tinyint, tinyint) returns tinyint
Note: random() is volatile, i.e. might return a different value for the same input.
round
Returns x rounded to the nearest integer.
Examples:
select round(2.1) as value; -- value '2.0'
Supported Signatures
function round(double) returns double
function round(double, bigint) returns double
function round(real) returns real
function round(real, bigint) returns real
function round(integer) returns integer
function round(integer, integer) returns integer
function round(decimal(p, s)) returns decimal(rp, rs)
function round(decimal(p, s), bigint) returns decimal(rp, s)
function round(bigint) returns bigint
function round(bigint, bigint) returns bigint
function round(smallint) returns smallint
function round(smallint, bigint) returns smallint
function round(tinyint) returns tinyint
function round(tinyint, bigint) returns tinyint
sign
Returns the signum function of x, that is.
Supported Signatures
function sign(bigint) returns bigint
function sign(decimal(p, s)) returns decimal(1, 0)
function sign(double) returns double
function sign(integer) returns integer
function sign(real) returns real
function sign(smallint) returns smallint
function sign(tinyint) returns tinyint
sin
Returns the sine of x.
Examples:
select sin(2.1) as value; -- value '-0.8632093666488737'
Supported Signatures
function sin(double) returns double
sinh
Returns the hyperbolic sine of x.
Examples:
SELECT sinh(1) AS value; -- value '1.1752011936438014'
Supported Signatures
function sinh(double) returns double
sqrt
Returns the square root of x.
Examples:
select sqrt(16) as value; -- value '4.0'
Supported Signatures
function sqrt(double) returns double
tan
Returns the tangent of x.
Examples:
select tan(0.5) as value; -- value '0.5463024898437905'
Supported Signatures
function tan(double) returns double
tanh
Returns the hyperbolic tangent of x.
Examples:
SELECT tanh(1.0) AS value; -- value '0.7615941559557649'
Supported Signatures
function tanh(double) returns double
to_base
Returns the base-radix representation of x.
Supported Signatures
function to_base(bigint, bigint) returns varchar
truncate
Returns x rounded to integer by dropping digits after decimal point.
Examples:
SELECT truncate(1.23456, 2) as value; -- value '1.23'
Supported Signatures
function truncate(decimal(p, s), bigint) returns decimal(p, s)
function truncate(decimal(p, s)) returns decimal(rp, 0)
function truncate(double) returns double
function truncate(real) returns real
width_bucket
Returns the bin number of x in an equi-width histogram with the specified bound1 and bound2 bounds and n number of buckets.
Supported Signatures
function width_bucket(double, array<double>) returns bigint
function width_bucket(double, double, double, bigint) returns bigint
wilson_interval_lower
Returns the lower bound of the Wilson score interval of a Bernoulli trial process at a confidence specified by the z-score z.
Supported Signatures
function wilson_interval_lower(bigint, bigint, double) returns double
wilson_interval_upper
Returns the upper bound of the Wilson score interval of a Bernoulli trial process at a confidence specified by the z-score z.
Supported Signatures
function wilson_interval_upper(bigint, bigint, double) returns double