Mathematical functions - Hologres - Alibaba Cloud Documentation Center

This topic describes the functions supported by Hologres and provides use examples of these functions.

Note

For more information about how to use string functions that are compatible with PostgreSQL, see Mathematical Functions and Operators.

Type	Function	Description
Common mathematical functions	ABS	Returns the absolute value of a number.
	CBRT	Returns the cube root of a number.
	CEIL	Rounds a number up to the nearest integer.
	CEILING	Rounds a number up to the nearest integer.
	DEGREES	Converts a radian to its equivalent in degrees.
	EXP	Returns the result of a constant raised to the power of a number.
	FLOOR	Rounds a number down to the nearest integer.
	LN	Returns the natural logarithm of a number.
	LOG	Returns the base 10 logarithm of a number.
	MOD	Returns the remainder of a number divided by number x.
	PI	Returns the value of constant π.
	POWER	Returns the result of a raised to the power of b.
	RADIANS	Converts an angle in degrees to its equivalent in radians.
	RANDOM	Returns a random number.
	ROUND	Rounds a number to the nearest integer or the nearest number with a fixed number of decimal places.
	SIGN	Returns the sign of a number.
	SQRT	Returns the square root of a number.
	TRUNC	Truncates a number to an integer or a number with a fixed number of decimal places.
	WIDTH_BUCKET	Divides a data set into buckets of equal width and returns the bucket number into which an operand falls.
Trigonometric functions	ACOS	Returns the arccosine of a number.
	ASIN	Returns the arcsine of a number.
	ATAN	Returns the arctangent of a number.
	ATAN2	Returns the arctangent of `y divided by x`.
	COS	Returns the cosine of a number.
	COT	Returns the cotangent of a number.
	SIN	Returns the sine of a number.
	TAN	Returns the tangent of a number.

Common mathematical functions

ABS

Description: Returns the absolute value of a number.
```
ABS(num)
```
Parameters
num: required. The type of the parameter value can be INT, BIGINT, REAL, DOUBLE PRECISION, NUMERIC, or DECIMAL.
Return value
A value of the INT, BIGINT, REAL, DOUBLE PRECISION, NUMERIC, or DECIMAL type is returned.
Example
```
SELECT ABS(-17.4);
```
The following result is returned:
```
 abs
-----
 17.4
```

CBRT

Description: Returns the cube root of a number.
```
CBRT(num)
```
Parameters
num: required. The type of the parameter value must be DOUBLE PRECISION.
Return value
A value of the DOUBLE PRECISION type is returned.

Example

SELECT CBRT(9);

The following result is returned:

      cbrt
-----------------
 2.0800838230519

CEIL

Description: Rounds a number up to the nearest integer.
```
CEIL(num)
```
Parameters
num: required. The type of the parameter value can be DOUBLE PRECISION or NUMERIC.
Return value
A value of the DOUBLE PRECISION or NUMERIC type is returned.
Example
```
SELECT CEIL(9.2);
```
The following result is returned:
```
 ceil
------
   10
```

CEILING

Description: Rounds a number up to the nearest integer.
```
CEILING(num)
```
Parameters
num: required. The type of the parameter value can be DOUBLE PRECISION or NUMERIC.
Return value
A value of the DOUBLE PRECISION or NUMERIC type is returned.
Example
```
SELECT CEILING(9.2);
```
The following result is returned:
```
 ceil
------
   10
```

DEGREES

Description: Converts a radian to its equivalent in degrees.
```
DEGREES(num)
```
Parameters
num: required. The type of the parameter value must be DOUBLE PRECISION.
Return value
A value of the DOUBLE PRECISION type is returned.

Example

SELECT DEGREES(3.2);

The following result is returned:

      degrees
--------------
183.34649444186343

EXP

Description: Returns the result of a constant raised to the power of a number.
```
EXP(num)
```
Parameters
num: required. The type of the parameter value can be DOUBLE PRECISION or NUMERIC.
Return value
A value of the DOUBLE PRECISION or NUMERIC type is returned.

Example

SELECT EXP(1);

The following result is returned:

             exp
----------------
2.718281828459045

FLOOR

Description: Rounds a number down to the nearest integer.
```
FLOOR(num)
```
Parameters
num: required. The type of the parameter value can be DOUBLE PRECISION or NUMERIC.
Return value
A value of the DOUBLE PRECISION or NUMERIC type is returned.
Example
```
SELECT FLOOR(3.8);
```
The following result is returned:
```
 floor
------
     3
```

LN

Description: Returns the natural logarithm of a number.
```
LN(num)
```
Parameters
num: required. The type of the parameter value can be DOUBLE PRECISION or NUMERIC.
Return value
A value of the DOUBLE PRECISION or NUMERIC type is returned.

Example

SELECT LN(3.8);

The following result is returned:

            ln
----------------
1.3350010667323401

LOG

Description: Returns the base 10 logarithm of a number.
```
LOG(num)
```
Parameters
num: required. The type of the parameter value can be DOUBLE PRECISION or NUMERIC.
Return value
A value of the DOUBLE PRECISION or NUMERIC type is returned.
Example
```
SELECT LOG(100);
```
The following result is returned:
```
 log
-----
   2
```

MOD

Description: Returns the remainder of a number divided by number x.
```
MOD(num, x)
```
Parameters
- num: required. The type of the parameter value must be NUMERIC.
- x: required. The type of the parameter value must be NUMERIC.
Return value
A value of the NUMERIC type is returned.
Example
```
SELECT MOD(9,4);
```
The following result is returned:
```
  mod
-----
    1
```

PI

Description: Returns the value of constant π.
```
PI()
```
Return value
The value of constant π is returned.

Example

SELECT PI();

The following result is returned:

        pi
------------------
 3.14159265358979

POWER

Description: Returns the result of a raised to the power of b.
```
POWER(a, b)
```
Parameters
- a: required. The type of the parameter value must be DOUBLE PRECISION.
- b: required. The type of the parameter value must be DOUBLE PRECISION.
Return value
A value of the DOUBLE PRECISION type is returned.

Example

SELECT POWER(9, 3);

The following result is returned:

 power
-------
   729

RADIANS

Description: Converts an angle in degrees to its equivalent in radians.
```
RADIANS(num)
```
Parameters
num: required. The type of the parameter value must be DOUBLE PRECISION.
Return value
A value of the DOUBLE PRECISION type is returned.

Example

SELECT RADIANS(45);

The following result is returned:

       radians
-------------------
 0.785398163397448

RANDOM

Description: Returns a random number.
```
RANDOM()
```
Return value
A random number of the DOUBLE PRECISION type is returned. Valid values: [0.0, 1.0).

Example

SELECT RANDOM();

The following result is returned:

      random
-------------------
 0.377819478977472

ROUND

Description
- Rounds a number to the nearest integer.
```
ROUND(num)
```
- Rounds a number to the nearest number with s decimal places.
```
ROUND(num, s)
```
Parameters
- num: required. The type of the parameter value can be DOUBLE PRECISION or NUMERIC.
  Note
  If you want to round a number to the nearest number with a fixed number of decimal places, we recommend that you convert the type of num to NUMERIC. Otherwise, the precision of the decimal places cannot be reached.
- s: optional. The type of the parameter value must be INT.
Return value
A value of the DOUBLE PRECISION or NUMERIC type is returned.

Examples

Example 1:
```
SELECT ROUND(42.4);
```
The following result is returned:
```
 round
-------
    42
```

Example 2:

SELECT ROUND(100/400::NUMERIC, 3);

The following result is returned:

 round
--------
 0.2500

SIGN

Description: Returns the sign of a number.
```
SIGN(num)
```
Parameters
num: required. The type of the parameter value can be DOUBLE PRECISION or NUMERIC.
Return value
If the parameter value is greater than 0, the value 1 is returned. If the parameter value is less than 0, the value -1 is returned. If the parameter value is 0, the value 0 is returned.
Example
```
SELECT SIGN(-8.4);
```
The following result is returned:
```
 sign
------
   -1
```

SQRT

Description: Returns the square root of a number.
```
SQRT(num)
```
Parameters
num: required. The type of the parameter value can be DOUBLE PRECISION or NUMERIC.
Return value
A value of the DOUBLE PRECISION or NUMERIC type is returned.

Example

SELECT SQRT(2);

The following result is returned:

      sqrt
-----------------
 1.4142135623731

TRUNC

Description
- Truncates a number to an integer.
```
TRUNC(num)
```
- Truncates a number to a number with s decimal places.
```
TRUNC(num, s)
```
Parameters
- num: required. The type of the parameter value must be NUMERIC.
- s: optional. The type of the parameter value must be INT.
Return value
A value of the NUMERIC type is returned.
Examples
- Example 1:
```
SELECT TRUNC(2.456);
```
  The following result is returned:
```
 trunc
-------
     2
```
- Example 2:
```
SELECT TRUNC(2.4564, 2);
```
  The following result is returned:
```
 trunc
-------
  2.45
```

WIDTH_BUCKET

Description: Divides a data set into buckets of equal width and returns the bucket number into which an operand falls.
```
WIDTH_BUCKET(value, start, end, num_buckets)
```
Parameters
value: required. The bucket number into which an operand falls. The type of the parameter value can be DOUBLE PRECISION or NUMERIC.
start: required. The start value of a bucket. The type of the parameter value can be DOUBLE PRECISION or NUMERIC.
end: required. The end value of a bucket. The type of the parameter value can be DOUBLE PRECISION or NUMERIC.
num_buckets: required. The number of buckets (intervals) to be divided. The type of the parameter value must be INT.
Return value
A value of the INT type is returned.

Example

--Data ranging from 0 to 100 falls into 5 buckets. 45 falls in the third bucket whose start value is 40 and end value is 60. 
SELECT WIDTH_BUCKET(45, 0, 100, 5);

The following result is returned:

 width_bucket
--------------
            3

Trigonometric functions

ACOS

Description: Returns the arccosine of a number.
```
ACOS(num)
```
Parameters
num: required. The type of the parameter value must be NUMERIC. Valid values: [-1,1].
Return value
A value of the DOUBLE PRECISION type is returned.

Example

SELECT ACOS(-1);

The following result is returned:

       acos
------------------
 3.14159265358979

ASIN

Description: Returns the arcsine of a number.
```
ASIN(num)
```
Parameters
num: required. The type of the parameter value must be DOUBLE PRECISION. Valid values: [-1,1].
Return value
A value of the DOUBLE PRECISION type is returned.

Example

SELECT ASIN(-1);

The following result is returned:

       asin
------------------
 -1.5707963267949

ATAN

Description: Returns the arctangent of a number.
```
ATAN(num)
```
Parameters
num: required. The type of the parameter value must be DOUBLE PRECISION.
Return value
A value of the DOUBLE PRECISION type is returned.

Example

SELECT ATAN(2);

The following result is returned:

       atan
------------------
 1.10714871779409

ATAN2

Description: Returns the arctangent of y divided by x.
```
ATAN2(y, x);
```
Parameters
- y: required. The type of the parameter value must be DOUBLE PRECISION.
- x: required. The type of the parameter value must be DOUBLE PRECISION.
Return value
A value of the DOUBLE PRECISION type is returned.

Example

SELECT ATAN2(4, 2);

The following result is returned:

      atan2
------------------
 1.10714871779409

COS

Description: Returns the cosine of a number.
```
COS(num)
```
Parameters
num: required. The type of the parameter value must be DOUBLE PRECISION.
Return value
A value of the DOUBLE PRECISION type is returned.

Example

SELECT COS(2);

The following result is returned:

        cos
--------------------
 -0.416146836547142

COT

Description: Returns the cotangent of a number.
```
COT(num)
```
Parameters
num: required. The type of the parameter value must be DOUBLE PRECISION.
Return value
A value of the DOUBLE PRECISION type is returned.

Example

SELECT COT(2);

The following result is returned:

        cot
--------------------
 -0.457657554360286

SIN

Description: Returns the sine of a number.
```
SIN(num)
```
Parameters
num: required. The type of the parameter value must be DOUBLE PRECISION.
Return value
A value of the DOUBLE PRECISION type is returned.

Example

SELECT SIN(2);

The following result is returned:

        sin
-------------------
 0.909297426825682

TAN

Description: Returns the tangent of a number.
```
TAN(num)
```
Parameters
num: required. The type of the parameter value must be DOUBLE PRECISION.
Return value
A value of the DOUBLE PRECISION type is returned.

Example

SELECT TAN(2);

The following result is returned:

        tan
-------------------
 -2.18503986326152