Relational Data - Functions - Rounding Functions

floor

Returns the largest rounded number less than or equal x. A rounded number is a multiple of 1 / 10 * N, or the nearest number of the appropriate data type if 1 / 10 * N isn’t exact.

Integer arguments may be rounded with negative N argument, with non-negative N the function returns x, i.e. does nothing.

If rounding causes an overflow (for example, floor(-128, -1)), the result is undefined.

Syntax

floor(x[, N])

Parameters

• x - The value to round. Float*, Decimal*, or (U)Int*.
• N . (U)Int*. The default is zero, which means rounding to an integer. Can be negative.

Returned value

A rounded number of the same type as x.

Examples

Query:

SELECT floor(123.45, 1) AS rounded

Result:

┌─rounded─┐
│   123.4 │
└─────────┘

Query:

SELECT floor(123.45, -1)

Result:

┌─rounded─┐
│     120 │
└─────────┘

ceiling

Like floor but returns the smallest rounded number greater than or equal x.

Syntax

ceiling(x[, N])

Alias: ceil

truncate

Like floor but returns the rounded number with largest absolute value that has an absolute value less than or equal to x’s.

Syntax

truncate(x[, N])

Alias: trunc.

Example

Query:

SELECT truncate(123.499, 1) as res;

┌───res─┐
│ 123.4 │
└───────┘

round

Rounds a value to a specified number of decimal places.

The function returns the nearest number of the specified order. If the input value has equal distance to two neighboring numbers, the function uses banker’s rounding for Float* inputs and rounds away from zero for the other number types (Decimal*.

Syntax

round(x[, N])

Arguments

• x — A number to round. Float*, Decimal*, or (U)Int*.
• N — The number of decimal places to round to. Integer. Defaults to 0.
  • If N > 0, the function rounds to the right of the decimal point.
  • If N < 0, the function rounds to the left of the decimal point.
  • If N = 0, the function rounds to the next integer.

Returned value:

A rounded number of the same type as x.

Examples

Example with Float inputs:

SELECT number / 2 AS x, round(x) FROM system.numbers LIMIT 3;

┌───x─┬─round(divide(number, 2))─┐
│   0 │                        0 │
│ 0.5 │                        0 │
│   1 │                        1 │
└─────┴──────────────────────────┘

Example with Decimal inputs:

SELECT cast(number / 2 AS  Decimal(10,4)) AS x, round(x) FROM system.numbers LIMIT 3;

┌───x─┬─round(CAST(divide(number, 2), 'Decimal(10, 4)'))─┐
│   0 │                                                0 │
│ 0.5 │                                                1 │
│   1 │                                                1 │
└─────┴──────────────────────────────────────────────────┘

To retain trailing zeros, enable setting output_format_decimal_trailing_zeros:

SELECT cast(number / 2 AS  Decimal(10,4)) AS x, round(x) FROM system.numbers LIMIT 3 settings output_format_decimal_trailing_zeros=1;

┌──────x─┬─round(CAST(divide(number, 2), 'Decimal(10, 4)'))─┐
│ 0.0000 │                                           0.0000 │
│ 0.5000 │                                           1.0000 │
│ 1.0000 │                                           1.0000 │
└────────┴──────────────────────────────────────────────────┘

Examples of rounding to the nearest number:

round(3.2, 0) = 3
round(4.1267, 2) = 4.13
round(22,-1) = 20
round(467,-2) = 500
round(-467,-2) = -500

Banker’s rounding.

round(3.5) = 4
round(4.5) = 4
round(3.55, 1) = 3.6
round(3.65, 1) = 3.6

See Also

• roundBankers

roundBankers

Rounds a number to a specified decimal position.

If the rounding number is halfway between two numbers, the function uses banker’s rounding. Banker’s rounding is a method of rounding fractional numbers When the rounding number is halfway between two numbers, it’s rounded to the nearest even digit at the specified decimal position. For example: 3.5 rounds up to 4, 2.5 rounds down to 2. It’s the default rounding method for floating point numbers defined in IEEE 754. The round function performs the same rounding for floating point numbers. The roundBankers function also rounds integers the same way, for example, roundBankers(45, -1) = 40.

In other cases, the function rounds numbers to the nearest integer.

Using banker’s rounding, you can reduce the effect that rounding numbers has on the results of summing or subtracting these numbers.

For example, sum numbers 1.5, 2.5, 3.5, 4.5 with different rounding:

• No rounding: 1.5 + 2.5 + 3.5 + 4.5 = 12.
• Banker’s rounding: 2 + 2 + 4 + 4 = 12.
• Rounding to the nearest integer: 2 + 3 + 4 + 5 = 14.

Syntax

roundBankers(x [, N])

Arguments

- `N > 0` — The function rounds the number to the given position right of the decimal point. Example: `roundBankers(3.55, 1) = 3.6`.
- `N < 0` — The function rounds the number to the given position left of the decimal point. Example: `roundBankers(24.55, -1) = 20`.
- `N = 0` — The function rounds the number to an integer. In this case the argument can be omitted. Example: `roundBankers(2.5) = 2`.

• x — A number to round. Float*, Decimal*, or (U)Int*.
• N — The number of decimal places to round to. Integer. Defaults to 0.
  • If N > 0, the function rounds to the right of the decimal point.
  • If N < 0, the function rounds to the left of the decimal point.
  • If N = 0, the function rounds to the next integer.

Returned value

A value rounded by the banker’s rounding method.

Examples

Query:

 SELECT number / 2 AS x, roundBankers(x, 0) AS b fROM system.numbers limit 10

Result:

┌───x─┬─b─┐
│   0 │ 0 │
│ 0.5 │ 0 │
│   1 │ 1 │
│ 1.5 │ 2 │
│   2 │ 2 │
│ 2.5 │ 2 │
│   3 │ 3 │
│ 3.5 │ 4 │
│   4 │ 4 │
│ 4.5 │ 4 │
└─────┴───┘

Examples of Banker’s rounding:

roundBankers(0.4) = 0
roundBankers(-3.5) = -4
roundBankers(4.5) = 4
roundBankers(3.55, 1) = 3.6
roundBankers(3.65, 1) = 3.6
roundBankers(10.35, 1) = 10.4
roundBankers(10.755, 2) = 10.76

See Also

• round

roundToExp2

Accepts a number. If the number is less than one, it returns 0. Otherwise, it rounds the number down to the nearest (whole non-negative) degree of two.

Syntax

roundToExp2(num)

Parameters

• num: A number representing an age in years. UInt/Float.

Returned value

• 0, for num Failed to parse (SVG (MathML can be enabled via browser plugin): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\textstyle \lt 1} . UInt8.
• num rounded down to the nearest (whole non-negative) degree of two. UInt/Float equivalent to the input type.

Example

Query:

SELECT *, roundToExp2(*) FROM system.numbers WHERE number IN (0, 2, 5, 10, 19, 50)

Result:

┌─number─┬─roundToExp2(number)─┐
│      0 │                   0 │
│      2 │                   2 │
│      5 │                   4 │
│     10 │                   8 │
│     19 │                  16 │
│     50 │                  32 │
└────────┴─────────────────────┘

roundDuration

Accepts a number. If the number is less than one, it returns 0. Otherwise, it rounds the number down to numbers from the set of commonly used durations: 1, 10, 30, 60, 120, 180, 240, 300, 600, 1200, 1800, 3600, 7200, 18000, 36000.

Syntax

roundDuration(num)

Parameters

• num: A number to round to one of the numbers in the set of common durations. UInt/Float.

Returned value

• 0, for num Failed to parse (unknown function "\lt"): {\textstyle \lt 1} .
• Otherwise, one of: 1, 10, 30, 60, 120, 180, 240, 300, 600, 1200, 1800, 3600, 7200, 18000, 36000. UInt16.

Example

Query:

SELECT *, roundDuration(*) FROM system.numbers WHERE number IN (0, 9, 19, 47, 101, 149, 205, 271, 421, 789, 1423, 2345, 4567, 9876, 24680, 42573)

Result:

┌─number─┬─roundDuration(number)─┐
│      0 │                     0 │
│      9 │                     1 │
│     19 │                    10 │
│     47 │                    30 │
│    101 │                    60 │
│    149 │                   120 │
│    205 │                   180 │
│    271 │                   240 │
│    421 │                   300 │
│    789 │                   600 │
│   1423 │                  1200 │
│   2345 │                  1800 │
│   4567 │                  3600 │
│   9876 │                  7200 │
│  24680 │                 18000 │
│  42573 │                 36000 │
└────────┴───────────────────────┘

roundAge

Accepts a number within various commonly used ranges of human age and returns either a maximum or a minimum within that range.

Syntax

roundAge(num)

Parameters

• age: A number representing an age in years. UInt/Float.

Returned value

• Returns 0, for Failed to parse (unknown function "\lt"): {\textstyle age \lt 1} .
• Returns 17, for ${\textstyle 1\leq age\leq 17}$.
• Returns 18, for ${\textstyle 18\leq age\leq 24}$.
• Returns 25, for ${\textstyle 25\leq age\leq 34}$.
• Returns 35, for ${\textstyle 35\leq age\leq 44}$.
• Returns 45, for ${\textstyle 45\leq age\leq 54}$.
• Returns 55, for ${\textstyle age\geq 55}$.

Type: UInt8.

Example

Query:

SELECT *, roundAge(*) FROM system.numbers WHERE number IN (0, 5, 20, 31, 37, 54, 72);

Result:

┌─number─┬─roundAge(number)─┐
│      0 │                0 │
│      5 │               17 │
│     20 │               18 │
│     31 │               25 │
│     37 │               35 │
│     54 │               45 │
│     72 │               55 │
└────────┴──────────────────┘

roundDown

Accepts a number and rounds it down to an element in the specified array. If the value is less than the lowest bound, the lowest bound is returned.

Syntax

roundDown(num, arr)

Parameters

• num: A number to round down. Numeric.
• arr: Array of elements to round age down to. Array of UInt/Float type.

Returned value

• Number rounded down to an element in arr. If the value is less than the lowest bound, the lowest bound is returned. UInt/Float type deduced from the type of arr.

Example

Query:

SELECT *, roundDown(*, [3, 4, 5]) FROM system.numbers WHERE number IN (0, 1, 2, 3, 4, 5)

Result:

┌─number─┬─roundDown(number, [3, 4, 5])─┐
│      0 │                            3 │
│      1 │                            3 │
│      2 │                            3 │
│      3 │                            3 │
│      4 │                            4 │
│      5 │                            5 │
└────────┴──────────────────────────────┘