This topic describes the numeric functions supported by AnalyticDB for MySQL clusters.

  • ABS: returns the absolute value of an argument.
  • ACOS: returns the arc cosine of an argument.
  • ASIN: returns the arc sine of an argument.
  • ATAN: returns the arc tangent of an argument.
  • ATAN2: returns the arc tangent of the result of x divided by y.
  • CEILING or CEIL: returns the value of x rounded up to the nearest integer.
  • COS: returns the cosine of an argument.
  • COT: returns the cotangent of an argument.
  • CRC32: returns the cyclic redundancy check (CRC) code of an argument.
  • DEGREES: converts radians to degrees.
  • EXP: returns the value of e (the base of the natural logarithm) raised to the power of x.
  • FLOOR: returns the value of x rounded down to the nearest integer.
  • LN: returns the natural logarithm of an argument.
  • LOG: returns the logarithm of an argument.
  • LOG2: returns the natural logarithm of an argument to the base 2.
  • LOG10: returns the natural logarithm of an argument to the base 10.
  • PI: returns the value of Pi. Pi is the ratio of the circumference of a circle to the diameter of the circle.
  • POWER/POW: returns the value of x raised to the power of y.
  • RADIANS: converts degrees to radians.
  • RAND: returns a random number within a specified numeric range.
  • ROUND: returns the rounded value of an argument.
  • SIGN: returns the sign of an argument.
  • SIN: returns the sine of an argument.
  • SQRT: returns the square root of an argument.
  • TAN: returns the tangent of an argument.

ABS

abs(x)
  • Description: This function returns the absolute value of x.
  • Data type of the input value: TINYINT, SMALLINT, INT, BIGINT, DOUBLE, FLOAT, or DECIMAL.
  • Data type of the return value:
    • When the data type of the input value is TINYINT, SMALLINT, INT, or BIGINT, the data type of the return value is BIGINT.
    • When the data type of the input value is DOUBLE or FLOAT, the data type of the return value is DOUBLE.
    • When the data type of the input value is DECIMAL, the data type of the return value is DECIMAL.
  • Examples:
    • Sample statement:
      SELECT abs(4.5);
      The following result is returned:
      +----------+
      | abs(4.5) |
      +----------+
      |      4.5 |
      +----------+
    • Sample statement:
      SELECT abs(-4);
      The following result is returned:
      +--------+
      | abs(4) |
      +--------+
      |      4 |
      +--------+

ACOS

acos(x)            
  • Description: This function returns the arc cosine of x.

    If x is greater than 1 or if x is less than -1, NULL is returned.

  • Data type of the input value: DOUBLE.
  • Data type of the return value: DOUBLE.
  • Example:
    SELECT acos(0.5);              
    The following result is returned:
    +--------------------+
    | acos(0.5)          |
    +--------------------+
    | 1.0471975511965979 |
    +--------------------+

ASIN

asin(x)            
  • Description: This function returns the arc sine of x.

    If x is greater than 1 or if x is less than -1, NULL is returned.

  • Data type of the input value: DOUBLE.
  • Data type of the return value: DOUBLE.
  • Example:
    SELECT asin(0.5);
    The following result is returned:
    +--------------------+
    | asin(0.5)          |
    +--------------------+
    | 0.5235987755982989 |
    +--------------------+

ATAN

atan(x)            
  • Description: This function returns the arc tangent of x.
  • Data type of the input value: DOUBLE.
  • Data type of the return value: DOUBLE.
  • Example:
    SELECT atan(0.5);             
    The following result is returned:
    +--------------------+
    | atan(0.5)          |
    +--------------------+
    | 0.4636476090008061 |
    +--------------------+

ATAN2

atan2(x, y)
atan(x, y)            
  • Description: This function returns the arc tangent of the result of x divided by y.
  • Data type of the input value: DOUBLE.
  • Data type of the return value: DOUBLE.
  • Example:
    SELECT atan2(0.5,0.3);
    +--------------------+
    | atan2(0.5,0.3)     |
    +--------------------+
    | 1.0303768265243125 |
    +--------------------+           

CEILING or CEIL

ceiling(x)
ceil(x)
  • Description: This function returns the value of x rounded up to the nearest integer.
  • Data type of the input value: TINYINT, SMALLINT, INT, BIGINT, DOUBLE, FLOAT, or DECIMAL.
  • Data type of the return value:
    • When the data type of the input value is TINYINT, SMALLINT, INT, or BIGINT, the data type of the return value is BIGINT.
    • When the data type of the input value is DOUBLE or FLOAT, the data type of the return value is DOUBLE.
    • When the data type of the input value is DECIMAL, the data type of the return value is DECIMAL.
  • Examples:
    • Sample statement:
      SELECT ceiling(4);
      The following result is returned:
      +------------+
      | ceiling(4) |
      +------------+
      |          4 |
      +------------+
    • Sample statement:
      SELECT ceil(-4.5);
      The following result is returned:
      +------------+
      | ceil(-4.5) |
      +------------+
      |         -4 |
      +------------+

COS

cos(x)            
  • Description: This function returns the cosine of x.
  • Data type of the input value: DOUBLE.
  • Data type of the return value: DOUBLE.
  • Example:
    SELECT cos(1.3);
    The following result is returned:
    +---------------------+
    | cos(1.3)            |
    +---------------------+
    | 0.26749882862458735 |
    +---------------------+

COT

cot(x)            
  • Description: This function returns the cotangent of x.
  • Data type of the input value: DOUBLE.
  • Data type of the return value: DOUBLE.
  • Example:
    SELECT cot(1.234);
    The following result is returned:
    +---------------------+
    | cot(1.234)          |
    +---------------------+
    | 0.35013639786701445 |
    +---------------------+

CRC32

crc32(x)            
  • Description: This function returns the CRC code of x.
  • Data type of the input value: VARBINARY.
  • Data type of the return value: BIGINT.
  • Example:
    SELECT crc32('China');                   
    The following result is returned:
    +-----------------+
    | crc32('China')   |
    +-----------------+
    |       737014929 |
    +-----------------+

DEGREES

degrees(x)            
  • Description: This function converts radians to degrees.
  • Data type of the input value: DOUBLE.
  • Data type of the return value: DOUBLE.
  • Example:
    SELECT degrees(1.3);
    The following result is returned:
    +-------------------+
    | degrees(1.3)      |
    +-------------------+
    | 74.48451336700703 |
    +-------------------+

EXP

exp(x)            
  • Description: This function returns the value of e (the base of the natural logarithm) raised to the power of x.
  • Data type of the input value: DOUBLE.
  • Data type of the return value: DOUBLE.
  • Example:
    SELECT exp(4.5);
    The following result is returned:
    +-------------------+
    | exp(4.5)          |
    +-------------------+
    | 90.01713130052181 |
    +-------------------+

FLOOR

floor(x)
  • Description: This function returns the value of x rounded down to the nearest integer.
  • Data type of the input value: TINYINT, SMALLINT, INT, BIGINT, DOUBLE, FLOAT, or DECIMAL.
  • Data type of the return value:
    • When the data type of the input value is TINYINT, SMALLINT, INT, or BIGINT, the data type of the return value is BIGINT.
    • When the data type of the input value is DOUBLE or FLOAT, the data type of the return value is DOUBLE.
    • When the data type of the input value is DECIMAL, the data type of the return value is DECIMAL.
  • Examples:
    • Sample statement:
      SELECT floor(7);
      The following result is returned:
      +----------+
      | floor(7) |
      +----------+
      |        7 |
      +----------+
    • Sample statement:
      SELECT floor(-3.5);
      The following result is returned:
      +-------------+
      | floor(-3.5) |
      +-------------+
      |          -4 |
      +-------------+

LN

ln(x)            
  • Description: This function returns the natural logarithm of x.
  • Data type of the input value: DOUBLE.
  • Data type of the return value: DOUBLE.
  • Example:
    SELECT ln(2.718281828459045);
    The following result is returned:
    +-----------------------+
    | ln(2.718281828459045) |
    +-----------------------+
    |                   1.0 |
    +-----------------------+

LOG

log(x)
log(x, y)            
  • Description:
    • When an argument is specified, this function returns the natural logarithm of x.
    • When two arguments are specified, this function returns the logarithm of y to the base x.
  • Data type of the input value: DOUBLE.
  • Data type of the return value: DOUBLE.
  • Examples:
    • Sample statement:
      SELECT log(16);
      The following result is returned:
      +-------------------+
      | log(16)           |
      +-------------------+
      | 2.772588722239781 |
      +-------------------+
    • Sample statement:
      SELECT log(10,100);
      The following result is returned:
      +-------------+
      | log(10,100) |
      +-------------+
      |         2.0 |
      +-------------+

LOG2

log2(x)            
  • Description: This function returns the natural logarithm of an argument to the base 2.
  • Data type of the input value: DOUBLE.
  • Data type of the return value: DOUBLE.
  • Example:
    SELECT log2(8.7654);
    The following result is returned:
    +-------------------+
    | log2(8.7654)      |
    +-------------------+
    | 3.131819928389146 |
    +-------------------+

LOG10

log10(x)            
  • Description: This function returns the natural logarithm of an argument to the base 10.
  • Data type of the input value: DOUBLE.
  • Data type of the return value: DOUBLE.
  • Example:
    SELECT log10(100.876);
    The following result is returned:
    +--------------------+
    | log10(100.876)     |
    +--------------------+
    | 2.0037878529824615 |
    +--------------------+

PI

pi()
  • Description: This function returns the value of Pi.
  • Data type of the return value: DOUBLE.
  • Example:
    SELECT pi();
    The following result is returned:
    +-------------------+
    | pi()              |
    +-------------------+
    | 3.141592653589793 |
    +-------------------+

POWER/POW

power(x, y)
pow(x, y)
  • Description: This function returns the value of x raised to the power of y.
  • Data type of the input value: DOUBLE.
  • Data type of the return value: DOUBLE.
  • Examples:
    • Sample statement:
      SELECT power(1.2,3.4);
      The following result is returned:
      +-------------------+
      | power(1.2,3.4)    |
      +-------------------+
      | 1.858729691979481 |
      +-------------------+
    • Sample statement:
      SELECT pow(-2,-3);
      The following result is returned:
      +------------+
      | pow(-2,-3) |
      +------------+
      |     -0.125 |
      +------------+

RADIANS

radians(x)            
  • Description: This function converts degrees to radians.
  • Data type of the input value: DOUBLE.
  • Data type of the return value: DOUBLE.
  • Example:
    SELECT radians(60.0);
    The following result is returned:
    +--------------------+
    | radians(60.0)      |
    +--------------------+
    | 1.0471975511965976 |
    +--------------------+

RAND

rand()
rand()*(y-x)+x
  • Description: You cannot specify input arguments for the rand() function. The rand() function randomly returns a number that is greater than or equal to 0 and less than 1.

    You can also use the rand()*(y-x)+x function. This function randomly returns a number within the range of x to y. The number must be greater than or equal to x and less than y.

  • Data type of the return value: DOUBLE.
  • Examples:
    • Sample statement:
      SELECT rand();
      The following result is returned:
      +--------------------+
      | rand()             |
      +--------------------+
      | 0.6613712594764614 |
      +--------------------+
    • The following sample statement returns a floating-point number within the range of 3 to 12.
      SELECT rand()*(12-3)+3;
      The following result is returned:
      +-------------------+
      | rand()*(12-3)+3   |
      +-------------------+
      | 9.073329270781976 |
      +-------------------+

ROUND

round(x)
round(x,d)
  • Description: This function returns the rounded value of x. d specifies the number of decimal places. The default value of d is 0. The data type of the return value is the same as that of x.
    • If x is null, NULL is returned.
    • If d is greater than 0, the argument is rounded to the specified number of decimal places.
    • If d is equal to 0, the argument is rounded to the nearest integer.
    • If d is less than 0, the argument is rounded to the specified number of decimal places to the left of the decimal point.
  • Data type of the input value: TINYINT, SMALLINT, INT, BIGINT, DOUBLE, FLOAT, or DECIMAL.
  • Data type of the return value:
    • When the data type of the input value is TINYINT, SMALLINT, INT, or BIGINT, the data type of the return value is BIGINT.
    • When the data type of the input value is DOUBLE or FLOAT, the data type of the return value is DOUBLE.
    • When the data type of the input value is DECIMAL, the data type of the return value is DECIMAL.
  • Examples:
    • The following sample statement rounds 345.983 to the nearest integer:
      SELECT round(345.983,0);
      The following result is returned:
      +------------------+
      | round(345.983,0) |
      +------------------+
      |          346.000 |
      +------------------+
    • The following sample statement rounds 345.123 to one decimal place:
      SELECT round(345.123,1);
      The following result is returned:
      +------------------+
      | round(345.123,1) |
      +------------------+
      |          345.100 |
      +------------------+
    • The following sample statement rounds 345.984 to one decimal place to the left of the decimal point:
      SELECT round(344.984,-1);                  
      The following result is returned:
      +-------------------+
      | round(344.984,-1) |
      +-------------------+
      |           340.000 |
      +-------------------+
    • The following sample statement rounds 345.984 to four decimal places to the left of the decimal point:
      SELECT round(345.984,-4);                  
      The following result is returned:
      +-------------------+
      | round(345.984,-4) |
      +-------------------+
      |             0.000 |
      +-------------------+

SIGN

sign(x)
  • Description: This function returns the corresponding value based on the sign of x.
    • If x is greater than 0, 1 is returned.
    • If x is equal to 0, 0 is returned.
    • If x is less than 0, -1 is returned.
  • Data type of the input value: TINYINT, SMALLINT, INT, BIGINT, DOUBLE, FLOAT, or DECIMAL.
  • Data type of the return value: BIGINT.
  • Examples:
    • Sample statement:
      SELECT sign(12);
      The following result is returned:
      +----------+
      | sign(12) |
      +----------+
      |        1 |
      +----------+
    • Sample statement:
      SELECT sign(-4.5);
      The following result is returned:
      +------------+
      | sign(-4.5) |
      +------------+
      |         -1 |
      +------------+

SIN

sin(x)            
  • Description: This function returns the sine of x.
  • Data type of the input value: DOUBLE.
  • Data type of the return value: DOUBLE.
  • Example:
    SELECT sin(1.234);
    The following result is returned:
    +--------------------+
    | sin(1.234)         |
    +--------------------+
    | 0.9438182093746337 |
    +--------------------+

SQRT

sqrt(x)            
  • Description: This function returns the square root of x.
  • Data type of the input value: DOUBLE.
  • Data type of the return value: DOUBLE.
  • Example:
    SELECT sqrt(4.222);
    The following result is returned:
    +-------------------+
    | sqrt(4.222)       |
    +-------------------+
    | 2.054750593137766 |
    +-------------------+

TAN

tan(x)            
  • Description: This function returns the tangent of x.
  • Data type of the input value: DOUBLE.
  • Data type of the return value: DOUBLE.
  • Example:
    SELECT tan(8);
    The following result is returned:
    +--------------------+
    | tan(8)             |
    +--------------------+
    | -6.799711455220379 |
    +--------------------+