Hive中的简单窗口函数应用（TOPN）

窗口函数：lead lag FIRST_VALUE

分析函数: RANK ROW_NUMBER

---

TOPN

row number

说明：
row_number() over ([partition col1] [order by col2])
rank() over ([partition col1] [order by col2])
dense_rank() over ([partition col1] [order by col2])
它们都是根据col1字段分组，然后对col2字段进行排序，对排序后的每行生成一个行号，这个行号从1开始递增
col1、col2都可以是多个字段，用‘,‘分隔

区别：
1）row_number：不管col2字段的值是否相等，行号一直递增，比如：有两条记录的值相等，但一个是第一，一个是第二
2）rank：上下两条记录的col2相等时，记录的行号是一样的，但下一个col2值的行号递增N（N是重复的次数），比如：有两条并列第一，下一个是第三，没有第二
3）dense_rank：上下两条记录的col2相等时，下一个col2值的行号递增1，比如：有两条并列第一，下一个是第二

 select empname,empjob,salary,deptno,row_number() over(partition by deptno order by salary desc ) rank from emp;

//测试原数据Hive TopNachinese98aenglish90amath90dchinese88cenglish82cmath98bmath79bchinese79benglish79zenglish90zmath89zchinese80emath99eenglish87denglish90create table t(name string, sub string, score int) row format delimited fields terminated by '\t';load data local inpath "/home/user01/grades.txt" into table t;

• 为每个学生的各门功课成绩排名

1、row_numberselect *,row_number() over (partition by name order by score desc) as rank from t;

• .rank //排序字段相同的记录使用相同的排名，下一个从值的行号递增N（N是重复的次数），比如：有两条并列第一，下一个是第三，没有第二

select *,rank() over (partition by name order by score asc) as rank from t;

3、dense_rank //排序字段相同的记录使用相同的排名，下一个值的行号递增1，如下：a的english和math并列第一，下一个chinese是第三，没有第二，b的三门都一样

select *,dense_rank() over (partition by name order by score asc) as rank from t;

业务实例：
统计每个学科的前二名

select * from (select *, row_number() over(partition by name order by score desc) as rank from t )t where rank <=2;select *,row_number() over () as rank from t rank <=3;

select area, barnd, yuan,  row_number() over (partition by area order by yuan desc) as rank  from order where rank <=3;

各地区热门商品统计
按地区 分组 再按各个商品的销量进行降序排名
北京 iphone7 70000 1
北京 xiaomi5 60000 2
北京 mate9 50000 3
北京 手机膜 40000 4
…
上海 xiaomi5 70000 1
上海 iphone7 60000 2
上海 mate9 50000 3
上海 手机膜 40000 4
..