Rounding functions¶
The following functions are used to round numbers.
floor¶
Returns the largest integer less than or equal to x, or rounds x down to a specified number of decimal places N. If N is negative, it rounds to the left of the decimal point.
Syntax¶
floor(x[, N])
Arguments¶
x: The value to round. Can be a Float, Decimal, or Int/UInt type.N: An integer specifying the number of decimal places. Defaults to0for rounding to a whole number. Can be negative to round to the left of the decimal point.
Returns¶
A rounded number of the same type as x.
Example¶
SELECT floor(123.45, 1) AS rounded
Result:
┌─rounded─┐ │ 123.4 │ └─────────┘
SELECT floor(123.45, -1)
Result:
┌─rounded─┐ │ 120 │ └─────────┘
ceiling¶
Returns the smallest integer greater than or equal to x, or rounds x up to a specified number of decimal places N. If N is negative, it rounds to the left of the decimal point.
Alias: ceil
Syntax¶
ceiling(x[, N])
Arguments¶
x: The value to round. Can be a Float, Decimal, or Int/UInt type.N: An integer specifying the number of decimal places. Defaults to0for rounding to a whole number. Can be negative to round to the left of the decimal point.
Returns¶
A rounded number of the same type as x.
Example¶
SELECT ceiling(123.45, 1) AS rounded_up
Result:
┌─rounded_up─┐ │ 123.5 │ └────────────┘
truncate¶
Truncates a number x by removing its fractional part, or rounds it towards zero to a specified number of decimal places N. If N is negative, it truncates to the left of the decimal point.
Alias: trunc
Syntax¶
truncate(x[, N])
Arguments¶
x: The value to truncate. Can be a Float, Decimal, or Int/UInt type.N: An integer specifying the number of decimal places. Defaults to0for truncating to a whole number. Can be negative to truncate to the left of the decimal point.
Returns¶
A truncated number of the same type as x.
Example¶
SELECT truncate(123.499, 1) as res
Result:
┌───res─┐ │ 123.4 │ └───────┘
round¶
Rounds a number x to the nearest value with N decimal places. For floating-point numbers, it uses banker's rounding when x is exactly halfway between two numbers. For other numeric types, it rounds away from zero in such cases.
Syntax¶
round(x[, N])
Arguments¶
x: The numeric value to round. Can be a Float, Decimal, or Int/UInt type.N: An integer specifying the number of decimal places. Defaults to0for rounding to the nearest whole number. PositiveNrounds to the right of the decimal point, negativeNrounds to the left.
Returns¶
A rounded number of the same type as x.
Example¶
Example with Float inputs:
SELECT number / 2 AS x, round(x) FROM system.numbers LIMIT 3
Result:
┌───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
Result:
┌───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
Result:
┌──────x─┬─round(CAST(divide(number, 2), 'Decimal(10, 4)'))─┐ │ 0.0000 │ 0.0000 │ │ 0.5000 │ 1.0000 │ │ 1.0000 │ 1.0000 │ └────────┴──────────────────────────────────────────────────┘
roundBankers¶
Rounds a number x to N decimal places using banker's rounding. This method rounds halfway values to the nearest even digit. For example, 2.5 rounds to 2, and 3.5 rounds to 4. This helps minimize cumulative rounding errors in calculations.
Syntax¶
roundBankers(x [, N])
Arguments¶
x: The numeric value to round. Can be a Float, Decimal, or Int/UInt type.N: An integer specifying the number of decimal places. Defaults to0for rounding to the nearest whole number. PositiveNrounds to the right of the decimal point, negativeNrounds to the left.
Returns¶
A value rounded by the banker's rounding method.
Example¶
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 │ └─────┴───┘
roundToExp2¶
Rounds a number num down to the nearest power of two. If num is less than 1, it returns 0.
Syntax¶
roundToExp2(num)
Arguments¶
Returns¶
Returns 0 if num is less than 1 (type UInt8). Otherwise, returns num rounded down to the nearest power of two (type matches input UInt or Float).
Example¶
SELECT number, roundToExp2(number) 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¶
Rounds a number num down to the closest value from a predefined set of common duration intervals. If num is less than 1, it returns 0.
Syntax¶
roundDuration(num)
Arguments¶
Returns¶
Returns 0 if num is less than 1. Otherwise, returns one of the predefined duration values: 1, 10, 30, 60, 120, 180, 240, 300, 600, 1200, 1800, 3600, 7200, 18000, 36000. Type: UInt16.
Example¶
SELECT number, roundDuration(number) 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¶
Categorizes an age num into predefined age groups and returns the lower bound of that group, or a specific value for certain ranges.
Syntax¶
roundAge(num)
Arguments¶
Returns¶
Returns 0 for ages less than 1. Returns 17 for ages 1-17. Returns 18 for ages 18-24. Returns 25 for ages 25-34. Returns 35 for ages 35-44. Returns 45 for ages 45-54. Returns 55 for ages 55 or greater. Type: UInt8.
Example¶
SELECT number, roundAge(number) 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¶
Rounds a number num down to the largest value in a provided array arr that is less than or equal to num. If num is smaller than all elements in arr, it returns the smallest element from arr.
Syntax¶
roundDown(num, arr)
Arguments¶
num: The numeric value to round down. Can be a Numeric type.arr: An Array of numeric values (UInt or Float) to use as rounding boundaries.
Returns¶
The num value rounded down to an element in arr. If num is less than the smallest element in arr, the smallest element is returned. The return type matches the element type of arr.
Example¶
SELECT number, roundDown(number, [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 │ └────────┴──────────────────────────────┘