Mathematical functions |V2.2.77|OceanBase Database| docs|Distributed Database

Mathematical functions

Last Updated：2023-08-18 09:26:34 Updated

ROUND

Declaration

ROUND(X), ROUND(X,D)

Description

This function rounds off a returned value to the specified length or precision.

The returned value X approximates to the nearest integer. Given two parameters, the returned value X is rounded off to D places to the right of the decimal point. To reserve X to D places to the left of the decimal point, set D to negative.

The return value is of the same type as that of the first independent variable (assuming that it is an integer, double-precision floating-point number, or decimal). This means that for an integer parameter, the result is also an integer (no fractional part).

For exact-value numbers, ROUND() complies with the rule of "round half away from zero" or "round toward nearest". For a value whose fractional part is 0.5 or greater than 0.5, the value is rounded up to the nearest integer if it is positive and rounded down to the nearest integer if it is negative.In other words, the value is rounded away from zero. For a value whose fractional part is less than 0.5, the value is rounded down to the nearest integer if it is positive and rounded up to the nearest integer if it is negative.

For approximate-value numbers, ROUND() complies with the rule of "round to nearest even": A value whose fractional part is exactly halfway between two integers is rounded to the nearest even integer.

Example

obclient> select round(2.15,2);
+---------------+
| round(2.15,2) |
+---------------+
|          2.15 |
+---------------+
1 row in set (0.00 sec)

obclient> select round(2555e-2,1);
+------------------+
| round(2555e-2,1) |
+------------------+
|             25.6 |
+------------------+
1 row in set (0.01 sec)

obclient> select round(25e-1), round(25.3e-1),round(35e-1);
+--------------+----------------+--------------+
| round(25e-1) | round(25.3e-1) | round(35e-1) |
+--------------+----------------+--------------+
|            2 |              3 |            4 |
+--------------+----------------+--------------+
1 row in set (0.00 sec)

CEIL

Declaration

CEIL(expr)

Description

This function returns the smallest integer that is greater than or equal to the specified expression.

Comparison operation is also supported. The comparison result is a Boolean value, which is converted to a numeric value. The result is 1 (TRUE) or 0 (FALSE).

If you enter NULL, the return value is NULL.

If you enter a string of digits, it can be automatically converted to a numeric value.

The return value is converted to a BIGINT value.

Example

obclient> select ceil(1.2), ceil(-1.2), ceil(1+1.5), ceil(1=1),ceil(1<1),ceil(null);
+-----------+------------+-------------+-----------+-----------+------------+
| ceil(1.2) | ceil(-1.2) | ceil(1+1.5) | ceil(1=1) | ceil(1<1) | ceil(null) |
+-----------+------------+-------------+-----------+-----------+------------+
|         2 |         -1  |           3  |         1 |         0 |       NULL |
+-----------+------------+-------------+-----------+-----------+------------+
1 row in set (0.00 sec)

obclient> select ceil(name);
ERROR 1054 (42S22): Unknown column 'name' in 'field list'

obclient> select ceil('2');
+-----------+
| ceil('2') |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

FLOOR

Declaration

FLOOR(expr)

Description

This function is similar to the CEIL(expr) function, and returns the maximum integer smaller than or equal to the specified expression.

Comparison operation is also supported. The comparison result is a Boolean value, which is converted to a numeric value. The result is 1 (TRUE) or 0 (FALSE).

If you enter NULL, the return value is NULL.

If you enter a string of digits, it can be automatically converted to a numeric value.

The return value is converted to a BIGINT value.

Example

obclient> select floor(1.2), floor(-1.2), floor(1+1.5), floor(1=1),floor(1<1),floor(null);
+------------+-------------+--------------+------------+------------+-------------+
| floor(1.2) | floor(-1.2) | floor(1+1.5) | floor(1=1) | floor(1<1) | floor(null) |
+------------+-------------+--------------+------------+------------+-------------+
|                                  |               1 |          140 |
+------------+-------------+--------------+------------+------------+-------------+
1 row in set (0.00 sec)

obclient> select floor(name);
ERROR 1054 (42S22): Unknown column 'name' in 'field list'

obclient> select floor('2');
+------------+
| floor('2') |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)

ABS

Declaration

ABS(expr)

Description

This function returns the absolute value of an expression. The return value type is the same as the data type of the numeric expression.

Comparison operation is also supported. The comparison result is a Boolean value, which is converted to a numeric value. The result is 1 (TRUE) or 0 (FALSE).

If you enter NULL, the return value is NULL.

If you enter a string of digits, it can be automatically converted to a numeric value.

The return value is converted to a BIGINT value.

Example

obclient> select abs(5), abs(-5.777), abs(0), abs(1/2), abs(1-5);
+--------+-------------+--------+----------+----------+
| abs(5) | abs(-5.777) | abs(0) | abs(1/2) | abs(1-5) |
+--------+-------------+--------+----------+----------+
| 5 | 5.777 | 0 | 0.5000 | 4 |
+--------+-------------+--------+----------+----------+
1 row in set (0.00 sec)

NEG

Declaration

NEG(expr)

Description

This function performs the complement operation on the operand. It subtracts the operand from zero and then returns the result.

Comparison operation is also supported. The comparison result is a Boolean value, which is converted to a numeric value. The result is 1 (TRUE) or 0 (FALSE). The complement operation is then performed on the result.

Example

obclient> select neg(1), neg(1+1), neg(2*3), neg(1=1), neg(5<1);
+--------+----------+----------+----------+----------+
| neg(1) | neg(1+1) | neg(2*3) | neg(1=1) | neg(5<1) |
+--------+----------+----------+----------+----------+
|     -1 |       -2 |       -6 |       -1 |        0 |
+--------+----------+----------+----------+----------+
1 row in set (0.01 sec)

SIGN

Declaration

SIGN(X)

Description

SIGN(X) returns the sign of a parameter as --1, 0, or 1, depending on the value of X. A negative value corresponds to the sign of --1 and a positive value corresponds to 1.

Comparison operation is also supported. The comparison result is a Boolean value, which is converted to a numeric value. The result is 1 (TRUE) or 0 (FALSE).

If you enter NULL, the return value is NULL.

Floating-point numbers and hexadecimal numbers are supported.

Example

obclient> SELECT SIGN(-32), SIGN(0), SIGN(234);
+-----------+---------+-----------+
| SIGN(-32) | SIGN(0) | SIGN(234) |
+-----------+---------+-----------+
|        -1 |       0 |         1 |
+-----------+---------+-----------+
1 row in set (0.01 sec)

obclient> select sign(null),sign(false),sign(0x01);
+------------+-------------+------------+
| sign(null) | sign(false) | sign(0x01) |
+------------+-------------+------------+
|       NULL |           0 |          1 |
+------------+-------------+------------+
1 row in set (0.00 sec)

CONV

Declaration

CONV(N, from_base, to_base)

Description

This function converts numbers of one number base to another. The return value is a string converted from from_base to to_base. Input parameter N can be an integer or string. The minimum base is 2 and the maximum base is 36. N is considered a signed number if to_base is a negative number. Otherwise, N is considered an unsigned number. If from_base is a negative number, it is considered an integer, and the sign is ignored. The N parameter supports only the int and string types. The from_base and to_base parameters support only the decimal int type, and the value range is [--36,--2] U [2,36].

Invalid inputs will cause errors. Invalid inputs include:

The from_base or to_base is not of the valid decimal int type.

The from_base or to_base exceeds the value range of [--36,--2] U [2,36].

N is not a valid numeric value. For example, the value is beyond the ranges of 0 to 9, a to z, and A to Z.

N is beyond the value range of from_base. For example from_base is 2, but N is 3.

N exceeds the maximum value range of int64: [--9223372036854775807,9223372036854775807].

Example

obclient> select conv(9223372036854775807,10,2);
+-----------------------------------------------------------------+
| conv(9223372036854775807,10,2) |
+-----------------------------------------------------------------+
| 111111111111111111111111111111111111111111111111111111111111111 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

obclient> select conv('-acc',21,-7);
+--------------------+
| conv('-acc',21,-7) |
+--------------------+
| -16425 |
+--------------------+
1 row in set (0.00 sec)

MOD

Declaration

MOD(N,M)

Description

This function returns the remainder of a number divided by another number. MOD(N,M), N % M, and N MOD M are equivalent.

MOD() works also for numeric values with a fractional part and returns the exact remainder after the division operation.

If a parameter in N or M is NULL, the return value is NULL. If M is 0, the return value is also 0.

Example

obclient> select mod(29,19), 29 mod 19, 29 % 19;
+------------+-----------+---------+
| mod(29,19) | 29 mod 19 | 29 % 19 |
+------------+-----------+---------+
|         10 |        10 |      10 |
+------------+-----------+---------+
1 row in set (0.00 sec)

POW

Declaration

POW(X,Y)

Description

This function returns the result of raising X to the power of Y.

If either of X orY is NULL, the return value is NULL.

Example

obclient> select pow(4,2), pow(4,-2), pow(1,null);
+----------+-----------+-------------+
| pow(4,2) | pow(4,-2) | pow(1,null) |
+----------+-----------+-------------+
|       16 |    0.0625 |        NULL |
+----------+-----------+-------------+
1 row in set (0.00 sec)

POWER

Declaration

POWER(X,Y)

Description

POWER(X,Y) and POW(X,Y) are synonymous functions.

RAND

Declaration

RAND([N])

Description

The RAND([N]) function accepts zero or one argument (N is called the random number seed) and returns a random floating-point number within the range of [0,1.0). To obtain a random integer in the range of [I, j), use the expression FLOOR(I + RAND() * (j - i)).

If the argument N is not specified, random number seed initialization is performed before the function is executed. Then, RAND() generates a random number based on this initial value. RAND() generates a different random number sequence each time.

If the argument N is specified, N is used as a seed to generate random numbers. Random values are generated based on whether N is a constant:

If N is a constant, N it is initialized as a random number seed before execution. Then, RAND(N) generates a random number based on this initial value. The same N value will generate the same random number sequence.

If N is a variable (for example, a column value), N is used as a random number seed to generate random numbers in each execution. The same N value will generate the same random number.

In addition to SECLET statements, RAND([N]) is also used in the WHERE, ORDER BY, and GROUP BY clauses and executed based on the preceding rules. For example, to randomly sort data in a table, run the following SQL statement: select from t1 order by rand(). To randomly sample 100 rows from a table, run the following SQL statement: select from t1 order by rand() limit 100.

Example

obclient> select a, B, rand() from t3;
+------+------+---------------------+
| a    | b    | rand()              |
+------+------+---------------------+
|    1 |    1 |   0.641815407799385 |
|    2 |    2 | 0.16825051248841966 |
|    3 |    3 |  0.9158063697775886 |
+------+------+---------------------+
3 rows in set (0.00 sec)

obclient> select a, B, rand() from t3;
+------+------+---------------------+
| a    | b    | rand()              |
+------+------+---------------------+
|    1 |    1 | 0.07428034215632857 |
|    2 |    2 |  0.6239826321825224 |
|    3 |    3 |   0.897072165177271 |
+------+------+---------------------+
3 rows in set (0.00 sec)

obclient> select a, B, rand(3) from t3;
+------+------+---------------------+
| a    | b    | rand(3)             |
+------+------+---------------------+
|    1 |    1 |  0.9057697559760601 |
|    2 |    2 | 0.37307905813034536 |
|    3 |    3 | 0.14808605345719125 |
+------+------+---------------------+
3 rows in set (0.00 sec)

obclient> select a, B, rand(3) from t3;
+------+------+---------------------+
| a    | b    | rand(3)             |
+------+------+---------------------+
|    1 |    1 |  0.9057697559760601 |
|    2 |    2 | 0.37307905813034536 |
|    3 |    3 | 0.14808605345719125 |
+------+------+---------------------+
3 rows in set (0.00 sec)

obclient> select a, B, rand(a), rand( B) from t3;
+------+------+---------------------+---------------------+
| a    | b    | rand(a)             | rand(b)             |
+------+------+---------------------+---------------------+
|    1 |    1 | 0.40540353712197724 | 0.40540353712197724 |
|    2 |    2 |  0.6555866465490187 |  0.6555866465490187 |
|    3 |    3 |  0.9057697559760601 |  0.9057697559760601 |
+------+------+---------------------+---------------------+
3 rows in set (0.00 sec)