AnalyticDB for MySQL supports the following numeric functions.
- ABS: returns the absolute value of an argument.
- ROUND: returns the result of rounding an argument.
- SQRT: returns the square root of an argument.
- 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.
- POWER/POW: returns the value of x raised to the power of y.
- RADIANS: converts degrees to radians.
- DEGREES: converts radians to degrees.
- SIGN: returns the value of the sign of an argument.
- CEILING/CEIL: returns the smallest integer value that is greater than the argument.
- FLOOR: returns the largest integer value that is less than the argument.
- EXP: returns the value of e (the base of the natural logarithm) raised to the power of x.
- COS: returns the cosine of an argument.
- ACOS: returns the arc cosine of an argument.
- TAN: returns the tangent of an argument.
- ATAN: returns the arc tangent of an argument.
- ATAN2: returns the arc tangent of the result of x divided by y.
- COT: returns the cotangent of an argument.
- ASIN: returns the arc sine of an argument.
- SIN: returns the sine of an argument.
- CRC32: returns the cyclic redundancy check (CRC) code of an argument.
ABS
abs(tinyint x)
abs(smallint x)
abs(int x)
abs(bigint x)
abs(float x)
abs(double x)
abs(decimal x)
- Description: This function returns the absolute value of
x
. - Return value types: LONG, DECIMAL, and DOUBLE.
- Example:
select abs(4.5); +----------+ | abs(4.5) | +----------+ | 4.5 |
select abs(4); +--------+ | abs(4) | +--------+ | 4 |
ROUND
round(tinyint x)
round(smallint x)
round(int x)
round(bigint x)
round(float x)
round(double x)
round(x, d)
- Description: This function rounds the
x
argument.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 result is rounded to the number of digits left of the decimal point.
- If
- Return value types: LONG, DECIMAL, and DOUBLE.
- Example:
select round(4); +----------+ | round(4) | +----------+ | 4 |
select round(4.5); +------------+ | round(4.5) | +------------+ | 5.0 |
select round(345.984, -1); +------------------------------+ | round(345.984, INTEGER '-1') | +------------------------------+ | 350.0 |
SQRT
sqrt(double x)
- Description: This function returns the square root of
x
. - Return value type: DOUBLE.
- Example:
select sqrt(4.222); +-------------------+ | sqrt(4.222) | +-------------------+ | 2.054750593137766 |
LN
ln(double x)
- Description: This function returns the natural logarithm of
x
. - Return value type: DOUBLE.
- Example:
select ln(2.718281828459045); +-----------------------+ | ln(2.718281828459045) | +-----------------------+ | 1.0 |
LOG
log(double x)
log(double x, double y)
- Description: If this function is called with one argument, this function returns the
natural logarithm of
x
. If called with two arguments, this function returns the logarithm ofy
to the basex
. - Return value type: DOUBLE.
- Example:
select log(16); +-------------------+ | log(16) | +-------------------+ | 2.772588722239781 |
select log(10,100); +--------------+ | log(10, 100) | +--------------+ | 2.0 |
LOG2
log2(double x)
- Description: returns the natural logarithm of an argument to the base
2
. - Return value type: DOUBLE.
- Example:
select log2(8.7654); +-------------------+ | log2(8.7654) | +-------------------+ | 3.131819928389146 |
LOG10
log10(double x)
- Description: This function returns the natural logarithm of an argument to the base 10.
- Return value type: DOUBLE.
- Example:
select log10(100.876); +--------------------+ | log10(100.876) | +--------------------+ | 2.0037878529824615 |
PI
pi()
- Description: This function returns the value of Pi.
- Return value type: DOUBLE.
- Example:
select pi(); +-------------------+ | pi() | +-------------------+ | 3.141592653589793 |
POWER/POW
power(double x, double y)
pow(double x, double y)
- Description: This function returns the value of
x
raised to the power ofy
. - Return value type: DOUBLE.
- Example:
select power(1.2,3.4); +-------------------+ | power(1.2, 3.4) | +-------------------+ | 1.858729691979481 |
select pow(1.2,3.4); +-------------------+ | pow(1.2, 3.4) | +-------------------+ | 1.858729691979481 |
RADIANS
radians(double x)
- Description: This function converts degrees to radians.
- Return value type: DOUBLE.
- Example:
select radians(60.0); +--------------------+ | radians(60.0) | +--------------------+ | 1.0471975511965976 |
DEGREES
degrees(double x)
- Description: This function converts radians to degrees.
- Return value type: DOUBLE.
- Example:
select degrees(1.3); +-------------------+ | degrees(1.3) | +-------------------+ | 74.48451336700703 |
SIGN
sign(smallint x)
sign(tinyint x)
sign(int x)
sign(bigint x)
sign(float x)
sign(double x)
sign(decimal x)
- Description: This function returns the sign of
x
. - Return value type: LONG.
- Example:
select sign(12); +----------+ | sign(12) | +----------+ | 1 |
select sign(4.5); +-----------+ | sign(4.5) | +-----------+ | 1 |
CEILING/CEIL
ceiling(tinyint x)
ceiling(smallint x)
ceiling(int x)
ceiling(bigint x)
ceiling(float x)
ceiling(double x)
- Description: This function returns the smallest integer value that is greater than
x
. - Return value types: LONG, DECIMAL, and DOUBLE.
- Example:
select ceiling(4); +------------+ | ceiling(4) | +------------+ | 4 |
select ceiling(2.3); +--------------+ | ceiling(2.3) | +--------------+ | 3.0 |
FLOOR
floor(tinyint x)
floor(smallint x)
floor(int x)
floor(bigint x)
floor(float x)
floor(double x)
- Description: This function returns the largest integer value that is less than
x
. - Return value types: LONG, DECIMAL, and DOUBLE.
- Example:
select floor(4.5); +------------+ | floor(4.5) | +------------+ | 4.0 |
select floor(7); +----------+ | floor(7) | +----------+ | 7 |
EXP
exp(double x)
- Description: This function returns the value of
e
(the base of the natural logarithm) raised to the power ofx
. - Return value type: DOUBLE.
- Example:
select exp(4.5); +-------------------+ | exp(4.5) | +-------------------+ | 90.01713130052181 |
COS
cos(double x)
- Description: This function returns the cosine of
x
. - Return value type: DOUBLE.
- Example:
select cos(1.3); +---------------------+ | cos(1.3) | +---------------------+ | 0.26749882862458735 |
ACOS
acos(double x)
- Description: This function returns the arc cosine of
x
.If
x
is greater than 1 orx
is less than -1,null
is returned. - Return value type: DOUBLE.
- Example:
select acos(0.5); +--------------------+ | acos(0.5) | +--------------------+ | 1.0471975511965979 |
TAN
tan(double x)
- Description: This function returns the tangent of
x
. - Return value type: DOUBLE.
- Example:
select tan(8); +--------------------+ | tan(8) | +--------------------+ | -6.799711455220379 |
ATAN
atan(double x)
- Description: This function returns the arc tangent of
x
. - Return value type: DOUBLE.
- Example:
select atan(0.5); +--------------------+ | atan(0.5) | +--------------------+ | 0.4636476090008061 |
ATAN2
atan2(double x, double y)
atan(double x, double y)
- Description: This function returns the arc tangent of the result of
x
divided byy
. - Return value type: DOUBLE.
- Example:
select atan2(0.5,0.3); +--------------------+ | atan2(0.5, 0.3) | +--------------------+ | 1.0303768265243125 |
select atan(0.5,0.3); +--------------------+ | atan(0.5, 0.3) | +--------------------+ | 1.0303768265243125 |
COT
cot(double x)
- Description: This function returns the cotangent of
x
. - Return value type: DOUBLE.
- Example:
select cot(1.234); +---------------------+ | cot(1.234) | +---------------------+ | 0.35013639786701445 |
ASIN
asin(double x)
- Description: This function returns the arc sine of
x
. - Return value type: DOUBLE.
- Example:
select asin(0.5); +--------------------+ | asin(0.5) | +--------------------+ | 0.5235987755982989 |
SIN
sin(double x)
- Description: This function returns the sine of
x
. - Return value type: DOUBLE.
- Example:
select sin(1.234); +--------------------+ | sin(1.234) | +--------------------+ | 0.9438182093746337 |
CRC32
crc32(varbinary x)
- Description: This function returns the Cyclic Redundancy Check (CRC) code of the
x
argument. - Return value type: LONG.
- Example:
select crc32(CAST('China' AS VARBINARY)); +------------------------------------+ | crc32(CAST('China' AS varbinary)) | +------------------------------------+ | 737014929 |