Hive中的简单窗口函数应用

窗口函数：lead   lag   FIRST_VALUE
分析函数: RANK ROW_NUMBER 


Hive中的简单窗口函数应用
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;


empname empjob salary deptno rank
MILLER CLERK 1300.0 10 1
CLARK MANAGER 2450.0 10 2
KING PRESIDENT 5000.0 10 3
SMITH CLERK 800.0 20 1
ADAMS CLERK 1100.0 20 2
JONES MANAGER 2975.0 20 3
SCOTT ANALYST 3000.0 20 4
FORD ANALYST 3000.0 20 5
JAMES CLERK 950.0 30 1
MARTIN SALESMAN 1250.0 30 2
WARD SALESMAN 1250.0 30 3
TURNER SALESMAN 1500.0 30 4
ALLEN SALESMAN 1600.0 30 5
BLAKE MANAGER 2850.0 30 6


//测试原数据
Hive TopN
a chinese 98
a english 90
a math 90
d chinese 88
c english 82
c math 98
b math 79
b chinese 79
b english 79
z english 90
z math 89
z chinese 80
e math 99
e english 87
d english 90


create 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_number
select *,row_number() over (partition by name order by score desc) as rank from t;


t.name t.sub t.score rank
a chinese 98 1
a english 90 2
a math 90 3
b chinese 79 1
b english 79 2
b math 79 3
c math 98 1
c english 82 2
d english 90 1
d chinese 88 2
e math 99 1
e english 87 2
z english 90 1
z math 89 2
z chinese 80 3


2.rank  //排序字段相同的记录使用相同的排名，下一个从值的行号递增N（N是重复的次数），比如：有两条并列第一，下一个是第三，没有第二
select *,rank() over (partition by name order by score asc) as rank from t;


t.name t.sub t.score rank
a english 90 1
a math 90 1
a chinese 98 3
b chinese 79 1
b english 79 1
b math 79 1
c english 82 1
c math 98 2
d chinese 88 1
d english 90 2
e english 87 1
e math 99 2
z chinese 80 1
z math 89 2
z english 90 3


3、dense_rank //排序字段相同的记录使用相同的排名，下一个值的行号递增1，如下：a的english和math并列第一，下一个chinese是第三，没有第二，b的三门都一样
select *,dense_rank() over (partition by name order by score asc) as rank from t;


t.name t.sub t.score rank
a english 90 1
a math 90 1
a chinese 98 2
b chinese 79 1
b english 79 1
b math 79 1
c english 82 1
c math 98 2
d chinese 88 1
d english 90 2
e english 87 1
e math 99 2
z chinese 80 1
z math 89 2
z english 90 3


业务实例：
统计每个学科的前二名  
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
..