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.
SQL command | Result |
---|---|
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
.
SQL command | Result |
---|---|
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
.
SQL command | Result |
---|---|
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
.
SQL command | Result |
---|---|
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.
SQL command | Result |
---|---|
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.
SQL command | Result |
---|---|
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.
SQL command | Result |
---|---|
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.
SQL command | Result |
---|---|
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)
.
SQL command | Result |
---|---|
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.
SQL command | Result |
---|---|
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
.
SQL command | Result |
---|---|
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
.
SQL command | Result |
---|---|
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
.
SQL command | Result |
---|---|
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
.
SQL command | Result |
---|---|
SELECT SQRT(4) | 2.0 |
SELECT SQRT(1) | 1.0 |
SELECT SQRT(null) | null |
LN
LN(x)
Computes the natural logarithm of x
.
SQL command | Result |
---|---|
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
.
SQL command | Result |
---|---|
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
.
SQL command | Result |
---|---|
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
.
SQL command | Result |
---|---|
SELECT ACOS(0.7) | 0.7953988301841436 |
ACOSH
ACOSH(x)
Computes the inverse hyperbolic cosine of x
.
SQL command | Result |
---|---|
SELECT ACOSH(90) | 5.192925985263684 |
ASIN
ASIN(x)
Computes the arc sine of x
.
SQL command | Result |
---|---|
SELECT ASIN(0.7) | 0.775397496610753 |
ASINH
ASINH(x)
Computes the inverse hyperbolic sine of x
.
SQL command | Result |
---|---|
SELECT ASINH(0.7) | 0.6526665660823557 |
ATAN
ATAN(x)
Computes the arc tangent of x
.
SQL command | Result |
---|---|
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, θ)
.
SQL command | Result |
---|---|
SELECT ATAN2(5, 3) | 1.0303768265243125 |
ATANH
ATANH(x)
Computes the inverse hyperbolic tangent of x
.
SQL command | Result |
---|---|
SELECT ATANH(0.7) | 0.8673005276940531 |
COS
COS(x)
Computes the cosine of x
.
SQL command | Result |
---|---|
SELECT COS(0.7) | 0.7648421872844885 |
COSH
COSH(x)
Computes the hyperbolic cosine of x
.
SQL command | Result |
---|---|
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.
SQL command | Result |
---|---|
SELECT HYPOT(3, 4) | 5.0 |
SIN
SIN(x)
Computes the sine of x
.
SQL command | Result |
---|---|
SELECT SIN(0.7) | 0.644217687237691 |
SINH
SINH(x)
Computes the hyperbolic sine of x
.
SQL command | Result |
---|---|
SELECT SINH(0.7) | 0.7585837018395334 |
TAN
TAN(x)
Computes the tangent of x
.
SQL command | Result |
---|---|
SELECT TAN(0.7) | 0.8422883804630794 |
TANH
TANH(x)
Computes the hyperbolic tangent of x
.
SQL command | Result |
---|---|
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.
SQL command | Result |
---|---|
SELECT BIT_COUNT(15, 4) | 4 |
- 15 = '1111' in binary *
BITWISE_AND
BITWISE_AND(x, y)
Returns the bitwise AND of x
and y
in 2’s complement representation.
-- 10101 & 111 = 101
SELECT
BITWISE_AND(37, 7)
5
BITWISE_OR
BITWISE_OR(x, y)
Returns the bitwise OR of x
and y
in 2’s complement representation.
-- 10001 | 111 = 10111
SELECT
BITWISE_OR(33, 7)
39
BITWISE_NOT
BITWISE_NOT(x)
Returns the bitwise NOT of x
in 2’s complement representation.
SQL command | Result |
---|---|
SELECT BITWISE_NOT(4) | -5 |
BITWISE_XOR
BITWISE_XOR(x, y)
Returns the bitwise XOR of x
and y
in 2’s complement representation.
SQL command | Result |
---|---|
SELECT BITWISE_XOR(21, 10) | 31 |
10101 | 01010 = 11111