I can teach you Snowflake analytics! Today we are going to learn about Ntile.
Each Ntile partition is assigned a number starting at one increasing to a value that is up to the Ntile partition number specified. So, with a Ntile of 4, the partitions are 1 through 4. Then, all the rows are distributed as evenly as possible into each partition from highest to lowest values. Normally, extra rows with the lowest value begin back in the lowest numbered partitions.
The picture below shows an example of a Ntile of 4. Assigning a different value to the <partitions> indicator of the Ntile function changes the number of partitions established, but in the first two examples, we will use a Ntile of 4.
The example below has an ORDER BY Daily_Sales ASC. Once the data sort happens, the row groups separate into four partitions.
The next example is slightly different because we have more rows and we sort in descending mode. However, once the sort takes place, the rows groups separate into four partitions. Why four? Because we are using a Ntile (4).
The next example shows a Ntile using 10, which refers to a decile. The list of Ntile names is also present.
Now, we are going to take the next example even farther by using a Ntile of three and adding a partition statement. The partition statement resets the calculation, so each partition groups within their partition.
The next example uses a QUALIFY statement. The example below is similar to above, but we are not limiting the rows with a WHERE clause, and we are using QUALIFY to return the rows in the first tile. We want to see the lowest tile of values for each Product_ID.
A WHERE clause limits the calculation of rows by filtering them out before, but the QUALIFY clause waits until the calculations take place, and then QUALIFY further filters the result set.
How would you like to use a tool that automatically writes the SQL for you? Download a free trial of Nexus at www.CoffingDW.com.
The Nexus Super Join Builder allows a user to see their tables visually. All the user has to do is place a checkmark on the columns they want on the report, and Nexus creates the SQL.
If you think the Super Join Builder is impressive, you haven’t seen anything yet. How would you like to join data across different systems, and have Nexus handle everything for you? Check out this next masterpiece. We are joining four tables, with each table residing on a different system. The four systems are Teradata, Oracle, SQL Server, and DB2. You can tell the different systems by the icons on the tables. This type of join refers to a Federated Query or a Cross-System join. Nexus does it better than any other tool.
I hope you enjoyed today’s Snowflake analytic lesson. See you next week.
CEO, Coffing Data Warehousing
Direct: 513 300-0341
YouTube channel: CoffingDW
Tom Coffing, better known as Tera-Tom, is the founder of Coffing Data Warehousing where he has been CEO for the past 25 years. Tom has written over 75 books on all aspects of Teradata, Netezza, Yellowbrick, Snowflake, Redshift, Aurora, Vertica, SQL Server, and Greenplum. Tom has taught over 1,000 classes worldwide, and he is the designer of the Nexus Product Line.