Search
Document Center

# MaxCompute:ROUND

Last Updated:Jul 21, 2023

Returns a number rounded to the specified decimal place.

## Syntax

``double|decimal round(<number>[, bigint <decimal_places>])``

## Parameters

• number: required. A value of the DOUBLE or DECIMAL type. If the input value is of the STRING or BIGINT type, the value is implicitly converted into a value of the DOUBLE type before calculation.

• decimal_places: optional. A constant of the BIGINT type. This parameter specifies the decimal place to which the number is rounded. If this parameter is not specified, the number is rounded to the ones place. The default value is 0.

Note

The value of decimal_places can be negative. A negative value indicates counting from the decimal point to the left, and the decimal part is excluded. If the value of decimal_places exceeds the length of the integer part, 0 is returned.

## Return value

A value of the DOUBLE or DECIMAL type is returned. The return value varies based on the following rules:

• If the value of number is of the DOUBLE or DECIMAL type, a value of the same type is returned.

• If the value of number is of the STRING or BIGINT type, a value of the DOUBLE type is returned.

• If the value of decimal_places is not of the BIGINT type, an error is returned.

• If the value of number or decimal_places is null, null is returned.

## Sample data

This section provides sample source data and examples for you to understand how to use the functions. In this topic, a table named mf_math_fun_t is created and data is inserted into the table. Sample statements:

``````create table if not exists mf_math_fun_t(
int_data     int,
bigint_data  bigint,
double_data  double,
decimal_data decimal,
float_data   float,
string_data  string
);
insert into mf_math_fun_t values
(null, -10, 0.525, 0.525BD, cast(0.525 as float), '10'),
(-20, null, -0.1, -0.1BD, cast(-0.1 as float), '-10'),
(0, -1, null, 20.45BD, cast(-1 as float), '30'),
(-40, 4, 0.89, null, cast(0.89 as float), '-30'),
(5, -50, -1, -1BD, null, '50'),
(-60, 6, 1.5, 1.5BD, cast(1.5 as float), '-50'),
(-1, -70, -7.5, -7.5BD, cast(-7.5 as float),null ),
(-80, 1, -10.2, -10.2BD, cast(-10.2 as float), '-1' ),
(9, -90, 2.58, 2.58BD, cast(2.58 as float), '0'),
(-100, 10, -5.8, -5.8BD, cast(-5.8 as float), '-90');``````

Query data from the mf_math_fun_t table. Sample statement:

``````select * from mf_math_fun_t;
-- The following result is returned:
+------------+-------------+-------------+--------------+------------+-------------+
| int_data   | bigint_data | double_data | decimal_data | float_data | string_data |
+------------+-------------+-------------+--------------+------------+-------------+
| NULL       | -10         | 0.525       | 0.525        | 0.525      | 10          |
| -20        | NULL        | -0.1        | -0.1         | -0.1       | -10         |
| 0          | -1          | NULL        | 20.45        | -1.0       | 30          |
| -40        | 4           | 0.89        | NULL         | 0.89       | -30         |
| 5          | -50         | -1.0        | -1           | NULL       | 50          |
| -60        | 6           | 1.5         | 1.5          | 1.5        | -50         |
| -1         | -70         | -7.5        | -7.5         | -7.5       | NULL        |
| -80        | 1           | -10.2       | -10.2        | -10.2      | -1          |
| 9          | -90         | 2.58        | 2.58         | 2.58       | 0           |
| -100       | 10          | -5.8        | -5.8         | -5.8       | -90         |
+------------+-------------+-------------+--------------+------------+-------------+``````

## Example: static data

``````-- The value 125.0 is returned.
select round(125.315);
-- The value 125.3 is returned.
select round(125.315, 1);
-- The value 125.32 is returned.
select round(125.315, 2);
-- The value 125.315 is returned.
select round(125.315, 3);
-- The value -125.32 is returned.
select round(-125.315, 2);
-- The value 100.0 is returned.
select round(123.345, -2);
-- The value null is returned.
select round(null);
-- The value 123.345 is returned.
select round(123.345, 4);
-- The value 0.0 is returned.
select round(123.345, -4);``````

## Examples: table data

Return numbers that are rounded to the specified decimal place in a column based on the sample data. Sample statement:

``select round(bigint_data, 1) as bigint_new, round(double_data, 2) as double_new, round(decimal_data, 1) as decimal_new, round(string_data) as string_new from mf_math_fun_t;``

The following result is returned:

``````+------------+------------+-------------+------------+
| bigint_new | double_new | decimal_new | string_new |
+------------+------------+-------------+------------+
| -10.0      | 0.53       | 0.5         | 10.0       |
| NULL       | -0.1       | -0.1        | -10.0      |
| -1.0       | NULL       | 20.5        | 30.0       |
| 4.0        | 0.89       | NULL        | -30.0      |
| -50.0      | -1.0       | -1          | 50.0       |
| 6.0        | 1.5        | 1.5         | -50.0      |
| -70.0      | -7.5       | -7.5        | NULL       |
| 1.0        | -10.2      | -10.2       | -1.0       |
| -90.0      | 2.58       | 2.6         | 0.0        |
| 10.0       | -5.8       | -5.8        | -90.0      |
+------------+------------+-------------+------------+``````

## Related functions

ROUND is a mathematical function. For more information about functions related to data computing and conversion, see Mathematical functions.