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:
The following information is returned:SELECT abs(4.5);
+----------+ | abs(4.5) | +----------+ | 4.5 | +----------+
The following information is returned:SELECT abs(-4);
+--------+ | abs(4) | +--------+ | 4 | +--------+
ACOS
acos(x)
- Description: This function returns the arc cosine of
x
.If
x
is greater than 1 or ifx
is less than -1,NULL
is returned. - Data type of the input value: DOUBLE.
- Data type of the return value: DOUBLE.
- Examples:
The following information is returned:SELECT acos(0.5);
+--------------------+ | acos(0.5) | +--------------------+ | 1.0471975511965979 | +--------------------+
ASIN
asin(x)
- Description: This function returns the arc sine of
x
.If
x
is greater than 1 or ifx
is less than -1,NULL
is returned. - Data type of the input value: DOUBLE.
- Data type of the return value: DOUBLE.
- Examples:
The following information is returned:SELECT asin(0.5);
+--------------------+ | 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:
The following information is returned:SELECT atan(0.5);
+--------------------+ | atan(0.5) | +--------------------+ | 0.4636476090008061 | +--------------------+
ATAN2
atan2(x, y)
- Description: This function returns the arc tangent of the result of
x
divided byy
. - Data type of the input value: DOUBLE.
- Data type of the return value: DOUBLE.
- Examples:
The following information is returned: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:
The following information is returned:SELECT ceiling(4);
+------------+ | ceiling(4) | +------------+ | 4 | +------------+
The following information is returned:SELECT ceil(-4.5);
+------------+ | 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:
The following information is returned:SELECT cos(1.3);
+---------------------+ | 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:
The following information is returned:SELECT cot(1.234);
+---------------------+ | 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:
The following information is returned:SELECT crc32('China');
+-----------------+ | 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:
The following information is returned:SELECT degrees(1.3);
+-------------------+ | 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 ofx
. - Data type of the input value: DOUBLE.
- Data type of the return value: DOUBLE.
- Examples:
The following information is returned:SELECT exp(4.5);
+-------------------+ | 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:
The following information is returned:SELECT floor(7);
+----------+ | floor(7) | +----------+ | 7 | +----------+
SELECT floor(0.8);
The following information is returned: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.+-------------+ | floor(0.8) | +-------------+ | 0 | +-------------+
SELECT floor(4/5);
The following information is returned: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.+-------------+ | 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:
The following information is returned:SELECT ln(2.718281828459045);
+-----------------------+ | 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 basex
.
- When an argument is specified, this function returns the natural logarithm of
- Data type of the input value: DOUBLE.
- Data type of the return value: DOUBLE.
- Examples:
The following information is returned:SELECT log(16);
+-------------------+ | log(16) | +-------------------+ | 2.772588722239781 | +-------------------+
The following information is returned:SELECT log(10,100);
+-------------+ | 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:
The following information is returned:SELECT log2(8.7654);
+-------------------+ | 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:
The following information is returned:SELECT log10(100.876);
+--------------------+ | log10(100.876) | +--------------------+ | 2.0037878529824615 | +--------------------+
PI
pi()
- Description: This function returns the value of Pi.
- Data type of the return value: DOUBLE.
- Examples:
The following information is returned:SELECT pi();
+-------------------+ | pi() | +-------------------+ | 3.141592653589793 | +-------------------+
POWER/POW
power(x, y)
pow(x, y)
- Description: This function returns the value of
x
raised to the power ofy
. - Data type of the input value: DOUBLE.
- Data type of the return value: DOUBLE.
- Examples:
The following information is returned:SELECT power(1.2,3.4);
+-------------------+ | power(1.2,3.4) | +-------------------+ | 1.858729691979481 | +-------------------+
The following information is returned:SELECT pow(-2,-3);
+------------+ | 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:
The following information is returned:SELECT radians(60.0);
+--------------------+ | 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 ofx
toy
. The number must be greater than or equal tox
and less thany
. - Data type of the return value: DOUBLE.
- Examples:
The following information is returned:SELECT rand();
+--------------------+ | rand() | +--------------------+ | 0.6613712594764614 | +--------------------+
- The following sample statement returns a floating-point number within the range of 3 to 12:
The following information is returned:SELECT rand()*(12-3)+3;
+-------------------+ | 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 ofd
is0
. The data type of the return value is the same as that ofx
.- If
x
isnull
,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.
- If
- 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:
The following information is returned:SELECT round(345.983,0);
+------------------+ | round(345.983,0) | +------------------+ | 346.000 | +------------------+
- The following sample statement rounds 345.123 to one decimal place:
The following information is returned:SELECT round(345.123,1);
+------------------+ | round(345.123,1) | +------------------+ | 345.100 | +------------------+
- The following sample statement rounds 345.984 to one decimal place to the left of the decimal point:
The following information is returned:SELECT round(344.984,-1);
+-------------------+ | round(344.984,-1) | +-------------------+ | 340.000 | +-------------------+
- The following sample statement rounds 345.984 to four decimal places to the left of the decimal point:
The following information is returned:SELECT round(345.984,-4);
+-------------------+ | round(345.984,-4) | +-------------------+ | 0.000 | +-------------------+
- The following sample statement rounds 345.983 to the nearest integer:
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.
- If
- Data type of the input value: TINYINT, SMALLINT, INT, BIGINT, DOUBLE, FLOAT, or DECIMAL.
- Data type of the return value: BIGINT.
- Examples:
The following information is returned:SELECT sign(12);
+----------+ | sign(12) | +----------+ | 1 | +----------+
The following information is returned:SELECT sign(-4.5);
+------------+ | 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:
The following information is returned:SELECT sin(1.234);
+--------------------+ | 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:
The following information is returned:SELECT sqrt(4.222);
+-------------------+ | 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:
The following information is returned:SELECT tan(8);
+--------------------+ | tan(8) | +--------------------+ | -6.799711455220379 | +--------------------+