NTILE [analytic]

Equally divides an ordered data set (partition) into a {value} number of subsets within a , where the subsets are numbered 1 through the value in parameter constant-value.

Equally divides an ordered data set (partition) into a {value} number of subsets within a window, where the subsets are numbered 1 through the value in parameter constant-value. For example, if constant-value= 4 and the partition contains 20 rows, NTILE divides the partition rows into four equal subsets of five rows. NTILE assigns each row to a subset by giving row a number from 1 to 4. The rows in the first subset are assigned 1, the next five are assigned 2, and so on.

If the number of partition rows is not evenly divisible by the number of subsets, the rows are distributed so no subset is more than one row larger than any other subset, and the lowest subsets have extra rows. For example, if constant-value= 4 and the number of rows = 21, the first subset has six rows, the second subset has five rows, and so on.

If the number of subsets is greater than the number of rows, then a number of subsets equal to the number of rows is filled, and the remaining subsets are empty.

Behavior type

Immutable

Syntax

NTILE ( constant-value ) OVER (
    [ window-partition-clause ]
    window-order-clause )

Parameters

constant-value
Specifies the number of subsets , where constant-value must resolve to a positive constant for each partition.
OVER()
See Analytic Functions.

Examples

The following query assigns each month's sales total into one of four subsets:

=> SELECT calendar_month_name AS MONTH, SUM(sales_quantity),
      NTILE(4) OVER (ORDER BY SUM(sales_quantity)) AS NTILE
   FROM store.store_sales_fact JOIN date_dimension
   USING(date_key)
   GROUP BY calendar_month_name
   ORDER BY NTILE;
   MONTH   |   SUM   | NTILE
-----------+---------+-------
 November  | 2040726 |     1
 June      | 2088528 |     1
 February  | 2134708 |     1
 April     | 2181767 |     2
 January   | 2229220 |     2
 October   | 2316363 |     2
 September | 2323914 |     3
 March     | 2354409 |     3
 August    | 2387017 |     3
 July      | 2417239 |     4
 May       | 2492182 |     4
 December  | 2531842 |     4
(12 rows)

See also