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.

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 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 result is rounded to the number of digits left of the decimal point.
  • 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 of y to the base x.
  • 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 of y.
  • 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 of x.
  • 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 or x 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 by y.
  • 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 |