FLOOR FUNCTION IN SNOWFLAKE

Explained: FLOOR() function in Snowflake?

by

Are you looking to find how to use the FLOOR() function within the Snowflake cloud data warehouse or maybe you are looking for a solution, how to use the FLOOR function in Snowflake? If you are looking for any of these problem solutions then you have landed on the correct page. I will also show you what it is and how to use the FLOOR() function. I will explain the FLOOR() function by taking a practical example. So don’t waste time, let’s start a step by step guide to understanding the FLOOR() expression.

What is FLOOR() Function ?

FLOOR() function returns the value from input expression rounded to the nearest equal or smaller integer, or to the nearest equal or smaller value with the specified number of places after the decimal point.

How can we use FLOOR() Function ?

The FLOOR() function can be used with numeric data types, such as FLOAT or NUMBER.

What is the Syntax of the FLOOR function?

-- Syntax : 

FLOOR( <input_expr> [, <scale_expr> ] )

FLOOR() Argument Details :

ParameterParameter Description
Input_ExprIt is the value of expression in Float or number
Scale_exprIt is defined as scale of digits after decimal
Table 1: Floor() Parameter table

input_expr:

It is the value of expression on which we operate. The data type which we can pass on in input expression must be numeric data type. For example: FLOAT or NUMBER.

scale_expr:

Scale_expr is basically the number of digits the floor function will include in the output after the decimal point. It evaluates to an integer from -38 to 38. Its default value is zero, meaning that the function will remove all the digits after the decimal point. For example: FLOOR(10.955) will return the value 11, since scale expression by default is 0 and in that case floor function will remove all digits after decimal point. 

Examples of FLOOR() function in Snowflake:

Firstly, Let’s take sample data to test the DATE_TRUNC function.

select floor(23.095), floor(-75.75);
+---------------+---------------+
| FLOOR(23.095) | FLOOR(-75.75) |
|---------------+---------------|
|            23 |           -76 |
+---------------+---------------+

As we can see in the above example, FLOOR function is demonstrated without the scale_expr, so FlOOR function will consider its default value i.e. 0 and will return the output as integer rounded to the nearest equal or smaller integer.

create table test2 (m float, scale integer);
insert into test2 (m,scale) values
                     (-375.875,-1),
                     (-75.875,0),
                     (-75.875,2),
                     (235.235,-2),
                     (35.235,0),
                     (35.235,1),
                     (35.235,3),
                     (35.235,70),
                     (35.235,NULL);
Output:
select m,scale,floor(m,scale) from test2;
+----------+-------+----------------+
|        M | SCALE | FLOOR(M,SCALE) |
|----------+-------+----------------|
| -375.875 |    -1 |       -380     |
|  -75.875 |     0 |        -76     |
|  -75.875 |     2 |        -75.88  |
|  235.235 |    -2 |        200     |
|   35.235 |     0 |         35     |
|   35.235 |     1 |         35.2   |
|   35.235 |     3 |         35.235 |
|   35.235 |    70 |         35.235 |
|   35.235 |  NULL |           NULL |
+----------+-------+----------------+

As we can see in the above example, the FLOOR function is demonstrated with the scale_expr with all different cases including scale set to negative number. 

Note: If scale_expr is negative, then it specifies the number of places before the decimal point to which to adjust the number. For example, if the scale is -2, then the result is a multiple of 100. Similarly, for scale -1, the result is a multiple of 10.

If the scale_expr is greater than the input_expr scale, then there will be no change in the output which happened in case of FLOOR(35.235,70), the output is 35.235 as scale expression value ‘70’ is larger than input expression scale ‘3’.

When should you use FLOOR() Function in Snowflake?

There are certain use case scenarios when it is recommended to use the FLOOR function within the Snowflake cloud data warehouse which are as follows: 

We can use this function whenever a nearest possible integer value is sufficient to do further calculations and makes no significant impact on the cost.

Floor() Snowflake Official Document Link

Limitations of FLOOR() Function in Snowflake?

  • FLOOR function can be used only with FLOAT or NUMBER.
  • FLOOR evaluates the scale expression between -38 to 38.

Real World Use Case Scenarios for FLOOR Function in Snowflake

  • It is used in many business applications of Insurance and banking domain as it always rounds down and returns the largest integer less than or equal to a given number. For example: If the monthly credit card bill to be paid by a customer is 89.132$, then in that case, the bank will send a credit card bill of $89 which can be done with the help of the FLOOR function.

Final Thoughts

In this article, we have learned about FLOOR function and its uses with the examples explained clearly. I have also covered different scenarios with a practical example that could be possible in normal projects. I hope this information which is provided here helped in gaining the knowledge and make you action ready on FLOOR.

Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.

Join Telegram Group
Download Free Azure Videos
Connect on LinkedIn

Prateek Gupta