Rockset

    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.

    SQL commandResult
    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 commandResult
    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 commandResult
    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 commandResult
    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 commandResult
    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 commandResult
    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 commandResult
    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 commandResult
    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 commandResult
    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 commandResult
    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 commandResult
    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 commandResult
    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 commandResult
    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 commandResult
    SELECT SQRT(4)2.0
    SELECT SQRT(1)1.0
    SELECT SQRT(null)null

    LN

    LN(x)

    Computes the natural logarithm of x.

    SQL commandResult
    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 commandResult
    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 commandResult
    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 commandResult
    SELECT ACOS(0.7)0.7953988301841436

    ACOSH

    ACOSH(x)

    Computes the inverse hyperbolic cosine of x.

    SQL commandResult
    SELECT ACOSH(90)5.192925985263684

    ASIN

    ASIN(x)

    Computes the arc sine of x.

    SQL commandResult
    SELECT ASIN(0.7)0.775397496610753

    ASINH

    ASINH(x)

    Computes the inverse hyperbolic sine of x.

    SQL commandResult
    SELECT ASINH(0.7)0.6526665660823557

    ATAN

    ATAN(x)

    Computes the arc tangent of x.

    SQL commandResult
    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 commandResult
    SELECT ATAN2(5, 3)1.0303768265243125

    ATANH

    ATANH(x)

    Computes the inverse hyperbolic tangent of x.

    SQL commandResult
    SELECT ATANH(0.7)0.8673005276940531

    COS

    COS(x)

    Computes the cosine of x.

    SQL commandResult
    SELECT COS(0.7)0.7648421872844885

    COSH

    COSH(x)

    Computes the hyperbolic cosine of x.

    SQL commandResult
    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 commandResult
    SELECT HYPOT(3, 4)5.0

    SIN

    SIN(x)

    Computes the sine of x.

    SQL commandResult
    SELECT SIN(0.7)0.644217687237691

    SINH

    SINH(x)

    Computes the hyperbolic sine of x.

    SQL commandResult
    SELECT SINH(0.7)0.7585837018395334

    TAN

    TAN(x)

    Computes the tangent of x.

    SQL commandResult
    SELECT TAN(0.7)0.8422883804630794

    TANH

    TANH(x)

    Computes the hyperbolic tangent of x.

    SQL commandResult
    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 commandResult
    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 commandResult
    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 commandResult
    SELECT BITWISE_XOR(21, 10)31

    10101 | 01010 = 11111