Hive SQL 的 ntile 分组切片函数

Hive SQL 的 ntile 函数用于将分组数据按照顺序切分成n组，并返回当前切片值。如果切片不均匀，默认增加第一个切片的分布。它把有序的数据集合「平均分配」到指定的数量（n）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差 1。

语法

Hive SQL 的 ntile 函数语法为：

bigint ntile(bigint <n>) over (partition by <col1>[, <col2>…]  
[order by <col1>[asc|desc][, <col2>[asc|desc]…]] [<windowing_clause>]))

参数：

• n：必填，代表切片的数量，为 BIGINT 类型；
• partition by [, …]：至少指定 1 个开窗口的列；
• order by col1[asc|desc][, col2[asc|desc]…]：可选，指定返回结果的排序方式。

返回：

• 返回 BIGINT 类型

它需要家配合窗口函数一起使用，将窗口分组数据按照顺序切片，并返回切片值。

案例

有以下数据，是一个班级名单：

'''

id|name|class|b_year|
--|----|-----|------|
 1|张涛  |    1|  1950|
 2|王琳  |    2|  2010|
 3|赵丹丹 |    1|  1996|
 4|李成  |    2|  2011|
 5|赵天成 |    3|  2000|
 6|田迪  |    1|  1988|
 7|王卫栋 |    2|  1966|
 8|周平  |    1|  1988|
 9|武明  |    3|  1977|
'''

我们按出生年 b_year 排序后，将其分为3个组（分桶分箱），ntile 会把组号（桶号）标注出来：

SELECT
    name,
    b_year,
    ntile(3) over(ORDER by b_year ASC) as nt3
FROM
    students
'''
name|b_year|nt3|
----|------|---|
张涛  |  1950|  1|
王卫栋 |  1966|  1|
武明  |  1977|  1|
田迪  |  1988|  2|
周平  |  1988|  2|
赵丹丹 |  1996|  2|
赵天成 |  2000|  3|
王琳  |  2010|  3|
李成  |  2011|  3|
'''

桶号为 1 的是前 33%，为 3 的为 66% 以后。

分组分桶

ntile 还可以将数据用窗口分组，再在分组（窗口）内标注桶号：

SELECT
    name,
    class,
    b_year,
    ntile(3) over(PARTITION by class ORDER by b_year ASC) as nt3
FROM
    students
'''
name|class|b_year|nt3|
----|-----|------|---|
张涛  |    1|  1950|  1|
田迪  |    1|  1988|  1|
周平  |    1|  1988|  2|
赵丹丹 |    1|  1996|  3|
王卫栋 |    2|  1966|  1|
王琳  |    2|  2010|  2|
李成  |    2|  2011|  3|
武明  |    3|  1977|  1|
赵天成 |    3|  2000|  2|
'''

以上先将数据按班级（class）进行窗口分组，然后在窗口内进行分桶。