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:

•  n is a literal positive integer. The bucket number is in the range of 1 to n.
• The PARTITION BY divides the result set returned from the FROM clause into partitions to which the NTILE() function is applied.
• The ORDER BY clause specifies the order in which the NTILE() 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 a difference of 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
);

INSERT INTO t(val) 
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9);


SELECT * FROM t;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 the 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_sales common 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:

Summary

• Use the MySQL NTILE() function to distribute rows into a specified number of groups.