薪水表中记录了员工的编号,所在部门编号,和薪水。 查询出每个部门除去最高、最低薪水后的平均薪水,并保留整数。(字节跳动面试题) 【解题步骤】 1.如何找出最高、最低薪水? 要求每个部门除去最高、最低薪水后的的平均薪水,所以应该查询出每个部门的最高、最低工资。 所以需要按每组来排名薪水,既要排名,又要分组的问题,需要用窗口函数来解决。 窗口函数的基本语法如下: 语法中<窗口函数>的位置,可以放以下两种函数: 1) 专用窗口函数,包括rank, dense_rank, row_number等专用窗口函数。 2) 聚合函数,如sum. avg, count, max, min等 在该题中,我们需要对部门进行分组,并对薪水进行排序: 查询结果如下,因为是降序排列,排名为1的是每个部门的最高薪水。 我们还需要再用一次窗口函数求出每个部门的最低薪水,也就是升序排列时,排名为1的是每个部门的最低薪水 查询结果如下。下图rank_1列数值为1(红色框)的是每个部门的最高薪水,rank_2列数值为1(蓝色框)的是每个部门的最低薪水。 2.如何去掉最高和最低薪水? 用where子句来筛选就可以了,where rank_1 > 1 and rank_2 > 1 但是这样运行会出错,因为按照sql的运行顺序,会先运行from和where子句,最后才运行select子句。 而rank_1和rank_2在select子句中,是最后运行的。所以运行时where子句中的rank_1和rank_2是不存在的。 所以,我们需要将前面的查询结果作为子查询后,再用where子句: 查询结果如下,此时已经是每个部门去掉最高、最低薪水后的结果了。 3.查询每个部门除去最高、最低薪水的平均薪水 看到“每个”这样的问题,要想到用分组(group by),平均薪水使用avg函数。 另外,题目还要求薪水保留整数。保留整数即保留0位小数,可以用format函数: 查询结果如下。 【本题考点】 1.考察解决复杂问题的能力,可以使用逻辑树分析方法,将复杂问题拆解问简单的子问题。 2.考查sql的运行顺序和子查询 3.遇到既要分组,又要排名的问题,要想到使用窗口函数 4.考查平均数的计算以及结果保留几位小数 【举一反三】 如图是某班6名同学的成绩: 请你写一个sql语句查询该6名同学的成绩中除去最高、最低分的后的平均分数,并保留2位小数。 推荐:如何从零学会sql?1 <窗口函数> over (partition by <用于分组的列名>
2 order by <用于排序的列名>)
1 select *,
2 rank() over (partition by 部门编号
3 order by 薪水 desc) as ranking
4 from 薪水表;
1 select *,
2 rank() over (partition by 部门编号 order by 薪水 desc) as rank_1,
3 rank() over (partition by 部门编号 order by 薪水) as rank_2
4 from 薪水表;
1 select *,
2 rank() over (partition by 部门编号 order by 薪水 desc) as rank_1,
3 rank() over (partition by 部门编号 order by 薪水) as rank_2
4 from 薪水表
5 where rank_1 >1 and rank_2 >1;
1 select *
2 from (
3 select *,
4 rank() over (partition by 部门编号 order by 薪水 desc) as rank_1,
5 rank() over (partition by 部门编号 order by 薪水) as rank_2
6 from 薪水表
7 ) as a
8 where a.rank_1 >1 and a.rank_2 >1;
1 format(N,D)
2 N是要格式化的数字
3 D是要舍入的小数位数。
1 select a.部门编号,format(avg(a.薪水),0) as 平均薪水
2 from
3 (
4 select *,
5 rank() over (partition by 部门编号 order by 薪水 desc) as rank_1,
6 rank() over (partition by 部门编号 order by 薪水) as rank_2
7 from 薪水表
8 ) as a
9 where a.rank_1 >1 and a.rank_2 >1
10 group by a.部门编号;
1 select format(avg(a.成绩),2) as 平均成绩
2 from
3 (
4 select *,
5 rank() over (order by 成绩 desc) as rank_1,
6 rank() over (order by 成绩) as rank_2
7 from 成绩表
8 ) as a
9 where a.rank_1 >1 and a.rank_2 >1;