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: 
    • SELECT abs(4.5);
      The following information is returned:
      +----------+
| abs(4.5) |
+----------+
|      4.5 |
+----------+
    • SELECT abs(-4);
      The following information 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.
  • Examples: 
    SELECT acos(0.5);
    The following information 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.
  • Examples: 
    SELECT asin(0.5);
    The following information 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.
  • Examples: 
    SELECT atan(0.5);
    The following information is returned:
    +--------------------+
| atan(0.5)          |
+--------------------+
| 0.4636476090008061 |
+--------------------+

ATAN2

atan2(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.
  • Examples: 
    SELECT atan2(0.5,0.3);
    The following information is returned:
    +--------------------+
| 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: 
    • SELECT ceiling(4);
      The following information is returned:
      +------------+
| ceiling(4) |
+------------+
|          4 |
+------------+
    • SELECT ceil(-4.5);
      The following information 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.
  • Examples: 
    SELECT cos(1.3);
    The following information 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.
  • Examples: 
    SELECT cot(1.234);
    The following information 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.
  • Examples: 
    SELECT crc32('China');
    The following information is returned:
    +-----------------+
| crc32('China')  |
+-----------------+
|      2704207136 |
+-----------------+

DEGREES

degrees(x)
  • Description: This function converts radians to degrees.
  • Data type of the input value: DOUBLE.
  • Data type of the return value: DOUBLE.
  • Examples: 
    SELECT degrees(1.3);
    The following information 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.
  • Examples: 
    SELECT exp(4.5);
    The following information 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: 
    • SELECT floor(7);
      The following information is returned:
      +----------+
| floor(7) |
+----------+
|        7 |
+----------+
    • SELECT floor(0.8);
      Note AnalyticDB for MySQL converts 0.8 to a value of the DECIMAL type. When the data type of the input value is DECIMAL, the data type of the return value is DECIMAL.
      The following information is returned:
      +-------------+
| floor(0.8)  |
+-------------+
|           0 |
+-------------+
    • SELECT floor(4/5);
      Note AnalyticDB for MySQL converts 4/5 to a value of the DOUBLE type. When the data type of the input value is DOUBLE, the data type of the return value is DOUBLE. You can use a CAST function to convert floor(4/5)=0.0 to a value of the INTEGER or BIGINT type. For more information, see CAST AS BIGINT.
      The following information is returned:
      +-------------+
| floor(4/5)  |
+-------------+
|         0.0 |
+-------------+

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.
  • Examples: 
    SELECT ln(2.718281828459045);
    The following information 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: 
    • SELECT log(16);
      The following information is returned:
      +-------------------+
| log(16)           |
+-------------------+
| 2.772588722239781 |
+-------------------+
    • SELECT log(10,100);
      The following information 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.
  • Examples:
    SELECT log2(8.7654);
    The following information 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.
  • Examples: 
    SELECT log10(100.876);
    The following information is returned:
    +--------------------+
| log10(100.876)     |
+--------------------+
| 2.0037878529824615 |
+--------------------+

PI

pi()
  • Description: This function returns the value of Pi.
  • Data type of the return value: DOUBLE.
  • Examples: 
    SELECT pi();
    The following information 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: 
    • SELECT power(1.2,3.4);
      The following information is returned:
      +-------------------+
| power(1.2,3.4)    |
+-------------------+
| 1.858729691979481 |
+-------------------+
    • SELECT pow(-2,-3);
      The following information 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.
  • Examples: 
    SELECT radians(60.0);
    The following information 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:
    • SELECT rand();
      The following information 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 information 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 information 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 information 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 information 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 information 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: 
    • SELECT sign(12);
      The following information is returned:
      +----------+
| sign(12) |
+----------+
|        1 |
+----------+
    • SELECT sign(-4.5);
      The following information 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.
  • Examples: 
    SELECT sin(1.234);
    The following information 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.
  • Examples: 
    SELECT sqrt(4.222);
    The following information 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.
  • Examples: 
    SELECT tan(8);
    The following information is returned:
    +--------------------+
| tan(8)             |
+--------------------+
| -6.799711455220379 |
+--------------------+