LeetCode 569.员工薪水中位数

数据准备

drop table Employee;

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');

需求

写一个SQL查询，找出每个公司的工资中位数。

输入

分析

1. 窗口函数新增一列rn1,以salary和id排序,均递增排序
2. 窗口函数新增一列rn2,以salary和id排序,均递减排序
3. 当二者相减,值为0或者1的时候,该数为中位数

输出

-- 窗口函数新增一列rn1,以salary和id排序,均递增排序
-- 窗口函数新增一列rn2,以salary和id排序,均递减排序
-- 当二者相减,值为0或者1的时候,该数为中位数
with t1 as (
    select *,
       row_number() over (partition by company order by salary,id) rn1,
       row_number() over (partition by company order by salary desc,id desc ) rn2
    from Employee
)
select id,company,salary
from t1
where if(rn1>=rn2,rn1-rn2,rn2-rn1) in (0,1)
;