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   |
+----+---------+--------+

3 解题思路

1. 中位数 (median) 是将一组数据按照从小到大的顺序排列 (或者从大到小的顺序也可以) 之后处在数列中点位置的数值, 是典型的位置平均数

如果数列是奇数, 中位数等于第 (n+1)/2 个数, 也就是 median = (n+1)/2
如果数列是偶数, 中位数等于第 n/2 和 n/2+1 个数的平均数, 也就是 median = n/2 or median = n/2 + 1
总结起来就是: 中位数在奇数或偶数的范围n/2 <= median <= n/2 + 1, 包含了 n/2, (n+1)/2, n/2 + 1

2. 使用 row_number() 函数按照公司 company 分组对工资 salary 进行排名, MySQL 排名函数

select id,company,salary,row_number() over (partition by company order by salary) as companySalaryRank,count(id) over (partition by company)                    as n
from Employee

查询结果

id	company	salary	companySalaryRank	n
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

3. 在公司的工资排名结果中查找中位数

select id,company,salary
from (select id,company,salary,row_number() over (partition by company order by salary) as companySalaryRank,count(id) over (partition by company)                    as nfrom Employee) as e
where companySalaryRank >= n / 2and companySalaryRank <= n / 2 + 1;

查询结果

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

3.2 解法 1

按照解题思路, 在 MySQL 中的 sql:

select id,company,salary
from (select id,company,salary,row_number() over (partition by company order by salary) as companySalaryRank,count(id) over (partition by company)                    as nfrom Employee) as e
where companySalaryRank between n / 2 and n / 2 + 1;

相似题目

LeetCode–571. 给定数字的频率查询中位数