Hive sql 面试题-求出每个部门工资最高的前三名员工，并计算这些员工的工资所占所属部门总工资的百分比

1. 求出每个部门工资最高的前三名员工，并计算这些员工的工资所占所属部门总工资的百分比

create table emp(empno string,ename string ,hiredate string ,sal int ,depto int );

insert into emp values('01','WARO1','22/2/1981','1250','30');
insert into emp values('02','WARO2','1/2/1981','4000','30');
insert into emp values('03','WARO3','2/2/1981','5000','30');
insert into emp values('04','WARO4','3/2/1981','6000','30');

insert into emp values('05','WARO5','9/2/1980','1250','40');
insert into emp values('06','WARO6','10/2/1982','1251','40');
insert into emp values('07','WARO7','7/2/1983','1252','40');
insert into emp values('08','WARO8','9/2/1984','1253','40');

select  empno, sal,depto, all_sal,num , ROUND(sal/all_sal ,2) as rate  from (  

select empno,sal, depto,  sum(sal) over(partition by depto  ) as all_sal ,row_number() over(partition by depto order by sal desc ) as num   

from emp ) t where num <= 3 ;

sum(sal) over() 总和 sum(sal) over(partition by depto ) 每组的和 sum(sal)
over(partition by depto order by sal hiredate desc ) 先按depto
分组,在按hiredate 排序，然后分组累计求和 sum(sal) over(order by hiredate )
先排序，然后分组累计求和