• Querying Your Data
  • 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.

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
Join us on Slack!
Building on Rockset? Come chat with us!