字节跳动面试题：你的平均薪水是多少？

薪水表中记录了员工的编号，所在部门编号，和薪水。

查询出每个部门除去最高、最低薪水后的平均薪水，并保留整数。（字节跳动面试题）

【解题步骤】

1. 如何找出最高、最低薪水？

要求每个部门除去最高、最低薪水后的的平均薪水，所以应该查询出每个部门的最高、最低工资。

所以需要按每组来排名薪水，既要排名，又要分组的问题，需要用窗口函数来解决。

窗口函数的基本语法如下：

<窗口函数> over (partition by <用于分组的列名>                
order by <用于排序的列名>)

语法中 <窗口函数> 的位置，可以放以下两种函数：

1）专用窗口函数，包括 rank, dense_rank, row_number 等专用窗口函数。

2）聚合函数，如 sum. avg, count, max, min 等

在该题中，我们需要对部门进行分组，并对薪水进行排序：

select *,   
      rank() over (partition by 部门编号 
                   order by 薪水 desc) as ranking
from 薪水表;

查询结果如下，因为是降序排列，排名为 1 的是每个部门的最高薪水。

我们还需要再用一次窗口函数求出每个部门的最低薪水，也就是升序排列时，排名为 1 的是每个部门的最低薪水

select *,   
  rank() over (partition by 部门编号 order by 薪水 desc) as rank_1,
  rank() over (partition by 部门编号 order by 薪水) as rank_2
from 薪水表;

查询结果如下。下图 rank_1 列数值为 1（红色框）的是每个部门的最高薪水，rank_2 列数值为 1（蓝色框）的是每个部门的最低薪水。

2. 如何去掉最高和最低薪水？

用 where 子句来筛选就可以了，where rank_1 > 1 and rank_2 > 1

select *,   
  rank() over (partition by 部门编号 order by 薪水 desc) as rank_1,
  rank() over (partition by 部门编号 order by 薪水) as rank_2
from 薪水表
where rank_1 >1 and rank_2 >1;

但是这样运行会出错，因为按照 sql 的运行顺序，会先运行 from 和 where 子句，最后才运行 select 子句。

而 rank_1 和 rank_2 在 select 子句中，是最后运行的。所以运行时 where 子句中的 rank_1 和 rank_2 是不存在的。

所以，我们需要将前面的查询结果作为子查询后，再用 where 子句：

select *
from (
  select *,
  rank() over (partition by 部门编号 order by 薪水 desc) as rank_1,
  rank() over (partition by 部门编号 order by 薪水) as rank_2
  from 薪水表
 ) as a
where a.rank_1 >1 and a.rank_2 >1;

查询结果如下，此时已经是每个部门去掉最高、最低薪水后的结果了。

3. 查询每个部门除去最高、最低薪水的平均薪水

看到 “每个” 这样的问题，要想到用分组（group by），平均薪水使用 avg 函数。

另外，题目还要求薪水保留整数。保留整数即保留 0 位小数，可以用 format 函数：

format(N,D)
N是要格式化的数字
D是要舍入的小数位数。

select a.部门编号,format(avg(a.薪水),0) as 平均薪水
from
  (
  select *,
  rank() over (partition by 部门编号 order by 薪水 desc) as rank_1,
  rank() over (partition by 部门编号 order by 薪水) as rank_2
  from 薪水表
  ) as a
where a.rank_1 >1 and a.rank_2 >1
group by a.部门编号;

查询结果如下。

【本题考点】

考察解决复杂问题的能力，可以使用逻辑树分析方法，将复杂问题拆解问简单的子问题。
考查 sql 的运行顺序和子查询
遇到既要分组，又要排名的问题，要想到使用窗口函数
考查平均数的计算以及结果保留几位小数

【举一反三】

如图是某班 6 名同学的成绩：

请你写一个 sql 语句查询该 6 名同学的成绩中除去最高、最低分的后的平均分数，并保留 2 位小数。

select format(avg(a.成绩),2) as 平均成绩
from
  (
  select *,
  rank() over (order by 成绩 desc) as rank_1,
  rank() over (order by 成绩) as rank_2
  from 成绩表
  ) as a
where a.rank_1 >1 and a.rank_2 >1;