Skip to navigation
SQL Reference

Math Functions and Operators

View as Markdown

Math Operators

Ocient® supports addition, subtraction, multiplication, and division between any two numeric types. Division involving two integral types always results in an integral type.

Decimal Precision

These rules determine the precision p and scale s for the result of a specified expression that involves decimals d1 and d2, with precisions p1 and p2, and scales s1 and s2:

OperationPrecisionScale
addition, subtractionmax(s1, s2) + max(p1-s1, p2-s2) + 1max(s1, s2)
multiplicationp1 + p2s1 + s2
divisionSee note.max(6, s1 + p2 + 1)

For division, the resulting precision is the minimum necessary to hold the whole number portion of the result. If that portion does not exist because the absolute value of the result is less than one, the resulting precision is one greater than the scale. For other operations, the result might still have a lower precision (but the same scale) than specified by these rules when the result would otherwise have a leading insignificant zero.

If the resulting precision exceeds 31, the database decreases both precision and scale until the precision is 31, except in division, where the minimal scale is always six. If the resulting precision still exceeds 31, the database truncates it to 31. The database returns an overflow error when the result exceeds the available integral portion of the result. When the database decreases the resulting scale, it truncates the excess fractional digits in the result.

Math Operators for Time Intervals

Ocient supports math operators for time interval values (hour, minute, second, etc.), including when used with time-series data types such as TIME, TIMESTAMP, and DATE. You can use addition, subtraction, multiplication, and division on time intervals like any other numeric value.

Division operations can produce inaccurate results because the system truncates the precision of the result to a whole number. For details, see Decimal Precision.

Examples

Add Interval Value to Timestamp

This example uses the MINUTES cast function to add six minutes to the TIMESTAMP value returned from the TIMESTAMP cast function of the '2023-04-05 12:15:05' string.

SELECT TIMESTAMP('2023-04-05 12:15:05') + (MINUTES(6));

Output: 2023-04-05 12:21:05.000

Add Fractional Interval Value to Timestamp

This example shows how division can produce an inaccurate result. The division operation in the example must produce a whole number. In other words, the system calculates two minutes as 5 / 2 = 2 instead of two and a half minutes as 5 / 2 = 2.5.

SELECT TIMESTAMP('2023-04-05 12:15:05') + (MINUTES(5) / 2);

Output: 2023-04-05 12:17:05.000

Math Functions

These scalar math functions operate on a single value and return a single value. They can appear anywhere where an ordinary value or condition can appear.

For a list of common math symbols supported in Ocient, see Math Operators.

ABS

Returns the absolute value of a specified floating-point number.

Syntax

ABS(x)

Example

SELECT ABS(-250.5);

Output: 250.5

ACOS

Returns the inverse cosine of a specified floating-point number.

Syntax

ACOS(x)

Example

SELECT ACOS(0.25);

Output: 1.318116071652818

ACOSH

Returns the hyperbolic arc-cosine of a specified floating-point number.

Syntax

ACOSH(x)

Example

SELECT ACOSH(2);

Output: 1.3169578969248166

ASIN

Returns the inverse sine of a specified floating-point number.

Syntax

ASIN(x)

Example

SELECT ASIN(0.25);

Output: 0.25268025514207865

ASINH

Returns the hyperbolic arc-sine of a specified floating-point number.

Syntax

ASINH(x)

Example

SELECT ASINH(2);

Output: 1.4436354751788103

ATAN

Returns the inverse tangent of a specified floating-point number.

Syntax

ATAN(x)

Example

SELECT ATAN(0.25);

Output: 0.24497866312686414

ATAN2

Returns the inverse tangent of two numeric, floating-point values.

Syntax

ATAN2(x, y)

Example

SELECT ATAN2(0.50, 1);

Output: 0.4636476090008061

ATANH

Returns the hyperbolic arc-tangent of a specified floating-point number.

Syntax

ATANH(x)

Example

SELECT ATANH(0.25);

Output: 0.25541281188299536

BICDF

The cumulative distribution function of the standard bivariate normal distribution, which returns the probability that a random sample of (A, B) is less than x and less than y (A <= x and B <= y). The correlation is set to 0 in the case where you specify two arguments only.

You can specify an optional rho coefficient to calculate the correlation.

For details, see the bivariate cumulative distribution function.

Syntax

BICDF(x, y [, rho])

Example

SELECT BICDF(1,2);

Output: 0.8222040420815764

BIPDF

The probability density function of the standard bivariate normal distribution returns the relative likelihood that a random sample would equal the specified values. The correlation is set to 0 in the case where you specify two arguments only.

You can specify an optional rho coefficient to calculate the correlation.

For details, see the bivariate probability density function.

Syntax

BIPDF(x, y, [, rho])

Example

SELECT BIPDF(1,2);

Output: 0.013064233284684893

BITFUNC

Performs a variety of bit operations.

The char_func can be any of these string literals: 'AND', 'OR', or 'XOR'. This argument determines the bitwise operation that is performed on arguments x and y.

Syntax

BITFUNC(char_func, x, y)
ArgumentData TypeDescription
character_funcCHARThis argument determines the bitwise operation performed on arguments x and y.
Can be any of these string literals: 'AND', 'OR', or 'XOR'.
To perform bit operations using a NOT operator, use the BITNOT function.
xINTA numeric value to be evaluated by the function.
yINTA numeric value to be evaluated by the function.

Example

SELECT BITFUNC('AND',11,12);

Output: 8

Example

SELECT BITFUNC('OR',11,12);

Output: 15

Example

SELECT BITFUNC('XOR',11,12);

Output: 7

BITAND

Alias for the BITFUNC syntax BITFUNC('AND', x, y).

BITNOT

Returns the bitwise negation of integral_x.

Syntax

BITNOT(integral_x)

Example

SELECT BITNOT(1);

Output: -2

BITOR

Alias for the BITFUNC syntax BITFUNC('OR', x, y).

BITXOR

Alias for the BITFUNC syntax BITFUNC('XOR', x, y).

BOOLFUNC

Performs a Boolean logical evaluation on arguments x and y. Numbers equal to 0 evaluate to false, and numbers not equal to 0 evaluate to true.

Syntax

BOOLFUNC(char_func, x, y)
ArgumentData TypeDescription
char_funcCHARThis argument determines the Boolean operation performed on arguments x and y.
Can be any of these string literals: 'AND', 'OR', or 'XOR'.
To perform operations using a NOT operator, use the BOOLNOT function.
xINTA numeric value for evaluation by the function.
yINTA numeric value for evaluation by the function.

Example

SELECT BOOLFUNC('AND',0,1);

Output: false

Example

SELECT BOOLFUNC('OR',0,1);

Output: true

Example

SELECT BOOLFUNC('XOR',0,1);

Output: true

BOOLAND

Alias for the BOOLFUNC syntax BOOLFUNC('AND', x, y).

BOOLNOT

Returns the logical negation of the BOOLFUNC function. Numbers equal to 0 evaluate to true, and numbers not equal to 0 evaluate to false.

Syntax

BOOLNOT(numeric_x)

Example

SELECT BOOLNOT(0);

Output: true

BOOLOR

Alias for the BOOLFUNC syntax BOOLFUNC('OR', x, y).

BOOLXOR

Alias for the BOOLFUNC syntax BOOLFUNC('XOR', x, y).

CBRT

Returns the cube root of the numeric value x.

Syntax

CBRT(x)

Example

SELECT CBRT(64);

Output: 4.0

CDF

The cumulative distribution function of the standard normal distribution. Returns the probability that a random sample is less than or equal to the specified value.

For details about the cumulative distribution function, see Cumulative distribution function.

Syntax

CDF(x)

Example

SELECT CDF(1.5);

Output: 0.9331927987311419

CEIL

Returns the nearest integer greater than or equal to x.

Syntax

CEIL(x)

Example

SELECT CEIL(23.45);

Output: 24.0

CEILING

Alias for CEIL.

COS

Returns the cosine of x.

Syntax

COS(x)

Example

SELECT COS(2);

Output: -0.4161468365471424

COSH

Returns the hyperbolic cosine of x.

Syntax

COSH(x)

Example

SELECT COSH(2);

Output: 3.7621956910836314

COT

Returns the cotangent of x.

Syntax

COT(x)

Example

SELECT COT(2);

Output: -0.45765755436028577

DEGREES

Returns the corresponding angle in degrees for x in radians.

Syntax

DEGREES(x)

Example

SELECT DEGREES(1.5);

Output: 85.94366926962348

DIV

Returns the result of x divided by y. If y is zero, returns NULL.

Syntax

DIV(x, y)

Example

SELECT DIV(45, 9);

Output: 5

ERF

The error function is used for measurements that follow a normal distribution. Use this function to find the probability that the error of a single measurement lies between [-a, a]. For details about the error function, see Error function.

Syntax

ERF(x)

Example

SELECT ERF(8);

Output: 1.0

ERFC

The complement of the error function. ERFC(x) = 1 - ERF(x).

Syntax

ERFC(x)

Example

SELECT ERFC(8);

Output: 1.1224297172982928E-29

EXP

Returns the exponential of x (e raised to the power of x).

Syntax

EXP(x)

Example

SELECT EXP(1);

Output: 2.718281828459045

FLOOR

Returns the nearest integer less than or equal to x.

Syntax

FLOOR(x)

Example

SELECT FLOOR(22.75);

Output: 22.0

GAMMA

Compute the gamma function of the specified value. For details about the gamma function, see the Gamma function.

Syntax

GAMMA(x)

Example

SELECT GAMMA(9.0);

Output: 40319.99999999999

HEXBINX

Returns the x-coordinate of the center of the nearest hexagonal bin to the point (x, y). Hexagonal bins have a side length of 1.

Syntax

HEXBINX(x, y)

Example

SELECT HEXBINX(5, 7);

Output: 4.5

HEXBINY

Returns the y-coordinate of the center of the nearest hexagonal bin to the point (x, y). Hexagonal bins have a side length of 1.

Syntax

HEXBINY(x, y)

Example

SELECT HEXBINY(5, 7);

Output: 7.794

IERF

The inverse of the ERF error function.

Syntax

IERF(x)

Example

SELECT IERF(0);

Output: 0.0

IERFC

The inverse of the complement of the error function.

Syntax

IERFC(x)

Example

SELECT IERFC(1);

Output: 0.0

LEAKYRELU

Returns the leaky rectified linear unit function of x.

If unspecified, the optional numeric_value_slope argument defaults to 0.01.

Syntax

LEAKYRELU(x [, numeric_value_slope] )

Example

SELECT LEAKYRELU(-40);

Output: -0.4

LEFT_SHIFT

Returns x shifted to the left by y bits. If y is less than 0, then x shifts to the right by the absolute value. The function throws an out-of-range error if you attempt a shift into the sign bit.

Syntax

LEFT_SHIFT(x, y)

Example

SELECT LEFT_SHIFT(1,2);

Output: 4

LN

Returns the natural logarithm of x (base e or 2.171828…).

Syntax

LN(x)

Example

SELECT LN(2);

Output: 0.6931471805599453

LOG2

Returns the base 2 logarithm of x.

Syntax

LOG2(x)

Example

SELECT LOG2(2);

Output: 1.0

LOG

Returns the base 10 logarithm of x.

The optional base argument specifies the numeral system to use. If unspecified, the function defaults to base 10.

Syntax

LOG([ base,] x)

Example

SELECT LOG(2);

Output: 0.3010299956639812

LOG_GAMMA

The natural logarithm of the absolute value of the gamma function.

Syntax

LOG_GAMMA(x)

Example

SELECT LOG_GAMMA(10);

Output: 12.80182748008147

MOD

Returns the remainder from x divided by y.

Syntax

MOD(x, y)

Example

SELECT MOD(12, 10);

Output: 2

PI

Returns the constant value of π.

Syntax

PI()

Example

SELECT PI();

Output: 3.141592653589793

PMOD

Returns the smallest non-negative equivalence class of x % y.

Syntax

PMOD(x, y)

Example

SELECT PMOD(12,10);

Output: 2

POWER

Returns x raised to the power of y. The return type FLOAT is returned for a FLOAT input, DECIMAL for DOUBLE input, and BIGINT otherwise.

Syntax

POWER(x, y)

Example

SELECT POWER(2, 4);

Output: 16

PROBIT

The inverse of the cumulative distribution function. The function accepts only decimal values between 0 and 1.

For details about the probit function, see the Probit model.

Syntax

PROBIT(x)

Example

SELECT PROBIT(0.4);

Output: -0.2533471031357998

RADIANS

Returns the corresponding angle in radians for x in degrees.

Syntax

RADIANS(x)

Example

SELECT RADIANS(2);

Output: 0.03490658503988659

RAND

Takes no argument and returns a random DOUBLE value in the range [0, 1).

Syntax

RAND()

Example

SELECT RAND();

Output: 0.6053105024272647

RELU

Returns the rectified linear unit function of x.

Syntax

RELU(x)

Example

SELECT RELU(-1);

Output: 0.0

RIGHT_SHIFT

Returns x shifted to the right by y bits. If y is less than 0, then x shifts to the left by the absolute value of y in bits. The function throws an out-of-range error if you attempt a shift into the sign bit.

Syntax

RIGHT_SHIFT(x, y)

Example

SELECT RIGHT_SHIFT(5,1);

Output: 2

ROUND

Returns x rounded to the nearest integer.

Syntax

ROUND(x)

Example

SELECT ROUND(12.49);

Output: 12.0

SIGN

Returns the positive (+1), zero (0), or negative (-1) sign of x.

Syntax

SIGN(x)

Example

SELECT SIGN(+12);

Output: 1

Example

select SIGN(0);

Output: 0

Example

SELECT SIGN(-12);

Output: -1

SIN

Returns the sine of x.

Syntax

SIN(x)

Example

SELECT SIN(2);

Output: 0.9092974268256817

SINH

Returns the hyperbolic sine of x.

Syntax

SINH(x)

Example

SELECT SINH(2);

Output: 3.626860407847019

SQRT

Returns the square root of x.

Syntax

SQRT(x)

Example

SELECT SQRT(81);

Output: 9

SQUARE

Returns the square of x.

Syntax

SQUARE(x)

Example

SELECT SQUARE(7);

Output: 49

TAN

Returns the tangent of x.

Syntax

TAN(x)

Example

SELECT TAN(1.75);

Output: -5.52037992250933

TANH

Returns the hyperbolic tangent of x.

Syntax

TANH(x)

Example

SELECT TANH(1.75);

Output: 0.9413755384972874

TO_BASE

Converts an integer value to its string representation in a specified base (radix).

Syntax

TO_BASE(value, base)
ArgumentData TypeDescription
valueBYTE, SMALLINT, INT, BIGINTThe integer value to convert to the specified base. Can be positive or negative.
baseBYTE, SMALLINT, INT, BIGINTThe base (radix) for the conversion. Must be an integer between 2 and 36 (inclusive). For bases greater than 10, lowercase letters (a-z) represent digits 10 through 35 (e.g., a = 10, b = 11, and z = 35).

Examples

Convert to Binary (Base 2)

This example converts the decimal value 7 to its binary representation.

SELECT TO_BASE(7, 2);

Output: 111

Convert to Octal (Base 8)

This example converts the decimal value 64 to its octal representation.

SELECT TO_BASE(64, 8);

Output: 100

TRUNC

Returns x truncated to y decimal places.

Syntax

TRUNC(x,y)

Example

SELECT TRUNC(1.23456,3);

Output: 1.234

TRUNCATE

Alias for TRUNC.

[Array Functions and Operators](Array Functions and Operators)

Matrix Functions and Operators

[Tuple Functions and Operators](Tuple Functions and Operators)

Number Formatting Functions

Data Types

Query Ocient