WINDOW FUNCTION NTILE

description

For NTILE(n), this function will divides rows in a sorted partition into a specific number of groups(in this case, n buckets). Each group is assigned a bucket number starting at one. For the case that cannot be distributed evenly, rows are preferentially allocated to the bucket with the smaller number. The number of rows in all buckets cannot differ by more than 1. For now, n must be constant positive integer.

  1. NTILE(n) OVER(partition_by_clause order_by_clause)

example

  1. select x, y, ntile(2) over(partition by x order by y) as ntile from int_t;
  3. | x | y    | rank     |
  4. |---|------|----------|
  5. | 1 | 1    | 1        |
  6. | 1 | 2    | 1        |
  7. | 1 | 2    | 2        |
  8. | 2 | 1    | 1        |
  9. | 2 | 2    | 1        |
  10. | 2 | 3    | 2        |
  11. | 3 | 1    | 1        |
  12. | 3 | 1    | 1        |
  13. | 3 | 2    | 2        |

keywords

  1. WINDOW,FUNCTION,NTILE