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.
• 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(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:
``````+--------------------+
+--------------------+
| 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 |
+--------------------+``````