In this tutorial, we will learn a very interesting window function- the NTILE() function. The NTILE() function is similar to the ROW_NUMBER function.
Read the tutorial carefully and make your hands dirty by writing the code, so that you will understand everything about the NTILE() function. So, let’s get started!
Also read: Introduction to Mysql Window Functions
Introduction to MySQL NTILE() Function
The NTILE() function is very similar to the ROW_NUMBER() function. But instead of directly assigning the row numbers, the NTILE() function divides the rows into a given number of groups and then assigns the numbers to the formed groups. This numbering starts from one.
Each group is assigned a number which is called a bucket number that indicates what number that group is.
Moreover, we can make a partition and use the NTILE() function so that it can create groups within the partitions and a new partition will get the numbers assigned from the start which is one.
A little bit confusing, right?
Let’s see the syntax first of the NTILE() function and then we will move to the example.
Also read: MySQL Window Functions – MySQL LAG Function
MySQL NTILE() Function Syntax
Here is the syntax of the NTILE() function.
NTILE(N) OVER (
PARTITION BY <expression>[{,<expression>...}]
ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)Code language: SQL (Structured Query Language) (sql)Where,
The ‘N’ specifies the number of groups to be formed within the partition. The bucket numbers are always in the range of 1 to N. Note that, N must be a positive integer and it can not be NULL from MySQL Version 8.0.22 onwards.
The PARTITION BY clause creates a partition and the N groups will be formed within the partition.
The ORDER BY clause will sort the rows in either ascending order or descending order and then the bucket numbers will be assigned to the groups of rows.
Please remember that the NTILE() method will create groups of two sizes with a difference of one if the number of partition rows is not completely divisible by n. In the ORDER BY clause’s specified order, the larger groupings are always placed ahead of the smaller ones.
To arrange partition rows in the appropriate order, you can use the NTILE() with the ORDER BY clause.
MySQL NTILE() Function Examples
Now we will take some examples to understand how to use the NTILE() function. For that, we need a table and some values in it. Let’s create a table first.
CREATE TABLE numbers(
num INT
);
INSERT INTO numbers VALUES(1),(1),(2),(2),(2),(3),(4),(5),(5),(5),(5);Code language: SQL (Structured Query Language) (sql)Let’s display the table data to check if the values are inserted correctly.
SELECT * FROM numbers;Code language: SQL (Structured Query Language) (sql)
Perfect! Let’s write a query to demonstrate the NTILE() function. Here, we will write a query to create 4 groups and the function will assign bucket numbers to the groups or rows.
SELECT num,
NTILE(4) OVER w AS 'ntile_rank'
FROM numbers
WINDOW w AS (ORDER BY num);Code language: SQL (Structured Query Language) (sql)Here, we are sorting the rows in ascending order and not creating any partition.
Let’s see the output.
As you can see, There is a total of eleven records in our table. So, according to the working of the NTILE() function, the first three groups will contain three rows each which will sum to the nine rows in total and the remaining two rows will make the last group.
Now we will create a partition and use the NTILE() function.
SELECT num,
NTILE(4) OVER w AS 'ntile_rank'
FROM numbers
WINDOW w AS (PARTITION BY num ORDER BY num);Code language: SQL (Structured Query Language) (sql)In this case, there are not enough rows in each partition to create the given number of groups. Hence, the NTILE() function will assign consecutive bucket numbers to the row. For example, if there are three rows in the first partition, then the function will assign one to three numbers to these rows as there is no fourth row available to assign the bucket number to.
As you can see in the output, we have received the expected output. Finally, we will write a query to see the difference between the output of the NTILE() function and the ROW_NUMBER() function.
SELECT num,
ROW_NUMBER() OVER w AS 'row_number',
NTILE(2) OVER w AS 'ntile_rank'
FROM numbers
WINDOW w AS (PARTITION BY num ORDER BY num);Code language: SQL (Structured Query Language) (sql)
As you can see, the ROW_NUMBER() assigns consecutive numbers to each row whereas the NTILE() divides the total number of rows and then assigns them numbers.
Summary
In this tutorial, we have learned:
- What is an NTILE() function
- How it works
- Syntax of the NTILE() function
- Examples of the NTILE() function
- Difference between the ROW_NUMBER and NTILE function
References
MySQL official documentation on the NTILE() function.1