WINDOW FUNCTION NTILE

Description

For NTILE(n), this function will divide rows in a partition into a specific number of groups (in this case, n buckets). Only when the previous bucket is full can the data be put into the next bucket. Each bucket is assigned a number starting from one. For the case that cannot be distributed evenly, rows are preferentially allocated to the bucket with the smaller number. The row number difference between buckets cannot be more than 1. For now, n must be a positive integer.

NTILE(n) OVER(partition_by_clause order_by_clause)

Example

select x, y, ntile(2) over(partition by x order by y) as ntile from int_t;
 
| x | y    | rank     |
|---|------|----------|
| 1 | 1    | 1        |
| 1 | 2    | 1        |
| 1 | 2    | 2        |
| 2 | 1    | 1        |
| 2 | 2    | 1        |
| 2 | 3    | 2        |
| 3 | 1    | 1        |
| 3 | 1    | 1        |
| 3 | 2    | 2        |

Keywords

WINDOW,FUNCTION,NTILE