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.

#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_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.

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