Mathematical functions - MaxCompute - Alibaba Cloud Documentation Center

Function	Description
ABS	Calculates the absolute value.
ACOS	Calculates the arccosine.
ASIN	Calculates the arcsine.
ATAN	Calculates the arctangent.
CEIL	Rounds up a number and returns the nearest integer.
CONV	Converts a number from one number system to another.
COS	Calculates the cosine.
COSH	Calculates the hyperbolic cosine.
COT	Calculates the cotangent.
EXP	Calculates the exponential value.
FLOOR	Rounds down a number and returns the nearest integer.
LN	Calculates the natural logarithm.
LOG	Calculates the logarithm.
POW	Calculates the nth power of a value.
RAND	Returns a random number.
ROUND	Returns a value rounded 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 the input value to the specified decimal place.
BIN	Calculates the binary code.
CBRT	Calculates the cube root.
CORR	Calculates the Pearson correlation coefficient.
DEGREES	Converts a radian value into a degree.
E	Calculates the value of e.
FACTORIAL	Calculates the factorial.
FORMAT_NUMBER	Converts a number into a string in the specified format.
HEX	Converts an integer or a string into a hexadecimal number.
LOG2	Calculates the logarithm of a number with the base number of 2.
LOG10	Calculates the logarithm of a number with the base number of 10.
PI	Calculates the value of π.
RADIANS	Converts a degree into a radian value.
SIGN	Returns the sign of the input value.
SHIFTLEFT	Shifts a value left by a specific number of places.
SHIFTRIGHT	Shifts a value right by a specific number of places.
SHIFTRIGHTUNSIGNED	Shifts an unsigned value right by a specific number of places.
UNHEX	Converts a hexadecimal string into a string.
WIDTH_BUCKET	Returns the ID of the bucket into which the value of a specific expression falls.

Usage notes

MaxCompute V2.0 provides extension date functions. If the functions that you use involve new data types, you must run one of the following SET commands to enable the MaxCompute V2.0 data type edition. The new data types include TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, and BINARY.

Session level: To use the MaxCompute V2.0 data type edition, you must add set odps.sql.type.system.odps2=true; before the SQL statement that you want to execute, and commit and execute them together.
Project level: The project owner can enable the MaxCompute V2.0 data type edition for the project based on the project requirements. The configuration takes effect after 10 to 15 minutes. Run the following command:
```
setproject odps.sql.type.system.odps2=true;
```
For more information about setproject, see Project operations. For more information about the precautions that you must take when you enable the MaxCompute V2.0 data type edition at the project level, see Data type editions.

Sample data

This section provides sample source data for you to understand how to use mathematical functions. In this topic, a table named mf_math_fun_t is created and data is inserted into the table. Sample statements:

create table if not exists mf_math_fun_t(
     int_data     int,
     bigint_data  bigint,
     double_data  double,
     decimal_data decimal,
     float_data   float,
     string_data  string
    );
insert into mf_math_fun_t values
(null, -10, 0.525, 0.525BD, cast(0.525 as float), '10'),
(-20, null, -0.1, -0.1BD, cast(-0.1 as float), '-10'),
(0, -1, null, 20.45BD, cast(-1 as float), '30'),
(-40, 4, 0.89, null, cast(0.89 as float), '-30'),
(5, -50, -1, -1BD, null, '50'),
(-60, 6, 1.5, 1.5BD, cast(1.5 as float), '-50'),
(-1, -70, -7.5, -7.5BD, cast(-7.5 as float),null ),
(-80, 1, -10.2, -10.2BD, cast(-10.2 as float), '-1' ),
(9, -90, 2.58, 2.58BD, cast(2.58 as float), '0'),
(-100, 10, -5.8, -5.8BD, cast(-5.8 as float), '-90');

Query data from the mf_math_fun_t table. Sample statement:

select * from mf_math_fun_t;
-- The following result is returned: 
+------------+-------------+-------------+--------------+------------+-------------+
| int_data   | bigint_data | double_data | decimal_data | float_data | string_data |
+------------+-------------+-------------+--------------+------------+-------------+
| NULL       | -10         | 0.525       | 0.525        | 0.525      | 10          |
| -20        | NULL        | -0.1        | -0.1         | -0.1       | -10         |
| 0          | -1          | NULL        | 20.45        | -1.0       | 30          |
| -40        | 4           | 0.89        | NULL         | 0.89       | -30         |
| 5          | -50         | -1.0        | -1           | NULL       | 50          |
| -60        | 6           | 1.5         | 1.5          | 1.5        | -50         |
| -1         | -70         | -7.5        | -7.5         | -7.5       | NULL        |
| -80        | 1           | -10.2       | -10.2        | -10.2      | -1          |
| 9          | -90         | 2.58        | 2.58         | 2.58       | 0           |
| -100       | 10          | -5.8        | -5.8         | -5.8       | -90         |
| NULL       | -10         | 0.525       | 0.525        | 0.525      | 10          |
| -20        | NULL        | -0.1        | -0.1         | -0.1       | -10         |
| 0          | -1          | NULL        | 20.45        | -1.0       | 30          |
| -40        | 4           | 0.89        | NULL         | 0.89       | -30         |
| 5          | -50         | -1.0        | -1           | NULL       | 50          |
| -60        | 6           | 1.5         | 1.5          | 1.5        | -50         |
| -1         | -70         | -7.5        | -7.5         | -7.5       | NULL        |
| -80        | 1           | -10.2       | -10.2        | -10.2      | -1          |
| 9          | -90         | 2.58        | 2.58         | 2.58       | 0           |
| -100       | 10          | -5.8        | -5.8         | -5.8       | -90         |
+------------+-------------+-------------+--------------+------------+-------------+

ABS

Syntax
```
bigint|double|decimal abs(<number>)
```
Description
This function calculates the absolute value of number.
Parameters
number: required. The value is of the DOUBLE, BIGINT, or DECIMAL type. If the input value is of the STRING type, the value is implicitly converted into a value of the DOUBLE type before calculation.

Note If the input value is of the BIGINT type and is greater than the maximum value of the BIGINT type, a value of the DOUBLE type is returned. However, the precision may be lost.
Return value
The data type of the return value depends on the data type of the input parameter. The return value varies based on the following rules:
- If number is of the DOUBLE, BIGINT, or DECIMAL type, a value of the same type is returned.
- If number is of the STRING type, a value of the DOUBLE type is returned.
- If number is set to null, null is returned.

Examples

Examples of static data

-- The value null is returned. 
select abs(null);
-- The value 1 is returned. 
select abs(-1);
-- The value 1.2 is returned. 
select abs(-1.2);
-- The value 2.0 is returned. 
select abs("-2");
-- The value 1.2232083745629837E32 is returned. 
select abs(122320837456298376592387456923748);
-- Calculate the absolute value of the id field in tbl1. The following example shows the usage of an ABS function in SQL statements. Other built-in functions, except window functions and aggregate functions, are used in a similar way. 
select abs(id) from tbl1;

Example of table data

Calculate the absolute value based on the Sample data. Sample statement:

select abs(bigint_data) as bigint_new, abs(double_data) as double_new, abs(decimal_data) as decimal_new, abs(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+------------+------------+-------------+------------+
| bigint_new | double_new | decimal_new | string_new |
+------------+------------+-------------+------------+
| 10         | 0.525      | 0.525       | 10.0       |
| NULL       | 0.1        | 0.1         | 10.0       |
| 1          | NULL       | 20.45       | 30.0       |
| 4          | 0.89       | NULL        | 30.0       |
| 50         | 1.0        | 1           | 50.0       |
| 6          | 1.5        | 1.5         | 50.0       |
| 70         | 7.5        | 7.5         | NULL       |
| 1          | 10.2       | 10.2        | 1.0        |
| 90         | 2.58       | 2.58        | 0.0        |
| 10         | 5.8        | 5.8         | 90.0       |
+------------+------------+-------------+------------+

ACOS

Syntax
```
double|decimal acos(<number>)
```
Description
This function calculates the arccosine of number.
Parameters
number: required. The value ranges from -1 to 1. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.
Return value
The data type of the return value depends on the data type of the input parameter. The value ranges from 0 to π. The return value varies based on the following rules:
- If number is of the DOUBLE or DECIMAL type, a value of the same type is returned.
- If number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.
- If the value of number does not fall into the range from -1 to 1, null is returned.
- If the value of number is null, null is returned.

Examples

Examples of static data

-- The value 0.5155940062460905 is returned. 
select acos("0.87");
-- The value 1.5707963267948966 is returned. 
select acos(0);
-- The value null is returned. 
select acos(null);

Example of table data

Calculate the arccosine based on the Sample data. Sample statement:

select acos(bigint_data) as bigint_new, acos(double_data) as double_new, acos(decimal_data) as decimal_new, acos(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+-------------------+--------------------+--------------------+---------------------+
| bigint_new        | double_new         | decimal_new        | string_new          |
+-------------------+--------------------+--------------------+---------------------+
| NULL              | 1.0180812136981134 | 1.0180812136981134 | NULL                |
| NULL              | 1.6709637479564565 | 1.6709637479564565 | NULL                |
| 3.141592653589793 | NULL               | NULL               | NULL                |
| NULL              | 0.4734511572720662 | NULL               | NULL                |
| NULL              | 3.141592653589793  | 3.141592653589793  | NULL                |
| NULL              | NULL               | NULL               | NULL                |
| NULL              | NULL               | NULL               | NULL                |
| 0.0               | NULL               | NULL               | 3.141592653589793   |
| NULL              | NULL               | NULL               | 1.5707963267948966  |
| NULL              | NULL               | NULL               | NULL                |
+-------------------+--------------------+--------------------+---------------------+

ASIN

Syntax
```
double|decimal asin(<number>)
```
Description
This function calculates the arcsine of number.
Parameters
number: required. The value ranges from -1 to 1. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.
Return value
The data type of the return value depends on the data type of the input parameter. The value ranges from -π/2 to π/2. The return value varies based on the following rules:
- If number is of the DOUBLE or DECIMAL type, a value of the same type is returned.
- If number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.
- If the value of number does not fall into the range from -1 to 1, null is returned.
- If the value of number is null, null is returned.

Examples

Examples of static data

-- The value 1.5707963267948966 is returned. 
select asin(1);
-- The value -1.5707963267948966 is returned. 
select asin(-1);
-- The value null is returned. 
select asin(null);

Example of table data

Calculate the arcsine based on the Sample data. Sample statement:

select asin(bigint_data) as bigint_new, asin(double_data) as double_new, asin(decimal_data) as decimal_new, asin(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+--------------------+---------------------+---------------------+---------------------+
| bigint_new         | double_new          | decimal_new         | string_new          |
+--------------------+---------------------+---------------------+---------------------+
| NULL               | 0.5527151130967832  | 0.5527151130967832  | NULL                |
| NULL               | -0.1001674211615598 | -0.1001674211615598 | NULL                |
| -1.5707963267948966| NULL                | NULL                | NULL                |
| NULL               | 1.0973451695228305  | NULL                | NULL                |
| NULL               | -1.5707963267948966 | -1.5707963267948966 | NULL                |
| NULL               | NULL                | NULL                | NULL                |
| NULL               | NULL                | NULL                | NULL                |
| 1.5707963267948966 | NULL                | NULL                | -1.5707963267948966 |
| NULL               | NULL                | NULL                | 0.0                 |
| NULL               | NULL                | NULL                | NULL                |
+--------------------+---------------------+---------------------+---------------------+

ATAN

Syntax
```
double atan(<number>)
```
Description
This function calculates the arctangent of number.
Parameters
number: required. The value is of the DOUBLE type. If the input value is of the STRING, BIGINT, or DECIMAL type, the value is implicitly converted into a value of the DOUBLE type before calculation.
Return value
A value of the DOUBLE type is returned. The value ranges from -π/2 to π/2. If the value of number is null, null is returned.

Examples

Examples of static data

-- The value 0.7853981633974483 is returned. 
select atan(1);
-- The value -0.7853981633974483 is returned. 
select atan(-1);
-- The value null is returned. 
select atan(null);

Example of table data

Calculate the arctangent based on the Sample data. Sample statement:

select atan(bigint_data) as bigint_new, atan(double_data) as double_new, atan(decimal_data) as decimal_new, atan(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+---------------------+----------------------+----------------------+---------------------+
| bigint_new          | double_new           | decimal_new          | string_new          |
+---------------------+----------------------+----------------------+---------------------+
| -1.4711276743037347 | 0.483447001567199    | 0.483447001567199    | 1.4711276743037347  |
| NULL                | -0.09966865249116204 | -0.09966865249116204 | -1.4711276743037347 |
| -0.7853981633974483 | NULL                 | 1.521935491607842    | 1.5374753309166493  |
| 1.3258176636680326  | 0.7272626879966904   | NULL                 | -1.5374753309166493 |
| -1.550798992821746  | -0.7853981633974483  | -0.7853981633974483  | 1.550798992821746   |
| 1.4056476493802699  | 0.982793723247329    | 0.982793723247329    | -1.550798992821746  |
| -1.5565115842075    | -1.4382447944982226  | -1.4382447944982226  | NULL                |
| 0.7853981633974483  | -1.473069419436178   | -1.473069419436178   | -0.7853981633974483 |
| -1.5596856728972892 | 1.2010277920014796   | 1.2010277920014796   | 0.0                 |
| 1.4711276743037347  | -1.4000611153196139  | -1.4000611153196139  | -1.5596856728972892 |
+---------------------+----------------------+----------------------+---------------------+

CEIL

Syntax
```
bigint ceil(<value>)
```
Description
This function rounds up value and returns the nearest integer.
Parameters
value: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.
Return value
A value of the BIGINT type is returned. If the value of number is null, null is returned.

Examples

Examples of static data

-- The value 2 is returned. 
select ceil(1.1);
-- The value -1 is returned. 
select ceil(-1.1);
-- The value null is returned. 
select ceil(null);

Example of table data

Round up a number based on the Sample data. Sample statement:

select ceil(bigint_data) as bigint_new, ceil(double_data) as double_new, ceil(decimal_data) as decimal_new, ceil(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+------------+------------+-------------+------------+
| bigint_new | double_new | decimal_new | string_new |
+------------+------------+-------------+------------+
| -10        | 1          | 1           | 10         |
| NULL       | 0          | 0           | -10        |
| -1         | NULL       | 21          | 30         |
| 4          | 1          | NULL        | -30        |
| -50        | -1         | -1          | 50         |
| 6          | 2          | 2           | -50        |
| -70        | -7         | -7          | NULL       |
| 1          | -10        | -10         | -1         |
| -90        | 3          | 3           | 0          |
| 10         | -5         | -5          | -90        |
+------------+------------+-------------+------------+

CONV

Syntax

string conv(<input>, bigint <from_base>, bigint <to_base>)

Description
This function converts a number from one number system to another.
Parameters
- input: required. The value is the integer you want to convert, which is of the STRING type. If the input value is of the BIGINT or DOUBLE type, the value is implicitly converted into a value of the STRING type before calculation.
- from_base and to_base: required. The values of these parameters are decimal numbers. The values can be 2, 8, 10, or 16. If the input value is of the STRING or DOUBLE type, it is implicitly converted into a value of the BIGINT type before calculation.
Return value
A value of the STRING type is returned. The return value varies based on the following rules:
- If the value of input, from_base, or to_base is null, null is returned.
- The conversion process runs at 64-bit precision. If an overflow occurs, null is returned.
- If the value of input is a negative value, null is returned. If the value of input is a decimal, the value is converted into an integer before the conversion of number systems. The decimal part is left out.

Examples

Examples of static data

-- The value 12 is returned. 
select conv('1100', 2, 10);
-- The value C is returned. 
select conv('1100', 2, 16);
-- The value 171 is returned. 
select conv('ab', 16, 10);
-- The value AB is returned. 
select conv('ab', 16, 16);
-- The value null is returned. 
select conv('1100', null, 10);

Example of table data

Convert a number into a binary value based on the Sample data. Sample statement:

select conv(bigint_data,10,2) as bigint_new, conv(double_data,10,2) as double_new, conv(decimal_data,10,2) as decimal_new, conv(string_data,10,2) as string_new from mf_math_fun_t;

The following result is returned:

+------------+------------+-------------+------------+
| bigint_new | double_new | decimal_new | string_new |
+------------+------------+-------------+------------+
| NULL       | 0          | 0           | 1010       |
| NULL       | NULL       | NULL        | NULL       |
| NULL       | NULL       | 10100       | 11110      |
| 100        | 0          | NULL        | NULL       |
| NULL       | NULL       | NULL        | 110010     |
| 110        | 1          | 1           | NULL       |
| NULL       | NULL       | NULL        | NULL       |
| 1          | NULL       | NULL        | NULL       |
| NULL       | 10         | 10          | 0          |
| 1010       | NULL       | NULL        | NULL       |
+------------+------------+-------------+------------+

COS

Syntax
```
double|decimal cos(<number>)
```
Description
This function calculates the cosine of number, which is a radian value.
Parameters
number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before calculation.
Return value
A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:
- If number is of the DOUBLE or DECIMAL type, a value of the same type is returned.
- If number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.
- If the value of number is null, null is returned.

Examples

Examples of static data

-- The value 2.6794896585028633e-8 is retuned. 
select cos(3.1415926/2);
-- The value -0.9999999999999986 is returned. 
select cos(3.1415926);
-- The value null is returned. 
select cos(null);

Example of table data

Calculate the cosine based on the Sample data. Sample statement:

select cos(bigint_data) as bigint_new, cos(double_data) as double_new, cos(decimal_data) as decimal_new, cos(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+---------------------+--------------------+----------------------+---------------------+
| bigint_new          | double_new         | decimal_new          | string_new          |
+---------------------+--------------------+----------------------+---------------------+
| -0.8390715290764524 | 0.8653239416229412 | 0.8653239416229412   | -0.8390715290764524 |
| NULL                | 0.9950041652780258 | 0.9950041652780258   | -0.8390715290764524 |
| 0.5403023058681398  | NULL               | -0.02964340851507803 | 0.15425144988758405 |
| -0.6536436208636119 | 0.6294120265736969 | NULL                 | 0.15425144988758405 |
| 0.9649660284921133  | 0.5403023058681398 | 0.5403023058681398   | 0.9649660284921133  |
| 0.960170286650366   | 0.0707372016677029 | 0.0707372016677029   | 0.9649660284921133  |
| 0.6333192030862999  | 0.3466353178350258 | 0.3466353178350258   | NULL                |
| 0.5403023058681398  | -0.7142656520272003| -0.7142656520272003  | 0.5403023058681398  |
| -0.4480736161291701 | -0.8464080412157756| -0.8464080412157756  | 1.0                 |
| -0.8390715290764524 | 0.8855195169413189 | 0.8855195169413189   | -0.4480736161291701 |
+---------------------+--------------------+----------------------+---------------------+

COSH

Syntax
```
double|decimal cosh(<number>)
```
Description
This function calculates the hyperbolic cosine of number.
Parameters
number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.
Return value
A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:
- If number is of the DOUBLE or DECIMAL type, a value of the same type is returned.
- If number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.
- If the value of number is null, null is returned.

Examples

Examples of static data

-- The value 2.5091784169949913 is returned. 
select cosh(3.1415926/2);
-- The value null is returned. 
select cosh(null);

Example of table data

Calculate the hyperbolic cosine based on the Sample data. Sample statement:

select cosh(bigint_data) as bigint_new, cosh(double_data) as double_new, cosh(decimal_data) as decimal_new, cosh(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+-----------------------+--------------------+--------------------+----------------------+
| bigint_new            | double_new         | decimal_new        | string_new           |
+-----------------------+--------------------+--------------------+----------------------+
| 11013.232920103324    | 1.1410071063729532 | 1.1410071063729532 | 11013.232920103324   |
| NULL                  | 1.0050041680558035 | 1.0050041680558035 | 11013.232920103324   |
| 1.5430806348152437    | NULL               | 380445243.96844625 | 5343237290762.231    |
| 27.308232836016487    | 1.42289270202111   | NULL               | 5343237290762.231    |
| 2.592352764293536e21  | 1.5430806348152437 | 1.5430806348152437 | 2.592352764293536e21 |
| 201.7156361224559     | 2.352409615243247  | 2.352409615243247  | 2.592352764293536e21 |
| 1.2577193354595834e30 | 904.0214837702166  | 904.0214837702166  | NULL                 |
| 1.5430806348152437    | 13451.593055733929 | 13451.593055733929 | 1.5430806348152437   |
| 6.102016471589204e38  | 6.636456081840602  | 6.636456081840602  | 1.0                  |
| 11013.232920103324    | 165.151293732197   | 165.151293732197   | 6.102016471589204e38 |
+-----------------------+--------------------+--------------------+----------------------+

COT

Syntax
```
double|decimal cot(<number>)
```
Description
This function calculates the cotangent of number, which is a radian value.
Parameters
number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.
Return value
A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:
- If number is of the DOUBLE or DECIMAL type, a value of the same type is returned.
- If number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.
- If the value of number is null, null is returned.

Examples

Examples of static data

-- The value 2.6794896585028643E-8 is returned. 
select cot(3.1415926/2);
-- The value null is returned. 
select cot(null);

Example of table data

Calculate the cotangent based on the Sample data. Sample statement:

select cot(bigint_data) as bigint_new, cot(double_data) as double_new, cot(decimal_data) as decimal_new, cot(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+-----------------------+--------------------+--------------------+----------------------+
| bigint_new            | double_new         | decimal_new        | string_new           |
+-----------------------+--------------------+--------------------+----------------------+
| -1.54235104535692     | 1.7264594764178474 | 1.7264594764178474 | 1.54235104535692     |
| NULL                  | -9.966644423259238 | -9.966644423259238 | -1.54235104535692    |
| -0.6420926159343308   | NULL               | -0.02965644140592836| -0.15611995216165922|
| 0.8636911544506167    | 0.8099792954471944 | NULL               | 0.15611995216165922  |
| 3.6778144508505695    | -0.6420926159343308| -0.6420926159343308| -3.6778144508505695  |
| -3.436353004180128    | 0.07091484430265245| 0.07091484430265245| 3.6778144508505695   |
| -0.8183574478651038   | -0.36954725630901636| -0.36954725630901636| NULL               |
| 0.6420926159343308    | -1.0205622016180353 | -1.0205622016180353 | -0.6420926159343308|
| 0.5012027833801532    | -1.5893944776331337 | -1.5893944776331337 | 1.0                |
| 1.54235104535692      | 1.9059736612916494  | 1.9059736612916494  | 0.5012027833801532 |
+-----------------------+---------------------+---------------------+--------------------+

EXP

Syntax
```
double|decimal exp(<number>)
```
Description
This function calculates the exponential value of number.
Parameters
number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.
Return value
A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:
- If number is of the DOUBLE or DECIMAL type, a value of the same type is returned.
- If number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.
- If the value of number is null, null is returned.

Examples

Examples of static data

-- The value 4.810477252069109 is returned. 
select exp(3.1415926/2);
-- The value null is returned. 
select exp(null);

Example of table data

Calculate the exponential value based on the Sample data. Sample statement:

select exp(bigint_data) as bigint_new, exp(double_data) as double_new, exp(decimal_data) as decimal_new, exp(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+-------------------------+-------------------------+-------------------------+-------------------------+
| bigint_new              | double_new              | decimal_new             | string_new              |
+-------------------------+-------------------------+-------------------------+-------------------------+
| 0.000045399929762484854 | 1.6904588483790914      | 1.6904588483790914      | 22026.465794806718      |
| NULL                    | 0.9048374180359595      | 0.9048374180359595      | 0.000045399929762484854 |
| 0.36787944117144233     | NULL                    | 760890487.9368925       | 10686474581524.463      |
| 54.598150033144236      | 2.4351296512898744      | NULL                    | 9.357622968840175e-14   |
| 1.9287498479639178e-22  | 0.36787944117144233     | 0.36787944117144233     | 5.184705528587072e21    |
| 403.4287934927351       | 4.4816890703380645      | 4.4816890703380645      | 1.9287498479639178e-22  |
| 3.975449735908647e-31   | 0.0005530843701478336   | 0.0005530843701478336   | NULL                    |
| 2.718281828459045       | 0.000037170318684126734 | 0.000037170318684126734 | 0.36787944117144233     |
| 8.194012623990515e-40   | 13.197138159658358      | 13.197138159658358      | 1.0                     |
| 22026.465794806718      | 0.0030275547453758153   | 0.0030275547453758153   | 8.194012623990515e-40   |
+-------------------------+-------------------------+-------------------------+-------------------------+

FLOOR

Syntax
```
bigint floor(<number>)
```
Description
This function rounds down number and returns the nearest integer that is no greater than the value of number.
Parameters
number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.
Return value
A value of the BIGINT type is returned. If the value of number is null, null is returned.

Examples

Examples of static data

-- The value 1 is returned. 
select floor(1.2);
-- The value 0 is returned. 
select floor(0.1);
-- The value -2 is returned. 
select floor(-1.2);
-- The value -1 is returned. 
select floor(-0.1);
-- The value 0 is returned. 
select floor(0.0);
-- The value 0 is returned. 
select floor(-0.0);
-- The value null is returned. 
select floor(null);

Example of table data

Round down a number based on the Sample data. Sample statement:

select floor(bigint_data) as bigint_new, floor(double_data) as double_new, floor(decimal_data) as decimal_new, floor(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+------------+------------+-------------+------------+
| bigint_new | double_new | decimal_new | string_new |
+------------+------------+-------------+------------+
| -10        | 0          | 0           | 10         |
| NULL       | -1         | -1          | -10        |
| -1         | NULL       | 20          | 30         |
| 4          | 0          | NULL        | -30        |
| -50        | -1         | -1          | 50         |
| 6          | 1          | 1           | -50        |
| -70        | -8         | -8          | NULL       |
| 1          | -11        | -11         | -1         |
| -90        | 2          | 2           | 0          |
| 10         | -6         | -6          | -90        |
+------------+------------+-------------+------------+

LN

Syntax
```
double|decimal ln(<number>)
```
Description
This function calculates the natural logarithm of number.
Parameters
number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.
Return value
A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:
- If number is of the DOUBLE or DECIMAL type, a value of the same type is returned.
- If the value of number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.
- If the value of number is a negative value or 0, null is returned.
- If the value of number is null, null is returned.

Examples

Examples of static data

-- The value 1.144729868791239 is returned. 
select ln(3.1415926);
-- The value null is returned. 
select ln(null);

Example of table data

Calculate the natural logarithm value based on the Sample data. Sample statement:

select ln(bigint_data) as bigint_new, ln(double_data) as double_new, ln(decimal_data) as decimal_new, ln(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+--------------------+----------------------+---------------------+---------------------+
| bigint_new         | double_new           | decimal_new         | string_new          |
+--------------------+----------------------+---------------------+---------------------+
| NULL               | -0.6443570163905132  | -0.6443570163905132 | 2.302585092994046   |
| NULL               | NULL                 | NULL                | NULL                |
| NULL               | NULL                 | 3.017982882488811   | 3.4011973816621555  |
| 1.3862943611198906 | -0.11653381625595151 | NULL                | NULL                |
| NULL               | NULL                 | NULL                | 3.912023005428146   |
| 1.791759469228055  | 0.4054651081081644   | 0.4054651081081644  | NULL                |
| NULL               | NULL                 | NULL                | NULL                |
| 0.0                | NULL                 | NULL                | NULL                |
| NULL               | 0.9477893989335261   | 0.9477893989335261  | NULL                |
| 2.302585092994046  | NULL                 | NULL                | NULL                |
+--------------------+----------------------+---------------------+---------------------+

LOG

Syntax
```
double log(<base>, <x>)
```
Description
This function calculates the logarithm of x whose base number is base.
Parameters
- base: required. The base number. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.
- x: required. The value for which the logarithm is calculated. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.
Return value
A value of the DOUBLE type is returned. The return value varies based on the following rules:
- If the value of base or x is null, null is returned.
- If the value of base or x is a negative value or 0, null is returned.
- If the value of base is 1, null is returned. The value 1 causes division by zero.

Examples

Examples of static data

-- The value 4.0 is returned. 
select log(2, 16);
-- The value null is returned. 
select log(2, null);

Example of table data

Calculate the logarithm value of a column whose base number is 2 based on the Sample data. Sample statement:

select log(2,bigint_data) as bigint_new, log(2,double_data) as double_new, log(2,decimal_data) as decimal_new, log(2,string_data) as string_new from mf_math_fun_t;

The following result is returned:

+--------------------+----------------------+--------------------+--------------------+
| bigint_new         | double_new           | decimal_new        | string_new         |
+--------------------+----------------------+--------------------+--------------------+
| NULL               | -0.929610672108602   | -0.929610672108602 | 3.3219280948873626 |
| NULL               | NULL                 | NULL               | NULL               |
| NULL               | NULL                 | 4.354028938054387  | 4.906890595608519  |
| 2.0                | -0.16812275880832692 | NULL               | NULL               |
| NULL               | NULL                 | NULL               | 5.643856189774724  |
| 2.584962500721156  | 0.5849625007211562   | 0.5849625007211562 | NULL               |
| NULL               | NULL                 | NULL               | NULL               |
| 0.0                | NULL                 | NULL               | NULL               |
| NULL               | 1.3673710656485296   | 1.3673710656485296 | NULL               |
| 3.3219280948873626 | NULL                 | NULL               | NULL               |
+--------------------+----------------------+--------------------+--------------------+

POW

Syntax
```
double|decimal pow(<x>, <y>)
```
Description
This function calculates the yth power of x, namely, x^y.
Parameters
- x: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.
- y: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.
Return value
A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:
- If x or y is of the DOUBLE or DECIMAL type, a value of the same type is returned.
- If x or y is of the STRING or BIGINT type, a value of the DOUBLE type is returned.
- If the value of x or y is null, null is returned.

Examples

Examples of static data

-- The value 65536.0 is returned. 
select pow(2, 16);
-- The value null is returned. 
select pow(2, null);

Example of table data

Calculate the second power of values in a column based on the Sample data. Sample statement:

select pow(bigint_data, 2) as bigint_new, pow(double_data, 2) as double_new, pow(decimal_data, 2) as decimal_new, pow(string_data, 2) as string_new from mf_math_fun_t;

The following result is returned:

+------------+----------------------+----------------------+------------+
| bigint_new | double_new           | decimal_new          | string_new |
+------------+----------------------+----------------------+------------+
| 100.0      | 0.275625             | 0.275625             | 100.0      |
| NULL       | 0.010000000000000002 | 0.010000000000000002 | 100.0      |
| 1.0        | NULL                 | 418.2025             | 900.0      |
| 16.0       | 0.7921               | NULL                 | 900.0      |
| 2500.0     | 1.0                  | 1.0                  | 2500.0     |
| 36.0       | 2.25                 | 2.25                 | 2500.0     |
| 4900.0     | 56.25                | 56.25                | NULL       |
| 1.0        | 104.03999999999999   | 104.03999999999999   | 1.0        |
| 8100.0     | 6.6564000000000005   | 6.6564000000000005   | 0.0        |
| 100.0      | 33.64                | 33.64                | 8100.0     |
+------------+----------------------+----------------------+------------+

RAND

Syntax
```
double rand(bigint <seed>)
```
Description
This function returns a random number of the DOUBLE type. The value ranges from 0 to 1.
Parameters
seed: optional. The value is of the BIGINT type. This parameter specifies the random seed that determines the starting point in generating random numbers.

Note You can use seed to determine the random number sequence. After seed is determined, the return value of this function is fixed. If the execution environment is the same and the seed value remains unchanged, the return value is the same. If you need to return different results, you must modify the seed value.
Return value
A value of the DOUBLE type is returned.

Examples

-- The value 4.7147460303803655E-4 is returned. 
select rand();
-- The value 0.7308781907032909 is returned. 
select rand(1);

ROUND

Syntax

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

Description
This function returns a number rounded to the specified decimal place.
Parameters
- number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.
- decimal_places: optional. The value is a constant of the BIGINT type. This parameter specifies the decimal place to which the number is rounded. If this parameter is not specified, the number is rounded to the ones place. The default value is 0.
  
  Note The value of decimal_places can be negative. A negative value indicates counting from the decimal point to the left, and the decimal part is excluded. If decimal_places exceeds the length of the integer part, 0 is returned.
Return value
A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:
- If number is of the DOUBLE or DECIMAL type, a value of the same type is returned.
- If number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.
- If decimal_places is not of the BIGINT type, an error is returned.
- If the value of number or decimal_places is null, null is returned.

Examples

Examples of static data

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

Example of table data

Return numbers that are rounded to the specified decimal place in a column based on the Sample data. Sample statement:

select round(bigint_data, 1) as bigint_new, round(double_data, 2) as double_new, round(decimal_data, 1) as decimal_new, round(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+------------+------------+-------------+------------+
| bigint_new | double_new | decimal_new | string_new |
+------------+------------+-------------+------------+
| -10.0      | 0.53       | 0.5         | 10.0       |
| NULL       | -0.1       | -0.1        | -10.0      |
| -1.0       | NULL       | 20.5        | 30.0       |
| 4.0        | 0.89       | NULL        | -30.0      |
| -50.0      | -1.0       | -1          | 50.0       |
| 6.0        | 1.5        | 1.5         | -50.0      |
| -70.0      | -7.5       | -7.5        | NULL       |
| 1.0        | -10.2      | -10.2       | -1.0       |
| -90.0      | 2.58       | 2.6         | 0.0        |
| 10.0       | -5.8       | -5.8        | -90.0      |
+------------+------------+-------------+------------+

SIN

Syntax
```
double|decimal sin(<number>)
```
Description
This function calculates the sine of number, which is a radian value.
Parameters
number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.
Return value
A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:
- If number is of the DOUBLE or DECIMAL type, a value of the same type is returned.
- If number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.
- If the value of number is null, null is returned.

Examples

Examples of static data

-- The value -0.3048106211022167 is returned. 
select sin(60);
-- The value null is returned. 
select sin(null);

Example of table data

Calculate the sine of values in a column based on the Sample data. Sample statement:

select sin(bigint_data) as bigint_new, sin(double_data) as double_new, sin(decimal_data) as decimal_new, sin(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+----------------------+----------------------+----------------------+----------------------+
| bigint_new           | double_new           | decimal_new          | string_new           |
+----------------------+----------------------+----------------------+----------------------+
| 0.5440211108893698   | 0.5012130046737979   | 0.5012130046737979   | -0.5440211108893698  |
| NULL                 | -0.09983341664682815 | -0.09983341664682815 | 0.5440211108893698   |
| -0.8414709848078965  | NULL                 | 0.9995605376022045   | -0.9880316240928618  |
| -0.7568024953079282  | 0.7770717475268238   | NULL                 | 0.9880316240928618   |
| 0.26237485370392877  | -0.8414709848078965  | -0.8414709848078965  | -0.26237485370392877 |
| -0.27941549819892586 | 0.9974949866040544   | 0.9974949866040544   | 0.26237485370392877  |
| -0.7738906815578891  | -0.9379999767747389  | -0.9379999767747389  | NULL                 |
| 0.8414709848078965   | 0.6998746875935423   | 0.6998746875935423   | -0.8414709848078965  |
| -0.8939966636005579  | 0.5325349075556212   | 0.5325349075556212   | 0.0                  |
| -0.5440211108893698  | 0.46460217941375737  | 0.46460217941375737  | -0.8939966636005579  |
+----------------------+----------------------+----------------------+----------------------+

SINH

Syntax
```
double|decimal sinh(<number>)
```
Description
This function calculates the hyperbolic sine of number.
Parameters
number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.
Return value
A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:
- If number is of the DOUBLE or DECIMAL type, a value of the same type is returned.
- If number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.
- If the value of number is null, null is returned.

Examples

Examples of static data

-- The value 5.343237290762231E12 is returned. 
select sinh(30);
-- The value null is returned. 
select sinh(null);

Example of table data

Calculate the hyperbolic sine of values in a column based on the Sample data. Sample statement:

select sinh(bigint_data) as bigint_new, sinh(double_data) as double_new, sinh(decimal_data) as decimal_new, sinh(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+------------------------+----------------------+----------------------+-----------------------+
| bigint_new             | double_new           | decimal_new          | string_new            |
+------------------------+----------------------+----------------------+-----------------------+
| -11013.232874703393    | 0.5494517420061382   | 0.5494517420061382   | 11013.232874703393    |
| NULL                   | -0.10016675001984403 | -0.10016675001984403 | -11013.232874703393   |
| -1.1752011936438014    | NULL                 | 380445243.96844625   | 5343237290762.231     |
| 27.28991719712775      | 1.0122369492687646   | NULL                 | -5343237290762.231    |
| -2.592352764293536e21  | -1.1752011936438014  | -1.1752011936438014  | 2.592352764293536e21  |
| 201.71315737027922     | 2.1292794550948173   | 2.1292794550948173   | -2.592352764293536e21 |
| -1.2577193354595834e30 | -904.0209306858466   | -904.0209306858466   | NULL                  |
| 1.1752011936438014     | -13451.593018563612  | -13451.593018563612  | -1.1752011936438014   |
| -6.102016471589204e38  | 6.560682077817757    | 6.560682077817757    | 0.0                   |
| 11013.232874703393     | -165.1482661774516   | -165.1482661774516   | -6.102016471589204e38 |
+------------------------+----------------------+----------------------+-----------------------+

SQRT

Syntax
```
double|decimal sqrt(<number>)
```
Description
This function calculates the square root of number.
Parameters
number: required. The value is of the DOUBLE or DECIMAL type. The value must be greater than 0. If the value is less than 0, null is returned. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.
Return value
A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:
- If number is of the DOUBLE or DECIMAL type, a value of the same type is returned.
- If number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.
- If the value of number is null, null is returned.

Examples

Examples of static data

-- The value 2.0 is returned. 
select sqrt(4);
-- The value null is returned. 
select sqrt(null);

Example of table data

Calculate the square root of values in a column based on the Sample data. Sample statement:

select sqrt(bigint_data) as bigint_new, sqrt(double_data) as double_new, sqrt(decimal_data) as decimal_new, sqrt(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+--------------------+--------------------+-------------------+--------------------+
| bigint_new         | double_new         | decimal_new       | string_new         |
+--------------------+--------------------+-------------------+--------------------+
| NULL               | 0.724568837309472  | 0.724568837309472 | 3.1622776601683795 |
| NULL               | NULL               | NULL              | NULL               |
| NULL               | NULL               | 4.522167621838006 | 5.477225575051661  |
| 2.0                | 0.9433981132056604 | NULL              | NULL               |
| NULL               | NULL               | NULL              | 7.0710678118654755 |
| 2.449489742783178  | 1.224744871391589  | 1.224744871391589 | NULL               |
| NULL               | NULL               | NULL              | NULL               |
| 1.0                | NULL               | NULL              | NULL               |
| NULL               | 1.606237840420901  | 1.606237840420901 | 0.0                |
| 3.1622776601683795 | NULL               | NULL              | NULL               |
+--------------------+--------------------+-------------------+--------------------+

TAN

Syntax
```
double|decimal tan(<number>)
```
Description
This function calculates the tangent of number, which is a radian value.
Parameters
number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.
Return value
A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:
- If number is of the DOUBLE or DECIMAL type, a value of the same type is returned.
- If number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.
- If the value of number is null, null is returned.

Examples

Examples of static data

-- The value -6.405331196646276 is returned. 
select tan(30);
-- The value null is returned. 
select tan(null);

Example of table data

Calculate the tangent of values in a column based on the Sample data. Sample statement:

select tan(bigint_data) as bigint_new, tan(double_data) as double_new, tan(decimal_data) as decimal_new, tan(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+----------------------+----------------------+----------------------+----------------------+
| bigint_new           | double_new           | decimal_new          | string_new           |
+----------------------+----------------------+----------------------+----------------------+
| -0.6483608274590866  | 0.5792200822893652   | 0.5792200822893652   | 0.6483608274590866   |
| NULL                 | -0.10033467208545055 | -0.10033467208545055 | -0.6483608274590866  |
| -1.5574077246549023  | NULL                 | -33.71948732190433   | -6.405331196646276   |
| 1.1578212823495775   | 1.2345994590490046   | NULL                 | 6.405331196646276    |
| 0.27190061199763077  | -1.5574077246549023  | -1.5574077246549023  | -0.27190061199763077 |
| -0.29100619138474915 | 14.101419947171719   | 14.101419947171719   | 0.27190061199763077  |
| -1.2219599181369434  | -2.706013866772691   | -2.706013866772691   | NULL                 |
| 1.5574077246549023   | -0.979852083895097   | -0.979852083895097   | -1.5574077246549023  |
| 1.995200412208242    | -0.6291704256385503  | -0.6291704256385503  | 0.0                  |
| 0.6483608274590866   | 0.5246662219468002   | 0.5246662219468002   | 1.995200412208242    |
+----------------------+----------------------+----------------------+----------------------+

TANH

Syntax
```
double|decimal tanh(<number>)
```
Description
This function calculates the hyperbolic tangent of number.
Parameters
number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.
Return value
A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:
- If number is of the DOUBLE or DECIMAL type, a value of the same type is returned.
- If number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.
- If the value of number is null, null is returned.

Examples

Examples of static data

-- The value 1.0 is returned. 
select tanh(30);
-- The value null is returned. 
select tanh(null);

Example of table data

Calculate the hyperbolic tangent of values in a column based on the Sample data. Sample statement:

select tanh(bigint_data) as bigint_new, tanh(double_data) as double_new, tanh(decimal_data) as decimal_new, tanh(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+---------------------+----------------------+----------------------+---------------------+
| bigint_new          | double_new           | decimal_new          | string_new          |
+---------------------+----------------------+----------------------+---------------------+
| -0.9999999958776927 | 0.48154979836430806  | 0.48154979836430806  | 0.9999999958776927  |
| NULL                | -0.09966799462495582 | -0.09966799462495582 | -0.9999999958776927 |
| -0.7615941559557649 | NULL                 | 1.0                  | 1.0                 |
| 0.999329299739067   | 0.7113937318189625   | NULL                 | -1.0                |
| -1.0                | -0.7615941559557649  | -0.7615941559557649  | 1.0                 |
| 0.9999877116507956  | 0.9051482536448664   | 0.9051482536448664   | -1.0                |
| -1.0                | -0.9999993881955461  | -0.9999993881955461  | NULL                |
| 0.7615941559557649  | -0.9999999972367348  | -0.9999999972367348  | -0.7615941559557649 |
| -1.0                | 0.9885821584459533   | 0.9885821584459533   | 0.0                 |
| 0.9999999958776927  | -0.9999816679925603  | -0.9999816679925603  | -1.0                |
+---------------------+----------------------+----------------------+---------------------+

TRUNC

Syntax

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

Description
This function truncates the input value of number to the specified decimal place.

Note If the Hive-compatible data type edition is used, this function is not a mathematical function. It is used to convert a date value. For more information about the related date function, see DATETRUNC. You must set the data type edition of your MaxCompute project based on your business requirements. For more information about data type editions, see Data type editions.
Parameters
- number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.
- decimal_places: optional. The value is a constant of the BIGINT type. This parameter specifies the position where the number is truncated. If this parameter is not specified, the number is truncated to the ones place. decimal_places can be a negative value, which indicates that the number is truncated from the decimal point to the left and the decimal part is left out. If decimal_places exceeds the length of the integer part, 0 is returned.
Return value
A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:
- If number is of the DOUBLE or DECIMAL type, a value of the same type is returned.
- If number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.
- If decimal_places is not of the BIGINT type, an error is returned.
- If the value of number or decimal_places is null, null is returned.
Note
- If a value of the DOUBLE type is returned, the return value may not be properly displayed. This issue exists in all systems. For more information, see trunc(125.815,1) in the following examples.
- The number is filled with zeros from the specified position.

Examples

Examples of static data

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

Example of table data

Truncate numbers in a column to the specified decimal place based on the Sample data. Sample statement:

select trunc(bigint_data, 1) as bigint_new, trunc(double_data,1) as double_new, trunc(decimal_data, 1) as decimal_new, trunc(string_data, 1) as string_new from mf_math_fun_t;

The following result is returned:

+------------+---------------------+-------------+------------+
| bigint_new | double_new          | decimal_new | string_new |
+------------+---------------------+-------------+------------+
| -10.0      | 0.5                 | 0.5         | 10.0       |
| NULL       | -0.1                | -0.1        | -10.0      |
| -1.0       | NULL                | 20.4        | 30.0       |
| 4.0        | 0.8                 | NULL        | -30.0      |
| -50.0      | -1.0                | -1          | 50.0       |
| 6.0        | 1.5                 | 1.5         | -50.0      |
| -70.0      | -7.5                | -7.5        | NULL       |
| 1.0        | -10.200000000000001 | -10.2       | -1.0       |
| -90.0      | 2.5                 | 2.5         | 0.0        |
| 10.0       | -5.800000000000001  | -5.8        | -90.0      |
+------------+---------------------+-------------+------------+

BIN

Syntax
```
string bin(<number>)
```
Description
This function calculates the binary code of number. This function is an extension function of MaxCompute V2.0.
Parameters
number: required. The value is of the BIGINT, INT, SMALLINT, or TINYINT type.
Return value
A value of the STRING type is returned. The return value varies based on the following rules:
- If number is not of the BIGINT, INT, SMALLINT, or TINYINT type, an error is returned.
- If the value of number is 0, 0 is returned.
- If the value of number is null, null is returned.

Examples

Examples of static data

-- The value 0 is returned. 
select bin(0);
-- The value null is returned. 
select bin(null);
-- The value 1100 is returned. 
select bin(12);

Example of table data

Calculate the binary code of values in the int_data and bigint_data columns based on the Sample data. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit this command along with SQL statements. 
set odps.sql.type.system.odps2=true;
select bin(int_data) as int_new, bin(bigint_data) as bigint_new from mf_math_fun_t;

The following result is returned:

+----------------------------------------------------------------------------+------------------------------------------------------------------+
| int_new                                                                    | bigint_new                                                       |
+----------------------------------------------------------------------------+------------------------------------------------------------------+
| NULL                                                                       | 1111111111111111111111111111111111111111111111111111111111110110 |
| 1111111111111111111111111111111111111111111111111111111111101100           | NULL                                                             |
| 0                                                                          | 1111111111111111111111111111111111111111111111111111111111111111 |
| 1111111111111111111111111111111111111111111111111111111111011000           | 100                                                              |
| 101                                                                        | 1111111111111111111111111111111111111111111111111111111111001110 |
| 1111111111111111111111111111111111111111111111111111111111000100           | 110                                                              |
| 1111111111111111111111111111111111111111111111111111111111111111           | 1111111111111111111111111111111111111111111111111111111110111010 |
| 1111111111111111111111111111111111111111111111111111111110110000           | 1                                                                |
| 1001                                                                       | 1111111111111111111111111111111111111111111111111111111110100110 |
| 1111111111111111111111111111111111111111111111111111111110011100           | 1010                                                             |
+----------------------------------------------------------------------------+------------------------------------------------------------------+

CBRT

Syntax
```
double cbrt(<number>)
```
Description
This function calculates the cube root of number. This function is an extension function of MaxCompute V2.0.
Parameters
number: required. The value is of the BIGINT, INT, SMALLINT, TINYINT, DOUBLE, FLOAT, or STRING type.
Return value
A value of the DOUBLE type is returned. The return value varies based on the following rules:
- If number is not of the BIGINT, INT, SMALLINT, TINYINT, DOUBLE, FLOAT, or STRING type, an error is returned.
- If the value of number is null, null is returned.

Examples

Examples of static data

-- The value 2.0 is returned. 
select cbrt(8);
-- The value null is returned. 
select cbrt(null);

Example of table data

Calculate the cube root of values in columns except the decimal_data column based on the Sample data. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit this command along with SQL statements. 
set odps.sql.type.system.odps2=true;
select cbrt(int_data) as int_new, cbrt(bigint_data) as bigint_new, cbrt(double_data) as double_new, cbrt(float_data) as float_new, cbrt(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+---------------------+---------------------+----------------------+---------------------+---------------------+
| int_new             | bigint_new          | double_new           | float_new           | string_new          |
+---------------------+---------------------+----------------------+---------------------+---------------------+
| NULL                | -2.1544346900318834 | 0.806714323012272    | 0.8067143108004823  | 2.1544346900318834  |
| -2.7144176165949063 | NULL                | -0.46415888336127786 | -0.46415888566678   | -2.1544346900318834 |
| 0.0                 | -1.0                | NULL                 | -1.0                | 3.107232505953859   |
| -3.4199518933533937 | 1.5874010519681996  | 0.9619001716077046   | 0.961900166454112   | -3.107232505953859  |
| 1.7099759466766968  | -3.6840314986403864 | -1.0                 | NULL                | 3.6840314986403864  |
| -3.9148676411688634 | 1.8171205928321394  | 1.1447142425533317   | 1.1447142425533317  | -3.6840314986403864 |
| -1.0                | -4.121285299808557  | -1.9574338205844317  | -1.9574338205844317 | NULL                |
| -4.308869380063767  | 1.0                 | -2.168702885250197   | -2.1687028717323127 | -1.0                |
| 2.080083823051904   | -4.481404746557165  | 1.3715339700741747   | 1.3715339565548288  | 0.0                 |
| -4.641588833612778  | 2.1544346900318834  | -1.7967017791430528  | -1.7967017988380907 | -4.481404746557165  |
+---------------------+---------------------+----------------------+---------------------+---------------------+

CORR

Syntax
```
double corr(<col1>, <col2>)
```
Description
This function calculates the Pearson correlation coefficient for two columns of data. This function is an extension function of MaxCompute V2.0.
Parameters
col1 and col2: required. The names of the two columns for which the Pearson correlation coefficient is calculated. The value is of the DOUBLE, BIGINT, INT, SMALLINT, TINYINT, FLOAT, or DECIMAL type. Data in the col1 and col2 columns can be of different data types.
Return value
A value of the DOUBLE type is returned. If an input column has a null value in a row, the row is not involved in the calculation.
Examples
Calculate the Pearson correlation coefficient for the double_data and float_data columns of data based on the Sample data. Sample statement:
```
select corr(double_data,float_data) from mf_math_fun_t;
```
The value 1.0 is returned.

DEGREES

Syntax
```
double degrees(<number>) 
```
Description
This function converts a radian value into a degree.
Parameters
number: required. The value is of the DOUBLE, BIGINT, INT, SMALLINT, TINYINT, FLOAT, DECIMAL, or STRING type. This function is an extension function of MaxCompute V2.0.
Return value
A value of the DOUBLE type is returned. If the value of number is null, null is returned.

Examples

Examples of static data

-- The value 90.0 is returned. 
select degrees(1.5707963267948966);
-- The value 0.0 is returned. 
select degrees(0);
-- The value null is returned. 
select degrees(null);

Example of table data

Obtain all degrees that correspond to all columns based on the Sample data. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit this command along with SQL statements. 
set odps.sql.type.system.odps2=true;
select degrees(int_data) as int_new, degrees(bigint_data) as bigint_new, degrees(double_data) as double_new, degrees(decimal_data) as decimal_new, degrees(float_data) as float_new, degrees(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| int_new             | bigint_new          | double_new          | decimal_new         | float_new           | string_new          |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| NULL                | -572.9577951308232  | 30.08028424436822   | 30.08028424436822   | 30.080282878330387  | 572.9577951308232   |
| -1145.9155902616465 | NULL                | -5.729577951308232  | -5.729577951308232  | -5.729578036685597  | -572.9577951308232  |
| 0.0                 | -57.29577951308232  | NULL                | 1171.6986910425335  | -57.29577951308232  | 1718.8733853924698  |
| -2291.831180523293  | 229.1831180523293   | 50.99324376664326   | NULL                | 50.99324294702057   | -1718.8733853924698 |
| 286.4788975654116   | -2864.7889756541163 | -57.29577951308232  | -57.29577951308232  | NULL                | 2864.7889756541163  |
| -3437.7467707849396 | 343.77467707849394  | 85.94366926962348   | 85.94366926962348   | 85.94366926962348   | -2864.7889756541163 |
| -57.29577951308232  | -4010.7045659157625 | -429.71834634811745 | -429.71834634811745 | -429.71834634811745 | NULL                |
| -4583.662361046586  | 57.29577951308232   | -584.4169510334397  | -584.4169510334397  | -584.416940105137   | -57.29577951308232  |
| 515.662015617741    | -5156.620156177409  | 147.8231111437524   | 147.8231111437524   | 147.82310677243132  | 0.0                 |
| -5729.5779513082325 | 572.9577951308232   | -332.31552117587745 | -332.31552117587745 | -332.31553210418014 | -5156.620156177409  |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+

E

Syntax
```
double e()
```
Description
This function calculates the value of e. This function is an extension function of MaxCompute V2.0.
Return value
A value of the DOUBLE type is returned.

Examples

-- The value 2.718281828459045 is returned. 
select e();

FACTORIAL

Syntax
```
bigint factorial(<number>)
```
Description
This function calculates the factorial of number. This function is an extension function of MaxCompute V2.0.
Parameters
number: required. The value is of the BIGINT, INT, SMALLINT, or TINYINT type and ranges from 0 to 20.
Return value
A value of the BIGINT type is returned. The return value varies based on the following rules:
- If the value of number is 0, 1 is returned.
- If the value of number is null or a value that does not fall into the range from 0 to 20, null is returned.

Examples

Examples of static data

-- The value 120 is returned. 5!=5*4*3*2*1=120
select factorial(5); 
-- The value 1 is returned. 
select factorial(0); 
-- The value null is returned. 
select factorial(null);

Example of table data

Calculate the factorial of values in the int_data and bigint_data columns based on the Sample data. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit this command along with SQL statements. 
set odps.sql.type.system.odps2=true;
select factorial(int_data) as int_new, factorial(bigint_data) as bigint_new from mf_math_fun_t;

The following result is returned.

+------------+------------+
| int_new    | bigint_new |
+------------+------------+
| NULL       | NULL       |
| NULL       | NULL       |
| 1          | NULL       |
| NULL       | 24         |
| 120        | NULL       |
| NULL       | 720        |
| NULL       | NULL       |
| NULL       | 1          |
| 362880     | NULL       |
| NULL       | 3628800    |
+------------+------------+

FORMAT_NUMBER

Syntax

string format_number(float|double|decimal <expr1>, <expr2>)

Description
This function converts a number into a string in the specified format. This function is an extension function of MaxCompute V2.0.
Parameters
- expr1: required. This parameter specifies the expression that you want to format. The value is of the DOUBLE, BIGINT, INT, SMALLINT, TINYINT, FLOAT, DECIMAL, or STRING type.
- expr2: required. This parameter specifies the format of the expression after the conversion. It can specify the number of decimal places that you want to retain. It can also be expressed in a format similar to #,###,###.##.
Return value
A value of the STRING type is returned. The return value varies based on the following rules:
- If expr2 is greater than 0, the value is rounded to the specified place after the decimal point.
- If expr2 is equal to 0, the value has no decimal point or decimal part.
- If expr2 is less than 0 or greater than 340, an error is returned.
- If the value of expr1 or expr2 is null, null is returned.

Examples

Examples of static data

-- The value 5.230 is returned. 
select format_number(5.230134523424545456,3);
-- The value 12,332.123 is returned. 
select format_number(12332.123456, '#,###,###,###.###');
-- The value null is returned. 
select format_number(null,3);

Example of table data

Retain values in all columns to the specified number of decimal places based on the Sample data. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit this command along with SQL statements. 
set odps.sql.type.system.odps2=true;
select format_number(int_data, 1) as int_new, format_number(bigint_data, 1) as bigint_new, format_number(double_data, 2) as double_new, format_number(decimal_data, 1) as decimal_new, format_number(float_data, 0) as float_new, format_number(string_data, 1) as string_new from mf_math_fun_t;

The following result is returned:

+---------+------------+------------+-------------+-----------+------------+
| int_new | bigint_new | double_new | decimal_new | float_new | string_new |
+---------+------------+------------+-------------+-----------+------------+
| NULL    | -10.0      | 0.53       | 0.5         | 1         | 10.0       |
| -20.0   | NULL       | -0.10      | -0.1        | -0        | -10.0      |
| 0.0     | -1.0       | NULL       | 20.5        | -1        | 30.0       |
| -40.0   | 4.0        | 0.89       | NULL        | 1         | -30.0      |
| 5.0     | -50.0      | -1.00      | -1.0        | NULL      | 50.0       |
| -60.0   | 6.0        | 1.50       | 1.5         | 2         | -50.0      |
| -1.0    | -70.0      | -7.50      | -7.5        | -8        | NULL       |
| -80.0   | 1.0        | -10.20     | -10.2       | -10       | -1.0       |
| 9.0     | -90.0      | 2.58       | 2.6         | 3         | 0.0        |
| -100.0  | 10.0       | -5.80      | -5.8        | -6        | -90.0      |
+---------+------------+------------+-------------+-----------+------------+

HEX

Syntax
```
string hex(<number>) 
```
Description
This function converts a number or a string into a hexadecimal number. This function is an extension function of MaxCompute V2.0.
Parameters
number: required. The value is of the DOUBLE, BIGINT, INT, SMALLINT, TINYINT, FLOAT, DECIMAL, or STRING type.
Return value
A value of the STRING type is returned. The return value varies based on the following rules:
- If the value of number is not 0 or null, a value of the STRING type is returned.
- If the value of number is 0, 0 is returned.
- If the value of number is null, null is returned.

Examples

Examples of static data

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

Example of table data

Convert numbers in all columns into hexadecimal numbers based on the Sample data. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit this command along with SQL statements. 
set odps.sql.type.system.odps2=true;
select hex(int_data) as int_new, hex(bigint_data) as bigint_new, hex(double_data) as double_new, hex(decimal_data) as decimal_new, hex(float_data) as float_new, hex(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+------------------+------------------+------------+-------------+------------+------------+
| int_new          | bigint_new       | double_new | decimal_new | float_new  | string_new |
+------------------+------------------+------------+-------------+------------+------------+
| NULL             | FFFFFFFFFFFFFFF6 | 302E353235 | 302E353235  | 302E353235 | 3130       |
| FFFFFFFFFFFFFFEC | NULL             | 2D302E31   | 2D302E31    | 2D302E31   | 2D3130     |
| 0                | FFFFFFFFFFFFFFFF | NULL       | 32302E3435  | 2D31       | 3330       |
| FFFFFFFFFFFFFFD8 | 4                | 302E3839   | NULL        | 302E3839   | 2D3330     |
| 5                | FFFFFFFFFFFFFFCE | 2D312E30   | 2D31        | NULL       | 3530       |
| FFFFFFFFFFFFFFC4 | 6                | 312E35     | 312E35      | 312E35     | 2D3530     |
| FFFFFFFFFFFFFFFF | FFFFFFFFFFFFFFBA | 2D372E35   | 2D372E35    | 2D372E35   | NULL       |
| FFFFFFFFFFFFFFB0 | 1                | 2D31302E32 | 2D31302E32  | 2D31302E32 | 2D31       |
| 9                | FFFFFFFFFFFFFFA6 | 322E3538   | 322E3538    | 322E3538   | 30         |
| FFFFFFFFFFFFFF9C | A                | 2D352E38   | 2D352E38    | 2D352E38   | 2D3930     |
+------------------+------------------+------------+-------------+------------+------------+

LOG2

Syntax
```
double log2(<number>)
```
Description
This function calculates the logarithm of number with the base number of 2. This function is an extension function of MaxCompute V2.0.
Parameters
number: required. The value is of the DOUBLE, BIGINT, INT, SMALLINT, TINYINT, FLOAT, DECIMAL, or STRING type.
Return value
A value of the DOUBLE type is returned. If the value of number is 0, a negative value, or null, null is returned.

Examples

Examples of static data

-- The value null is returned. 
select log2(null);
-- The value null is returned. 
select log2(0);
-- The value 3.0 is returned. 
select log2(8);

Example of table data

Calculate the logarithm of all columns with the base number of 2 based on the Sample data. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit this command along with SQL statements. 
set odps.sql.type.system.odps2=true;
select log2(int_data) as int_new, log2(bigint_data) as bigint_new, log2(double_data) as double_new, log2(decimal_data) as decimal_new, log2(float_data) as float_new, log2(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+--------------------+--------------------+----------------------+--------------------+----------------------+--------------------+
| int_new            | bigint_new         | double_new           | decimal_new        | float_new            | string_new         |
+--------------------+--------------------+----------------------+--------------------+----------------------+--------------------+
| NULL               | NULL               | -0.929610672108602   | -0.929610672108602 | -0.9296107376258038  | 3.3219280948873626 |
| NULL               | NULL               | NULL                 | NULL               | NULL                 | NULL               |
| NULL               | NULL               | NULL                 | 4.354028938054387  | NULL                 | 4.906890595608519  |
| NULL               | 2.0                | -0.16812275880832692 | NULL               | -0.16812278199699915 | NULL               |
| 2.321928094887362  | NULL               | NULL                 | NULL               | NULL                 | 5.643856189774724  |
| NULL               | 2.584962500721156  | 0.5849625007211562   | 0.5849625007211562 | 0.5849625007211562   | NULL               |
| NULL               | NULL               | NULL                 | NULL               | NULL                 | NULL               |
| NULL               | 0.0                | NULL                 | NULL               | NULL                 | NULL               |
| 3.1699250014423126 | NULL               | 1.3673710656485296   | 1.3673710656485296 | 1.367371022986166    | NULL               |
| NULL               | 3.3219280948873626 | NULL                 | NULL               | NULL                 | NULL               |
+--------------------+--------------------+----------------------+--------------------+----------------------+--------------------+

LOG10

Syntax
```
double log10(<number>)
```
Description
This function calculates the logarithm of number with the base number of 10. This function is an extension function of MaxCompute V2.0.
Parameters
number: required. The value is of the DOUBLE, BIGINT, INT, SMALLINT, TINYINT, FLOAT, DECIMAL, or STRING type.
Return value
A value of the DOUBLE type is returned. If the value of number is 0, a negative value, or null, null is returned.

Examples

Examples of static data

-- The value null is returned. 
select log10(null);
-- The value null is returned. 
select log10(0);
-- The value 0.9030899869919435 is returned. 
select log10(8);

Example of table data

Calculate the logarithm of values in all columns with the base number of 10 based on the Sample data. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit this command along with SQL statements. 
set odps.sql.type.system.odps2=true;
select log10(int_data) as int_new, log10(bigint_data) as bigint_new, log10(double_data) as double_new, log10(decimal_data) as decimal_new, log10(float_data) as float_new, log10(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+--------------------+--------------------+---------------------+---------------------+-----------------------+--------------------+
| int_new            | bigint_new         | double_new          | decimal_new         | float_new             | string_new         |
+--------------------+--------------------+---------------------+---------------------+-----------------------+--------------------+
| NULL               | NULL               | -0.2798406965940431 | -0.2798406965940431 | -0.27984071631668606  | 1.0                |
| NULL               | NULL               | NULL                | NULL                | NULL                  | NULL               |
| NULL               | NULL               | NULL                | 1.3106933123433606  | NULL                  | 1.4771212547196624 |
| NULL               | 0.6020599913279623 | -0.0506099933550872 | NULL                | -0.050610000335573106 | NULL               |
| 0.6989700043360187 | NULL               | NULL                | NULL                | NULL                  | 1.6989700043360185 |
| NULL               | 0.7781512503836435 | 0.17609125905568124 | 0.17609125905568124 | 0.17609125905568124   | NULL               |
| NULL               | NULL               | NULL                | NULL                | NULL                  | NULL               |
| NULL               | 0.0                | NULL                | NULL                | NULL                  | NULL               |
| 0.9542425094393249 | NULL               | 0.4116197059632301  | 0.4116197059632301  | 0.411619693120579     | NULL               |
| NULL               | 1.0                | NULL                | NULL                | NULL                  | NULL               |
+--------------------+--------------------+---------------------+---------------------+-----------------------+--------------------+

PI

Syntax
```
double pi()
```
Description
This function calculates the value of π. This function is an extension function of MaxCompute V2.0.
Return value
A value of the DOUBLE type is returned.

Examples

-- The value 3.141592653589793 is returned. 
select pi();

RADIANS

Syntax
```
double radians(<number>)
```
Description
This function converts a degree into a radian value. This function is an extension function of MaxCompute V2.0.
Parameters
number: required. The value is of the DOUBLE, BIGINT, INT, SMALLINT, TINYINT, FLOAT, DECIMAL, or STRING type.
Return value
A value of the DOUBLE type is returned. If the value of number is null, null is returned.

Examples

Examples of static data

-- The value 1.5707963267948966 is returned. 
select radians(90);
-- The value 0.0 is returned. 
select radians(0);
-- The value null is returned. 
select radians(null);

Example of table data

Convert values in all columns into a radian value based on the Sample data. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit this command along with SQL statements. 
set odps.sql.type.system.odps2=true;
select radians(int_data) as int_new, radians(bigint_data) as bigint_new, radians(double_data) as double_new, radians(decimal_data) as decimal_new, radians(float_data) as float_new, radians(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+-----------------------+-----------------------+------------------------+------------------------+------------------------+-----------------------+
| int_new               | bigint_new            | double_new             | decimal_new            | float_new              | string_new            |
+-----------------------+-----------------------+------------------------+------------------------+------------------------+-----------------------+
| NULL                  | -0.17453292519943295  | 0.00916297857297023    | 0.00916297857297023    | 0.009162978156851308   | 0.17453292519943295   |
| -0.3490658503988659   | NULL                  | -0.0017453292519943296 | -0.0017453292519943296 | -0.0017453292780017621 | -0.17453292519943295  |
| 0.0                   | -0.017453292519943295 | NULL                   | 0.3569198320328404     | -0.017453292519943295  | 0.5235987755982988    |
| -0.6981317007977318   | 0.06981317007977318   | 0.015533430342749534   | NULL                   | 0.015533430093078181   | -0.5235987755982988   |
| 0.08726646259971647   | -0.8726646259971648   | -0.017453292519943295  | -0.017453292519943295  | NULL                   | 0.8726646259971648    |
| -1.0471975511965976   | 0.10471975511965977   | 0.02617993877991494    | 0.02617993877991494    | 0.02617993877991494    | -0.8726646259971648   |
| -0.017453292519943295 | -1.2217304763960306   | -0.1308996938995747    | -0.1308996938995747    | -0.1308996938995747    | NULL                  |
| -1.3962634015954636   | 0.017453292519943295  | -0.17802358370342158   | -0.17802358370342158   | -0.17802358037447025   | -0.017453292519943295 |
| 0.15707963267948966   | -1.5707963267948966   | 0.045029494701453704   | 0.045029494701453704   | 0.04502949336987316    | 0.0                   |
| -1.7453292519943295   | 0.17453292519943295   | -0.10122909661567112   | -0.10122909661567112   | -0.10122909994462247   | -1.5707963267948966   |
+-----------------------+-----------------------+------------------------+------------------------+------------------------+-----------------------+

SIGN

Syntax
```
double sign(<number>)
```
Description
This function returns the sign of the input value. This function is an extension function of MaxCompute V2.0.
Parameters
number: required. The value is of the DOUBLE, BIGINT, INT, SMALLINT, TINYINT, FLOAT, DECIMAL, or STRING type.
Return value
A value of the DOUBLE type is returned. The return value varies based on the following rules:
- If the value of number is a positive value, 1.0 is returned.
- If the value of number is a negative value, -1.0 is returned.
- If the value of number is 0, 0.0 is returned.
- If the value of number is null, null is returned.

Examples

Examples of static data

-- The value -1.0 is returned. 
select sign(-2.5);
-- The value 1.0 is returned. 
select sign(2.5);
-- The value 0.0 is returned. 
select sign(0);
-- The value null is returned. 
select sign(null);

Example of table data

Obtain the sign of numbers in all columns based on the Sample data. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit this command along with SQL statements. 
set odps.sql.type.system.odps2=true;
select sign(int_data) as int_new, sign(bigint_data) as bigint_new, sign(double_data) as double_new, sign(decimal_data) as decimal_new, sign(float_data) as float_new, sign(string_data) as string_new from mf_math_fun_t;

The following result is returned:

+------------+------------+------------+-------------+------------+------------+
| int_new    | bigint_new | double_new | decimal_new | float_new  | string_new |
+------------+------------+------------+-------------+------------+------------+
| NULL       | -1.0       | 1.0        | 1           | 1.0        | 1.0        |
| -1.0       | NULL       | -1.0       | -1          | -1.0       | -1.0       |
| 0.0        | -1.0       | NULL       | 1           | -1.0       | 1.0        |
| -1.0       | 1.0        | 1.0        | NULL        | 1.0        | -1.0       |
| 1.0        | -1.0       | -1.0       | -1          | NULL       | 1.0        |
| -1.0       | 1.0        | 1.0        | 1           | 1.0        | -1.0       |
| -1.0       | -1.0       | -1.0       | -1          | -1.0       | NULL       |
| -1.0       | 1.0        | -1.0       | -1          | -1.0       | -1.0       |
| 1.0        | -1.0       | 1.0        | 1           | 1.0        | 0.0        |
| -1.0       | 1.0        | -1.0       | -1          | -1.0       | -1.0       |
+------------+------------+------------+-------------+------------+------------+

SHIFTLEFT

Syntax

int shiftleft(tinyint|smallint|int <number1>, int <number2>)
bigint shiftleft(bigint <number1>, int <number2>)

Description
This function shifts a value left by a specific number of places (<<). This function is an extension function of MaxCompute V2.0.
Parameters
- number1: required. The value is of the TINYINT, SMALLINT, INT, or BIGINT type.
- number2: required. The value is of the INT type.
Return value
A value of the INT or BIGINT type is returned. The return value varies based on the following rules:
- If number1 is not of the TINYINT, SMALLINT, INT, or BIGINT type, an error is returned.
- If number2 is not of the INT type, an error is returned.
- If the value of number1 or number2 is null, null is returned.

Examples

Examples of static data

-- The value 4 is returned. The following statement shifts the binary value of 1 two places to the left (1<<2,0001 shifted to be 0100). 
select shiftleft(1,2); 
-- The value 32 is returned. The following statement shifts the binary value of 4 three places to the left (4<<3,0100 shifted to be 100000). 
select shiftleft(4,3);
-- The value null is returned. 
select shiftleft(null,2);

Example of table data

Shift numbers in the int_data and bigint_data columns left by a specific number of places based on the Sample data. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit this command along with SQL statements. 
set odps.sql.type.system.odps2=true;
select shiftleft(int_data, 1) as int_new, shiftleft(bigint_data, 1) as bigint_new from mf_math_fun_t;

The following result is returned:

+---------+------------+
| int_new | bigint_new |
+---------+------------+
| NULL    | -20        |
| -40     | NULL       |
| 0       | -2         |
| -80     | 8          |
| 10      | -100       |
| -120    | 12         |
| -2      | -140       |
| -160    | 2          |
| 18      | -180       |
| -200    | 20         |
+---------+------------+

SHIFTRIGHT

Syntax

int shiftright(tinyint|smallint|int <number1>, int <number2>)
bigint shiftright(bigint <number1>, int <number2>)

Description
This function shifts a value right by a specific number of places (>>). This function is an extension function of MaxCompute V2.0.
Parameters
- number1: required. The value is of the TINYINT, SMALLINT, INT, or BIGINT type.
- number2: required. The value is of the INT type.
Return value
A value of the INT or BIGINT type is returned. The return value varies based on the following rules:
- If number1 is not of the TINYINT, SMALLINT, INT, or BIGINT type, an error is returned.
- If number2 is not of the INT type, an error is returned.
- If the value of number1 or number2 is null, null is returned.

Examples

Examples of static data

-- The value 1 is returned. The following statement shifts the binary value of 4 two places to the right (4>>2,0100 shifted to be 0001). 
select shiftright(4,2);
-- The value 4 is returned. The following statement shifts the binary value of 32 three places to the right (32>>3,100000 shifted to be 0100). 
select shiftright(32,3);
-- The value null is returned. 
select shiftright(null,3);

Example of table data

Shift numbers in the int_data and bigint_data columns right by a specific number of places based on the Sample data. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit this command along with SQL statements. 
set odps.sql.type.system.odps2=true;
select shiftright(int_data, 1) as int_new, shiftright(bigint_data, 1) as bigint_new from mf_math_fun_t;

The following result is returned:

+---------+------------+
| int_new | bigint_new |
+---------+------------+
| NULL    | -5         |
| -10     | NULL       |
| 0       | -1         |
| -20     | 2          |
| 2       | -25        |
| -30     | 3          |
| -1      | -35        |
| -40     | 0          |
| 4       | -45        |
| -50     | 5          |
+---------+------------+

SHIFTRIGHTUNSIGNED

Syntax

int shiftrightunsigned(tinyint|smallint|int <number1>, int <number2>)
bigint shiftrightunsigned(bigint <number1>, int <number2>)

Description
This function shifts an unsigned value right by a specific number of places (>>>). This function is an extension function of MaxCompute V2.0.
Parameters
- number1: required. The value is an integer of the TINYINT, SMALLINT, INT, or BIGINT type.
- number2: required. The value is an integer of the INT type.
Return value
A value of the INT or BIGINT type is returned. The return value varies based on the following rules:
- If number1 is not of the TINYINT, SMALLINT, INT, or BIGINT type, an error is returned.
- If number2 is not of the INT type, an error is returned.
- If the value of number1 or number2 is null, null is returned.

Examples

Examples of static data

-- The value 2 is returned. The following statement shifts the binary unsigned value of 8 two places to the right (8>>>2,1000 shifted to be 0010). 
select shiftrightunsigned(8,2);
-- The value 1073741820 is returned. The following statement shifts the binary value of -14 two places to the right (-14>>>2, 11111111 11111111 11111111 11110010 shifted to be 00111111 11111111 11111111 11111100). 
select shiftrightunsigned(-14,2);
-- The value null is returned. 
select shiftrightunsigned(-14,null);

Example of table data

Shift unsigned values in the int_data and bigint_data columns right by a specific number of places based on the Sample data. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit this command along with SQL statements. 
set odps.sql.type.system.odps2=true;
select shiftrightunsigned(int_data, 1) as int_new, shiftrightunsigned(bigint_data, 1) as bigint_new from mf_math_fun_t;

The following result is returned:

+------------+---------------------+
| int_new    | bigint_new          |
+------------+---------------------+
| NULL       | 9223372036854775803 |
| 2147483638 | NULL                |
| 0          | 9223372036854775807 |
| 2147483628 | 2                   |
| 2          | 9223372036854775783 |
| 2147483618 | 3                   |
| 2147483647 | 9223372036854775773 |
| 2147483608 | 0                   |
| 4          | 9223372036854775763 |
| 2147483598 | 5                   |
+------------+---------------------+

UNHEX

Syntax
```
binary unhex(string <number>)
```
Description
This function converts a hexadecimal string into a string. This function is an extension function of MaxCompute V2.0.
Parameters
number: required. The value is a hexadecimal string.
Return value
A value of the BINARY type is returned. The return value varies based on the following rules:
- If the value of number is 0, an error is returned.
- If the value of number is null, null is returned.

Examples

-- The value abc is returned. 
select unhex('616263');
-- The value abc is returned. 
select unhex(616263);
-- The value null is returned. 
select unhex(null);

WIDTH_BUCKET

Syntax

width_bucket(numeric <expr>, numeric <min_value>, numeric <max_value>, int <num_buckets>)

Description
This function specifies the number of buckets and the minimum and maximum values of the acceptable range for a bucket. It allows you to construct equi-width buckets, in which the bucket range is divided into intervals that have an identical size. It returns the ID of the bucket into which the value of a specific expression falls. This function supports the following data types: DECIMAL(precision,scale) in the MaxCompute V2.0 data type edition, BIGINT, INT, FLOAT, DOUBLE, and DECIMAL. For more information, see MaxCompute V2.0 data type edition. This function is an extension function of MaxCompute V2.0.
Parameters
- expr: required. This parameter specifies the expression for which you want to identify the matching bucket ID.
- min_value: required. This parameter specifies the minimum value of the acceptable range for the bucket.
- max_value: required. This parameter specifies the maximum value of the acceptable range for the bucket. The value must be greater than min_value.
- num_buckets: required. This parameter specifies the number of buckets. The value must be greater than 0.
Return value
A value of the BIGINT type is returned. The value ranges from 0 to num_buckets plus 1. The return value varies based on the following rules:
- If the value of expr is less than that of min_value, 0 is returned.
- If the value of expr is greater than that of max_value, the value of num_buckets plus 1 is returned.
- If the value of expr is null, null is returned. In other cases, the ID of the bucket into which the value falls is returned. The bucket ID is calculated based on the following formula: Bucket ID = floor(num_buckets × (expr - min_value)/(max_value - min_value) + 1).
- If the value of min_value, max_value, or num_buckets is null, null is returned.

Examples

Example 1: The values of all input parameters are not null. Sample statement:

select key,value,width_bucket(value,100,500,5) as value_group
from values 
    (1,99),
    (2,100),
    (3,199),
    (4,200),
    (5,499),
    (6,500),
    (7,501),
    (8,NULL)
as t(key,value);

The following result is returned:

+-------+--------+-------------+
| key   | value  | value_group |
+-------+--------+-------------+
| 1     | 99     | 0           |
| 2     | 100    | 1           |
| 3     | 199    | 2           |
| 4     | 200    | 2           |
| 5     | 499    | 5           |
| 6     | 500    | 6           |
| 7     | 501    | 6           |
| 8     | \N     | \N          |
+-------+--------+-------------+

Example 2: The value of an input parameter is null. Sample statement:

select key,value,width_bucket(value,100,500,null) as value_group
from values
    (1,99),
    (2,100),
    (3,199),
    (4,200),
    (5,499),
    (6,500),
    (7,501),
    (8,NULL)
as t(key,value);

The following result is returned:

+------+-------+-------------+
| key  | value | value_group |
+------+-------+-------------+
| 1    | 99    | NULL        |
| 2    | 100   | NULL        |
| 3    | 199   | NULL        |
| 4    | 200   | NULL        |
| 5    | 499   | NULL        |
| 6    | 500   | NULL        |
| 7    | 501   | NULL        |
| 8    | NULL  | NULL        |
+------+-------+-------------+