This topic describes the mathematical functions supported by MaxCompute SQL. This topic also describes the syntax, parameters, and examples of the mathematical functions. You can select to use mathematical functions based on your business requirements to perform operations, such as data computation and data type conversion.
The following table lists mathematical functions that are supported by MaxCompute SQL.
Function | Description |
Calculates the absolute value. | |
Calculates the arccosine. | |
Calculates the arctangent of expr1 and expr2. | |
Calculates the arcsine. | |
Calculates the arctangent. | |
Calculates the binary code. | |
Calculates the cube root. | |
Rounds up a number and returns the nearest integer. | |
Converts a number from one number system to another. | |
Calculates the Pearson correlation coefficient. | |
Calculates the cosine. | |
Calculates the hyperbolic cosine. | |
Calculates the cotangent. | |
Converts a radian value into a degree. | |
Calculates the value of e. | |
Calculates the exponential value. | |
Calculates the factorial. | |
Filters the elements in an array. | |
Rounds down a number and returns the nearest integer. | |
Converts a number into a string in the specified format. | |
Converts an integer or a string into a hexadecimal number. | |
Checks whether the value of an expression is NaN. | |
Calculates the natural logarithm. | |
Calculates the logarithm. | |
Calculates the logarithm of a number whose base number is 10. | |
Calculates the logarithm of a number whose base number is 2. | |
Returns the negative value of an expression. | |
Calculates the value of π. | |
Returns the value of an expression. | |
Calculates the nth power of a value. | |
Converts a degree into a radian value. | |
Returns a random number. | |
Returns a value rounded to the specified decimal place. | |
Shifts a value left by a specific number of places. | |
Shifts a value right by a specific number of places. | |
Shifts an unsigned value right by a specific number of places. | |
Returns the sign of the input value. | |
Calculates the sine. | |
Calculates the hyperbolic sine. | |
Calculates the square root. | |
Calculates the tangent. | |
Calculates the hyperbolic tangent. | |
Truncates the input value to the specified decimal place. | |
This function converts a hexadecimal string into a string. | |
Returns the ID of the bucket into which the value of a specific expression falls. |
For more information about operators, such as the operator that is used to calculate remainders, see Arithmetic operators.
Precautions
MaxCompute V2.0 provides additional functions. If the functions that you use involve new data types that are supported in the MaxCompute V2.0 data type edition, you must execute the SET statement 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. To enable the MaxCompute V2.0 data type edition at the project level, 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 | 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
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.
NoteIf 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 the value of number is of the DOUBLE, BIGINT, or DECIMAL type, a value of the same type is returned.
If the value of number is of the STRING type, a value of the DOUBLE type is returned.
If the value of number is set to null, null is returned.
Examples
Examples of static data
-- The return value is null. select abs(null); -- The return value is 1. select abs(-1); -- The return value is 1.2. select abs(-1.2); -- The return value is 2.0. select abs("-2"); -- The return value is 1.2232083745629837 e32. 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
Calculates the arccosine of number.
Parameters
number: required. The value ranges from -1 to 1. A value 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 the value of 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 does not fall into the range of -1 to 1, null is returned. If the Hive-compatible data type edition is enabled, NaN is returned.
If the value of number is null, null is returned.
Examples
Examples of static data
-- The return value is 0.5155940062460905. select acos("0.87"); -- The return value is 1.5707963267948966. select acos(0); -- The return value is null. 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 | +-------------------+--------------------+--------------------+---------------------+
ATAN2
Syntax
double atan2(<expr1>, <expr2>)Description
Calculates the arctangent of expr1 and expr2.
Parameters
expr1: required. A value 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.
expr2: required. A value 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 return value ranges from
-π/2 to π/2. If the value of expr1 or expr2 is null, null is returned.Examples
-- The return value is 0.0. select atan2(0, 0);
ASIN
Syntax
double|decimal asin(<number>)Description
Calculates the arcsine of number.
Parameters
number: required. The value ranges from -1 to 1. A value 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 return value ranges from
-π/2 to π/2. The return value varies based on the following rules:If the value of 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 does not fall into the range of -1 to 1, null is returned. If the Hive-compatible data type edition is enabled, NaN is returned.
If the value of number is null, null is returned.
Examples
Examples of static data
-- The return value is 1.5707963267948966. select asin(1); -- The return value is 1.5707963267948966. select asin(-1); -- The return value is null. 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
Calculates the arctangent of number.
Parameters
number: required. A value 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 return value ranges from
-π/2 to π/2. If the value of number is null, null is returned.Examples
Examples of static data
-- The return value is 0.7853981633974483. select atan(1); -- The return value is -0.7853981633974483. select atan(-1); -- The return value is null. 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 | +---------------------+----------------------+----------------------+---------------------+
BIN
Syntax
string bin(<number>)Description
Calculates the binary code of number. This function is an additional 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 the value of 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 return value is 0. select bin(0); -- The return value is null. select bin(null); -- The return value is 1100. 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 the SQL statement. 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
Calculates the cube root of number. This function is an additional 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 the value of 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 return value is 2.0. select cbrt(8); -- The return value is null. 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 the SQL statement. 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 | +---------------------+---------------------+----------------------+---------------------+---------------------+
CEIL
Syntax
bigint ceil(<value>)Description
Rounds up value and returns the nearest integer.
Parameters
value: required. A value 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 return value is 2. select ceil(1.1); -- The return value is -1. select ceil(-1.1); -- The return value is null. 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
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, the value 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, it is converted into an integer before the conversion of number systems. The decimal part is left out.
Examples
Examples of static data
-- The return value is 12. select conv('1100', 2, 10); -- The return value is C. select conv('1100', 2, 16); -- The return value is 171. select conv('ab', 16, 10); -- The return value is AB. select conv('ab', 16, 16); -- The return value is null. 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 | +------------+------------+-------------+------------+
CORR
Syntax
double corr(<col1>, <col2>)Description
Calculates the Pearson correlation coefficient for two columns of data. This function is an additional 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 return value is 1.0.
COS
Syntax
double|decimal cos(<number>)Description
Calculates the cosine of number, which is a radian value.
Parameters
number: required. A value 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 the value of 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 null, null is returned.
Examples
Examples of static data
-- The return value is 2.6794896585028633e-8. select cos(3.1415926/2); -- The return value is -0.9999999999999986. select cos(3.1415926); -- The return value is null. 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
Calculates the hyperbolic cosine of number.
Parameters
number: required. A value 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 the value of 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 null, null is returned.
Examples
Examples of static data
-- The return value is 2.5091784169949913. select cosh(3.1415926/2); -- The return value is null. 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
Calculates the cotangent of number, which is a radian value.
Parameters
number: required. A value 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 the value of 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 null, null is returned.
Examples
Examples of static data
-- The return value is 2.6794896585028643E-8. select cot(3.1415926/2); -- The return value is null. select cot(null);Example of table data
Calculate the cotangent based on the sample data. Sample statement:
selectcot(bigint_data)asbigint_new,cot(double_data)asdouble_new,cot(decimal_data)asdecimal_new,cot(string_data)asstring_newfrommf_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 | +-----------------------+---------------------+---------------------+--------------------+
DEGREES
Syntax
double degrees(<number>)Description
Converts a radian value into a degree.
Parameters
number: required. A value of the DOUBLE, BIGINT, INT, SMALLINT, TINYINT, FLOAT, DECIMAL, or STRING type. This function is an additional 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 return value is 90.0. select degrees(1.5707963267948966); -- The return value is 0.0. select degrees(0); -- The return value is null. 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 the SQL statement. 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
Calculates the value of
e. This function is an additional function of MaxCompute V2.0.Return value
A value of the DOUBLE type is returned.
Examples
-- The return value is 2.718281828459045. select e();
EXP
Syntax
double|decimal exp(<number>)Description
Calculates the exponential value of number.
Parameters
number: required. A value 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 the value of 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 null, null is returned.
Examples
Examples of static data
-- The return value is 4.810477252069109. select exp(3.1415926/2); -- The return value is null. 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 | +-------------------------+-------------------------+-------------------------+-------------------------+
FACTORIAL
Syntax
bigint factorial(<number>)Description
Calculates the factorial of number. This function is an additional 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 return value is 120. 5!=5*4*3*2*1=120 select factorial(5); -- The return value is 1. select factorial(0); -- The return value is null. 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 the SQL statement. 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 | +------------+------------+
FILTER
Syntax
array<T> filter(array<T> <a>, function<T,boolean> <func>)Parameters
a: required. This parameter specifies an array.
Tinarray<T>specifies the data type of the elements in the array. The elements can be of any data type.func: required. This parameter specifies the built-in function, user-defined function, or expression that is used to filter the elements in Array a. The value must be of the same data type as the elements in Array a. The output result of the function or expression is of the BOOLEAN type.
Return value
A value of the ARRAY type is returned.
Examples
-- The return value is [2, 3]. select filter(array(1, 2, 3), x -> x > 1);NoteIn this example, the combination of a hyphen and a closing angle bracket (->) is used. For more information about how to use the combination of a hyphen and a closing angle bracket
(->)in Lambda functions, see Lambda functions.
FLOOR
Syntax
bigint floor(<number>)Description
Rounds down number and returns the nearest integer that is no greater than the value of number.
Parameters
number: required. A value 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 return value is 1. select floor(1.2); -- The return value is 0. select floor(0.1); -- The return value is -2. select floor(-1.2); -- The return value is -1. select floor(-0.1); -- The return value is 0. select floor(0.0); -- The return value is 0. select floor(-0.0); -- The return value is null. 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 | +------------+------------+-------------+------------+
FORMAT_NUMBER
Syntax
string format_number(float|double|decimal <expr1>, <expr2>)Description
Converts a number into a string in the specified format. This function is an additional function of MaxCompute V2.0.
Parameters
expr1: required. This parameter specifies the expression that you want to format. A value 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 return value is 5.230. select format_number(5.230134523424545456,3); -- The return value is 12,332.123. select format_number(12332.123456, '#,###,###,###.###'); -- The return value is null. 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 the SQL statement. 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
Converts a number or a string into a hexadecimal number. This function is an additional function of MaxCompute V2.0.
Parameters
number: required. A value 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 return value is 0. select hex(0); -- The return value is 616263. select hex('abc'); -- The return value is 11. select hex(17); -- The return value is 3137. 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 the SQL statement. 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 | +------------------+------------------+------------+-------------+------------+------------+
ISNAN
Syntax
boolean isnan(<expr>)Description
Checks whether the value of expr is NaN.
Parameters
expr: required. A value 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
If the value of expr is NaN, True is returned. Otherwise, False is returned.
If the value of expr is null, False is returned.
Examples
-- The return value is False. SELECT isnan(100.1);
LN
Syntax
double|decimal ln(<number>)Description
Calculates the natural logarithm of number.
Parameters
number: required. A value 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 the value of 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 return value is 1.144729868791239. select ln(3.1415926); -- The return value is null. 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
Calculates the logarithm of x whose base number is base.
Parameters
base: required. The base number. A value 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. A value 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 return value is 4.0. select log(2, 16); -- The return value is null. 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 | +--------------------+----------------------+--------------------+--------------------+
LOG10
Syntax
double log10(<number>)Description
Calculates the logarithm of number whose base number is 10. This function is an additional function of MaxCompute V2.0.
Parameters
number: required. A value 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 return value is null. select log10(null); -- The return value is null. select log10(0); -- The return value is 0.9030899869919435. select log10(8);Example of table data
Calculate the logarithms of values in all columns whose base number is 10 based on the sample data. Sample statement:
-- Enable the MaxCompute V2.0 data type edition. Commit this command along with the SQL statement. 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 | +--------------------+--------------------+---------------------+---------------------+-----------------------+--------------------+
LOG2
Syntax
double log2(<number>)Description
Calculates the logarithm of number whose base number is 2. This function is an additional function of MaxCompute V2.0.
Parameters
number: required. A value 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 return value is null. select log2(null); -- The return value is null. select log2(0); -- The return value is 3.0. select log2(8);Example of table data
Calculate the logarithms of all columns whose base number is 2 based on the sample data. Sample statement:
-- Enable the MaxCompute V2.0 data type edition. Commit this command along with the SQL statement. 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 | +--------------------+--------------------+----------------------+--------------------+----------------------+--------------------+
NEGATIVE
Syntax
TINYINT|SMALLINT|INT|BIGINT|DOUBLE|DECIMAL negative(TINYINT|SMALLINT|INT|BIGINT|DOUBLE|DECIMAL <expr>)Description
Returns the negative value of expr.
Parameters
expr: required. The value is an expression of the TINYINT, SMALLINT, INT, BIGINT, DOUBLE, or DECIMAL type.
Return value
The negative value of expr is returned.
Examples
-- The return value is -1. SELECT negative(1);
PI
Syntax
double pi()Description
Calculates the value of π. This function is an additional function of MaxCompute V2.0.
Return value
A value of the DOUBLE type is returned.
Examples
-- The return value is 3.141592653589793. select pi();
POSITIVE
Syntax
TINYINT|SMALLINT|INT|BIGINT|DOUBLE|DECIMAL positive(TINYINT|SMALLINT|INT|BIGINT|DOUBLE|DECIMAL <expr>)Description
Returns the value of expr.
Parameters
expr: required. The value is an expression of the TINYINT, SMALLINT, INT, BIGINT, DOUBLE, or DECIMAL type.
Return value
The value of expr is returned.
Examples
-- The return value is 1. SELECT positive(1);
POW
Syntax
double|decimal pow(<x>, <y>)Description
Calculates the yth power of x, namely,
x^y.Parameters
x: required. A value 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. A value 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 return value is 65536.0. select pow(2, 16); -- The return value is null. 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 | +------------+----------------------+----------------------+------------+
RADIANS
Syntax
double radians(<number>)Description
Converts a degree into a radian value. This function is an additional function of MaxCompute V2.0.
Parameters
number: required. A value 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 return value is 1.5707963267948966. select radians(90); -- The return value is 0.0. select radians(0); -- The return value is null. select radians(null);Example of table data
Convert values in all columns into radian values based on the sample data. Sample statement:
-- Enable the MaxCompute V2.0 data type edition. Commit this command along with the SQL statement. 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 | +-----------------------+-----------------------+------------------------+------------------------+------------------------+-----------------------+
RAND
Syntax
double rand(bigint <seed>)Description
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.
NoteYou 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.
You can add the following parameter before the SQL statement, the default value is
false.set odps.sql.executionengine.enable.rand.time.seed=true | false;When the parameter value is false, the RAND function uses the current InstanceId as a random number for initializing the seed, which ensures the idempotence of the function.
When the parameter value is true, the RAND function uses the current system time as a random number for initializing the seed. However, the RAND function is no longer idempotent and cannot be used as a Shuffle Key, because it results in different outcomes upon re-execution.
Return value
A value of the DOUBLE type is returned.
Examples
-- The return value is 4.7147460303803655E-4. select rand(); -- The return value is 0.7308781907032909. select rand(1);
ROUND
Syntax
double|decimal round(<number>[, bigint <decimal_places>])Description
Returns a number rounded to the specified decimal place.
Parameters
number: required. A value 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.
NoteThe 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 the value of 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 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 return value is 125.0. select round(125.315); -- The return value is 125.3. select round(125.315, 1); -- The return value is 125.32. select round(125.315, 2); -- The return value is 125.315. select round(125.315, 3); -- The return value is -125.32. select round(-125.315, 2); -- The return value is 100.0. select round(123.345, -2); -- The return value is null. select round(null); -- The return value is 123.345. select round(123.345, 4); -- The return value is 0.0. 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 | +------------+------------+-------------+------------+
SHIFTLEFT
Syntax
int shiftleft(tinyint|smallint|int <number1>, int <number2>) bigint shiftleft(bigint <number1>, int <number2>)Description
Shifts a value left by a specific number of places (<<). This function is an additional 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 the value of number1 is not of the TINYINT, SMALLINT, INT, or BIGINT type, an error is returned.
If the value of 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 return value is 4. 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 return value is 32. 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 return value is null. 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 the SQL statement. 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
Shifts a value right by a specific number of places (>>). This function is an additional 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 the value of number1 is not of the TINYINT, SMALLINT, INT, or BIGINT type, an error is returned.
If the value of 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 return value is 1. 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 return value is 4. 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 return value is null. 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 the SQL statement. 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
Shifts an unsigned value right by a specific number of places (>>>). This function is an additional 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 the value of number1 is not of the TINYINT, SMALLINT, INT, or BIGINT type, an error is returned.
If the value of 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 return value is 2. 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 return value is 1073741820. 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 return value is null. 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 the SQL statement. 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 | +------------+---------------------+
SIGN
Syntax
double sign(<number>)Description
Returns the sign of the input value. This function is an additional function of MaxCompute V2.0.
Parameters
number: required. A value 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 return value is -1.0. select sign(-2.5); -- The return value is 1.0. select sign(2.5); -- The return value is 0.0. select sign(0); -- The return value is null. select sign(null);Example of table data
Obtain the signs 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 the SQL statement. 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 | +------------+------------+------------+-------------+------------+------------+
SIN
Syntax
double|decimal sin(<number>)Description
Calculates the sine of number, which is a radian value.
Parameters
number: required. A value 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 the value of 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 null, null is returned.
Examples
Examples of static data
-- The return value is -0.3048106211022167. select sin(60); -- The return value is null. 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
Calculates the hyperbolic sine of number.
Parameters
number: required. A value 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 the value of 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 null, null is returned.
Examples
Examples of static data
-- The return value is 5.343237290762231E12. select sinh(30); -- The return value is null. 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
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 the value of 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 null, null is returned.
Examples
Examples of static data
-- The return value is 2.0. select sqrt(4); -- The return value is null. 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
Calculates the tangent of number, which is a radian value.
Parameters
number: required. A value 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 the value of 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 null, null is returned.
Examples
Examples of static data
-- The return value is -6.405331196646276. select tan(30); -- The return value is null. 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
Calculates the hyperbolic tangent of number.
Parameters
number: required. A value 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 the value of 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 null, null is returned.
Examples
Examples of static data
-- The return value is 1.0. select tanh(30); -- The return value is null. 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
Truncates the input value of number to the specified decimal place.
NoteIf 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 configure 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. A value 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 the value of 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 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.
NoteIf a value of the DOUBLE type is returned, the return value may not be displayed properly. This issue exists in all systems. For more information, see
trunc(125.815,1)in this example.The number is filled with zeros from the specified position.
Examples
Examples of static data
-- The return value is 125.0. select trunc(125.815,0); -- The return value is 125.80000000000001. select trunc(125.815,1); -- The return value is 125.81. select trunc(125.815,2); -- The return value is 125.815. select trunc(125.815,3); -- The return value is -125.81. select trunc(-125.815,2); -- The return value is 120.0. select trunc(125.815,-1); -- The return value is 100.0. select trunc(125.815,-2); -- The return value is 0.0. select trunc(125.815,-3); -- The return value is 123.345. select trunc(123.345,4); -- The return value is 0.0. select trunc(123.345,-4); -- The return value is null. 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 | +------------+---------------------+-------------+------------+
UNHEX
Syntax
binary unhex(string <number>)Description
Converts a hexadecimal string into a string. This function is an additional 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 return value is abc. select unhex('616263'); -- The return value is abc. select unhex(616263); -- The return value is null. select unhex(null);
WIDTH_BUCKET
Syntax
width_bucket(numeric <expr>, numeric <min_value>, numeric <max_value>, int <num_buckets>)Description
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 additional 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 named 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. The following commands provide an example:
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 | NULL | NULL | +-------+--------+-------------+Example 2: The value of an input parameter is null. The following commands provide an example:
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 | +------+-------+-------------+
References
If the preceding built-in functions do not meet your business requirements, MaxCompute also provides custom functions. For more information about UDFs, see Overview.
For more information about FAQ about SQL of MaxCompute, see the following topics:
For more information about common errors and FAQ about built-in functions of MaxCompute, see the following topics: