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.
![](https://crackdataanalytics.com/wp-content/uploads/2021/11/sql_round_floor_ceil.png?w=899)
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)