In MySQL, the NTILE() function is a window function that divides a partition into a given number of groups (buckets) and returns the bucket number of the current row within its partition.

Syntax

The syntax goes like this:

NTILE(N) over_clause

Where N is the number of buckets to divide the partition into.

It also requires an OVER clause, which determines how the rowset is partitioned and ordered before the window function is applied.

Example

Here’s a basic example to demonstrate how it works:

SELECT 
    DogName,
    Activity,
    Score,
    NTILE( 4 ) 
        OVER ( ORDER BY Score ) 
    AS "NTILE"
FROM Dogs;

Result:

+---------+-------------+-------+-------+
| DogName | Activity    | Score | NTILE |
+---------+-------------+-------+-------+
| Max     | Keep Quiet  |     1 |     1 |
| Bruno   | Keep Quiet  |     2 |     1 |
| Cooper  | Keep Quiet  |     8 |     1 |
| Bruno   | Fetch Stick |    43 |     2 |
| Cooper  | Wag Tail    |    51 |     2 |
| Bruno   | Wag Tail    |    65 |     3 |
| Cooper  | Fetch Stick |    67 |     3 |
| Max     | Wag Tail    |    87 |     4 |
| Max     | Fetch Stick |    91 |     4 |
+---------+-------------+-------+-------+
9 rows in set (0.00 sec)

We haven’t applied any partitioning here, and so the whole rowset is divided into the given number of buckets (four).

Partitioning

This example uses a PARTITION BY clause to partition the results by activity:

SELECT 
    DogName,
    Activity,
    Score,
    NTILE( 2 ) 
        OVER ( 
            PARTITION BY Activity
            ORDER BY Score 
            ) 
    AS "NTILE"
FROM Dogs;

Result:

+---------+-------------+-------+-------+
| DogName | Activity    | Score | NTILE |
+---------+-------------+-------+-------+
| Bruno   | Fetch Stick |    43 |     1 |
| Cooper  | Fetch Stick |    67 |     1 |
| Max     | Fetch Stick |    91 |     2 |
| Max     | Keep Quiet  |     1 |     1 |
| Bruno   | Keep Quiet  |     2 |     1 |
| Cooper  | Keep Quiet  |     8 |     2 |
| Cooper  | Wag Tail    |    51 |     1 |
| Bruno   | Wag Tail    |    65 |     1 |
| Max     | Wag Tail    |    87 |     2 |
+---------+-------------+-------+-------+

Changing the Order

Here’s what happens when we change the order to descending order:

SELECT 
    DogName,
    Activity,
    Score,
    NTILE( 2 ) 
        OVER ( 
            PARTITION BY Activity
            ORDER BY Score DESC
            ) 
    AS "NTILE"
FROM Dogs;

Result:

+---------+-------------+-------+-------+
| DogName | Activity    | Score | NTILE |
+---------+-------------+-------+-------+
| Max     | Fetch Stick |    91 |     1 |
| Cooper  | Fetch Stick |    67 |     1 |
| Bruno   | Fetch Stick |    43 |     2 |
| Cooper  | Keep Quiet  |     8 |     1 |
| Bruno   | Keep Quiet  |     2 |     1 |
| Max     | Keep Quiet  |     1 |     2 |
| Max     | Wag Tail    |    87 |     1 |
| Bruno   | Wag Tail    |    65 |     1 |
| Cooper  | Wag Tail    |    51 |     2 |
+---------+-------------+-------+-------+

We still get two buckets, but some rows are assigned different bucket numbers due to the different ordering.

Here it is again, but with NTILE() applied to two different columns, each with different ordering:

SELECT 
    DogName,
    Activity,
    Score,
    NTILE( 2 ) 
        OVER ( 
            PARTITION BY Activity
            ORDER BY Score 
            ) 
    AS "ASC",
    NTILE( 2 ) 
        OVER ( 
            PARTITION BY Activity
            ORDER BY Score DESC
            ) 
    AS "DESC"
FROM Dogs;

Result:

+---------+-------------+-------+-----+------+
| DogName | Activity    | Score | ASC | DESC |
+---------+-------------+-------+-----+------+
| Max     | Fetch Stick |    91 |   2 |    1 |
| Cooper  | Fetch Stick |    67 |   1 |    1 |
| Bruno   | Fetch Stick |    43 |   1 |    2 |
| Cooper  | Keep Quiet  |     8 |   2 |    1 |
| Bruno   | Keep Quiet  |     2 |   1 |    1 |
| Max     | Keep Quiet  |     1 |   1 |    2 |
| Max     | Wag Tail    |    87 |   2 |    1 |
| Bruno   | Wag Tail    |    65 |   1 |    1 |
| Cooper  | Wag Tail    |    51 |   1 |    2 |
+---------+-------------+-------+-----+------+