mysql查询中位数（窗口函数的使用）

mysql查询中位数（leetcode569的延伸）

Create table If Not Exists Employee (Id int, Company varchar(255), Salary int)
Truncate table Employee
insert into Employee (Id, Company, Salary) values ('1', 'A', '2341')
insert into Employee (Id, Company, Salary) values ('2', 'A', '341')
insert into Employee (Id, Company, Salary) values ('3', 'A', '15')
insert into Employee (Id, Company, Salary) values ('4', 'A', '15314')
insert into Employee (Id, Company, Salary) values ('5', 'A', '451')
insert into Employee (Id, Company, Salary) values ('6', 'A', '513')
insert into Employee (Id, Company, Salary) values ('7', 'B', '15')
insert into Employee (Id, Company, Salary) values ('8', 'B', '13')
insert into Employee (Id, Company, Salary) values ('9', 'B', '1154')
insert into Employee (Id, Company, Salary) values ('10', 'B', '1345')
insert into Employee (Id, Company, Salary) values ('11', 'B', '1221')
insert into Employee (Id, Company, Salary) values ('12', 'B', '234')
insert into Employee (Id, Company, Salary) values ('13', 'C', '2345')
insert into Employee (Id, Company, Salary) values ('14', 'C', '2645')
insert into Employee (Id, Company, Salary) values ('15', 'C', '2645')
insert into Employee (Id, Company, Salary) values ('16', 'C', '2652')
insert into Employee (Id, Company, Salary) values ('17', 'C', '65')

leetcode569：求员工薪水中位数

SELECT *,
case when abs(r1-r2)=1 then 0.5*sum(salary) over(partition by company)
when r1=r2 then salary end as median
from (select *,
cast(row_number() over(partition by company ORDER BY salary desc ,id desc) as signed) r1, 
cast(row_number() over(partition by company order by salary asc, id asc) as signed) r2
from employee) t
where abs(r1-r2)=1 or r1=r2

注意

• SQL求中位数可以运用ROW_NUMBER()窗口函数正序反续构建两个列
• 为对正反序列进行绝对值计算，需要cast()函数将其转化为signed类型
• 若数据量为偶数，则中位数则为ABS(r1,r2)=1的两位数的平均值；若数据量为奇数，则中位数则为r1=r2的那位数
• 然后搭配case when 语句以及sum()over()窗口函数即可求出各组中位数