# 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

*= 4 and the partition contains 20 rows,*

`constant-value`

`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
must resolve to a positive constant for each partition.`constant-value`

`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)
```