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 a null return value

#Operators

Rockset supports the basic arithmetic operators shown below on any combination of int and float values.

OperatorDescription
+ xpositive sign
- xnegative sign
x + yaddition
x - ysubtraction
x * ymultiplication
x / ydivision
x % ymodulo (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
Join us on Slack!
Building on Rockset? Come chat with us!