MySQL 求中位数 两个案例

MySQL 求中位数 两个案例

 

简单案例

简单案例数据准备工作见此

 

用户变量法：

SET @rownum := -1;

SELECT
AVG(t.mark) as median_num
FROM
    (SELECT @rownum:=@rownum + 1 AS rowindex,
    marks AS mark
    FROM median_even
    # (或medain_odd)
    ORDER BY marks) AS t
WHERE t.rowindex IN (FLOOR(@rownum / 2) , CEIL(@rownum / 2));

原理：

1. 排序，默认asc
2. 定义用户变量，为数据进行标号（从0开始，初始取-1）
3. 无论求中位数的字段的个数是奇数还是偶数，都取最大编号的一半的floor()和CEIL()，就是向下和向上取整，然后求平均，结果总是中位数

关乎用户变量（SET @xxx:=x）的拓展：https://blog.csdn.net/JesseYoung/article/details/40779631

 

复杂案例

题目：https://leetcode-cn.com/problems/median-employee-salary/

SELECT Id, Company, Salary
FROM Employee
WHERE Id in (
    SELECT e1.Id
    FROM Employee e1
    JOIN Employee e2
    ON e1.Company = e2.Company
    GROUP BY e1.Id
    HAVING SUM(CASE WHEN e1.Salary >= e2.Salary THEN 1 ELSE 0 END) >= COUNT(*)/2 
    AND SUM(CASE WHEN e1.Salary <= e2.Salary THEN 1 ELSE 0 END) >= COUNT(*)/2
    )
GROUP BY Company, Salary
ORDER BY Company

-- 然后将工资>=的数量与COUNT(*)/2，进行对比，将工资<=的数量与COUNT(*)/2 进行对比
-- 还不理解的是    HAVING SUM(CASE WHEN e1.Salary >= e2.Salary THEN 1 ELSE 0 END) >= COUNT(*)/2 AND SUM(CASE WHEN e1.Salary <= e2.Salary THEN 1 ELSE 0 END) >= COUNT(*)/2的结果是什么

• 首先对表进行自连接，主键为id，但这里on的是company，使得比如id为1的A就会对应六次A，每行两个A且对应着两列工资。内连接为工资的对比建立了基础。（因为这里是"各个公司的员工工资中位数"，因此要内连接到Company）

• 然后按照id分组，使得每个id的行数是id所在公司的员工数，每个id都对应该公司所有员工的工资

• 对于下面语句的理解是

   HAVING SUM(CASE WHEN e1.Salary >= e2.Salary THEN 1 ELSE 0 END) >= COUNT(*)/2 AND SUM(CASE WHEN e1.Salary <= e2.Salary THEN 1 ELSE 0 END) >= COUNT(*)/2
  

  

 

---

另外拓展：https://leetcode-cn.com/problems/find-median-given-frequency-of-numbers/

方法一：

SELECT 
AVG(Number)median 
FROM
(SELECT n1.Number FROM Numbers n1 JOIN Numbers n2 ON n1.Number>=n2.Number 
 GROUP BY 
 n1.Number 
 HAVING 
 SUM(n2.Frequency)>=(SELECT SUM(Frequency) FROM Numbers)/2 
 AND 
 SUM(n2.Frequency)-AVG(n1.Frequency)<=(SELECT SUM(Frequency) FROM Numbers)/2
)s

• 核心原理：如果 n1.Number 为中位数，n1.Number（包含本身）前累计的数字应大于等于总数/2 同时n1.Number（不包含本身）前累计数字应小于等于总数/2

方法二：

select avg(n) as median from 
(
    select Number as n, @c1 + 1 as 'c1', (@c1 := @c1 + Frequency) as 'c2', t2.s
    from Numbers, (select @c1 := 0) t1, (select sum(Frequency) as s
    from Numbers) t2
    order by n
) tmp
where c1 <= s/2 + 1 and c2 >= s/2

拓展题目还未完全理解。