一、题目
有员工薪资表t_salary_026,包含员工ID（emp_id)，部门ID(depart_id)，薪水(salary),请计算去除最高最低薪资后的平均薪水；（每个部门员工数不少于3人）
+---------+------------+-----------+| emp_id | depart_id | salary |+---------+------------+-----------+| 1001 | 1 | 5000.00 || 1002 | 1 | 10000.00 || 1003 | 1 | 20000.00 || 1004 | 1 | 30000.00 || 1005 | 1 | 6000.00 || 1006 | 1 | 10000.00 || 1007 | 1 | 11000.00 || 1008 | 2 | 3000.00 || 1009 | 2 | 7000.00 || 1010 | 2 | 9000.00 || 1011 | 2 | 30000.00 |+---------+------------+-----------+
二、分析
1.找到部门内的最高最低薪资，去掉这些行，考察点为row_number()开窗函数；
2.本题的难点在于同时去掉最高和最低，所以需要按照不同的排序进行处理，所以需要进行两次开窗，排序；
3.去除最高最低薪资之后，按照部门分组进行分组取平均即可；
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
三、SQL
1）分别按照正序和倒序进行开窗，得到部门最高最低薪资记录
selectemp_id,depart_id,salary,row_number()over(partition by depart_id order by salary asc) as asc_order,row_number()over(partition by depart_id order by salary desc) as desc_orderfrom t_salary_026;
查询结果
2)去掉最高最低薪资，这里只能有第一行，不能取排序最大值进行去重，那样处理起来太复杂（不能确定排序最大值是多少，这也是进行两次开窗排序的原因）
selectemp_id,depart_id,salary,asc_order,desc_orderfrom(selectemp_id,depart_id,salary,row_number()over(partition by depart_id order by salary asc) as asc_order,row_number()over(partition by depart_id order by salary desc) as desc_orderfrom t_salary_026)twhere asc_order >1 and desc_order >1
查询结果
3）查询部门平均薪水
selectdepart_id,avg(salary) as avg_salaryfrom(selectemp_id,depart_id,salary,row_number()over(partition by depart_id order by salary asc) as asc_order,row_number()over(partition by depart_id order by salary desc) as desc_orderfrom t_salary_026)twhere asc_order >1 and desc_order >1group by depart_id
查询结果
引伸：
1.题目中每个部门员工>=3人，则去除最高最低之后至少还有1人，如果部门人员为2人或者1人该如何处理？
2.如果最高和最低薪资不止一人，该如何处理?（row_number、rank究竟改用哪个）
四、建表语句和数据插入
--建表语句CREATE TABLE t_salary_026 (emp_id bigint,depart_id bigint,salary decimal(16,2)) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';--插入数据insert into t_salary_026 (emp_id,depart_id,salary)values(1001,1,5000.00),(1002,1,10000.00),(1003,1,20000.00),(1004,1,30000.00),(1005,1,6000.00),(1006,1,10000.00),(1007,1,11000.00),(1008,2,3000.00),(1009,2,7000.00),(1010,2,9000.00),(1011,2,30000.00);
原文作者：数据仓库技术；如有侵权，请联系管理员删除。
请登录后发表评论
登录
全部评论
