Mathematical functions
The following table describes the mathematical functions that are supported by PolarDB-X.
PolarDB-X supports the following mathematical functions.
| Function | Description | Example |
|---|---|---|
| ABS(x) | Returns the absolute value of the x argument. | Calculate the absolute value of -1. SELECT ABS(-1); Sample response: --1 |
| ACOS(x) | Calculates the arccosine of the x argument. The unit of the x argument is in radians. | SELECT ACOS(0.25); |
| ASIN(x) | Calculates the arcsine of the x argument. The unit of the x argument is in radians. | SELECT ASIN(0.25); |
| ATAN(x) | Calculates the arctangent of the x argument. The unit of the x argument is in radians. | SELECT ATAN(2.5); |
| ATAN2(n, m) | Calculates the arctangent of the angle between the ray to the point (n, m) and the positive x-axis. The unit of the angle is in radians. | SELECT ATAN2(-0.8, 2); |
| AVG(expression) | Returns the average value of an expression. The expression is a field. | Calculate the average value of the Price field in the Products table. SELECT AVG(Price) AS AveragePrice FROM Products; |
| CEIL(x) | Returns the minimum integer that is not less than the x argument. | SELECT CEIL(1.5); Sample response: --2 |
| CEILING(x) | Returns the minimum integer that is not less than the x argument. | SELECT CEILING(1.5); Sample response: --2 |
| COS(x) | Calculates the cosine of the x argument. The unit of the x argument is in radians. | SELECT COS(2); |
| COT(x) | Calculates the cotangent of the x argument. The unit of the x argument is in radians. | SELECT COT(6); |
| COUNT(expression) | Returns the number of records in an expression. The expression is a field or an asterisk (*). | Query the number of records in the ProductID field in the Products table. SELECT COUNT(ProductID) AS NumberOfProducts FROM Products; |
| DEGREES(x) | Converts an angle x that is expressed in radians to degrees. | SELECT DEGREES(3.1415926535898); Sample response: -- 180.0000000000004 |
| n DIV m | Returns the integer portion of the quotient that is obtained by dividing operand n by operand m. | Query the integer portion of the quotient that is obtained by dividing 10 by 5. SELECT 10 DIV 5; Sample response: -- 2 |
| EXP(x) | Calculates the value of e to the power of x. In the formula, e indicates Euler's number. | Calculate the value of e to the power of 3. SELECT EXP(3); Sample response: -- 20.085536923187668 |
| FLOOR(x) | Returns the maximum integer that is not greater than the x argument. | Query the maximum integer that is not greater than 1.5. SELECT FLOOR(1.5); Sample response: -- 1 |
| GREATEST(expr1, expr2, expr3, ...) | Returns the maximum value from a list of values. | Query the maximum number from a list of numbers. SELECT GREATEST(3, 12, 34, 8, 25); Sample response: -- 34 Query the maximum string from a list of strings. SELECT GREATEST("Google", "Runoob", "Apple"); Sample response: -- Runoob |
| LEAST(expr1, expr2, expr3, ...) | Returns the minimum value from a list of values. | Query the minimum number from a list of numbers. SELECT LEAST(3, 12, 34, 8, 25); Sample response: -- 3 Query the minimum string from a list of strings. SELECT LEAST("Google", "Runoob", "Apple"); Sample response: -- Apple |
| LN | Calculates the natural logarithm of a number. The natural logarithm of a number is its logarithm to the base of the mathematical constant e. | Calculate the natural logarithm of 2. SELECT LN(2); Sample response: -- 0.6931471805599453 |
| LOG(x) or LOG(base, x) | Returns the natural logarithm of x or the logarithm of x to a specified base. The natural logarithm of a number is its logarithm to the base of the mathematical constant e. If the base argument is specified, the function returns the logarithm of x to the base argument. | SELECT LOG(20.085536923188) Sample response: -- 3 SELECT LOG(2, 4); Sample response: -- 2 |
| LOG10(x) | Calculates the decimal logarithm of x. | SELECT LOG10(100) ; Sample response: -- 2 |
| LOG2(x) | Calculates the binary logarithm of x. | Calculate the binary logarithm of 6. SELECT LOG2(6); Sample response: -- 2.584962500721156 |
| MAX(expression) | Returns the maximum value in an expression. | Query the maximum value in the Price field in the Products table. SELECT MAX(Price) AS LargestPrice FROM Products; |
| MIN(expression) | Returns the minimum value in an expression. | Query the minimum value in the Price field in the Products table. SELECT MIN(Price) AS MinPrice FROM Products; |
| MOD(x,y) | Calculates the remainder that is obtained by dividing x by y. | Calculate the remainder that is obtained by dividing 5 by 2. SELECT MOD(5,2); Sample response: -- 1 |
| PI() | Returns the value of pi that is rounded to six decimal places. Sample response: 3.141593. | SELECT PI(); Sample response: --3.141593 |
| POW(x,y) | Calculates the value of x to the power of y. | Calculate 2 to the power of 3. SELECT POW(2,3); Sample response: -- 8 |
| POWER(x,y) | Calculates the value of x to the power of y. | Calculate 2 to the power of 3. SELECT POWER(2,3); Sample response: -- 8 |
| RADIANS(x) | Converts an angle x that is expressed in degrees to radians. | Convert 180° to a value in radians. SELECT RADIANS(180); Sample response: -- 3.1415926535898 |
| RAND() | Returns a random number from 0 to 1. | SELECT RAND(); Sample response: --0.93099315644334 |
| ROUND(x) | Rounds the x argument to the nearest integer. | SELECT ROUND(1.23456; Sample response: --1 |
| SIGN(x) | Returns a number to indicate whether the x argument is 0, negative, or positive. The value -1 is returned if the x argument is negative. The value 0 is returned if the x argument is 0. The value 1 is returned if the x argument is positive. | SELECT SIGN(-10); Sample response: -- (-1) |
| SIN(x) | Calculates the sine of the x argument. The unit of the x argument is in radians. | SELECT SIN(RADIANS(30)); Sample response: -- 0.5 |
| SQRT(x) | Calculates the square root of the x argument. | Calculate the square root of 25. SELECT SQRT(25); Sample response: -- 5 |
| SUM(expression) | Calculates the sum of the values in an expression. | Calculate the sum of the values in the Quantity field in the OrderDetails table. SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails; |
| TAN(x) | Calculates the tangent of the x argument. The unit of the x argument is in radians. | SELECT TAN(1.75); Sample response: -- -5.52037992250933 |
| TRUNCATE(x,y) | Truncates the argument x to y decimal places without rounding a decimal value. The ROUND(x) function rounds x to the nearest integer. | SELECT TRUNCATE(1.23456,3); Sample response: -- 1.234 |