MySQL NTILE Function
Summary: in this tutorial, you will learn how to use the MySQL NTILE() function to divide rows into a specified number of groups.
Introduction to MySQL NTILE() function
The MySQL NTILE() function divides rows in a sorted partition into a specific number of groups. Each group is assigned a bucket number starting at one. For each row, the NTILE() function returns a bucket number representing the group to which the row belongs.
The following shows 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)
In this syntax:
-
nis a literal positive integer. The bucket number is in the range from 1 ton. - The
PARTITION BYdivides the result set returned from theFROMclause into partitions to which theNTILE()function is applied. - The
ORDER BYclause specifies the order in which theNTILE()values are assigned to the rows in a partition.
Note that if the number of partition rows is not divisible by n, the NTILE() function will result in groups of two sizes with the difference by one. The larger groups always come before the smaller group in the order specified by the ORDER BY clause.
On the other hand, if the total of partition rows is divisible by n, the rows will be divided evenly among groups.
See the following table that stores nine integers from one to nine:
CREATE TABLE t (
val INT NOT NULL
);VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9);
Code language: SQL (Structured Query Language) (sql)
If you use the NTILE() function to divide nine rows into four groups, you will end up at the first group with three rows and other three groups with four rows.
See the following demonstration:
SELECT
val,
NTILE (4) OVER (
ORDER BY val
) bucket_no
FROM
t;
Code language: SQL (Structured Query Language) (sql)
Here is the output:
As you can see from the output, the first group has three rows while the other groups have two rows.
Let’s change the number of groups from four to three as shown in the following query:
SELECT
val,
NTILE (3) OVER (
ORDER BY val
) bucket_no
FROM
t;
Code language: SQL (Structured Query Language) (sql)
The result set now has three groups with the same number of rows.
MySQL NTILE() function example
We will use the orders, orderDetails , and products tables from the sample database for the demonstration.
See the following query:
WITH productline_sales AS (
SELECT productline,
year(orderDate) order_year,
ROUND(SUM(quantityOrdered * priceEach),0) order_value
FROM orders
INNER JOIN orderdetails USING (orderNumber)
INNER JOIN products USING (productCode)
GROUP BY productline, order_year
)
SELECT
productline,
order_year,
order_value,
NTILE(3) OVER (
PARTITION BY order_year
ORDER BY order_value DESC
) product_line_group
FROM
productline_sales;
Code language: SQL (Structured Query Language) (sql)
In this example:
- First, in the
productline_salescommon table expression, we get the total order value of every product line by year. - Then, we use the
NTILE()function to divide the sales by product line in each year into three groups.
Here is the output:
In this tutorial, you have learned how to use the MySQL NTILE() function to distribute rows into a specified number of groups.