NTILE [Analytic]
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
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()
|
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)