Are you looking to find what is the ROUND() function within the Snowflake cloud data warehouse or maybe you are looking for a solution on how to use the round down in the Snowflake? You may also wanted to understand how to round to nearest 5 or round to 2 decimal places or precision. If you are looking for any of these problem solutions then you have landed on the correct page. I will also explain round in Snowflake with details by taking a practical examples. So don’t waste time let’s start step by step guide to understand the ROUND() expression.
Round function in the Snowflake is use to round or truncate the decimal portion of the given number or the numerical expression. It can be used to convert the decimal to integer or to remove extra decimal places from the decimal number.
Contents
What is the ROUND() Function in Snowflake?
ROUND() function is used to round/truncate the input expression. Round function also takes the scale, hence you can mention up to how many places you wanted to round of the number or float.
- If the scale expression < 0 then result will be multiple of 10*(scale)
- If the scale expression > input expression scale then, the function does not have any effect.
- If any of the expressions are NULL, then the resultant value is also NULL.
What is the Syntax of the ROUND in Snowflake?
-- Syntax :
ROUND( Input_expression, [ Scale_expression] )
ROUND Input Argument Details:
| Input_expression | Number or Float (Mandatory) |
| Scale_expression | Number of digits (optional parameter) |
Input_expression:
This could be any numerical column or expression which result into numerical value.
Scale_expression:
The Number of digits the output should include after decimal point. If you don’t provide any value for this parameter than it will treat scale =0, that means it round will remove all the decimal places.
Return :
The data type of returned value is same as data type of input expression. If the data type is Number then the returned data type is also Number.
Examples of Round in Snowflake :
Round example with Scale =0
select round(138.1357), round(-973.905);
+-----------------+-----------------+
| ROUND(138.1357) | ROUND(-973.905) |
|-----------------+-----------------|
| 138 | -974 |
+-----------------+-----------------+
Round example with Scale other than 0
//Create and load a table:
create or replace temp table RandomSample(f float, d decimal(10, 3));
+----------------------------------+
| status |
|----------------------------------|
| Table RANDOMSAMPLE successfully created. |
+----------------------------------+
insert into RANDOMSAMPLE (f, d) values
( -10.005, -10.005),
( -1.005, -1.005),
( 1.005, 1.005),
( 10.005, 10.005)
+-------------------------+
| number of rows inserted |
|-------------------------|
| 4 |
+-------------------------+
select f, round(f, 2) from RANDOMSAMPLE ;
+---------+-------------+
| F | ROUND(F, 2) |
|---------+-------------|
| -10.005 | -10.01 |
| -1.005 | -1 |
| 1.005 | 1 |
| 10.005 | 10.01 |
+---------+-------------+
//Difference between rounded FLOAT and rounded DECIMAL values:
select f, round(f, 2),
d, round(d, 2)
from RANDOMSAMPLE
order by 1;
+---------+-------------+---------+-------------+
| F | ROUND(F, 2) | D | ROUND(D, 2) |
|---------+-------------+---------+-------------|
| -10.005 | -10.01 | -10.005 | -10.01 |
| -1.005 | -1 | -1.005 | -1.01 |
| 1.005 | 1 | 1.005 | 1.01 |
| 10.005 | 10.01 | 10.005 | 10.01 |
+---------+-------------+---------+-------------+
What is difference between Rounding FLOAT VS Rounding DECIMAL?
Float numbers stored in slightly different manner compare to the decimal. For example when you store the 2.05 as floating number then it get saved as 2.0499999 like that. This gives different result when you round compare to storing the decimal number. Decimal numbers get saved as the exact number only hence give expected answer while rounding them.
Example of Rounding FLOAT VS DECIMAL
//Difference between rounded FLOAT and rounded DECIMAL values:
select f, round(f, 2),
d, round(d, 2)
from RANDOMSAMPLE
order by 1;
+---------+-------------+---------+-------------+
| F | ROUND(F, 2) | D | ROUND(D, 2) |
|---------+-------------+---------+-------------|
| -10.005 | -10.01 | -10.005 | -10.01 |
| -1.005 | -1 | -1.005 | -1.01 |
| 1.005 | 1 | 1.005 | 1.01 |
| 10.005 | 10.01 | 10.005 | 10.01 |
+---------+-------------+---------+-------------+
When you should use ROUND in Snowflake?
- Assume that you wanted to show result as round figure.
- Convert the decimal into the integer.
- Truncate the decimal places in the decimal/floating number.
Snowflake Round Official Documentation Link
Final Thoughts
In this article we have learned about ROUND() function and their uses with the examples explained clearly. I have also covered different scenario with practical example that could be possible. I hope the information that was provided is helped in gaining the knowledge.
Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.
- For Azure Study material Join Telegram group : Telegram group link:
- Azure Jobs and other updates Follow me on LinkedIn: Azure Updates on LinkedIn
- Azure Tutorial Videos: Videos Link
