Contents

Mathematical functions

Mathematical functions can be applied on values in a numerical column.

ABS

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE

Obtain the absolute value for each row of a numeric column.

Example

SELECT ABS(<col_1>)
FROM <table_name>

CEIL

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE

Obtain the value rounded up to the nearest integer.

See also

FLOOR, ROUND

Example

SELECT CEIL(<col_1>)
FROM <table_name>

FLOOR

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE

Obtain the value rounded down to the nearest integer.

See also

CEIL, ROUND

Example

SELECT FLOOR(<col_1>)
FROM <table_name>

ROUND

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE

Obtain the value rounded to the nearest integer. Allows specifying the precision N that indicates the number of decimal places to use for rounding.

See also

CEIL, FLOOR

Examples

Round to the nearest integer.

SELECT ROUND(<col_1>)
FROM <table_name>

Round to the nearest hundredth.

SELECT ROUND(<col_1>, 2)
FROM <table_name>

GREATEST

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE

Obtain the greatest value from a list of columns or literals.

See also

LEAST

Example

Compare values between columns.

SELECT GREATEST(<col_1>, <col_2>)
FROM <table_name>

An equivalent notation

SELECT CASE
    WHEN <col_1> >= <col_2> THEN <col_1>
    ELSE <col_2>
END
FROM <table_name>

LEAST

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE

Obtain the smallest value from a list of columns or literals.

See also

GREATEST

Example

Compare values between columns.

SELECT LEAST(<col_1>, <col_2>)
FROM <table_name>

An equivalent notation

SELECT CASE
    WHEN <col_1> <= <col_2> THEN <col_1>
    ELSE <col_2>
END
FROM <table_name>

LN

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE

Obtain the value of a natural logarithm for each row in a numerical column.

Warning

Throws an error for values smaller than 0.

See also

LOG10

Example

SELECT LN(<col_1>)
FROM <table_name>

LOG10

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE

Obtain the value of a logarithm with base 10 for each row in a numerical column.

Warning

Returns NULL for +inf and -inf. Throws an error for values smaller than 0.

See also

LN

Example

SELECT LOG10(<col_1>)
FROM <table_name>

MOD

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE

Obtain the remainder of the division between two columns or literals: the first parameter to the MOD function is the divident and the second one is the divider.

Warning

Throws an error if the divider equals 0.

Examples

The divident and the divider are both columns.

SELECT MOD(<col_1>, <col_2>)
FROM <table_name>

The divident is a column and the divider is a literal.

SELECT MOD(<col_1>, <literal>)
FROM <table_name>

POWER

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE

Obtain the result of raising value to a specified power: the first parameter to the POWER function is the base and the second one is the exponent.

See also

SQRT

Examples

The base and the exponent are both columns.

SELECT POWER(<col_1>, <col_2>)
FROM <table_name>

The base is a column and the exponent is a literal.

SELECT POWER(<col_1>, <literal>)
FROM <table_name>

SQRT

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE

Obtain the square root of value.

See also

POWER

Examples

Find the square root of each value in a column.

SELECT SQRT(<col_1>)
FROM <table_name>

Equivalently.

SELECT POWER(<col_1>, 0.5)
FROM <table_name>

RAND

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE

Obtain a random number in the rage \([0,1)\) (includes 0 but excludes 1). This function takes no parameters.

Example

SELECT RAND()
FROM <table_name>

COS

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE

Obtain the cosine value for each row of a numeric column. The input value is interpreted as radians, not degrees.

See also

ACOS, ASIN, ATAN, SIN, TAN

Example

SELECT COS(<col_1>)
FROM <table_name>

ACOS

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE

Obtain the degree value (in radians, range \([0,\pi]\)) for each row of a numeric column where each value represents a value of a cosine function.

Warning

Returns NULL for +inf and -inf, and an actual NULL value. Throws an error for values outside of the \([-1,1]\) range.

See also

ASIN, ATAN, COS, SIN, TAN

Example

SELECT ACOS(<col_1>)
FROM <table_name>

SIN

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE

Obtain the sine value for each row of a numeric column. The input value is interpreted as radians, not degrees.

See also

ACOS, ASIN, ATAN, COS, TAN

Example

SELECT SIN(<col_1>)
FROM <table_name>

ASIN

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE

Obtain the degree value (in radians, range \([0,\pi]\)) for each row of a numeric column where each value represents a value of a sine function.

Warning

Returns NULL for +inf and -inf, and an actual NULL value. Throws an error for values outside of the \([-1,1]\) range.

See also

ACOS, ATAN, COS, SIN, TAN

Example

SELECT ASIN(<col_1>)
FROM <table_name>

TAN

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE

Obtain the tangent value for each row of a numeric column. The input value is interpreted as radians, not degrees.

See also

ACOS, ASIN, ATAN, COS, SIN

Example

SELECT TAN(<col_1>)
FROM <table_name>

ATAN

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE

Obtain the degree value (in radians, range \([-\pi/2,\pi/2]\)) for each row of a numeric column where each value represents a value of a sine function.

Warning

Returns NULL for NULL value.

See also

ACOS, ASIN, COS, SIN, TAN

Example

SELECT ATAN(<col_1>)
FROM <table_name>
Conditional functions String functions