ABS

Function definition:
Double abs(Double number)
Bigint abs(Bigint number)
Decimal abs(Decimal number)

Usage:

Returns an absolute value.

Parameter description:

number: It is any number of Type Double, Bigint, or Decimal.
  • If the input is Bigint and return Bigint.
  • If the input is Double, return Double. 
  • If the input is Decimal, return Decimal. 

If the input is String, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

The return result depends on the type of input parameter. Example, if the input is null, return null.

Note

When the value of input Bigint type exceeds the maximum value of Bigint, return Double type. In this case, the precision may be absent.

Example:
abs(null) = null
abs(-1) = 1
abs(-1.2) = 1.2
abs("-2") = 2.0
abs(122320837456298376592387456923748) = 1.2232083745629837e32
The following is a completed ABS function example used in SQL. The use methods of other built-in functions (except Window Function and Aggregation Function) are similar.
select abs(id) from tbl1;
-- Take the absolute value of the id field in tbl1.

ACOS

Function definition:
Double acos(Double number)
Decimal acos(Decimal number)

Usage:

Calculates the inverse cosine of a number.

Parameter description:

number: Double or Decima type, -1<=number <=1.  If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type, the value is between 0 to π. If number is null, return null.

Example
acos("0.87") = 0.5155940062460905
acos(0) = 1.5707963267948966

ASIN

Function definition:
Double asin(Double number)
Decimal asin(Decimal number)

Usage:

Calculates the inverse sine function of number.

Parameter description:

number: Double or Decima type, -1<=number <=1.  If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type, the value is between -π/2 to π/2. If the number is null, return null.

Example:
asin(1) = 1.5707963267948966
asin(-1) = -1.5707963267948966

ATAN

Function definition:
Double atan(Double number)

Usage:

Calculates the back-cut function of number.

Parameter description:

Number: Double type, if the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double type, the value is between -π/2 to π/2. If the number is null, return null.

Example
atan(1) = 0.7853981633974483
atan(-1) = -0.7853981633974483

CEIL

Function definition:
Bigint ceil(Double value)
Bigint ceil(Decimal value)

Usage:

This function returns the smallest integral value not less than the argument.

Parameter description:

value: Double or Decimal type, If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.  

Return value:

Returns the Bigint type. If the number is null, return null.

Example:
ceil(1.1) = 2
ceil(-1.1) = -1

CONV

Function definition:
String conv(String input, Bigint from_base, Bigint to_base)

Usage:

Converts a number into a Hexadecimal number.

Parameter description:
  • input: an integer to be converted, represented by String. Accept the implicit conversion of Bigint and Double.
  • from_base, to_base: Decimal value, the acceptable values can be 2, 8, 10 and 16.  Accept the implicit conversion of String and Double.

Return value

Returns the String type. If the number is null, return null. The conversion process runs at a 64-bit precision. An exception is thrown when overflow occurs. If the input is a negative value (begin with ‘-’), an exception is thrown. If the input value is a decimal, it is converted to an integer before hex conversion. The decimal part is excluded.

Example:
conv('1100', 2, 10) = '12'
conv('1100', 2, 16) = 'c'
conv('ab', 16, 10) = '171'
conv('ab', 16, 16) = 'ab'

COS

Function definition:
Double cos(Double number)
Decimal cos(Decimal number)

Usage:

Input is the radian value.

Parameter description:

number: Double or Decimal type. If the input is String, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type. If the number is NULL, return NULL.

Example
cos(3.1415926/2)=2.6794896585028633e-8
cos(3.1415926)=-0.9999999999999986

COSH

Function definition:
Double cosh(Double number)
Decimal cosh(Decimal number)

Usage:

It is the Hyperbolic cosine function

Parameter description:

number: Double or Decimal type. If the input is String, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type. If the number is NULL, return NULL.

COT

Function definition:
Double cot(Double number)
Decimal cot(Decimal number)

Usage:

Inputs the radian value.

Parameter description:

number: Double or Decimal type.  If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type. If the number is NULL, return NULL.

EXP

Function definition:
Double exp(Double number)
Decimal exp(Decimal number)

Usage:

It is the Exponential function.

Return value:

Returns the exponent value of number.

Parameter description:

number: Double or Decimal type.  If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type. If the number is NULL, return NULL.

FLOOR

Function definition:
Bigint floor(Double number)
Bigint floor(Decimal number)

Usage:

Returns the largest integral value not greater than the argument.

Parameter description:

number: Double or Decimal type. If the input is String or Bigint type, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Bigint type.  If the input is null, return null.

Example
floor(1.2)=1
floor(1.9)=1
floor(0.1)=0
floor(-1.2)=-2
floor(-0.1)=-1
floor(0.0)=0
Floor (-0.0) = 0

LN

Function definition:
Double ln(Double number)
Decimal ln(Decimal number)

Usage:

Returns the natural logarithm of the number.

Parameter description:

number: Double or Decimal type.
  • If the input is String or Bigint type, it is converted to Double by implicit conversion. If the input is another type, an error occurs.
  •  If the number is null, return null. If number is negative or 0, an exception is thrown.

Return value:

Returns the Double or Decimal type.

LOG

Function definition:
Double log(Double base, Double x)
Decimal log (decimal base, decimal X)

Usage:

 Returns the logarithm of x whose base number is base.

Parameter description:
  • base: Double or Decimal type. If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.
  • x: Double or Decimal type. If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the logarithm value of Double or Decimal type. 
  •  If base or x is null, return null. 
  • If one of base or x is negative or zero, it causes abnormality.
  • If base is 1, it also causes abnormality.

POW

Function definition:
Double pow(Double x, Double y)
Decimal pow(Decimal x, Decimal y)

Usage:

Return x to the yth power, that is x^y.

Parameter description:
  • X: Double or Decimal type. If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.
  • Y: Double or Decimal type. If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type. If X or Y is null, return null.

RAND

Function definition:
Double rand(Bigint seed)

Usage:

Return a random number (that changes from row to row), Specifying the seed makes sure the generated random number sequence is deterministic, Return value range is from 0 to 1.

Parameter description:

seed: Bigint type, random number seed, to determine starting values of the random number sequence.

Return Value:

Returns the Double type.

Example:
select rand() from dual;
select rand(1) from dual;

ROUND

Function definition:
Double round(Double number, [Bigint Decimal_places])
Decimal round(Decimal number, [Bigint Decimal_places])

Usage:

Four to five homes to the specific decimal point position.

Parameter description:
  • number: Double or Decimal type.  If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.
  • Decimal_place: A Bigint type constant, four to five homes to the decimal point position. If it is other type, an exception is thrown. If you exclude it, it indicates four to five homes into a single digit. The default value is zero

Return value:

Returns the Double or Decimal type.  If number or Decimal_places is null, return null.

Note

Decimal_places can be negative. The negative is counted from decimal point to the left. Deletethe decimal part. If decimal_place is greater than the length of the integer part, return 0.

Example:
round(125.315) = 125.0
round(125.315, 0) = 125.0
Round (125.315, 1) = 125.3
round(125.315, 2) = 125.32
round(125.315, 3) = 125.315
round(-125.315, 2) = -125.32
round(123.345, -2) = 100.0
round(null) = null
round(123.345, 4) = 123.345
round(123.345, -4) = 0.0

SIN

Function definition:
Double sin(Double number)
Decimal sin(Decimal number)

Usage:

Calculates the sine function of number, the input is the radian value.

Parameter description:

number: Double or Decimal type.  If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type. If the number is NULL, return NULL.

SINH

Function definition:
Double sinh(Double number)
Decimal sinh(Decimal number)

Usage:

Calculates the hyperbolic sine function of number.

Parameter description:

number: Double or Decimal type.  If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type. If the number is NULL, return NULL.

SQRT

Function definition:
Double sqrt(Double number)
Decimal sqrt(Decimal number)

Usage:

Calculates the square root of number.

Parameter description:

number: Double or Decimal type, must be greater than zero, if it is less than zero, an exception occur.   If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type. If the number is NULL, return NULL.

TAN

Function definition:
Double tan(Double number)
Decimal tan(Decimal number)

Usage:

Calculates the tangent function of the number, the input is the radian value.

Parameter description:

number: Double or Decimal type. If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type. If the number is NULL, return NULL.

TANH

Function definition:
Double tanh(Double number)
Decimal tanh(Decimal number)

Usage:

Calculates the hyperbolic tangent function of number.

Parameter description:

number: Double or Decimal type.  If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.

Return value:

Returns the Double or Decimal type. If the number is NULL, return NULL.

TRUNC

Function definition:
Double trunc(Double number[, Bigint Decimal_places])
Decimal trunc(Decimal number[, Bigint Decimal_places])

Usage:

This function is used to intercept the input number to a specified decimal point place.

Parameter description:
  • number: Double or Decimal type. If the input is String or Bigint, it is converted to Double by implicit conversion. If the input is another type, an error occurs.
  • Decimal_places: a Bigint type constant, the decimal point place to intercept the number. Other types are converted to Bigint. If this parameter is excluded, default to intercept to single digit.

Return value:

Returns the  Double or Decimal type. If the number or Decimal_places is NULL, return NULL.

Note
  • If the Double type is returned, the display of the returned result may not be as expected, such as trunc(125.815, 1) (this problem exists in all the systems).
  • The part to be truncated is supplemented by zero.
  • Decimal_places can be negative. The negative is truncated from the decimal point to the left and delete the decimal part. If Decimal_places are greater than the length of the integer, return zero.
Example:
trunc(125.815) = 125.0
trunc(125.815, 0) =125.0
trunc(125.815, 1) = 125.80000000000001
trunc(125.815, 2) = 125.81
trunc(125.815, 3) = 125.815
trunc(-125.815, 2) = -125.81
trunc(125.815, -1) = 120.0
trunc(125.815, -2) = 100.0
trunc(125.815, -3) = 0.0
trunc(123.345, 4) = 123.345
trunc(123.345, -4) = 0.0

Maxcomputerte2.0 New Extended Mathematical Functions

With the upgrade to MaxCompute 2.0, some mathematical functions have been added to the product. If a new function uses a new data type, add the following set statement before using the new functions SQL statement:
set odps.sql.type.system.odps2=true;

The new extended functions are described as follows.

LOG2

Function definition:
Double log2(Double number)
Double log2(Decimal number)
Note
Before the SQL statement which uses the LOG2 function, add set odps.sql.type.system.odps2=true; to use the new data type function normally.

Usage:

Returns the log base 2 of a specific number.

Parameter description:

number: Double or Decimal type.

Return Value:

Returns the Double type. If the input is zero or null, the returned value is null.

The example is as follows:
log2(null)=null
log2(0)=null
log2(8)=3.0

LOG10

Function definition:
Double log10(Double number)
Double log10(Decimal number)
Note
Before the SQL statement which uses the LOG10 function, add set odps.sql.type.system.odps2=true; to use the new data type function normally.

Usage:

Returns the log base 10 of the specific number.

Parameter description:

number: Double or Decimal type.

Return Value:

Returns the Double type. If the input is zero or null, the returned value is null.

The example is as follows:
log10(null)=null
log10(0)=null
log10(8)=0.9030899869919435

BIN

Function definition:
String bin(Bigint number)
Note
Before the SQL statement which uses the function, add set odps.sql.type.system.odps2=true; to use the new data type function normally.

Usage:

Returns the binary code expression for the specific number.

Parameter description:

number: Bigint type.

Return value

String type.  If the input is zero, then zero is returned; if the input is null, null is returned.

Example:
bin(0)='0'
bin(null)='null'
bin(12)='1100'

HEX

Function definition:
String hex(Bigint number) 
String hex(String number)
String hex (binary number)
Note
Before the SQL statement which uses the HEX function, add set odps.sql.type.system.odps2=true; to use the new data type function normally.

Usage:

This function is used to converts integers or characters to hexadecimal format.

Parameter description:

number: If number is of the Bigint type, the hexadecimal format of the number is returned. If this variable is a String type, the hexadecimal format of the string is returned.

Return value

Returns the String type. If the input is zero, then zero is returned; if the input is null, an exception is returned.

Example:
hex(0)=0
hex('abc')='616263'
hex(17)='11'
hex('17')='3137'
hex(null) results in an exception and returns failed.
Note
If the input parameter is a Binary type, add set  odps.sql.type.system.odps2=true;, and submit it with SQL to use the new data type normally.

UNHEX

Function definition:
BINARY unhex(String number)
Note
Before the SQL statement which uses the UNHEX function, add set odps.sql.type.system.odps2=true; to use the new data type function normally.

Usage:

Returns the string represented by a given hexadecimal string.

Parameter description:

number: A hexadecimal string.

Return value:

Returns the Binary type. If the input is zero, failed is returned. If the input is null, null is returned.

Example:
Unhex ('616263') = 'abc'
unhex(616263)='abc'

RADIANS

Function definition:
Double radians(Double number)
Note
Before the SQL statement which uses the RADIANS function, add set odps.sql.type.system.odps2=true; to use the new data type function normally.

Usage:

This function is used to converts degrees to radians.

Parameter description:

number: Double type.

Return value:

Returns the Double type, if the input is null, null is returned.

Example:
radians(90)=1.5707963267948966
radians(0)=0.0
radians(null)=null

DEGREES

Function definition:
Double degrees(Double number) 
Double degrees(Decimal number)
Note
Before the SQL statement which uses the function, add set odps.sql.type.system.odps2=true; to use the new data type function normally.

Usage:

This function is used to converts radians to degrees.

Parameter description:

number: Double or Decimal type.

Return value:

Returns Double data type. If the input is null, null is returned.

Example:
degrees(1.5707963267948966)=90.0
degrees(0)=0.0
Degrees (null) = NULL

SIGN

Function definition:
Double sign(Double number)
Double sign(Decimal number)
Note
Before the SQL statement which uses the SIGN function, add set odps.sql.type.system.odps2=true; to use the new data type function normally.

Usage:

Applies the sign of the input data. 1.0 indicates a positive number and -1.0 indicates a negative number. Otherwise, 0.0 is returned.

Parameter description:

number: Double or Decimal type.

Return value:

Returns Double data type. If the input is 0, 0.0 is returned. If the input is null, null is returned.

Example:
sign(-2.5)=-1.0
Sign (2.5) = 1.0
sign(0)=0.0
sign(null)=null

E

Function definition:
Double e()
Note
Before the SQL statement which uses the E function, add set odps.sql.type.system.odps2=true; to use the new data type function normally.

Usage:

This function is used to return the e value.

Return Value:

Returns the Double type.

Example:
e()=2.718281828459045

PI

Function definition:
Double pi()
Note
Before the SQL statement which uses the PI function, add set odps.sql.type.system.odps2=true; to use the new data type function normally.

Usage:

This function is used to return the π value.

Return Value:

Returns the Double type.

Example:
pi()=3.141592653589793

FACTORIAL

Function definition:
Bigint factorial(Int number)
Note
Before the SQL statement which uses the FACTORIAL function, add set odps.sql.type.system.odps2=true; to use the new data type function normally.

Usage:

This function is used to return the factorial for the specific number.

Parameter description:

number: Int-type data, range: [0 –20].

Return value:

Returns the Bigint type, if the input is zero, one is returned. If the input is null or outside the range [0 –20], null is returned.

Example:
factorial(5)=120 --5! = 5*4*3*2*1 = 120

CBRT

Function definition:
Double cbrt(Double number)
Note
Before the SQL statement which uses the CBRT function, add set odps.sql.type.system.odps2=true; to use the new data type function normally.

Usage:

This function is used to return the cube root.

Parameter description:

number: Double type.

Return value:

Returns Double data type. If the input is null, null is returned.

Example:
cbrt(8)=2
cbrt(null)=null

SHIFTLEFT

Function definition:
Int shiftleft(Tinyint|Smallint|Int number1, Int number2)
Bigint shiftleft(Bigint number1, Int number2)
Note
Before the SQL statement which uses the SHIFTLEFT function, add set odps.sql.type.system.odps2=true; to use the new data type function normally.

Usage:

Shifts to the left by a given number of places (<<).

Parameter description:
  • number1: Tinyint|Smallint|Int|Bigint integer.
  • number2: An Int integer.

Return value:

Returns the Int or Bingint type.

Example:
shiftleft(1,2)=4  --Shifts the binary value of 1 two places to the left (1<<2,0001 shifted to 0100)
shiftleft(4,3)=32  --Shifts the binary value of 4 three places to the left (4<<3,0100 shifted to 10,0000)

SHIFTRIGHT

Function definition:
Int shiftright(Tinyint|Smallint|Int number1, Int number2)
Bigint shiftright(Bigint number1, Int number2)
Note
Before the SQL statement which uses the SHIFTRIGHT function, add set odps.sql.type.system.odps2=true; to use the new data type function normally.

Usage:

This function is used for shifts right by a given number of places (>>).

Parameter description:
  • number1: Tinyint|Smallint|Int|Bigint integer.
  • number2: An Int integer.

Return value:

Returns the Int or Bigint type.

Example:
shiftright(4,2)=1 -- Shifts the binary value of 4 two places to the right (4>>2,0100 shifted to 0001)
shiftright(32,3)=4 -- Shifts the binary value of 32 three places to the right (32>>3,100000 shifted to 0100)

SHIFTRIGHTUNSIGNED

The command format is as follows:
Int shiftrightunsigned(Tinyint|Smallint|Int number1, Int number2)
Bigint shiftrightunsigned(Bigint number1, Int number2)
Note
Before the SQL statement which uses the SHIFTRIGHTUNSIGNED function, add set odps.sql.type.system.odps2=true; to use the new data type function normally.

The command description is as follows:

This function is used for unsigned right shift by a given number of places (>>>).

Parameter description:
  • number1: Tinyint|Smallint|Int|Bigint integer.
  • number2: An Int integer.

Return value:

Returns the Int or Bigint type.

Example:
shiftrightunsigned(8,2)=2 -- Shifts the unsigned binary value of 8 two places to the right (8>>>2,1000 shifted to 0010)
shiftrightunsigned(-14,2)=1073741820  -- Shifts the unsigned binary value of -14 two places to the right (-14>>>2, 11111111 11111111 11111111 11110010 shifted to 00111111 11111111 11111111 11111100)