Rounding functions

Rounding functions provide various ways to adjust numeric values to specified levels of precision.

​

ClickHouse function reference

​

floor

Rounds a number down to the nearest integer or to a specified number of decimal places.

Syntax:

floor(x[, N])

Arguments:

• x (numeric): The value to round. Can be Float*, Decimal*, or (U)Int*.
• N (numeric, optional): The number of decimal places to round to. Default is 0 (round to integer). Can be negative.

Returns:

A rounded number of the same type as x.

Examples:

SELECT
	taco_price,
	floor(taco_price) AS rounded_price
FROM
	taco_menu;

Result:

| taco_price | rounded_price |
|------------|---------------|
| 3.99       | 3             |
| 4.50       | 4             |
| 5.25       | 5             |

This function is useful for rounding prices, measurements, or any decimal values down to the nearest whole number or specified decimal place.

​

ceiling

Rounds a number up to the nearest integer or to a specified number of decimal places.

Syntax:

ceiling(x[, N])

Alias:

• ceil

Arguments:

• x (numeric): The value to round. Can be a Float32, Float64, Decimal*, or Int* type.
• N (Int, optional): An integer specifying the number of decimal places to round to. Default is 0.

Returns:

The smallest rounded number greater than or equal to x. The return type is the same as the input type.

Examples:

SELECT
  ceiling(3.14) AS rounded_up;

Result:

| rounded_up |
|------------|
| 4          |

Rounding to a specified number of decimal places:

SELECT
  ceiling(123.45, 1) AS rounded_decimal;

Result:

| rounded_decimal |
|-----------------|
| 123.5           |

Rounding to the nearest power of two:

SELECT
	ceiling(taco_price, -1) AS rounded_taco_price
FROM
	taco_menu
WHERE
	taco_name = 'Carne Asada Supreme';

Result:

| rounded_taco_price |
|--------------------|
| 10                 |

In this example, if the ‘Carne Asada Supreme’ taco costs $7.99, it would be rounded up to$10 (nearest 10).

​

truncate

Rounds a number down to a specified number of decimal places.

Syntax:

truncate(x[, N])

Alias:

• trunc

Arguments:

• x (numeric): The number to truncate. Can be Float*, Decimal*, or (U)Int*.
• N (Int, optional): The number of decimal places to keep. Default is 0.
  • If N > 0, truncates to the right of the decimal point.
  • If N < 0, truncates to the left of the decimal point.
  • If N = 0, truncates to an integer.

Returns:

A truncated number of the same type as x.

Example:

SELECT
	truncate(123.4567, 2) AS precise_price,
	truncate(123.4567, -2) AS rounded_hundreds,
	truncate(123.4567, 1) AS menu_price

Result:

| precise_price | rounded_hundreds  | menu_price |
|---------------|-------------------|------------|
| 123.45        | 100               | 123.4      |

In this example:

• precise_price shows the taco price truncated to 2 decimal places.
• rounded_hundreds rounds down to the nearest hundred.
• menu_price displays the taco prices on the menu, truncated to 1 decimal place.

​

round

Rounds a value to a specified number of decimal places.

Syntax:

round(x[, N])

Arguments:

• x (numeric): A number to round. Can be Float32, Float64, Decimal*, or Int*.
• N (Int*, optional): The number of decimal places to round to. Defaults to 0.
  • If N > 0, rounds to the right of the decimal point.
  • If N < 0, rounds to the left of the decimal point.
  • If N = 0, rounds to the nearest integer.

Returns:

A rounded number of the same type as x.

Description:

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 Float32 and Float64 inputs and rounds away from zero for other number types (Decimal* and Int*).

Example:

SELECT
	taco_price,
	round(taco_price, 1) AS rounded_price
FROM
	taco_menu;

Result:

| taco_price | rounded_price |
|------------|---------------|
| 3.14       | 3.1           |
| 2.75       | 2.8           |
| 4.99       | 5.0           |

In this example, round is used to round taco prices to one decimal place. Note how 2.75 rounds up to 2.8 (away from zero for Decimal type), while 3.14 rounds down to 3.1.

SELECT
	round(3.5) AS rounded_float,
	round(CAST(3.5 AS Decimal(10,1))) AS rounded_decimal;

| rounded_float | rounded_decimal |
|---------------|-----------------|
| 4             | 4               |

Arguments:

• x (numeric): A number to round. Can be Float*, Decimal*, or (U)Int*.
• N (Int, optional): The number of decimal places to round to. Optional, defaults to 0.
  • If N > 0, rounds to the right of the decimal point.
  • If N < 0, rounds to the left of the decimal point.
  • If N = 0, rounds to the nearest integer.

Returns:

A value rounded using banker’s rounding, of the same type as x.

Description:

Banker’s rounding is a method of rounding fractional numbers where ties (numbers exactly halfway between two integers) are rounded to the nearest even integer. This method helps reduce bias in rounding operations.

For example:

• 3.5 rounds to 4
• 2.5 rounds to 2
• 3.55 rounds to 3.6 (with N=1)
• 3.65 rounds to 3.6 (with N=1)

This function performs banker’s rounding for both floating-point and integer numbers.

Example:

SELECT
	price,
	roundBankers(price, 1) AS rounded_price
FROM
	taco_menu
WHERE
	name IN ('Carne Asada', 'Al Pastor', 'Pescado');

Result:

| price | rounded_price |
|-------|---------------|
| 3.50  | 3.5           |
| 3.25  | 3.2           |
| 4.75  | 4.8           |

In this example, roundBankers rounds the taco prices to one decimal place using banker’s rounding. Note how 3.25 rounds to 3.2 instead of 3.3.

See Also:

• round function, which uses different rounding rules for non-float types.

​

roundToExp2

Rounds a number down to the nearest (whole non-negative) power of two.

Syntax:

roundToExp2(x)

Arguments:

• x (numeric): A number to round. Can be any numeric type.

Returns:

• If x < 1, returns 0.
• Otherwise, returns x rounded down to the nearest power of two.
• Return type matches the input type.

Example:

SELECT
	taco_count,
	roundToExp2(taco_count) AS rounded_taco_count
FROM
	taco_orders
WHERE
	taco_count IN (0, 2, 5, 10, 19, 50);

Result:

| taco_count | rounded_taco_count |
|------------|--------------------|
| 0          | 0                  |
| 2          | 2                  |
| 5          | 4                  |
| 10         | 8                  |
| 19         | 16                 |
| 50         | 32                 |

In this example, roundToExp2 rounds the number of tacos ordered down to the nearest power of two. This could be useful for grouping orders into size categories based on powers of two.

​

roundDuration

Rounds a number down to a commonly used duration value.

Syntax:

roundDuration(num)

Arguments:

• num (UInt64 or Float64): A number to round.

Returns:

• A rounded duration value. UInt16.

If the input is less than 1, the function returns 0. Otherwise, it rounds the number down to one of the following commonly used durations (in seconds): 1, 10, 30, 60, 120, 180, 240, 300, 600, 1200, 1800, 3600, 7200, 18000, 36000.

Example:

SELECT
	order_duration,
	roundDuration(order_duration) AS rounded_duration
FROM
	taco_orders;

Result:

| order_duration | rounded_duration |
|----------------|------------------|
| 45             | 30               |
| 190            | 180              |
| 800            | 600              |
| 2500           | 1800             |
| 5400           | 3600             |

In this example, roundDuration is used to round the order_duration (in seconds) to the nearest commonly used duration. This can be useful for grouping or categorizing order durations into standardized time intervals.

​

roundAge

Rounds an age value to predefined age ranges commonly used in demographic analysis.

Syntax:

roundAge(age)

Arguments:

• age (UInt or Float): A number representing an age in years.

Returns:

A rounded age value based on predefined ranges. (UInt8)

• 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

Example:

SELECT
	customer_id,
	age,
	roundAge(age) AS age_group
FROM
	taco_shop_customers
WHERE
	age IN (0, 5, 20, 31, 37, 54, 72);

Result:

| customer_id | age | age_group |
|-------------|-----|-----------|
| 1           | 0   | 0         |
| 2           | 5   | 17        |
| 3           | 20  | 18        |
| 4           | 31  | 25        |
| 5           | 37  | 35        |
| 6           | 54  | 45        |
| 7           | 72  | 55        |

This function is useful for grouping customers into age brackets for demographic analysis or targeted marketing campaigns in your taco shop.

​

roundDown

Rounds a number down to the nearest element in a specified array.

Syntax:

roundDown(num, arr)

Arguments:

• num (numeric): The number to round down.
• arr (Array of UInt or Float): An array of elements to round down to.

Returns:

The input number rounded down to an element in the array. If the value is less than the lowest bound in the array, the lowest bound is returned. The return type matches the type of elements in arr.

Example:

SELECT
	taco_price,
	roundDown(taco_price, [3, 4, 5]) AS rounded_price
FROM
	taco_menu
WHERE
	taco_price IN (2.50, 3.75, 4.20, 5.10, 6.00);

Result:

| taco_price | rounded_price |
|------------|---------------|
| 2.50       | 3             |
| 3.75       | 3             |
| 4.20       | 4             |
| 5.10       | 5             |
| 6.00       | 5             |

In this example, roundDown rounds each taco_price to the nearest lower value in the array [3, 4, 5]. Prices below 3 are rounded to 3, and prices above 5 are rounded to 5.