NTILE
Divides an ordered data set equally into the number of buckets specified by the argument value. Buckets are sequentially numbered 1 through the argument value.
For additional window functions, see Window Functions.
Syntax
NTILE( <val> ) OVER ( [ PARTITION BY <expr0> ] ORDER BY <expr1> [ { ASC | DESC } ] )
Parameter | Description |
<val> | An integer expression used for the NTILE() function to specify the number of buckets for division. |
<expr0> | An expression used for the partition by clause. |
<expr1> | An expression used for the order by clause. |
If there is a remainder after dividing the rows in a partition by the argument value, it will result in buckets of different sizes. For example,
- NTILE(2) over 5 rows will result in 2 buckets, the first with 3 rows, the second with 2
- NTILE(3) over 5 rows results in 3 buckets, the first 2 with 2 rows each and the last with one.
Example
The example below divides students with the same grade level into three groups.
SELECT
first_name,
NTILE(3) OVER (PARTITION BY grade_level) AS ntile_buckets
FROM
class_test;
Returns:
' +------------+---------------+
' | first_name | ntile_buckets |
' +------------+---------------+
' | Frank | 1 |
' | Humphrey | 1 |
' | Iris | 2 |
' | Sammy | 2 |
' | Peter | 3 |
' | Jojo | 3 |
' | Brunhilda | 1 |
' | Franco | 1 |
' | Thomas | 2 |
' | Gary | 2 |
' | Charles | 3 |
' | Jesse | 3 |
' | Roseanna | 1 |
' | Wanda | 1 |
' | Shangxiu | 2 |
' | Larry | 2 |
' | Otis | 3 |
' | Deborah | 1 |
' | Yolinda | 2 |
' | Albert | 3 |
' +------------+---------------+