Round in CockroachDB
In this document, we will study Round in CockroachDB from more than one perspective, including:
- What does the Cockroach ROUND function do and what is the syntax?
- In what circumstances do we use the ROUND function in our Cockroach SQL scripts?
- How do we use the ROUND function in our Cockroach database queries?
- In addition, we’ll look at some functions that behave similar to the way ROUND does, some of which may be more useful than the ROUND function for specific needs. These include FLOOR, CEILING, and TRUNC.
What is Cockroach ROUND?
The CockroachDB ROUND function returns a number after rounding the initial number to a certain precision. By “precision” here, we mean a specific number of decimal places.
Syntax of Round
intRounded := Round(Number_to_round, [intPrecision])
Notice here that the intPrecision parameter you see above as the second parameter is optional. If this parameter is not supplied, Cockroach assumes zero.
Why use the ROUND function? We will make use of the ROUND function in a Cockroach database environment using SQL so as to best understand how we can incorporate use of the ROUND function into our Cockroach-based projects. Let us start with some sample data, a table called tblTechnologies:
We will now write some SQL that retrieves a rating of each language or database system from the intRating column of the tblTechnologies table you see above. The thing to note: we want to have the value we retrieve to be consistently in the format of ##.#, i.e., having precision limited to only one number after the decimal.
, ROUND(intRating, 1) AS intRating_rounded
Analysis of the SQL above:
- SELECT: We chose two columns to retrieve from our tblTechnologies table; txtTechnology and intRating_rounded.
- ROUND: This tells CockroachDB to take the number in the intRating column, round it up to 1 decimal, and name the result AS intRating_round.
- FROM: For the above commands, use the table called “tblTechnologies” as the source of our query data.
Executing the SQL query above returns the following recordset of data:
Notice in the row where the txtTechnology column has a value of “Python”, how the Round() function turned 15.382 into 15.4? That’s because Round looks at the number after our specified decimal cut-off position, 1 in this case, and asks, “Is this number equal to or greater than 5?” If the answer is yes, then the preceding number, at position 1, is incremented by 1. So 15.38 turns into 15.4 because the 8 here is greater than 5.
What if we used FLOOR instead of ROUND on the same table? Let’s see:
, FLOOR(intRating) AS intRating_floored
Notice how FLOOR gives us no choice about where to cut off; it removes everything after the decimal point, basically delivering an integer.
Back to the ROUND function: What if we were to round, but with a precision of 2 instead of 1? First, let us begin out with our original data:
Now we will modify our query to change the precision we are wanting from the ROUND function, changing the “1” to a “2”:
, ROUND(intRating, 2) AS intRating_round
Which gives us back the following record set:
Notice what happened when we changed that second parameter? When Cockroach didn’t have numbers to go on, because the original value’s precision wasn’t “deep” enough for a particular row, the SQL engine used a zero value for that decimal position.
Functions similar to ROUND
Before we wrap up about the ROUND function: there may be situations where you first thought the solution would be to use Round() but actually, the Ceiling(), Floor(), or Trunc() functions may work better. Let us take a brief look at each of those functions:
CEILING(): This function rounds the decimal up to the next higher integer. The decimal is eliminated when the number is rounded up to an integer. Put another way, “Take our number, round up, and make it an integer.” If Cockroach SQL had an INTEGER function, CEILING could be mimic’d like so: INTEGER(ROUND(x,1)).
Ceiling function examples
CEILING(3.8) returns 4
CEILING(5.3) returns 5
Floor function examples
As you saw above, FLOOR rounds the decimal up to the next higher integer. The decimal is eliminated when the number is rounded up to an integer. We could say it like, “Round your number down and make it an integer.”
FLOOR(9.7) returns 9
FLOOR(9.3) returns 9
Trunc function examples
The TRUNC function does not round, it splices out the original number at the specified precision point supplied to the function. If no parameter for precision is given, the decimal is eliminated, thus creating a whole number. Put another way, “Look at a number and remove everything after the decimal.” And there’s more.
If no precision is supplied, zero is assumed.
TRUNC(5.75) returns 5
TRUNC(5.31) returns 5
TRUNC(5.31, 1) returns 5.3
Note: It is important to distinguish between the TRUNC function and the TRUNCATE SQL statement in Cockroach.
In this document we learned how to best use the ROUND function for Cockroach in SQL and explored other similar functions such as CEILING, FLOOR, and TRUNC. Code samples are included.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started