Math Functions and Operators - Ocient Documentation

Math Operators

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:

Operation	Precision	Scale
addition, subtraction	max(`s1`, `s2`) + max(`p1`-`s1`, `p2`-`s2`) + 1	max(`s1`, `s2`)
multiplication	`p1` + `p2`	`s1` + `s2`
division	See 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.

SQL

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.

SQL

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

SQL

ABS(x)

Example

SQL

SELECT ABS(-250.5);

Output: 250.5

ACOS

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

SQL

ACOS(x)

Example

SQL

SELECT ACOS(0.25);

Output: 1.318116071652818

ACOSH

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

SQL

ACOSH(x)

Example

SQL

SELECT ACOSH(2);

Output: 1.3169578969248166

ASIN

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

SQL

ASIN(x)

Example

SQL

SELECT ASIN(0.25);

Output: 0.25268025514207865

ASINH

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

SQL

ASINH(x)

Example

SQL

SELECT ASINH(2);

Output: 1.4436354751788103

ATAN

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

SQL

ATAN(x)

Example

SQL

SELECT ATAN(0.25);

Output: 0.24497866312686414

ATAN2

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

SQL

ATAN2(x, y)

Example

SQL

SELECT ATAN2(0.50, 1);

Output: 0.4636476090008061

ATANH

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

SQL

ATANH(x)

Example

SQL

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

SQL

BICDF(x, y [, rho])

Example

SQL

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

SQL

BIPDF(x, y, [, rho])

Example

SQL

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

SQL

BITFUNC(char_func, x, y)

Argument	Data Type	Description
`character_func`	`CHAR`	This 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.
`x`	`INT`	A numeric value to be evaluated by the function.
`y`	`INT`	A numeric value to be evaluated by the function.

Example

SQL

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

Output: 8 Example

SQL

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

Output: 15 Example

SQL

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

SQL

BITNOT(integral_x)

Example

SQL

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

SQL

BOOLFUNC(char_func, x, y)

Argument	Data Type	Description
`char_func`	`CHAR`	This 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.
`x`	`INT`	A numeric value for evaluation by the function.
`y`	`INT`	A numeric value for evaluation by the function.

Example

SQL

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

Output: false Example

SQL

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

Output: true Example

SQL

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

SQL

BOOLNOT(numeric_x)

Example

SQL

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

SQL

CBRT(x)

Example

SQL

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

SQL

CDF(x)

Example

SQL

SELECT CDF(1.5);

Output: 0.9331927987311419

CEIL

Returns the nearest integer greater than or equal to x. Syntax

SQL

CEIL(x)

Example

SQL

SELECT CEIL(23.45);

Output: 24.0

CEILING

Alias for CEIL.

COS

Returns the cosine of x. Syntax

SQL

COS(x)

Example

SQL

SELECT COS(2);

Output: -0.4161468365471424

COSH

Returns the hyperbolic cosine of x. Syntax

SQL

COSH(x)

Example

SQL

SELECT COSH(2);

Output: 3.7621956910836314

COT

Returns the cotangent of x. Syntax

SQL

COT(x)

Example

SQL

SELECT COT(2);

Output: -0.45765755436028577

DEGREES

Returns the corresponding angle in degrees for x in radians. Syntax

SQL

DEGREES(x)

Example

SQL

SELECT DEGREES(1.5);

Output: 85.94366926962348

DIV

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

SQL

DIV(x, y)

Example

SQL

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

SQL

ERF(x)

Example

SQL

SELECT ERF(8);

Output: 1.0

ERFC

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

SQL

ERFC(x)

Example

SQL

SELECT ERFC(8);

Output: 1.1224297172982928E-29

EXP

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

SQL

EXP(x)

Example

SQL

SELECT EXP(1);

Output: 2.718281828459045

FLOOR

Returns the nearest integer less than or equal to x. Syntax

SQL

FLOOR(x)

Example

SQL

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

SQL

GAMMA(x)

Example

SQL

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

SQL

HEXBINX(x, y)

Example

SQL

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

SQL

HEXBINY(x, y)

Example

SQL

SELECT HEXBINY(5, 7);

Output: 7.794

IERF

The inverse of the ERF error function. Syntax

SQL

IERF(x)

Example

SQL

SELECT IERF(0);

Output: 0.0

IERFC

The inverse of the complement of the error function. Syntax

SQL

IERFC(x)

Example

SQL

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

SQL

LEAKYRELU(x [, numeric_value_slope] )

Example

SQL

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

SQL

LEFT_SHIFT(x, y)

Example

SQL

SELECT LEFT_SHIFT(1,2);

Output: 4

LN

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

SQL

LN(x)

Example

SQL

SELECT LN(2);

Output: 0.6931471805599453

LOG2

Returns the base 2 logarithm of x. Syntax

SQL

LOG2(x)

Example

SQL

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

SQL

LOG([ base,] x)

Example

SQL

SELECT LOG(2);

Output: 0.3010299956639812

LOG_GAMMA

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

SQL

LOG_GAMMA(x)

Example

SQL

SELECT LOG_GAMMA(10);

Output: 12.80182748008147

MOD

Returns the remainder from x divided by y. Syntax

SQL

MOD(x, y)

Example

SQL

SELECT MOD(12, 10);

Output: 2

PI

Returns the constant value of π. Syntax

SQL

PI()

Example

SQL

SELECT PI();

Output: 3.141592653589793

PMOD

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

SQL

PMOD(x, y)

Example

SQL

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

SQL

POWER(x, y)

Example

SQL

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

SQL

PROBIT(x)

Example

SQL

SELECT PROBIT(0.4);

Output: -0.2533471031357998

RADIANS

Returns the corresponding angle in radians for x in degrees. Syntax

SQL

RADIANS(x)

Example

SQL

SELECT RADIANS(2);

Output: 0.03490658503988659

RAND

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

SQL

RAND()

Example

SQL

SELECT RAND();

Output: 0.6053105024272647

RELU

Returns the rectified linear unit function of x. Syntax

SQL

RELU(x)

Example

SQL

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

SQL

RIGHT_SHIFT(x, y)

Example

SQL

SELECT RIGHT_SHIFT(5,1);

Output: 2

ROUND

Returns x rounded to the nearest integer. Syntax

SQL

ROUND(x)

Example

SQL

SELECT ROUND(12.49);

Output: 12.0

SIGN

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

SQL

SIGN(x)

Example

SQL

SELECT SIGN(+12);

Output: 1 Example

SQL

select SIGN(0);

Output: 0 Example

SQL

SELECT SIGN(-12);

Output: -1

SIN

Returns the sine of x. Syntax

SQL

SIN(x)

Example

SQL

SELECT SIN(2);

Output: 0.9092974268256817

SINH

Returns the hyperbolic sine of x. Syntax

SQL

SINH(x)

Example

SQL

SELECT SINH(2);

Output: 3.626860407847019

SQRT

Returns the square root of x. Syntax

SQL

SQRT(x)

Example

SQL

SELECT SQRT(81);

Output: 9

SQUARE

Returns the square of x. Syntax

SQL

SQUARE(x)

Example

SQL

SELECT SQUARE(7);

Output: 49

TAN

Returns the tangent of x. Syntax

SQL

TAN(x)

Example

SQL

SELECT TAN(1.75);

Output: -5.52037992250933

TANH

Returns the hyperbolic tangent of x. Syntax

SQL

TANH(x)

Example

SQL

SELECT TANH(1.75);

Output: 0.9413755384972874

TO_BASE

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

SQL

TO_BASE(value, base)

Argument	Data Type	Description
`value`	`BYTE`, `SMALLINT`, `INT`, `BIGINT`	The integer value to convert to the specified base. Can be positive or negative.
`base`	`BYTE`, `SMALLINT`, `INT`, `BIGINT`	The 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.

SQL

SELECT TO_BASE(7, 2);

Output: 111 Convert to Octal (Base 8) This example converts the decimal value 64 to its octal representation.

SQL

SELECT TO_BASE(64, 8);

Output: 100

TRUNC

Returns x truncated to y decimal places. Syntax

SQL

TRUNC(x,y)

Example

SQL

SELECT TRUNC(1.23456,3);

Output: 1.234

TRUNCATE

Alias for TRUNC. Array Functions and Operators Matrix Functions and Operators Tuple Functions and Operators Number Formatting Functions Data Types Query Ocient

OcientAIQ Unified Data Platform

Documentation Index

​Math Operators

​Decimal Precision

​Math Operators for Time Intervals

​Math Functions

​ABS

​ACOS

​ACOSH

​ASIN

​ASINH

​ATAN

​ATAN2

​ATANH

​BICDF

​BIPDF

​BITFUNC

​BITAND

​BITNOT

​BITOR

​BITXOR

​BOOLFUNC

​BOOLAND

​BOOLNOT

​BOOLOR

​BOOLXOR

​CBRT

​CDF

​CEIL

​CEILING

​COS

​COSH

​COT

​DEGREES

​DIV

​ERF

​ERFC

​EXP

​FLOOR

​GAMMA

​HEXBINX

​HEXBINY

​IERF

​IERFC

​LEAKYRELU

​LEFT_SHIFT

​LN

​LOG2

​LOG

​LOG_GAMMA

​MOD

​PI

​PMOD

​POWER

​PROBIT

​RADIANS

​RAND

​RELU

​RIGHT_SHIFT

​ROUND

​SIGN

​SIN

​SINH

​SQRT

​SQUARE

​TAN

​TANH

​TO_BASE

​TRUNC

​TRUNCATE

​Related Links

Math Operators

Decimal Precision

Math Operators for Time Intervals

Math Functions

ABS

ACOS

ACOSH

ASIN

ASINH

ATAN

ATAN2

ATANH

BICDF

BIPDF

BITFUNC

BITAND

BITNOT

BITOR

BITXOR

BOOLFUNC

BOOLAND

BOOLNOT

BOOLOR

BOOLXOR

CBRT

CDF

CEIL

CEILING

COS

COSH

COT

DEGREES

DIV

ERF

ERFC

EXP

FLOOR

GAMMA

HEXBINX

HEXBINY

IERF

IERFC

LEAKYRELU

LEFT_SHIFT

LN

LOG2

LOG

LOG_GAMMA

MOD

PI

PMOD

POWER

PROBIT

RADIANS

RAND

RELU

RIGHT_SHIFT

ROUND

SIGN

SIN

SINH

SQRT

SQUARE

TAN

TANH

TO_BASE

TRUNC

TRUNCATE

Related Links