1 题目描述

表: Employee

+--------------+---------+  
| Column Name  | Type    |  
+--------------+---------+  
| id           | int     |  
| company      | varchar |  
| salary       | int     |  
+--------------+---------+

id 是该表的主键列 (具有唯一值的列)
该表的每一行表示公司和一名员工的工资
编写解决方案, 找出每个公司的工资中位数
以任意顺序 返回结果表

2 测试用例

输入:
Employee 表:

+----+---------+--------+  
| id | company | salary |  
+----+---------+--------+  
| 1  | A       | 2341   |  
| 2  | A       | 341    |  
| 3  | A       | 15     |  
| 4  | A       | 15314  |  
| 5  | A       | 451    |  
| 6  | A       | 513    |  
| 7  | B       | 15     |  
| 8  | B       | 13     |  
| 9  | B       | 1154   |  
| 10 | B       | 1345   |  
| 11 | B       | 1221   |  
| 12 | B       | 234    |  
| 13 | C       | 2345   |  
| 14 | C       | 2645   |  
| 15 | C       | 2645   |  
| 16 | C       | 2652   |  
| 17 | C       | 65     |  
+----+---------+--------+

输出:

+----+---------+--------+  
| id | company | salary |  
+----+---------+--------+  
| 5  | A       | 451    |  
| 6  | A       | 513    |  
| 12 | B       | 234    |  
| 9  | B       | 1154   |  
| 14 | C       | 2645   |  
+----+---------+--------+

解释:
A 公司总共有 6 条记录, 中位数工资是去掉两个最高和两个最低工资, 符合要求的是两个: 451, 513
B 公司总共有 6 条记录, 中位数工资是去掉两个最高和两个最低工资, 符合要求的是两个: 234, 1154
C 公司总共有 5 条记录, 中位数工资是去掉两个最高和两个最低工资, 符合要求的只有一个: 2645

3 解题思路

按照公司分组, 对工资进行排名 row_number() over (partition by company order by salary), 以及按照公司分组统计每家公司的工资数量 count(*) over (partition by company) as companyCount

select *,  
     row_number() over (partition by company order by salary) as rowNum,  
     count(*) over (partition by company)                     as companyCount  
from Employee

查询结果

+--+-------+------+------+------------+  
|id|company|salary|rowNum|companyCount|  
+--+-------+------+------+------------+  
|2 |A      |341   |2     |6           |  
|5 |A      |451   |3     |6           |  
|6 |A      |513   |4     |6           |  
|1 |A      |2341  |5     |6           |  
|4 |A      |15314 |6     |6           |  
|3 |A      |15    |1     |6           |  
|12|B      |234   |3     |6           |  
|10|B      |1345  |6     |6           |  
|11|B      |1221  |5     |6           |  
|9 |B      |1154  |4     |6           |  
|7 |B      |15    |2     |6           |  
|8 |B      |13    |1     |6           |  
|17|C      |65    |1     |5           |  
|13|C      |2345  |2     |5           |  
|14|C      |2645  |3     |5           |  
|15|C      |2645  |4     |5           |  
|16|C      |2652  |5     |5           |  
+--+-------+------+------+------------+

中位数的工资, 按照题目测试用例, 可以理解为: 假设公司的工资数据量总数为 n, 符合要求的数据:

当 n 为偶数时, 符合要求的数据为: rowNum = n/2 和 rowNum = n/2 + 1
当 n 为奇数时, 符合要求的数据为: rowNum = (n + 1)/2

这里为了简便的查找出符合要求的 rowNum, 可以使用 round(n/2) 和 round((n + 1)/2)
round() 函数的使用介绍[[MySQL 常用函数]]

select id, company, salary  
from (select *,  
             row_number() over (partition by company order by salary) as rowNum,  
             count(*) over (partition by company)                     as companyCount  
      from Employee) as e  
where rowNum in (round(companyCount / 2), round((companyCount + 1) / 2));

查询结果

+--+-------+------+  
|id|company|salary|  
+--+-------+------+  
|5 |A      |451   |  
|6 |A      |513   |  
|12|B      |234   |  
|9 |B      |1154  |  
|14|C      |2645  |  
+--+-------+------+

LeetCode--569. 员工薪水中位数

1 题目描述

2 测试用例

3 解题思路