The NTILE Analytic Function

[JimKnicely]

The NTILE(N) analytic function equally divides an ordered data set (partition) into a number of subsets within a window, with buckets (subsets) numbered 1 through N.

Example 1:

In the first example we use NTILE(4) so that the result set is divided into 4 groups (often referred to as a QUARTILE):

Code: Select all

dbadmin=> SELECT vr.month,
dbadmin->        SUM(day) total_rentals,
dbadmin->        NTILE(4) OVER(ORDER BY SUM(day)) AS NTILE
dbadmin->   FROM video_rentals vr
dbadmin->  GROUP
dbadmin->     BY vr.month
dbadmin->  ORDER
dbadmin->     BY NTILE;
month | total_rentals | NTILE
-------+---------------+-------
JUL   |           275 |     1
AUG   |           295 |     1
MAY   |           300 |     1
SEP   |           305 |     2
JUN   |           325 |     2
FEB   |           345 |     2
MAR   |           367 |     3
JAN   |           450 |     3
OCT   |           460 |     3
NOV   |           590 |     4
APR   |           600 |     4
DEC   |           770 |     4
(12 rows)

Note: In descriptive statistics, the quartiles of a set of values are the three points that divide the data set into four equal groups, each representing a fourth of the population being sampled. A QUARTILE is a type of QUANTILE.

Example 2:

In the second example we use NTILE(6). The records are now divided into 6 buckets (or 6-quantiles):

Code: Select all

dbadmin=> SELECT vr.month,
dbadmin->        SUM(day) total_rentals,
dbadmin->        NTILE(6) OVER(ORDER BY SUM(day)) AS NTILE
dbadmin->   FROM video_rentals vr
dbadmin->  GROUP
dbadmin->     BY vr.month
dbadmin->  ORDER
dbadmin->     BY NTILE;
month | total_rentals | NTILE
-------+---------------+-------
JUL   |           275 |     1
AUG   |           295 |     1
MAY   |           300 |     2
SEP   |           305 |     2
JUN   |           325 |     3
FEB   |           345 |     3
MAR   |           367 |     4
JAN   |           450 |     4
OCT   |           460 |     5
NOV   |           590 |     5
APR   |           600 |     6
DEC   |           770 |     6
(12 rows) 

Note: Since the Vertica Forums are a G rated family affair, I won’t say what 6-quantiles are called… but I can bet you can figure it out

Have fun!