Hive Day04~函数

rownumber() 和 over()函数

常用用于求分布topn
测试：求每个人前两高的分数
vi score.txt
zhangsan,1,90
zhangsan,2,95
zhangsan,3,68
lisi,1,88
lisi,2,95
lisi,3,98

hive> create table t_score(name string,kcid string,score int)
row format delimited
fields terminated by ‘,’;

hive>load data local inpath ‘/home/score.txt’ into table t_score;

hive> select *,row_number() over(partition by name order by score desc) from t_score;

hive>select name,kcid,score
from
(select *,row_number() over(partition by name order by score desc) as rank from t_score) tmp
where rank<3;

求出每个用户评分最高的3部电影
求出每个用户评分最高的3部电影
hive> create table t_rate_topn_uid
as
select uid,movie,rate,ts
from
(select *,row_number() over(partition by uid order by rate desc) as rank from t_rate) tmp
where rank<11;
hive> select * from t_rate_topn_uid where uid=1;

自定义函数

vi user.txt
1,zhangsan:20-1999063017:30:00-beijing
2,lisi:30-1989063017:30:00-shanghai
3,wangwu:22-1997063017:30:00-neimeng
hive> create table user_info(info string)
row format delimited;
hive> load data local inpath ‘/home/user.txt’ into table user_info;

例：查看每个电影评分最高的那个人

创建hive外部表
create external table rate(uid string,movie string,rate int)
row format delimited
files terminated by ‘,’
location ‘/dianying/rate’;

这样我们就创建了一个hive表
可以查看hive表
Select * from t_rate;

查看每个人电影评分最高的那个人
select uid,max(rate) from t_rate group by uid;