Mastering PostgreSQL 12 - Third Edition

The ntile() function

Some applications require data to be split into ideally equal groups. The ntile() function will do exactly that for you.

The following example shows how data can be split into groups:

test=# SELECT year, production,               ntile(4) OVER (ORDER BY production)  FROM  t_oil 
  WHERE  country = 'Iraq' 
         AND year BETWEEN 2000 AND 2006; year  | production | ntile 
------+------------+------- 
2003  |       1344 |    1 
2005  |       1833 |    1 
2006  |       1999 |    2 
2004  |       2030 |    2 
2002  |       2116 |    3 
2001  |       2522 |    3 
2000  |       2613 |    4  
(7 rows) 

The query splits data into four groups. The trouble is that only seven rows are selected, which makes it impossible to create four even groups. As you can see, PostgreSQL will fill up the first three groups and make the last one a bit ...