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.

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.

xxxxxxxxxx

SELECT

    ABS(5)

xxxxxxxxxx

5

xxxxxxxxxx

SELECT

    ABS(-5)

xxxxxxxxxx

5

xxxxxxxxxx

SELECT

    ABS(0)

xxxxxxxxxx

0

xxxxxxxxxx

SELECT

    ABS(null)

xxxxxxxxxx

null

#FLOOR

FLOOR(x)

Returns the largest integral value that is not greater than x.

xxxxxxxxxx

SELECT

    FLOOR(2.4)

xxxxxxxxxx

2.0

xxxxxxxxxx

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