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 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 |

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 |