hive分箱函数ntile()

ntile()函数的作用是等频分箱，把观测值进行有序排列（默认升序），根据观测值的总个数等分为k部分，每部分当作一个分箱，即百分位数的概念，可以根据箱号选取前或后n分之几的数据。

函数方法：
ntile(n) over(order by col) as bucket_num
n是指定的分箱数量。如果不能平均分配，则优先分配较小编号的箱，并且各个箱中能放的行数最多相差1
备注：NULL值的处理，可以设置单独为一组，或者默认为最小值

select  col
        -- NULL默认为最小值
        , ntile(2) over( order by col) as group1
        -- 将NULL单独为1组
        , if(col is null, null, ntile(2) over( partition by if(col is null, 1, 0) order by col) as group2
from(
    select cast(col as int) as col
    from(
        select stack(5, 'NULL', '1', '2', '3', '4') as col
    ) as a
) as a

percent_rank() over(order by col)：先得出每个值对应的百分位数，再根据实际需求分箱

select  col
        -- 根据百分位数划分
        , if(group1<0.5, 1, if(group1<=1.0, 2, null)) as group1
        , if(group2<0.5, 1, if(group2<=1.0, 2, null)) as group2
from(
    select  col
            -- NULL默认为最小值
            , percent_rank() over( order by col) as group1
            -- 将NULL单独为1组
            , if(col is null, null, percent_rank() over( partition by if(col is null, 1, 0) order by col) as group2
    from(
        select cast(col as int) as col
        from(
            select stack(5, 'NULL', '1', '2', '3', '4') as col
        ) as a
    ) as a
) as a

————————————————
版权声明：本文为CSDN博主「喜东东cc」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_34105362/article/details/82767445