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

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MWPVI8a2yIwxj8dqAhO%2F-MWPVWhX91VFKKCDZfDN%2F%E4%BD%A0%E7%9A%84%E5%B9%B3%E5%9D%87%E8%96%AA%E6%B0%B4%E6%98%AF%E5%A4%9A%E5%B0%91_1.webp?alt=media\&token=a81dea9d-b710-4d7c-9bd5-0f3b2ff9cc85)

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

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MWPVI8a2yIwxj8dqAhO%2F-MWPVasYFtAbnEt0BsoN%2F%E4%BD%A0%E7%9A%84%E5%B9%B3%E5%9D%87%E8%96%AA%E6%B0%B4%E6%98%AF%E5%A4%9A%E5%B0%91_2.webp?alt=media\&token=79d14427-d26e-48e1-a228-811d3ca2b04e)

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

**【解题步骤】**

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

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

所以需要按每组来排名薪水，**既要排名，又要分组的问题**，需要用[窗口函数](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649249645\&idx=2\&sn=d1295b268eff974fe52a2c0f8bdadccb\&chksm=835fdf5db428564b7d42f733cdd45c6ce1aeeff0da8cdfac2f5047354bc815895f1a7fd282de\&scene=21#wechat_redirect)来解决。

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

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

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

1） 专用窗口函数，包括 rank, dense\_rank, row\_number 等专用窗口函数。

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

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

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

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

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MWPVI8a2yIwxj8dqAhO%2F-MWPVnNrYqjOHji33MZF%2F%E4%BD%A0%E7%9A%84%E5%B9%B3%E5%9D%87%E8%96%AA%E6%B0%B4%E6%98%AF%E5%A4%9A%E5%B0%91_3.png?alt=media\&token=b0c415da-e349-4d7e-8897-a04c085f2c2d)

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

```sql
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（蓝色框）的是每个部门的最低薪水。

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MWPVI8a2yIwxj8dqAhO%2F-MWPVw_6cd6uOqG0puQb%2F%E4%BD%A0%E7%9A%84%E5%B9%B3%E5%9D%87%E8%96%AA%E6%B0%B4%E6%98%AF%E5%A4%9A%E5%B0%91_4.png?alt=media\&token=fd82124e-6e16-4f6f-8cbd-69e8564e2ae6)

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

用 where 子句来筛选就可以了，where rank\_1 > 1 and rank\_2 > 1

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MWPVzryJWjMekj0uRXd%2F-MWPW2uh52zO7xRN1lya%2F%E4%BD%A0%E7%9A%84%E5%B9%B3%E5%9D%87%E8%96%AA%E6%B0%B4%E6%98%AF%E5%A4%9A%E5%B0%91_5.png?alt=media\&token=45ede5bc-b717-40b5-9869-68c9f44c9b34)

```sql
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 的运行顺序](http://mp.weixin.qq.com/s?__biz=MzU5NTc1ODI5Mw==\&mid=2247484035\&idx=1\&sn=f6cc38ffb91396061ab7f101257262db\&chksm=fe6c5f39c91bd62f88162ea5ef75a9b757139cad11f02ca6f3de2db931cc89e621921da0dc2d\&scene=21#wechat_redirect)，会先运行 from 和 where 子句，最后才运行 select 子句。

而 rank\_1 和 rank\_2 在 select 子句中，是最后运行的。所以运行时 where 子句中的 rank\_1 和 rank\_2 是不存在的。

所以，我们需要将前面的查询结果作为[子查询](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649249645\&idx=2\&sn=d1295b268eff974fe52a2c0f8bdadccb\&chksm=835fdf5db428564b7d42f733cdd45c6ce1aeeff0da8cdfac2f5047354bc815895f1a7fd282de\&scene=21#wechat_redirect)后，再用 where 子句：

```sql
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;
```

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

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MWPVzryJWjMekj0uRXd%2F-MWPWDYg9acK_-MdhY3O%2F%E4%BD%A0%E7%9A%84%E5%B9%B3%E5%9D%87%E8%96%AA%E6%B0%B4%E6%98%AF%E5%A4%9A%E5%B0%91_6.png?alt=media\&token=5933de14-8520-4f41-880f-6c35dd464889)

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

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

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

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

```sql
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.部门编号;
```

查询结果如下。

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MWPVzryJWjMekj0uRXd%2F-MWPWPog9cLfyTPvbexi%2F%E4%BD%A0%E7%9A%84%E5%B9%B3%E5%9D%87%E8%96%AA%E6%B0%B4%E6%98%AF%E5%A4%9A%E5%B0%91_7.png?alt=media\&token=e6462de7-0e32-4cd3-af95-56490f80d3d2)

**【本题考点】**

1. 考察解决复杂问题的能力，可以使用[逻辑树分析方法](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649248793\&idx=2\&sn=1fe86f3083bd9c8ad1a427898199c71c\&chksm=835fdc29b428553fabf47f23dc05f8f6c296cbcc06ce7e409ff712e81141fbf9ad0aa9b2b99d\&scene=21#wechat_redirect)，将复杂问题拆解问简单的子问题。
2. 考查 sql 的运行顺序和[子查询](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649249645\&idx=2\&sn=d1295b268eff974fe52a2c0f8bdadccb\&chksm=835fdf5db428564b7d42f733cdd45c6ce1aeeff0da8cdfac2f5047354bc815895f1a7fd282de\&scene=21#wechat_redirect)
3. 遇到既要分组，又要排名的问题，要想到使用[窗口函数](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649249645\&idx=2\&sn=d1295b268eff974fe52a2c0f8bdadccb\&chksm=835fdf5db428564b7d42f733cdd45c6ce1aeeff0da8cdfac2f5047354bc815895f1a7fd282de\&scene=21#wechat_redirect)
4. 考查平均数的计算以及结果保留几位小数

**【举一反三】**

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

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MWPVzryJWjMekj0uRXd%2F-MWPWSrT046pWSnitxmL%2F%E4%BD%A0%E7%9A%84%E5%B9%B3%E5%9D%87%E8%96%AA%E6%B0%B4%E6%98%AF%E5%A4%9A%E5%B0%91_8.webp?alt=media\&token=2033b068-0335-4f4e-b409-01c1a03be9d4)

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

```sql
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;
```

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MWPVzryJWjMekj0uRXd%2F-MWPWchkurjOLl0ZxUo_%2F%E4%BD%A0%E7%9A%84%E5%B9%B3%E5%9D%87%E8%96%AA%E6%B0%B4%E6%98%AF%E5%A4%9A%E5%B0%91_9.png?alt=media\&token=7ae914a4-86a3-4576-bf60-0d76502e0aee)

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MWPVzryJWjMekj0uRXd%2F-MWPWgRx9F3HlAQFaht-%2F%E4%BD%A0%E7%9A%84%E5%B9%B3%E5%9D%87%E8%96%AA%E6%B0%B4%E6%98%AF%E5%A4%9A%E5%B0%91_10.png?alt=media\&token=d40475bf-8dc8-4f90-9cca-de078a29a98f)

[推荐：如何从零学会 sql？](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649249645\&idx=2\&sn=d1295b268eff974fe52a2c0f8bdadccb\&chksm=835fdf5db428564b7d42f733cdd45c6ce1aeeff0da8cdfac2f5047354bc815895f1a7fd282de\&scene=21#wechat_redirect)

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MWPVzryJWjMekj0uRXd%2F-MWPWkrmBPhmhfcVfHvY%2F%E4%BD%A0%E7%9A%84%E5%B9%B3%E5%9D%87%E8%96%AA%E6%B0%B4%E6%98%AF%E5%A4%9A%E5%B0%91_11.webp?alt=media\&token=277037b8-a0dd-4504-8f97-d949db969631)
