Mathematical Functions

abs(x) [same as x]

Returns the absolute value of x.

add(x, y) [same as x]

Returns the result of adding x to y. The types of x and y must be the same. For integral types, overflow results in an error. Corresponds to sparks’s operator +.

ceil(x) [same as x]

Returns x rounded up to the nearest integer. Supported types are: BIGINT and DOUBLE.

divide(x, y) double

Returns the results of dividing x by y. Performs floating point division. Corresponds to Spark’s operator /.

SELECT 3 / 2; -- 1.5
SELECT 2L / 2L; -- 1.0
SELECT 3 / 0; -- NULL
exp(x) double

Returns Euler’s number raised to the power of x.

floor(x) [same as x]

Returns x rounded down to the nearest integer. Supported types are: BIGINT and DOUBLE.

multiply(x, y) [same as x]

Returns the result of multiplying x by y. The types of x and y must be the same. For integral types, overflow results in an error. Corresponds to Spark’s operator *.

not(x) boolean

Logical not.

SELECT not true; -- false
SELECT not false; -- true
SELECT not NULL; -- NULL
pmod(n, m) [same as n]

Returns the positive remainder of n divided by m.

power(x, p) double

Returns x raised to the power of p.

rand() double

Returns a random value with independent and identically distributed uniformly distributed values in [0, 1).

SELECT rand(); -- 0.9629742951434543
SELECT rand(0); -- 0.7604953758285915
SELECT rand(null); -- 0.7604953758285915
remainder(n, m) [same as n]

Returns the modulus (remainder) of n divided by m. Corresponds to Spark’s operator %.

round(x, d) [same as x]

Returns x rounded to d decimal places using HALF_UP rounding mode. In HALF_UP rounding, the digit 5 is rounded up.

subtract(x, y) [same as x]

Returns the result of subtracting y from x. The types of x and y must be the same. For integral types, overflow results in an error. Corresponds to Spark’s operator -.

unaryminus(x) [same as x]

Returns the negative of x. Corresponds to Spark’s operator -.