每日sql-不同公司员工工资中位数

不同公司员工工资中位数

源数据

插入数据

Create table If Not Exists Employee (Id int, Company varchar(255), Salary int);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);

中位数

SELECT
    e1.Id,e1.Company,e1.Salary
FROM
    (SELECT Id, Company, Salary, @rnk1:=if(@pre1=Company, @rnk1+1, 1) rnk, @pre1:=Company
    FROM Employee, (SELECT @rnk1:=0, @pre1:=null)init
    ORDER by Company, Salary, Id)e1 
    JOIN 
    (SELECT Id, Company, Salary, @rnk2:=if(@pre2=Company, @rnk2+1, 1) rnk, @pre2:=Company
    FROM Employee, (SELECT @rnk2:=0, @pre2:=null)init
    ORDER by Company, Salary DESC, Id DESC)e2
    on e1.Id=e2.Id
WHERE abs(e1.rnk - e2.rnk)<=1

数据为偶数的需要取平均值

单行数据取中位数

SELECT AVG(DISTINCT a.Salary) AS median_salary
FROM
 (SELECT a.Salary
  FROM Employee AS a, Employee AS b
  GROUP BY a.Salary
  HAVING SUM(CASE WHEN b.Salary >= a.Salary THEN 1 ELSE 0 END) >= COUNT(*) / 2 
  AND SUM(CASE WHEN b.Salary <= a.Salary THEN 1 ELSE 0 END) >= COUNT(*) / 2 ) AS a;