SQL Reference > Mathematical Functions

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.

FLOOR

FLOOR(x)

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

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!