Use AnalyticDB for MySQL numeric functions - AnalyticDB - Alibaba Cloud Documentation Center

You can use numeric functions to perform various operations on numeric data. This topic describes the numeric function syntax and provides examples on how to use the numeric functions in AnalyticDB for MySQL.

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 logarithm of an argument to the base 2.
LOG10: returns the 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 or 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.

Example:

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.

Example:

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.

Example:

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.

Example:

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.

Example:

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.

Example:

SELECT crc32('China');

The following information 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 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.

Example:

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 the "CAST AS BIGINT" section of the CAST functions topic.
  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.

Example:

SELECT ln(2.718281828459045);

The following information is returned:

+-----------------------+
| ln(2.718281828459045) |
+-----------------------+
|                   1.0 |
+-----------------------+

LOG

log(x)
log(x, y)

Description:
- When only the x argument is specified, this function returns the natural logarithm of x.
- When both the x and y 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 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 information is returned:

+-------------------+
| log2(8.7654)      |
+-------------------+
| 3.131819928389146 |
+-------------------+

LOG10

log10(x)

Description: This function returns the 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 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.

Example:

SELECT pi();

The following information is returned:

+-------------------+
| pi()              |
+-------------------+
| 3.141592653589793 |
+-------------------+

POWER or 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.

Example:

SELECT radians(60.0);

The following information is returned:

+--------------------+
| radians(60.0)      |
+--------------------+
| 1.0471975511965976 |
+--------------------+

RAND

rand()
rand()*(y-x)+x

Description: This function returns a random number within a specified numeric range. 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 returned number is 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 a value that indicates 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.

Example:

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.

Example:

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.

Example:

SELECT tan(8);

The following information is returned:

+--------------------+
| tan(8)             |
+--------------------+
| -6.799711455220379 |
+--------------------+