Number approximation in SQL – round, ceil and floor functions

11/13/2021 tejas Crack SQL 0

Approximating and standardizing the numbers is one of the common data cleaning requirement before data analytics. In this blog we will discuss 3 useful functions for such requirements.

Before moving further the examples used are codes written on and outputs got on Postgres shell

  • round() function for rounding float: We often have float of various length in the dataset especially when values are converted from one unit to other. When it comes to data warehousing & analysis it is a good practice to have a uniformity among the quantities. Round() function comes in handy while dealing with float in SQL.

here is syntax:

round(<number/column>,<digits from decimal point>)

example:

test_1=# select round(15.4567,2) as round_value;
 round_value
-------------
       15.46
(1 row)

here in this example the number 15.4567 was passed to round function and requirement was to round the value for 2 places from decimal point.

The round function works exactly as mathematical approximation, if next digit is more than 5 last rounding digit will get incremented. We can see that in our example, we are rounding the number to 2 decimal points, digit next to that is 6 hence the number is rounded to 15.46. If it was less than 5 it would have remained 15.45,

Note: There is one programming concept you need to learn, called – floating point error

  • ceil() function to have nearest higher integer: There are other requirements where you need to approximate the float to nearest highest integer or next integer.

syntax:

ceil(<float/column>)

for example:

test_1=#  select ceil(15.4567) as appr_value;
 appr_value
------------
         16
(1 row)
  • floor() function to have a nearest lower integer: Similar to ceil, If you have to approximate a numbers to nearest lowest integer then you will have to use floor function.

syntax:

floor(<float/column>)

example:

test_1=# select floor(15.4567);
 floor
-------
    15
(1 row)

Comparison:

Below is an example showing comparison between all three functions. I have table naming test

with one column called number and float values in it; below is a query with all three functions and their returns.

comparison round, ceil, floor 
test_1=# select number, round((number::numeric),2), ceil(number), floor(number) from test;
 number  | round | ceil | floor
---------+-------+------+-------
 15.4567 | 15.46 |   16 |    15
 76.1234 | 76.12 |   77 |    76
 15.1515 | 15.15 |   16 |    15
(3 rows)