Hive中分组取topN_row_number-rank和dense_rank的使用.pdfVIP

0. 参考资料 /u/2000675/blog/1604035 0. 数据表 create table dws_db.employee (empid int ,deptid int ,salary decimal(10,2)); insert into dws_db.employee values(1,10,5500.00); insert into dws_db.employee values(2,10,4500.00); insert into dws_db.employee values(3,20,1900.00); insert into dws_db.employee values(4,20,4800.00); insert into dws_db.employee values(5,40,6500.00); insert into dws_db.employee values(6,40,14500.00); insert into dws_db.employee values(7,40,44500.00); insert into dws_db.employee values(8,50,6500.00); insert into dws_db.employee values(9,50,7500.00); 1. 分组-组内排序 对deptid 分组，组内根据salary 排序 select *, row_number() over(partition by deptid order by salary) as rank from employee 结果： 值相同，先出现的排名靠前 2. 分区-组内排序-取topN 对 分组，组内根据 降序排序，取每组内的前 名。 deptid salary 1 select * from (select *, row_number() over(partition by deptid order by salary desc) as salary_rankfrom employee) t where salary_rank <= 1 3. 不分组-只对某个字段排序 所有数据进行排名，不用分组，只用 partition by 1 即可 select *, row_number() over(partition by 1 order by salary desc) as salary_rankfrom employee 4. rank() over() rank() over(partition by deptid order by salary desc ) select *, rank() over(partition by deptid order by salary desc) as salary_rank from employee 值相同是，排名会并列，下一个移动 ， 为值相同的个数。 N N 结果： 5. dense_rank() select *, dense_rank() over(partition by deptid order by salary desc) as salary_rank from employee