Mathematical functions

Lists mathematical functions available in the data analysis feature for computing data and converting data types. Each function includes its syntax, parameters, and examples.

Function	Description
ABS	Calculates the absolute value.
ASIN	Calculates the arcsine.
COS	Calculates the cosine.
COSH	Calculates the hyperbolic cosine.
CEIL	Rounds up to the nearest integer.
EXP	Calculates e raised to the power of a value.
FLOOR	Rounds down to the nearest integer.
LOG	Calculates the logarithm.
POW	Calculates x raised to the power of y.
RAND	Returns a random number.
ROUND	Rounds to the specified decimal place.
SIN	Calculates the sine.
SINH	Calculates the hyperbolic sine.
SQRT	Calculates the square root.
TAN	Calculates the tangent.
TANH	Calculates the hyperbolic tangent.
TRUNC	Truncates to the specified decimal place.
HEX	Converts an integer or string to hexadecimal.
PI	Calculates the value of π.
UNHEX	Converts a hexadecimal string to a character string.

ABS

Syntax

double abs(double <number>)
bigint abs(bigint <number>)
decimal abs(decimal <number>)

Description
Calculates the absolute value of number.
Parameters
number: required. If number is DOUBLE, BIGINT, or DECIMAL, the return type matches the input.
- BIGINT input returns BIGINT.
- DOUBLE input returns DOUBLE.
- DECIMAL input returns DECIMAL.
- STRING input is implicitly converted to DOUBLE before calculation.
- Returns null if the input is null.
- Other data types return an error.
Note If a BIGINT input exceeds the maximum BIGINT value, a DOUBLE value is returned with possible precision loss.
Return value
DOUBLE, BIGINT, or DECIMAL, matching the input type.

Examples

-- The return value is NULL. 
    abs(null);

-- The return value is 1. 
    abs(-1);

-- The return value is 1.2. 
    abs(-1.2);

-- The return value is 2.0. 
    abs("-2");

-- The value 1.2232083745629837E32 is returned. 
    abs(122320837456298376592387456923748);

ASIN

Syntax

double asin(double <number>)
decimal asin(decimal <number>)

Description
Calculates the arcsine of number.
Parameters
number: required. DOUBLE or DECIMAL, range: -1 to 1. STRING or BIGINT inputs are implicitly converted to DOUBLE. Other types return an error.
Return value
DOUBLE or DECIMAL, range: -π/2 to π/2. Returns null if the input is null.

Examples

--The return value is 1.5707963267948966. 
    asin(1);
-- The return value is 1.5707963267948966. 
    asin(-1);

CEIL

Syntax

bigint ceil(double <value>)
bigint ceil(decimal <value>)

Description
Rounds up value to the nearest integer.
Parameters
value: required. DOUBLE or DECIMAL. STRING or BIGINT inputs are implicitly converted to DOUBLE. Other types return an error.
Return value
BIGINT. Returns null if the input is null.

Examples

-- The return value is 2. 
    ceil(1.1);
-- The return value is -1. 
    ceil(-1.1);

COS

Syntax

double cos(double <number>)
decimal cos(decimal <number>)

Description
Calculates the cosine of number, which is a radian value.
Parameters
number: required. DOUBLE or DECIMAL. STRING or BIGINT inputs are implicitly converted to DOUBLE. Other types return an error.
Return value
DOUBLE or DECIMAL. Returns null if the input is null.

Examples

-- The return value is 2.6794896585028633e-8. 
    cos(3.1415926/2);

-- The return value is -0.9999999999999986. 
    cos(3.1415926);

COSH

Syntax

double cosh(double <number>)
decimal cosh(decimal <number>)

Description
Calculates the hyperbolic cosine of number.
Parameters
number: required. DOUBLE or DECIMAL. STRING or BIGINT inputs are implicitly converted to DOUBLE. Other types return an error.
Return value
DOUBLE or DECIMAL. Returns null if the input is null.

Example

-- The return value is 2.5091784169949913. 
    cosh(3.1415926/2);

EXP

Syntax

double exp(double <number>)
decimal exp(decimal <number>)

Description
Calculates e raised to the power of number.
Parameters
number: required. DOUBLE or DECIMAL. STRING or BIGINT inputs are implicitly converted to DOUBLE. Other types return an error.
Return value
DOUBLE or DECIMAL. Returns null if the input is null.

Example

-- The return value is 4.810477252069109. 
    exp(3.1415926/2);

FLOOR

Syntax

bigint floor(double <number>)
bigint floor(decimal <number>)

Description
Rounds down number to the nearest integer.
Parameters
number: required. DOUBLE or DECIMAL. STRING or BIGINT inputs are implicitly converted to DOUBLE. Other types return an error.
Return value
BIGINT. Returns null if the input is null.

Examples

-- The return value is 1. 
    floor(1.2);

-- The return value is 0. 
    floor(0.1);

-- The return value is -2. 
    floor(-1.2);

-- The return value is -1. 
    floor(-0.1);

-- The return value is 0. 
    floor(0.0);

-- The return value is 0. 
    floor(-0.0);

LOG

Syntax

double log(double <base>, double <x>)
decimal log(decimal <base>, decimal <x>)

Description
Calculates the logarithm of x with base base.
Parameters
- base: required. DOUBLE or DECIMAL. STRING or BIGINT inputs are implicitly converted to DOUBLE. Other types return an error.
- x: required. DOUBLE or DECIMAL. STRING or BIGINT inputs are implicitly converted to DOUBLE. Other types return an error.
Return value
DOUBLE or DECIMAL.
- Returns null if any input is null.
- Returns an error if any input is negative or 0.
- Returns an error if base is 1 (division by zero).

Example

-- The return value is 4.0. 
    log(2, 16);

POW

Syntax

double pow(double <x>, double <y>)
decimal pow(decimal <x>, decimal <y>)

Description
Calculates the yth power of x, namely, ^{x^y}.
Parameters
- x: required. DOUBLE or DECIMAL. STRING or BIGINT inputs are implicitly converted to DOUBLE. Other types return an error.
- y: required. DOUBLE or DECIMAL. STRING or BIGINT inputs are implicitly converted to DOUBLE. Other types return an error.
Return value
DOUBLE or DECIMAL. Returns null if any input is null.

Example

-- The return value is 65536.0. 
    pow(2, 16);

RAND

Syntax
```
double rand(bigint <seed>)
```
Description
Returns a random DOUBLE value between 0 and 1.
Parameters
seed: optional. BIGINT. Specifies the random seed that determines the starting point for random number generation.

Note After seed is determined, the return value of this function is fixed. seed controls the random number sequence.
Return value
DOUBLE.

Examples

-- The rerturn value is 4.7147460303803655E-4. 
    rand();
-- The return value is 0.7308781907032909. 
    rand(1);

ROUND

Syntax

double round(double <number>[, bigint <decimal_places>])
decimal round(decimal <number>[, bigint <decimal_places>])

Description
Rounds a number to the specified decimal place.
Parameters
- number: required. DOUBLE or DECIMAL. STRING or BIGINT inputs are implicitly converted to DOUBLE. Other types return an error.
- decimal_places: optional. BIGINT constant. Specifies the decimal place to round to. Default: 0 (ones place). Other types return an error.
  Note A negative decimal_places counts leftward from the decimal point, excluding the decimal part. If decimal_places exceeds the integer length, 0 is returned.
Return value
DOUBLE or DECIMAL. Returns null if any input is null.

Examples

-- The return value is 125.0. 
    round(125.315);
-- The return value is 125.3. 
    round(125.315, 1);
-- The return value is 125.32. 
    round(125.315, 2);
-- The return value is 125.315. 
    round(125.315, 3);
-- The return value is -125.32. 
    round(-125.315, 2);
-- The return value is 100.0. 
    round(123.345, -2);
-- The return value is null. 
    round(null);
-- The return value is 123.345. 
    round(123.345, 4);
-- The return value is 0.0. 
    round(123.345, -4);

SIN

Syntax

double sin(double <number>)
decimal sin(decimal <number>)

Description
Calculates the sine of number, which is a radian value.
Parameters
number: required. DOUBLE or DECIMAL (radian value). STRING or BIGINT inputs are implicitly converted to DOUBLE. Other types return an error.
Return value
DOUBLE or DECIMAL. Returns null if the input is null.

Example

-- The return value is -0.3048106211022167. 
    sin(60);

SINH

Syntax

double sinh(double <number>)
decimal sinh(decimal <number>)

Description
Calculates the hyperbolic sine of number.
Parameters
number: required. DOUBLE or DECIMAL. STRING or BIGINT inputs are implicitly converted to DOUBLE. Other types return an error.
Return value
DOUBLE or DECIMAL. Returns null if the input is null.

Example

-- The return value is 5.343237290762231E12. 
    sinh(30);

SQRT

Syntax

double sqrt(double <number>)
decimal sqrt(decimal <number>)

Description
Calculates the square root of number.
Parameters
number: required. DOUBLE or DECIMAL, must be greater than 0. STRING or BIGINT inputs are implicitly converted to DOUBLE. Other types return an error.
Return value
DOUBLE or DECIMAL. Returns null if the input is null.

Example

-- The return value is 2.0. 
    sqrt(4);

TAN

Syntax

double tan(double <number>)
decimal tan(decimal <number>)

Description
Calculates the tangent of number, which is a radian value.
Parameters
number: required. DOUBLE or DECIMAL (radian value). STRING or BIGINT inputs are implicitly converted to DOUBLE. Other types return an error.
Return value
DOUBLE or DECIMAL. Returns null if the input is null.

Example

-- The return value is -6.405331196646276. 
    tan(30);

TANH

Syntax

double tanh(double <number>)
decimal tanh(decimal <number>)

Description
Calculates the hyperbolic tangent of number.
Parameters
number: required. DOUBLE or DECIMAL. STRING or BIGINT inputs are implicitly converted to DOUBLE. Other types return an error.
Return value
DOUBLE or DECIMAL. Returns null if the input is null.
Example
```
-- The return value is 1.0. 
tanh(30);
```

TRUNC

Syntax

double trunc(double <number>[, bigint <decimal_places>])
decimal trunc(decimal <number>[, bigint <decimal_places>])

Description
Truncates number to the specified decimal place.
Parameters
- number: required. DOUBLE or DECIMAL. STRING or BIGINT inputs are implicitly converted to DOUBLE. Other types return an error.
- decimal_places: optional. BIGINT constant. Specifies the truncation position. Other types are converted to BIGINT. Default: 0 (ones place).
  Note A negative decimal_places truncates leftward from the decimal point, excluding the decimal part. If decimal_places exceeds the integer length, 0 is returned.
Return value
DOUBLE or DECIMAL. Returns null if any input is null.
Note
- DOUBLE return values may not display properly due to floating-point precision. Example: trunc(125.815,1) returns 125.80000000000001.
- The number is zero-filled from the specified position.

Examples

-- The return value is 125.0. 
    trunc(125.815,0);
-- The return value is 125.80000000000001. 
    trunc(125.815,1);
-- The return value is 125.81. 
    trunc(125.815,2);
-- The return value is 125.815. 
    trunc(125.815,3);
-- The return value is -125.81. 
    trunc(-125.815,2);
-- The return value is 120.0. 
    trunc(125.815,-1);
-- The return value is 100.0. 
    trunc(125.815,-2);
-- The return value is 0.0. 
    trunc(125.815,-3);
-- The return value is 123.345. 
    trunc(123.345,4);
-- The return value is 0.0. 
    trunc(123.345,-4);

HEX

Syntax

string hex(bigint <number>) 
string hex(string <number>)
string hex(binary <number>)

Description
Converts an integer or string to hexadecimal.
Parameters
number: required. If number is BIGINT, returns a hexadecimal number. If number is STRING, returns the hexadecimal representation of each character.
Return value
- Non-zero, non-null input returns a STRING.
- Input of 0 returns 0.
- Null input returns an error.

Examples

--The return value is 0. 
    hex(0);
-- The return value is 616263. 
    hex('abc');
-- The return value is 11. 
    hex(17);
-- The return value is 3137. 
    hex('17');
-- An error is returned.  
    hex(null);

UNHEX

Syntax
```
binary unhex(string <number>)
```
Description
Converts a hexadecimal string to a character string.
Parameters
number: required. A hexadecimal string.
Return value
BINARY. Returns an error if the input is 0. Returns null if the input is null.

Examples

--The return value is abc 
    unhex('616263');
-- The return value is abc. 
    unhex(616263);

PI

Syntax
```
double pi()
```
Description
Returns the value of π.
Return value
DOUBLE.

Example

-- The return value is 3.141592653589793. 
    pi();