Math functions

Reference for math functions in HotSQL. Names, signatures, and examples match the engine exactly.

abs

Returns the absolute value of a number.

abs(numeric_expression)

Arguments

• numeric_expression: Numeric expression.

> SELECT abs(-5);
+----------+
| abs(-5)  |
+----------+
| 5        |
+----------+

ceil

Returns the nearest integer greater than or equal to a number.

ceil(numeric_expression)

Arguments

• numeric_expression: Numeric expression.

> SELECT ceil(3.14);
+------------+
| ceil(3.14) |
+------------+
| 4.0        |
+------------+

cot

Returns the cotangent of a number.

cot(numeric_expression)

Arguments

• numeric_expression: Numeric expression.

> SELECT cot(1);
+---------+
| cot(1)  |
+---------+
| 0.64209 |
+---------+

factorial

Factorial of a non-negative integer. Errors if the argument is negative or the result overflows.

factorial(numeric_expression)

Arguments

• numeric_expression: Numeric expression.

> SELECT factorial(5);
+---------------+
| factorial(5)  |
+---------------+
| 120           |
+---------------+

floor

Returns the nearest integer less than or equal to a number.

floor(numeric_expression)

Arguments

• numeric_expression: Numeric expression.

> SELECT floor(3.14);
+-------------+
| floor(3.14) |
+-------------+
| 3.0         |
+-------------+

gcd

Returns the greatest common divisor of expression_x and expression_y. Returns 0 if both inputs are zero.

gcd(expression_x, expression_y)

Arguments

• expression_x: First numeric expression.
• expression_y: Second numeric expression.

> SELECT gcd(48, 18);
+------------+
| gcd(48,18) |
+------------+
| 6          |
+------------+

isnan

Returns true if a given number is +NaN or -NaN otherwise returns false.

isnan(numeric_expression)

Arguments

• numeric_expression: Numeric expression.

> SELECT isnan(1);
+----------+
| isnan(1) |
+----------+
| false    |
+----------+

iszero

Returns true if a given number is +0.0 or -0.0 otherwise returns false.

iszero(numeric_expression)

Arguments

• numeric_expression: Numeric expression.

> SELECT iszero(0);
+------------+
| iszero(0)  |
+------------+
| true       |
+------------+

lcm

Returns the least common multiple of expression_x and expression_y. Returns 0 if either input is zero.

lcm(expression_x, expression_y)

Arguments

• expression_x: First numeric expression.
• expression_y: Second numeric expression.

> SELECT lcm(4, 5);
+----------+
| lcm(4,5) |
+----------+
| 20       |
+----------+

log

Returns the base-x logarithm of a number. Can either provide a specified base, or if omitted then takes the base-10 of a number.

log(base, numeric_expression)
log(numeric_expression)

Arguments

• base: Base numeric expression.
• numeric_expression: Numeric expression.

> SELECT log(10);
+---------+
| log(10) |
+---------+
| 1.0     |
+---------+

nanvl

Returns the first argument if it's not NaN. Returns the second argument otherwise.

nanvl(expression_x, expression_y)

Arguments

• expression_x: Numeric expression to return if it's not NaN. Can be a constant, column, or function, and any combination of arithmetic operators.
• expression_y: Numeric expression to return if the first expression is NaN. Can be a constant, column, or function, and any combination of arithmetic operators.

> SELECT nanvl(0, 5);
+------------+
| nanvl(0,5) |
+------------+
| 0          |
+------------+

pi

Returns an approximate value of π.

pi()

power

Returns a base expression raised to the power of an exponent.

power(base, exponent)

Arguments

• base: Numeric expression.
• exponent: Exponent numeric expression.

> SELECT power(2, 3);
+-------------+
| power(2,3)  |
+-------------+
| 8           |
+-------------+

random

Returns a random float value in the range [0, 1). The random seed is unique to each row.

random()

> SELECT random();
+------------------+
| random()         |
+------------------+
| 0.7389238902938  |
+------------------+

round

Rounds a number to the nearest integer.

round(numeric_expression[, decimal_places])

Arguments

• numeric_expression: Numeric expression.
• decimal_places: Optional. The number of decimal places to round to. Defaults to 0.

> SELECT round(3.14159);
+--------------+
| round(3.14159)|
+--------------+
| 3.0          |
+--------------+

signum

Returns the sign of a number. Negative numbers return -1. Zero and positive numbers return 1.

signum(numeric_expression)

Arguments

• numeric_expression: Numeric expression.

> SELECT signum(-42);
+-------------+
| signum(-42) |
+-------------+
| -1          |
+-------------+

trunc

Truncates a number to a whole number or truncated to the specified decimal places.

trunc(numeric_expression[, decimal_places])

Arguments

• numeric_expression: Numeric expression.
• decimal_places: Optional. The number of decimal places to truncate to. Defaults to 0 (truncate to a whole number). If decimal_places is a positive integer, truncates digits to the right of the decimal point. If decimal_places is a negative integer, replaces digits to the left of the decimal point with 0.

  > SELECT trunc(42.738);
  +----------------+
  | trunc(42.738)  |
  +----------------+
  | 42             |
  +----------------+