Mysql 中位数计算

1.数据准备和需求

数据准备：

create table employee
(
    id   int auto_increment primary key comment '编号',
    company varchar(255) comment '公司',
    salary int comment '工资'
);
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);

计算每个公司工资的中位数，结果如下：

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

2.结果实现

思路1：N为奇数，中位数排序编号是(N+1)/2；N为偶数，中位数排序编号是N/2和N/2+1，不管奇偶数就是(N+1)/2向下取整和(N+2)/2向下取整。

select id, company, salary
from
(select id,
       company,
       salary,
        cast(row_number() over (partition by company order by salary,id) as signed) asc_salary,
        count(1) over (partition by company) total_num
from employee) t
where asc_salary in ((total_num+1)/2,total_num/2,total_num/2+1)
-- where asc_salary in (floor((total_num + 1)/2), floor((total_num + 2)/2))
;

思路2：N为奇数，正序排序编号和逆序排序编号相等(相差0)，N为偶数，正序排序编号和逆序排序编号互换(相差1)；

select id, company, salary
from
(select id,
       company,
       salary,
       cast(row_number() over (partition by company order by salary,id) as signed) asc_salary,
       cast(row_number() over (partition by company order by salary desc,id desc) as signed) desc_salary
from employee) t
where asc_salary = desc_salary or abs(asc_salary-desc_salary) = 1;

思路3：不管奇数和偶数，正序排序编号、逆序排序编号大于等于N/2；

select id, company, salary
from
(select id,
       company,
       salary,
        cast(row_number() over (partition by company order by salary,id) as signed) asc_salary,
        cast(row_number() over (partition by company order by salary desc,id desc) as signed) desc_salary,
        count(1) over (partition by company) total_num
from employee) t
where asc_salary >= total_num/2
and desc_salary >= total_num/2;

思路4：不管数组长度是奇是偶，也不管元素是否唯一，中位数出现的频率一定大于等于 大于它的数与小于它的数的绝对值之差。

select min(id) id
			 a.company,
       a.salary
from employee as a,
     employee as b
where a.company = b.company
group by a.company, a.salary
having sum(a.salary = b.salary) >= abs(sum(sign(a.salary - b.salary)))