分析函数hive计算均值_Hive分析函数

Hive分析函数

image.png

Example:

Ntile(分片)

使用场景：计算百分之几的用户的结果

给了用户和每个用户对应的消费信息表， 计算花费前50%的用户的平均消费；

-- 把用户和消费表，按消费下降顺序平均分成2份

drop table if exists test_by_payment_ntile;

create table test_by_payment_ntile as

select

nick,

payment ,

NTILE(2) OVER(ORDER BY payment desc) AS rn

from test_nick_payment;

-- 分别对每一份计算平均值，就可以得到消费靠前50%和后50%的平均消费

select

'avg_payment' as inf,

t1.avg_payment_up_50 as avg_payment_up_50,

t2.avg_payment_down_50 as avg_payment_down_50

from

(select

avg(payment) as avg_payment_up_50

from test_by_payment_ntile

where rn=1

)t1

join

(select

avg(payment) as avg_payment_down_50

from test_by_payment_ntile