Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31

NTILE ​

Rank a result set into a specific number of groups ("tiles"), based on an ordered set of rows. For example, NTILE(100) ranks results into percentiles.

NTILE(expression) OVER { window_name | ( window_definition ) }

The expression argument must be a positive whole number or an expression that evaluates to a whole number, such as 3, 10, or 50. Decimal values and negative values are not allowed. This expression may not contain window functions.

The ORDER BY clause in the window_definition is technically optional but highly recommended for all NTILE queries. Meaningful ranking values depend on an ordered result set. The frame clause, if specified, is ignored.

For the full window_definition syntax, see Syntax for Window Functions.

Usage Notes ​

Note the following expected behavior with this function:

  • NTILE ranks rows with an even distribution across the groups. The number of rows that fall into each group may differ by no more than 1 row. Remainder rows are placed in each group, starting with group 1. For example, an NTILE(3) function over a partition of 10 rows will place 4 rows in group 1 and 3 rows in groups 2 and 3.
  • If the expression for the function is greater than the number of rows in the partition, only the first n ranking values are used, starting with 1. For example, NTILE(100) over a partition of 66 rows assigns rankings from 1 to 66 only.
  • If the expression for the function is not a constant, the first value that the expression returns defines the number of groups. For example: ntile(seasonid*10), where seasonid is a column name, computes 20 groups if the first value found in the column is 2.

Examples ​

The following example ranks teams into three groups (numbered 1, 2, and 3), based on stadium capacity:

premdb=# select name, city, stadium, capacity, 
ntile(3) over(order by capacity desc) ranking 
from team 
where capacity>0 and city in('London','Manchester','Birmingham') 
order by ranking;
        name         |    city    |     stadium      | capacity | ranking 
----------------------+------------+------------------+----------+---------
 Manchester United    | Manchester | Old Trafford     |    75635 |       1
 Arsenal              | London     | Emirates Stadium |    60260 |       1
 Manchester City      | Manchester | Etihad Stadium   |    55097 |       1
 Aston Villa          | Birmingham | Villa Park       |    42785 |       1
 Chelsea              | London     | Stamford Bridge  |    41663 |       1
 Tottenham Hotspur    | London     | White Hart Lane  |    36284 |       2
 West Ham United      | London     | Upton Park       |    35016 |       2
 Birmingham City      | Birmingham | St. Andrew's     |    30016 |       2
 Charlton Athletic    | London     | The Valley       |    27111 |       2
 West Bromwich Albion | Birmingham | The Hawthorns    |    27000 |       2
 Crystal Palace       | London     | Selhurst Park    |    26255 |       3
 Wimbledon            | London     | Selhurst Park    |    26255 |       3
 Fulham               | London     | Craven Cottage   |    25700 |       3
 Queens Park Rangers  | London     | Loftus Road      |    18439 |       3
(14 rows)

The following query partitions the NTILE results by the city column:

premdb=# select name, city, stadium, capacity, 
ntile(3) over(partition by city order by capacity desc) ranking 
from team 
where capacity>0 and city in('London','Manchester','Birmingham') 
order by city;
        name         |    city    |     stadium      | capacity | ranking 
----------------------+------------+------------------+----------+---------
 Aston Villa          | Birmingham | Villa Park       |    42785 |       1
 Birmingham City      | Birmingham | St. Andrew's     |    30016 |       2
 West Bromwich Albion | Birmingham | The Hawthorns    |    27000 |       3
 Arsenal              | London     | Emirates Stadium |    60260 |       1
 Chelsea              | London     | Stamford Bridge  |    41663 |       1
 Tottenham Hotspur    | London     | White Hart Lane  |    36284 |       1
 West Ham United      | London     | Upton Park       |    35016 |       2
 Charlton Athletic    | London     | The Valley       |    27111 |       2
 Crystal Palace       | London     | Selhurst Park    |    26255 |       2
 Wimbledon            | London     | Selhurst Park    |    26255 |       3
 Fulham               | London     | Craven Cottage   |    25700 |       3
 Queens Park Rangers  | London     | Loftus Road      |    18439 |       3
 Manchester United    | Manchester | Old Trafford     |    75635 |       1
 Manchester City      | Manchester | Etihad Stadium   |    55097 |       2
(14 rows)

The following example clearly shows the expected behavior when the result set is not evenly divisible by the NTILE expression, which is 10 in this case. Because the query returns 14 rows, the first four rankings (1-4) are assigned twice each, then the last six appear once each (5-10). Remainder rows are allocated evenly over the groups, starting from 1, until they are exhausted. Note that duplicate values in the ORDER BY column (capacity in this case) may be assigned different rankings.

premdb=# select name, city, stadium, capacity, 
ntile(10) over(order by capacity desc) ranking 
from team 
where capacity>0 and city in('London','Manchester','Birmingham') 
order by ranking;
        name         |    city    |     stadium      | capacity | ranking 
----------------------+------------+------------------+----------+---------
 Manchester United    | Manchester | Old Trafford     |    75635 |       1
 Arsenal              | London     | Emirates Stadium |    60260 |       1
 Manchester City      | Manchester | Etihad Stadium   |    55097 |       2
 Aston Villa          | Birmingham | Villa Park       |    42785 |       2
 Chelsea              | London     | Stamford Bridge  |    41663 |       3
 Tottenham Hotspur    | London     | White Hart Lane  |    36284 |       3
 West Ham United      | London     | Upton Park       |    35016 |       4
 Birmingham City      | Birmingham | St. Andrew's     |    30016 |       4
 Charlton Athletic    | London     | The Valley       |    27111 |       5
 West Bromwich Albion | Birmingham | The Hawthorns    |    27000 |       6
 Crystal Palace       | London     | Selhurst Park    |    26255 |       7
 Wimbledon            | London     | Selhurst Park    |    26255 |       8
 Fulham               | London     | Craven Cottage   |    25700 |       9
 Queens Park Rangers  | London     | Loftus Road      |    18439 |      10
(14 rows)

Parent topic:Window Functions