In Standard SQL, the ntile function can be expressed as shown:
The function will divide the rows into the defined const_int_expr parts based on the row order and returns a 1-based part number which is assigned to each row.
For more verbose and explicit syntax, we can express it as shown:
The parts parameter defines the total number of parts in which the rows are divided. The value must be an integer or a query that returns a positive integer.
The partition by clause will define the expression that separates the rows into sets of sections. The ntile function is then applied to these partitions.
Order by will specify the order in which the rows are ordered. This is applied in each partition.
SQL Ntile – Practical Example
To illustrate using the ntile function, let us use a practical example. Start by creating a database to store the sample data.
Switch to the database and create a table as shown:
NOTE: The above query is provided for PostgreSQL databases. Feel free to switch the query above to match your desired database engine.
Once you have the table schema defined, we can proceed and add sample data. Run the query below to add sample data.
The above adds sample data to the table. We will use this data to illustrate how to use the ntile function.
Example 1
Let’s use the ntile function to divide the above data into 3 separate rows based on the category.
The above query should partition the result by the category and apply the ntile function. The resulting table is shown below:
Closing
Using this article, we discovered how to what the ntile function does and how to use it. Check the documentation for your database engine to explore more.
Thanks for reading!!
