MaxCompute SQL provides common mathematical functions that you can use for development. You can select mathematical functions based on your business requirements for data computing and data type conversions. This topic describes the syntax, parameters, and examples of mathematical functions supported by MaxCompute SQL.

The following table lists mathematical functions supported by MaxCompute SQL.
Function Description
ABS Calculates the absolute value.
ACOS Calculates the arccosine.
ASIN Calculates the arcsine.
ATAN Calculates the arctangent.
CEIL Rounds up a number and returns the nearest integer.
CONV Converts a number from one number system to another.
COS Calculates the cosine.
COSH Calculates the hyperbolic cosine.
COT Calculates the cotangent.
EXP Calculates the exponential value.
FLOOR Rounds down a number and returns the nearest integer.
LN Calculates the natural logarithm.
LOG Calculates the logarithm.
POW Calculates the nth power of a value.
RAND Returns a random number.
ROUND Returns a value rounded to the specified decimal place.
SIN Calculates the sine.
SINH Calculates the hyperbolic sine.
SQRT Calculates the square root.
TAN Calculates the tangent.
TANH Calculates the hyperbolic tangent.
TRUNC Truncates the input value to the specified decimal place.
BIN Calculates the binary code.
CBRT Calculates the cube root.
DEGREES Converts a radian value to a degree.
E Calculates the value of e.
FACTORIAL Calculates the factorial.
FORMAT_NUMBER Converts a number to a string in the specified format.
HEX Converts an integer or a string into a hexadecimal number.
LOG2 Calculates the logarithm of a number with the base number of 2.
LOG10 Calculates the logarithm of a number with the base number of 10.
PI Calculates the value of π.
RADIANS Converts a degree to a radian value.
SIGN Returns the sign of the input value.
SHIFTLEFT Shifts a value left by a specific number of places.
SHIFTRIGHT Shifts a value right by a specific number of places.
SHIFTRIGHTUNSIGNED Shifts an unsigned value right by a specific number of places.
UNHEX Converts a hexadecimal string to a string.
WIDTH_BUCKET Returns the ID of the bucket into which the value of a specific expression falls.

ABS

  • Syntax
    double abs(double <number>)
    bigint abs(bigint <number>)
    decimal abs(decimal <number>)
  • Description

    This function calculates the absolute value of number.

  • Parameters
    number: required. If number is of the DOUBLE, BIGINT, or DECIMAL type, a value of the same type is returned.
    • If number is of the BIGINT type, the return value must be of the BIGINT type.
    • If number is of the DOUBLE type, the return value must be of the DOUBLE type.
    • If number is of the DECIMAL type, the return value must be of the DECIMAL type.
    • If number is of the STRING type, it is implicitly converted into a value of the DOUBLE type before the calculation.
    • If the input value is set to null, null is returned.
    • If number is of a data type other than the preceding types, an error is returned.
    Note If number 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 type of the return value depends on that of the input value, which can be DOUBLE, BIGINT, or DECIMAL.

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

ACOS

  • Syntax
    double acos(double <number>)
    decimal acos(decimal <number>)
  • Description

    This function calculates the arccosine of number.

  • Parameters

    number: required. The value is of the DOUBLE or DECIMAL type and ranges from -1 to 1. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before the calculation. If the input value is of another data type, an error is returned.

  • Return value

    A value of the DOUBLE or DECIMAL type is returned. The value ranges from 0 to π. If the input parameter is set to null, null is returned.

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

ASIN

  • Syntax
    double asin(double <number>)
    decimal asin(decimal <number>)
  • Description

    This function calculates the arcsine of number.

  • Parameters

    number: required. The value is of the DOUBLE or DECIMAL type and ranges from -1 to 1. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before the calculation. If the input value is of another data type, an error is returned.

  • Return value

    A value of the DOUBLE or DECIMAL type is returned. The value ranges from -π/2 to π/2. If the input parameter is set to null, null is returned.

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

ATAN

  • Syntax
    double atan(double <number>)
  • Description

    This function calculates the arctangent of number.

  • Parameters

    number: required. The value is of the DOUBLE type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before the calculation. If the input value is of another data type, an error is returned.

  • Return value

    A value of the DOUBLE type is returned. The value ranges from -π/2 to π/2. If the input parameter is set to null, null is returned.

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

CEIL

  • Syntax
    bigint ceil(double <value>)
    bigint ceil(decimal <value>)
  • Description

    This function rounds up value and returns the nearest integer.

  • Parameters

    value: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before the calculation. If the input value is of another data type, an error is returned.

  • Return value

    A value of the BIGINT type is returned. If the input parameter is set to null, null is returned.

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

CONV

  • Syntax
    string conv(string <input>, bigint <from_base>, bigint <to_base>)
  • Description

    This function converts a number from one number system to another.

  • Parameters
    • input: required. The value is the integer you want to convert, which is of the STRING type. If the input value is of the BIGINT or DOUBLE type, it is implicitly converted into a value of the STRING type before the calculation.
    • from_base and to_base: required. The values of these parameters are decimal numbers. The values can be 2, 8, 10, or 16. If the input value is of the STRING or DOUBLE type, it is implicitly converted into a value of the BIGINT type before the calculation.
  • Return value

    A value of the STRING type is returned. If an input value is set to null, null is returned. The conversion process runs at 64-bit precision. If an overflow occurs, an error is returned. If the input value is a negative value that begins with a hyphen (-), an error is returned. If the input value is a decimal, it is converted into an integer before the conversion of number systems. The decimal part is left out.

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

COS

  • Syntax
    double cos(double <number>)
    decimal cos(decimal <number>)
  • Description

    This function calculates the cosine of number, which is a radian value.

  • Parameters

    number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before the calculation. If the input value is of another data type, an error is returned.

  • Return value

    A value of the DOUBLE or DECIMAL type is returned. If the input parameter is set to null, null is returned.

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

COSH

  • Syntax
    double cosh(double <number>)
    decimal cosh(decimal <number>)
  • Description

    This function calculates the hyperbolic cosine of number.

  • Parameters

    number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before the calculation. If the input value is of another data type, an error is returned.

  • Return value

    A value of the DOUBLE or DECIMAL type is returned. If the input parameter is set to null, null is returned.

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

COT

  • Syntax
    double cot(double <number>)
    decimal cot(decimal <number>)
  • Description

    This function calculates the cotangent of number, which is a radian value.

  • Parameters

    number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before the calculation. If the input value is of another data type, an error is returned.

  • Return value

    A value of the DOUBLE or DECIMAL type is returned. If the input parameter is set to null, null is returned.

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

EXP

  • Syntax
    double exp(double <number>)
    decimal exp(decimal <number>)
  • Description

    This function calculates the exponential value of number.

  • Parameters

    number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before the calculation. If the input value is of another data type, an error is returned.

  • Return value

    A value of the DOUBLE or DECIMAL type is returned. If the input parameter is set to null, null is returned.

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

FLOOR

  • Syntax
    bigint floor(double <number>)
    bigint floor(decimal <number>)
  • Description

    This function rounds down number and returns the nearest integer that is no greater than the value of number.

  • Parameters

    number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before the calculation. If the input value is of another data type, an error is returned.

  • Return value

    A value of the BIGINT type is returned. If the input parameter is set to null, null is returned.

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

LN

  • Syntax
    double ln(double <number>)
    decimal ln(decimal <number>)
  • Description

    This function calculates the natural logarithm of number.

  • Parameter
    number: required. The value is of the DOUBLE or DECIMAL type.
    • If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before the calculation. If the input value is of another data type, an error is returned.
    • If the input value is set to null, null is returned. If the input value is a negative value or 0, an error is returned.
  • Return value

    A value of the DOUBLE or DECIMAL type is returned.

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

LOG

  • Syntax
    double log(double <base>, double <x>)
    decimal log(decimal <base>, decimal <x>)
  • Description

    This function calculates the logarithm of x whose base number is base.

  • Parameters
    • base: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before the calculation. If the input value is of another data type, an error is returned.
    • x: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before the calculation. If the input value is of another data type, an error is returned.
  • Return value
    The logarithm value of the DOUBLE or DECIMAL type is returned.
    • If an input value is set to null, null is returned.
    • If an input value is a negative value or 0, an error is returned.
    • If the value of base is 1, an error is returned. The value 1 causes division by zero.
  • Examples
    -- The value 4.0 is returned. 
    select log(2, 16);

POW

  • Syntax
    double pow(double <x>, double <y>)
    decimal pow(decimal <x>, decimal <y>)
  • Description

    This function calculates the yth power of x, namely, x^y.

  • Parameters
    • x: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before the calculation. If the input value is of another data type, an error is returned.
    • y: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before the calculation. If the input value is of another data type, an error is returned.
  • Return value

    A value of the DOUBLE or DECIMAL type is returned. If an input parameter is set to null, null is returned.

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

RAND

  • Syntax
    double rand(bigint <seed>)
  • Description

    This function returns a random number of the DOUBLE type. The value ranges from 0 to 1.

  • Parameters

    seed: optional. The value is of the BIGINT type. This parameter specifies the random seed that determines the starting point in generating random numbers.

    Note You can use seed to determine the random number sequence. After seed is determined, the return value of this function is fixed.
  • Return value

    A value of the DOUBLE type is returned.

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

ROUND

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

    This function returns a number rounded to the specified decimal place.

  • Parameters
    • number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before the calculation. If the input value is of another data type, an error is returned.
    • decimal_places: optional. The value is a constant of the BIGINT type. The value is rounded to the specified decimal place. If it is of another data type, an error is returned. If this parameter is not specified, the number is rounded to the ones place. The default value is 0.
      Note The value of decimal_places can be negative. A negative value is counted 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. If an input parameter is set to null, null is returned.

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

SIN

  • Syntax
    double sin(double <number>)
    decimal sin(decimal <number>)
  • Description

    This function calculates the sine of number, which is a radian value.

  • Parameters

    number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before the calculation. If the input value is of another data type, an error is returned.

  • Return value

    A value of the DOUBLE or DECIMAL type is returned. If the input parameter is set to null, null is returned.

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

SINH

  • Syntax
    double sinh(double <number>)
    decimal sinh(decimal <number>)
  • Description

    This function calculates the hyperbolic sine of number.

  • Parameters

    number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before the calculation. If the input value is of another data type, an error is returned.

  • Return value

    A value of the DOUBLE or DECIMAL type is returned. If the input parameter is set to null, null is returned.

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

SQRT

  • Syntax
    double sqrt(double <number>)
    decimal sqrt(decimal <number>)
  • Description

    This function calculates the square root of number.

  • Parameters

    number: required. The value is of the DOUBLE or DECIMAL type. The value must be greater than 0. If the value is less than 0, an error is returned. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before the calculation. If the input value is of another data type, an error is returned.

  • Return value

    A value of the DOUBLE or DECIMAL type is returned. If the input parameter is set to null, null is returned.

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

TAN

  • Syntax
    double tan(double <number>)
    decimal tan(decimal <number>)
  • Description

    This function calculates the tangent of number, which is a radian value.

  • Parameters

    number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before the calculation. If the input value is of another data type, an error is returned.

  • Return value

    A value of the DOUBLE or DECIMAL type is returned. If the input parameter is set to null, null is returned.

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

TANH

  • Syntax
    double tanh(double <number>)
    decimal tanh(decimal <number>)
  • Description

    This function calculates the hyperbolic tangent of number.

  • Parameters

    number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before the calculation. If the input value is of another data type, an error is returned.

  • Return value

    A value of the DOUBLE or DECIMAL type is returned. If the input parameter is set to null, null is returned.

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

TRUNC

  • Syntax
    double trunc(double <number>[, bigint <decimal_places>])
    decimal trunc(decimal <number>[, bigint <decimal_places>])
  • Description
    This function truncates the input value of number to the specified decimal place.
    Note If the Hive-compatible data type edition is used, this function is not a mathematical function. It is used to convert a date value. For more information about the related date function, see DATETRUNC. You must set the data type edition of your MaxCompute project based on your business requirements. For more information about data type editions, see Data type editions.
  • Parameters
    • number: required. The value is of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, it is implicitly converted into a value of the DOUBLE type before the calculation. If the input value is of another data type, an error is returned.
    • decimal_places: optional. The value is a constant of the BIGINT type. This parameter specifies the position where the number is truncated. If the value is of another data type, it is converted into a value of the BIGINT type. 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. If any input parameter is set to null, null is returned.

    Note
    • If a value of the DOUBLE type is returned, the return value may not be properly displayed. This issue exists in all systems. For more information, see trunc(125.815,1) in the following examples.
    • The number is filled with zeros from the specified position.
  • Examples
    -- The value 125.0 is returned. 
    select trunc(125.815,0);
    -- The value 125.80000000000001 is returned. 
    select trunc(125.815,1);
    -- The value 125.81 is returned. 
    select trunc(125.815,2);
    -- The value 125.815 is returned. 
    select trunc(125.815,3);
    -- The value -125.81 is returned. 
    select trunc(-125.815,2);
    -- The value 120.0 is returned. 
    select trunc(125.815,-1);
    -- The value 100.0 is returned. 
    select trunc(125.815,-2);
    -- The value 0.0 is returned. 
    select trunc(125.815,-3);
    -- The value 123.345 is returned. 
    select trunc(123.345,4);
    -- The value 0.0 is returned. 
    select trunc(123.345,-4);

Additional functions of MaxCompute V2.0

MaxCompute V2.0 provides additional functions. If the functions you use involve new data types, execute the following SET statement to enable these data types. The new data types include TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, and BINARY.
  • Session level: To use a new data type, you must insert set odps.sql.type.system.odps2=true; before the SQL statement, and commit and execute them together.
  • Project level: The project owner can configure new data types for the project as required. It takes 10 to 15 minutes for the settings to take effect. Run the following command:
    setproject odps.sql.type.system.odps2=true;
    For more information about setproject, see Project operations. For the precautions you must take when you enable data types at the project level, see Data type editions.

BIN

  • Syntax
    string bin(bigint <number>)
  • Description

    This function calculates the binary code of number.

  • Parameters

    number: required. The value is of the BIGINT type.

  • Return value

    A value of the STRING type is returned. If the input value is 0, 0 is returned. If the input parameter is set to null, null is returned.

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

CBRT

  • Syntax
    double cbrt(double <number>)
  • Description

    This function calculates the cube root of number.

  • Parameters

    number: required. The value is of the DOUBLE type.

  • Return value

    A value of the DOUBLE type is returned. If the input parameter is set to null, null is returned.

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

DEGREES

  • Syntax
    double degrees(double <number>) 
    double degrees(decimal <number>)
  • Description

    This function converts a radian value to a degree.

  • Parameters

    number: required. The value is of the DOUBLE or DECIMAL type.

  • Return value

    A value of the DOUBLE type is returned. If the input parameter is set to null, null is returned.

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

E

  • Syntax
    double e()
  • Description

    This function calculates the value of e.

  • Return value

    This function returns a value of the DOUBLE type.

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

FACTORIAL

  • Syntax
    bigint factorial(int <number>)
  • Description

    This function calculates the factorial of number.

  • Parameters

    number: required. The value is of the INT type and ranges from 0 to 20.

  • Return value

    A value of the BIGINT type is returned. If the input value is 0, 1 is returned. If the input value is null or a value that does not fall into the range from 0 to 20, null is returned.

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

FORMAT_NUMBER

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

    This function converts a number to a string in the specified format.

  • Parameters
    • expr1: required. This parameter specifies the expression that you want to format.
    • expr2: required. This parameter specifies the format of the expression after the conversion. It can be of the INT type to specify the number of decimal places. It can also be expressed in the format of #,###,###.##.
      • 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.
  • Return value

    A value of the STRING type is returned.

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

HEX

  • Syntax
    string hex(bigint <number>) 
    string hex(string <number>)
    string hex(binary <number>)
  • Description

    This function converts an integer or a string to a hexadecimal number.

  • Parameters

    number: required. If number is of the BIGINT type, a hexadecimal number is returned. If number is of the STRING type, a string in the hexadecimal format is returned.

  • Return value
    • If the input value is not 0 or null, a value of the STRING type is returned.
    • If the input value is 0, 0 is returned.
    • If the input value is set to null, an error is returned.
  • Examples
    -- The value 0 is returned. 
    select hex(0);
    -- The value 616263 is returned. 
    select hex('abc');
    -- The value 11 is returned. 
    select hex(17);
    -- The value 3137 is returned. 
    select hex('17');
    -- An error is returned.  
    select hex(null);

LOG2

  • Syntax
    double log2(double <number>)
    double log2(decimal <number>)
  • Description

    This function calculates the logarithm of number with the base number of 2.

  • Parameters

    number: required. The value is of the DOUBLE or DECIMAL type.

  • Return value

    A value of the DOUBLE type is returned. If the input value is set to 0 or null, null is returned.

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

LOG10

  • Syntax
    double log10(double <number>)
    double log10(decimal <number>)
  • Description

    This function calculates the logarithm of number with the base number of 10.

  • Parameters

    number: required. The value is of the DOUBLE or DECIMAL type.

  • Return value

    A value of the DOUBLE type is returned. If the input value is set to 0 or null, null is returned.

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

PI

  • Syntax
    double pi()
  • Description

    This function calculates the value of π.

  • Return value

    This function returns a value of the DOUBLE type.

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

RADIANS

  • Syntax
    double radians(double <number>)
  • Description

    This function converts a degree to a radian value.

  • Parameters

    number: required. The value is of the DOUBLE type.

  • Return value

    A value of the DOUBLE type is returned. If the input parameter is set to null, null is returned.

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

SIGN

  • Syntax
    double sign(double <number>)
    double sign(decimal <number>)
  • Description

    This function returns the sign of the input value.

  • Parameters

    number: required. The value is of the DOUBLE or DECIMAL type.

  • Return value
    A value of the DOUBLE type is returned.
    • If the input value is a positive value, 1.0 is returned.
    • If the input value is a negative value, -1.0 is returned.
    • If the input value is 0, 0.0 is returned.
    • If the input parameter is set to null, null is returned.
  • Examples
    -- The value -1.0 is returned. 
    select sign(-2.5);
    -- The value 1.0 is returned. 
    select sign(2.5);
    -- The value 0.0 is returned. 
    select sign(0);
    -- The return value is null. 
    select sign(null);

SHIFTLEFT

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

    This function shifts a value left by a specific number of places (<<).

  • 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.

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

SHIFTRIGHT

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

    This function shifts a value right by a specific number of places (>>).

  • 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.

  • Examples
    -- The value 1 is returned. The following statement shifts the binary value of 4 two places to the right (4>>2,0100 shifted to be 0001). 
    select shiftright(4,2);
    -- The 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);

SHIFTRIGHTUNSIGNED

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

    This function shifts an unsigned value right by a specific number of places (>>>).

  • 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.

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

UNHEX

  • Syntax
    binary unhex(string <number>)
  • Description

    This function converts a hexadecimal string to a string.

  • Parameters

    number: required. The value is a hexadecimal string.

  • Return value

    A value of the BINARY type is returned. If the input value is 0, an error is returned. If the input parameter is set to null, null is returned.

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

WIDTH_BUCKET

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

    This function specifies the number of buckets and the minimum and maximum values of the acceptable range for a bucket. It allows you to construct equi-width buckets, in which the bucket range is divided into intervals that have an identical size. It returns the ID of the bucket into which the value of a specific expression falls. This function supports the following data types: DECIMAL(precision,scale) in the MaxCompute V2.0 data type edition, BIGINT, INT, FLOAT, DOUBLE, and DECIMAL. For more information, see MaxCompute V2.0 data type edition.

  • 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. 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).

  • Examples
    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 information is returned:
    +-------+--------+-------------+
    | key   | value  | value_group |
    +-------+--------+-------------+
    | 1     | 99     | 0           |
    | 2     | 100    | 1           |
    | 3     | 199    | 2           |
    | 4     | 200    | 2           |
    | 5     | 499    | 5           |
    | 6     | 500    | 6           |
    | 7     | 501    | 6           |
    | 8     | \N     | \N          |
    +-------+--------+-------------+