- Querying Your Data
- SQL Reference
- Mathematical Functions
Mathematical Functions
This page covers mathematical operators and functions available in Rockset.
All functions accept arguments of types int
and float
. The return type is float
, unless
otherwise specified.
Note: A
null
argument to any mathematical function will result in anull
return value
#Operators
Rockset supports the basic arithmetic operators shown below on any combination of int
and float
values.
Operator | Description |
---|---|
+ x | positive sign |
- x | negative sign |
x + y | addition |
x - y | subtraction |
x * y | multiplication |
x / y | division |
x % y | modulo (remainder) |
#Basic Functions
#ABS
ABS(x)
Returns absolute value of x
. Return type is the same as input.
SELECT
ABS(5)
5
SELECT
ABS(-5)
5
SELECT
ABS(0)
0
SELECT
ABS(null)
null
#FLOOR
FLOOR(x)
Returns the largest integral value that is not greater than x
.
SELECT
FLOOR(2.4)
2.0
SELECT
FLOOR(2.6)
2.0
SELECT
FLOOR(2)
2.0
SELECT
FLOOR(null)
null
#TRUNCATE
TRUNCATE(x)
Rounds x
toward zero, returning the nearest integral value that is not larger in magnitude than x
.
SELECT
TRUNCATE(2.4)
2.0
SELECT
TRUNCATE(2.6)
2.0
SELECT
TRUNCATE(-2.4)
-2.0
#TRUNC
TRUNC(x)
Alias of TRUNCATE
.
#CEIL
CEIL(x)
Returns the smallest integral value that is not less than x
.
SELECT
CEIL(2.4)
3.0
SELECT
CEIL(2.6)
3.0
SELECT
CEIL(3)
3.0
SELECT
CEIL(null)
null
#CEILING
CEILING(x)
Alias of CEIL
.
#ROUND
ROUND(x)
Returns the integral value that is nearest to x
, with halfway cases rounded away from zero.
SELECT
ROUND(2.4)
2.0
SELECT
ROUND(2.6)
3.0
SELECT
ROUND(3)
3.0
SELECT
ROUND(null)
null
#GREATEST
GREATEST(a, b, c, ...)
Returns the argument that is greater than or equal to all other arguments.
SELECT
GREATEST(1)
1
SELECT
GREATEST(1, 2, 3)
3
SELECT
GREATEST(1, null, 3)
null
#LEAST
LEAST(a, b, c, ...)
Returns the argument that is less than or equal to all other arguments.
SELECT
LEAST(1)
1
SELECT
LEAST(1, 2, 3)
1
SELECT
LEAST(1, null, 3)
null
#SIGN
SIGN(x)
Returns sign of x
as an integer: -1
if x
is negative, 0
if x
is zero, 1
if x
is positive.
SELECT
SIGN(5)
1
SELECT
SIGN(-5)
-1
SELECT
SIGN(0)
0
SELECT
SIGN(null)
null
#RAND
RAND()
Returns a pseudo-random value in the range [0.0, 1.0)
.
SELECT
RAND()
0.08367730533758584
SELECT
RAND()
0.20026947414188864
SELECT
RAND()
0.5600549036289146
#IS_NAN
IS_NAN(x)
Returns true if the input is a floating point Not-A-Number, for instance, due to dividing zero by zero or taking the square root of a negative number. Non-numeric inputs yield null.
SELECT
IS_NAN(CAST('NaN' as FLOAT))
true
SELECT
IS_NAN(1.0)
false
#FROM_BASE
FROM_BASE(string, base)
Returns the value of string
interpreted as a number in base
.
SELECT
FROM_BASE('101', 2)
5
SELECT
FROM_BASE('ab', 16)
171
SELECT
FROM_BASE('-21', 8)
-17
#Log and Exponential Functions
#EXP
EXP(x)
Computes e
to the power of x
.
SELECT
EXP(1)
2.718281828459045
SELECT
EXP(0)
1.0
SELECT
EXP(null)
null
#POW
POW(x, y)
Computes x
to the power of y
.
SELECT
POW(2, 5)
32.0
SELECT
POW(5, 0)
1.0
SELECT
POW(5, null)
null
#POWER
POWER(x, y)
Alias of POW
.
#SQRT
SQRT(x)
Computes the square root of x
.
SELECT
SQRT(4)
2.0
SELECT
SQRT(1)
1.0
SELECT
SQRT(null)
null
#LN
LN(x)
Computes the natural logarithm of x
.
SELECT
LN(10)
2.302585092994046
SELECT
LN(EXP(5))
5.0
SELECT
LN(null)
null
SELECT
LN(0)
Error: 'division by zero'
#LOG
LOG(x)
Alias of LN
.
#LOG10
LOG10(x)
Computes the base-10 logarithm of x
.
SELECT
LOG10(100)
2.0
SELECT
LOG10(1)
0.0
SELECT
LOG10(null)
null
SELECT
LOG10(0)
Error: 'division by zero'
#LOG2
LOG2(x)
Computes the base-2 logarithm of x
.
SELECT
LOG2(32)
5.0
SELECT
LOG2(1)
0.0
SELECT
LOG2(null)
null
SELECT
LOG2(0)
Error: 'division by zero'
#Trigonometric Functions
#ACOS
ACOS(x)
Computes the arc cosine of x
.
SELECT
ACOS(0.7)
0.7953988301841436
#ACOSH
ACOSH(x)
Computes the inverse hyperbolic cosine of x
.
SELECT
ACOSH(90)
5.192925985263684
#ASIN
ASIN(x)
Computes the arc sine of x
.
SELECT
ASIN(0.7)
0.775397496610753
#ASINH
ASINH(x)
Computes the inverse hyperbolic sine of x
.
SELECT
ASINH(0.7)
0.6526665660823557
#ATAN
ATAN(x)
Computes the arc tangent of x
.
SELECT
ATAN(0.7)
0.6107259643892086
#ATAN2
ATAN2(y, x)
Computes the arc tangent of y / x
, but with proper sign for quadrant correction. That is, correctly computes the angle θ
when converting from the Cartesian coordinates (x, y)
to the polar coordinates (r, θ)
.
SELECT
ATAN2(5, 3)
1.0303768265243125
#ATANH
ATANH(x)
Computes the inverse hyperbolic tangent of x
.
SELECT
ATANH(0.7)
0.8673005276940531
#COS
COS(x)
Computes the cosine of x
.
SELECT
COS(0.7)
0.7648421872844885
#COSH
COSH(x)
Computes the hyperbolic cosine of x
.
SELECT
COSH(0.7)
1.255169005630943
#HYPOT
HYPOT(x, y)
Computes SQRT(x*x + y*y)
, that is, the length of the hypothenuse of a right-angled triangle with sides of lengths x
and y
. This is also the distance between the point at coordinates (x, y)
and origin.
SELECT
HYPOT(3, 4)
5.0
#SIN
SIN(x)
Computes the sine of x
.
SELECT
SIN(0.7)
0.644217687237691
#SINH
SINH(x)
Computes the hyperbolic sine of x
.
SELECT
SINH(0.7)
0.7585837018395334
#TAN
TAN(x)
Computes the tangent of x
.
SELECT
TAN(0.7)
0.8422883804630794
#TANH
TANH(x)
Computes the hyperbolic tangent of x
.
SELECT
TANH(0.7)
0.6043677771171636
#Bitwise Functions
#BIT_COUNT
BIT_COUNT(x, bits)
Count the number of bits set in x
(treated as bits
-bit signed integer) in 2’s complement representation.
-- 15 = '1111' in binary
SELECT
BIT_COUNT(15, 4)
4
#BITWISE_NOT
BITWISE_NOT(x)
Returns the bitwise NOT of x
in 2’s complement representation.
SELECT
BITWISE_NOT(4)
-5
#BITWISE_XOR
BITWISE_XOR(x, y)
Returns the bitwise XOR of x
and y
in 2’s complement representation.
-- 10101 | 01010 = 11111
SELECT
BITWISE_XOR(21, 10)
31