Numeric functions syntax reference and examples - AnalyticDB

AnalyticDB for MySQL supports the following numeric functions for arithmetic, rounding, trigonometric, logarithmic, and other mathematical operations on numeric data.

Function index

Category	Function	Description
Arithmetic	ABS	Returns the absolute value
	SIGN	Returns the sign of a number
	SQRT	Returns the square root
	EXP	Returns e raised to the power of x
	POWER / POW	Returns x raised to the power of y
	CRC32	Returns the cyclic redundancy check (CRC) value
Rounding	CEILING / CEIL	Rounds up to the nearest integer
	FLOOR	Rounds down to the nearest integer
	ROUND	Rounds to a specified number of decimal places
Trigonometric	SIN	Returns the sine
	COS	Returns the cosine
	TAN	Returns the tangent
	COT	Returns the cotangent
	ASIN	Returns the arc sine
	ACOS	Returns the arc cosine
	ATAN	Returns the arc tangent
	ATAN2	Returns the arc tangent of x/y
	DEGREES	Converts radians to degrees
	RADIANS	Converts degrees to radians
	PI	Returns the value of pi
Logarithmic	LN	Returns the natural logarithm
	LOG	Returns the natural logarithm, or the logarithm to a specified base
	LOG2	Returns the base-2 logarithm
	LOG10	Returns the base-10 logarithm
Random	RAND	Returns a random floating-point number

ABS

abs(expr)

Returns the absolute value of expr.

Arguments

expr: The numeric expression. Accepted types: TINYINT, SMALLINT, INT, BIGINT, DOUBLE, FLOAT, or DECIMAL.

Return type

Input type	Return type
TINYINT, SMALLINT, INT, BIGINT	BIGINT
DOUBLE, FLOAT	DOUBLE
DECIMAL	DECIMAL

Examples

SELECT abs(4.5);

+----------+
| abs(4.5) |
+----------+
|      4.5 |
+----------+

SELECT abs(-4);

+--------+
| abs(4) |
+--------+
|      4 |
+--------+

ACOS

acos(expr)

Returns the arc cosine of expr, in radians.

Arguments

expr: A DOUBLE value. Must be in the range [-1, 1].

Return type: DOUBLE.

Usage notes

If expr is greater than 1 or less than -1, NULL is returned.

Example

SELECT acos(0.5);

+--------------------+
| acos(0.5)          |
+--------------------+
| 1.0471975511965979 |
+--------------------+

ASIN

asin(expr)

Returns the arc sine of expr, in radians.

Arguments

expr: A DOUBLE value. Must be in the range [-1, 1].

Return type: DOUBLE.

Usage notes

If expr is greater than 1 or less than -1, NULL is returned.

Example

SELECT asin(0.5);

+--------------------+
| asin(0.5)          |
+--------------------+
| 0.5235987755982989 |
+--------------------+

ATAN

atan(expr)

Returns the arc tangent of expr, in radians.

Arguments

expr: A DOUBLE value.

Return type: DOUBLE.

Example

SELECT atan(0.5);

+--------------------+
| atan(0.5)          |
+--------------------+
| 0.4636476090008061 |
+--------------------+

ATAN2

atan2(x, y)

Returns the arc tangent of x divided by y, in radians.

Arguments

x: A DOUBLE value (the numerator).
y: A DOUBLE value (the denominator).

Return type: DOUBLE.

Example

SELECT atan2(0.5, 0.3);

+--------------------+
| atan2(0.5,0.3)     |
+--------------------+
| 1.0303768265243125 |
+--------------------+

CEILING or CEIL

ceiling(expr)
ceil(expr)

Returns expr rounded up to the nearest integer.

Arguments

expr: The numeric expression. Accepted types: TINYINT, SMALLINT, INT, BIGINT, DOUBLE, FLOAT, or DECIMAL.

Return type

Input type	Return type
TINYINT, SMALLINT, INT, BIGINT	BIGINT
DOUBLE, FLOAT	DOUBLE
DECIMAL	DECIMAL

Examples

SELECT ceiling(4);

+------------+
| ceiling(4) |
+------------+
|          4 |
+------------+

SELECT ceil(-4.5);

+------------+
| ceil(-4.5) |
+------------+
|         -4 |
+------------+

COS

cos(expr)

Returns the cosine of expr, where expr is in radians.

Arguments

expr: A DOUBLE value (angle in radians).

Return type: DOUBLE.

Example

SELECT cos(1.3);

+---------------------+
| cos(1.3)            |
+---------------------+
| 0.26749882862458735 |
+---------------------+

COT

cot(expr)

Returns the cotangent of expr, where expr is in radians.

Arguments

expr: A DOUBLE value (angle in radians).

Return type: DOUBLE.

Example

SELECT cot(1.234);

+---------------------+
| cot(1.234)          |
+---------------------+
| 0.35013639786701445 |
+---------------------+

CRC32

crc32(expr)

Returns the cyclic redundancy check (CRC) value of expr.

Arguments

expr: A VARBINARY value.

Return type: BIGINT.

Example

SELECT crc32('China');

+-----------------+
| crc32('China')  |
+-----------------+
|       737014929 |
+-----------------+

DEGREES

degrees(expr)

Converts expr from radians to degrees.

Arguments

expr: A DOUBLE value (angle in radians).

Return type: DOUBLE.

Example

SELECT degrees(1.3);

+-------------------+
| degrees(1.3)      |
+-------------------+
| 74.48451336700703 |
+-------------------+

EXP

exp(expr)

Returns e (the base of the natural logarithm) raised to the power of expr.

Arguments

expr: A DOUBLE value (the exponent).

Return type: DOUBLE.

Example

SELECT exp(4.5);

+-------------------+
| exp(4.5)          |
+-------------------+
| 90.01713130052181 |
+-------------------+

FLOOR

floor(expr)

Returns expr rounded down to the nearest integer.

Arguments

expr: The numeric expression. Accepted types: TINYINT, SMALLINT, INT, BIGINT, DOUBLE, FLOAT, or DECIMAL.

Return type

Input type	Return type
TINYINT, SMALLINT, INT, BIGINT	BIGINT
DOUBLE, FLOAT	DOUBLE
DECIMAL	DECIMAL

Usage notes

AnalyticDB for MySQL converts numeric literals like 0.8 to DECIMAL. The return type follows the input type — floor(0.8) returns a DECIMAL 0, not an integer 0.
Division expressions like 4/5 evaluate as DOUBLE, so floor(4/5) returns 0.0 (DOUBLE). To get an integer result, wrap the output in a CAST function.

Examples

SELECT floor(7);

+----------+
| floor(7) |
+----------+
|        7 |
+----------+

floor(0.8) — input 0.8 is treated as DECIMAL, so the return type is DECIMAL:

SELECT floor(0.8);

+-------------+
| floor(0.8)  |
+-------------+
|           0 |
+-------------+

floor(4/5) — the expression 4/5 evaluates to DOUBLE, so the return type is DOUBLE:

SELECT floor(4/5);

+-------------+
| floor(4/5)  |
+-------------+
|         0.0 |
+-------------+

LN

ln(expr)

Returns the natural logarithm of expr.

Arguments

expr: A DOUBLE value.

Return type: DOUBLE.

Example

SELECT ln(2.718281828459045);

+-----------------------+
| ln(2.718281828459045) |
+-----------------------+
|                   1.0 |
+-----------------------+

LOG

log(expr)
log(base, expr)

Returns the logarithm of expr. With one argument, returns the natural logarithm. With two arguments, returns the logarithm of expr to the specified base.

Arguments

expr: A DOUBLE value (the input expression).
base (optional): A DOUBLE value (the logarithm base).

Return type: DOUBLE.

Examples

Natural logarithm of 16:

SELECT log(16);

+-------------------+
| log(16)           |
+-------------------+
| 2.772588722239781 |
+-------------------+

Logarithm of 100 to the base 10:

SELECT log(10, 100);

+-------------+
| log(10,100) |
+-------------+
|         2.0 |
+-------------+

LOG2

log2(expr)

Returns the base-2 logarithm of expr.

Arguments

expr: A DOUBLE value.

Return type: DOUBLE.

Example

SELECT log2(8.7654);

+-------------------+
| log2(8.7654)      |
+-------------------+
| 3.131819928389146 |
+-------------------+

LOG10

log10(expr)

Returns the base-10 logarithm of expr.

Arguments

expr: A DOUBLE value.

Return type: DOUBLE.

Example

SELECT log10(100.876);

+--------------------+
| log10(100.876)     |
+--------------------+
| 2.0037878529824615 |
+--------------------+

PI

pi()

Returns the value of pi (the ratio of a circle's circumference to its diameter).

Return type: DOUBLE.

Example

SELECT pi();

+-------------------+
| pi()              |
+-------------------+
| 3.141592653589793 |
+-------------------+

POWER or POW

power(x, y)
pow(x, y)

Returns x raised to the power of y.

Arguments

x: A DOUBLE value (the base).
y: A DOUBLE value (the exponent).

Return type: DOUBLE.

Examples

SELECT power(1.2, 3.4);

+-------------------+
| power(1.2,3.4)    |
+-------------------+
| 1.858729691979481 |
+-------------------+

SELECT pow(-2, -3);

+------------+
| pow(-2,-3) |
+------------+
|     -0.125 |
+------------+

RADIANS

radians(expr)

Converts expr from degrees to radians.

Arguments

expr: A DOUBLE value (angle in degrees).

Return type: DOUBLE.

Example

SELECT radians(60.0);

+--------------------+
| radians(60.0)      |
+--------------------+
| 1.0471975511965976 |
+--------------------+

RAND

rand()
rand() * (y - x) + x

Returns a random floating-point number. rand() returns a value in [0, 1). To get a random number in a custom range [x, y), use rand() * (y - x) + x.

Return type: DOUBLE.

Usage notes

rand() takes no arguments.
The result is uniformly distributed: greater than or equal to x and less than y.

Examples

Random number in [0, 1):

SELECT rand();

+--------------------+
| rand()             |
+--------------------+
| 0.6613712594764614 |
+--------------------+

Random number in [3, 12):

SELECT rand() * (12 - 3) + 3;

+-------------------+
| rand()*(12-3)+3   |
+-------------------+
| 9.073329270781976 |
+-------------------+

ROUND

round(expr [, scale])

Returns expr rounded to scale decimal places. The default scale is 0. The return type matches the input type.

Arguments

expr: The numeric expression to round. Accepted types: TINYINT, SMALLINT, INT, BIGINT, DOUBLE, FLOAT, or DECIMAL.
scale (optional): An integer specifying the number of decimal places. Defaults to 0.

Return type

Input type	Return type
TINYINT, SMALLINT, INT, BIGINT	BIGINT
DOUBLE, FLOAT	DOUBLE
DECIMAL	DECIMAL

Usage notes

If expr is NULL, NULL is returned.
If scale is greater than 0, the result is rounded to that many decimal places.
If scale equals 0, the result is rounded to the nearest integer.
If scale is less than 0, the result is rounded to that many places to the left of the decimal point. For example, scale = -1 rounds to the nearest 10.

Examples

Round to the nearest integer (scale = 0):

SELECT round(345.983, 0);

+------------------+
| round(345.983,0) |
+------------------+
|          346.000 |
+------------------+

Round to one decimal place (scale = 1):

SELECT round(345.123, 1);

+------------------+
| round(345.123,1) |
+------------------+
|          345.100 |
+------------------+

Round to one place left of the decimal point (scale = -1):

SELECT round(344.984, -1);

+-------------------+
| round(344.984,-1) |
+-------------------+
|           340.000 |
+-------------------+

Round to four places left of the decimal point (scale = -4):

SELECT round(345.984, -4);

+-------------------+
| round(345.984,-4) |
+-------------------+
|             0.000 |
+-------------------+

SIGN

sign(expr)

Returns the sign of expr as an integer: 1 for positive, 0 for zero, and -1 for negative.

Arguments

expr: The numeric expression. Accepted types: TINYINT, SMALLINT, INT, BIGINT, DOUBLE, FLOAT, or DECIMAL.

Return type: BIGINT.

Examples

SELECT sign(12);

+----------+
| sign(12) |
+----------+
|        1 |
+----------+

SELECT sign(-4.5);

+------------+
| sign(-4.5) |
+------------+
|         -1 |
+------------+

SIN

sin(expr)

Returns the sine of expr, where expr is in radians.

Arguments

expr: A DOUBLE value (angle in radians).

Return type: DOUBLE.

Example

SELECT sin(1.234);

+--------------------+
| sin(1.234)         |
+--------------------+
| 0.9438182093746337 |
+--------------------+

SQRT

sqrt(expr)

Returns the square root of expr.

Arguments

expr: A DOUBLE value.

Return type: DOUBLE.

Example

SELECT sqrt(4.222);

+-------------------+
| sqrt(4.222)       |
+-------------------+
| 2.054750593137766 |
+-------------------+

TAN

tan(expr)

Returns the tangent of expr, where expr is in radians.

Arguments

expr: A DOUBLE value (angle in radians).

Return type: DOUBLE.

Example

SELECT tan(8);

+--------------------+
| tan(8)             |
+--------------------+
| -6.799711455220379 |
+--------------------+