Rounding functions - ClickHouse Documentation

The documentation below is generated from the system.functions system table

ceil

Introduced in: v1.1.0 Like floor but returns the smallest rounded number greater than or equal to x. If rounding causes an overflow (for example, ceiling(255, -1)), the result is undefined. Syntax

ceiling(x[, N])

Aliases: ceiling Arguments

x — The value to round. Float* or Decimal* or (U)Int*
N — Optional. The number of decimal places to round to. Defaults to zero, which means rounding to an integer. Can be negative. (U)Int*

Returned value Returns a rounded number of the same type as x. Float* or Decimal* or (U)Int* Examples Basic usage

Query

SELECT ceiling(123.45, 1) AS rounded

Response

┌─rounded─┐
│   123.5 │
└─────────┘

Negative precision

Query

SELECT ceiling(123.45, -1)

Response

┌─ceiling(123.45, -1)─┐
│                 130 │
└─────────────────────┘

floor

Introduced in: v1.1.0 Returns the largest rounded number less than or equal to x, where the 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 a negative N argument. With non-negative N the function returns x. If rounding causes an overflow (for example, floor(-128, -1)), the result is undefined. Syntax

floor(x[, N])

Arguments

x — The value to round. Float* or Decimal* or (U)Int*
N — Optional. The number of decimal places to round to. Defaults to zero, which means rounding to an integer. Can be negative. (U)Int*

Returned value Returns a rounded number of the same type as x. Float* or Decimal* or (U)Int* Examples Usage example

Query

SELECT floor(123.45, 1) AS rounded

Response

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

Negative precision

Query

SELECT floor(123.45, -1)

Response

┌─floor(123.45, -1)─┐
│               120 │
└───────────────────┘

round

Introduced in: v1.1.0 Rounds a value to a specified number of decimal places N.

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.

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*). If rounding causes an overflow (for example, round(255, -1)), the result is undefined. Syntax

round(x[, N])

Arguments

x — A number to round. Float* or Decimal* or (U)Int*
N — Optional. The number of decimal places to round to. Defaults to 0. (U)Int*

Returned value Returns a rounded number of the same type as x. Float* or Decimal* or (U)Int* Examples Float inputs

Query

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

Response

┌───x─┬─round(x)─┐
│   0 │        0 │
│ 0.5 │        0 │
│   1 │        1 │
└─────┴──────────┘

Decimal inputs

Query

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

Response

┌───x─┬─round(x)─┐
│   0 │        0 │
│ 0.5 │        1 │
│   1 │        1 │
└─────┴──────────┘

roundAge

Introduced in: v1.1.0 Takes a number representing a human age, compares it to standard age ranges, and returns either the highest or lowest value of the range the number falls within.

Returns 0, for age < 1.
Returns 17, for 1 ≤ age ≤ 17.
Returns 18, for 18 ≤ age ≤ 24.
Returns 25, for 25 ≤ age ≤ 34.
Returns 35, for 35 ≤ age ≤ 44.
Returns 45, for 45 ≤ age ≤ 54.
Returns 55, for age ≥ 55.

Syntax

roundAge(num)

Arguments

age — A number representing an age in years. (U)Int* or Float*

Returned value Returns either the highest or lowest age of the range age falls within. UInt8 Examples Usage example

Query

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

Response

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

roundBankers

Introduced in: v20.1.0 Rounds a number to a specified decimal position N. If the rounding number is halfway between two numbers, the function uses a method of rounding called banker’s rounding, which is the default rounding method for floating point numbers defined in IEEE 754.

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

Notes

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.
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.

Use banker’s rounding for summation or subtraction of numbersUsing 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

x — A number to round. (U)Int* or Decimal* or Float*
[, N] — Optional. The number of decimal places to round to. Defaults to 0. (U)Int*

Returned value Returns a value rounded by the banker’s rounding method. (U)Int* or Decimal* or Float* Examples Basic usage

Query

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

Response

┌───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 │
└─────┴───┘

roundDown

Introduced in: v20.1.0 Rounds a number down to an element in the specified array. If the value is less than the lower bound, the lower bound is returned. Syntax

roundDown(num, arr)

Arguments

num — A number to round down. (U)Int* or Decimal* or Float*
arr — Array of elements to round num down to. Array((U)Int*) or Array(Float*)

Returned value Returns a number rounded down to an element in arr. If the value is less than the lowest bound, the lowest bound is returned. (U)Int* or Float* Examples Usage example

Query

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

Response

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

roundDuration

Introduced in: v1.1.0 Rounds a number down to the closest from a set of commonly used durations: 1, 10, 30, 60, 120, 180, 240, 300, 600, 1200, 1800, 3600, 7200, 18000, 36000. If the number is less than one, it returns 0. Syntax

roundDuration(num)

Arguments

num — A number to round to one of the numbers in the set of common durations. (U)Int* or Float*

Returned value Returns 0, for num < 1. Otherwise, one of: 1, 10, 30, 60, 120, 180, 240, 300, 600, 1200, 1800, 3600, 7200, 18000, 36000. UInt16 Examples Usage 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)

Response

┌─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 │
└────────┴───────────────────────┘

roundToExp2

Introduced in: v1.1.0 Rounds a number down to the nearest (whole non-negative) power of two. If the number is less than one, it returns 0. Syntax

roundToExp2(num)

Arguments

num — A number to round. (U)Int* or Float*

Returned value Returns num rounded down to the nearest (whole non-negative) power of two, otherwise 0 for num < 1. (U)Int* or Float* Examples Usage example

Query

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

Response

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

trunc

Introduced in: v1.1.0 Like floor but returns the rounded number with the largest absolute value less than or equal to that of x. Syntax

truncate(x[, N])

Aliases: truncate Arguments

x — The value to round. Float* or Decimal* or (U)Int*
N — Optional. The number of decimal places to round to. Defaults to zero, which means rounding to an integer. (U)Int*

Returned value Returns a rounded number of the same type as x. Float* or Decimal* or (U)Int* Examples Basic usage

Query

SELECT truncate(123.499, 1) AS res;

Response

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

​ceil

​floor

​round

​roundAge

​roundBankers

​roundDown

​roundDuration

​roundToExp2

​trunc

ceil

floor

round

roundAge

roundBankers

roundDown

roundDuration

roundToExp2

trunc