MySQL中如何查询中位数

员工薪水中位数

题目描述：

预期答案：

解法1

既然是求解中位数，我们首先想到的是根据中位数的定义进行求解：奇数个数字时，中位数是中间的数字；偶数个数字时，中位数中间两个数的均值。本题不进行求解均值，而是将两个中位数全部显示。

根据定义，为了查询中位数，我们需要知道3点信息：

总数是奇数个还是偶数个
待查找数字总数
每个数字的排序编号

前两点信息在MySQL中非常简单，只需简单的count计数即可，而排序编号则需要借助辅助方法。在MySQL8.0以上版本引入了窗口函数后非常容易实现，但以前的版本则仅可通过自定义变量的方式获得排序值。这里如何对员工薪水进行分组排序不再展开

在有了排名和数字总数之后，如何判断是中位数呢？这里计数字总数为N，则

N为奇数，中位数排序编号是(N+1)/2=N/2+0.5
N为偶数，中位数排序编号是N/2和N/2+1

进一步地，N为奇数和N为偶数是互斥的，求解出的中位数排序编号也是互斥的，也就是说3个排序编号不会同时取得整数，从而可以不加区分的直接判断即可。

查询SQL语句：

SELECT

     e1.Id, e1.Company, e1.Salary

 FROM

     (SELECT Id, Company, Salary, @rnk:=if(@pre=Company, @rnk+1, 1) rnk, @pre:=Company

     FROM Employee, (SELECT @rnk:=0, @pre:=null)init

     ORDER by Company, Salary, Id)e1

     JOIN

     (SELECT Company, count(*) cnt FROM Employee GROUP by Company) e2

     using(Company)

WHERE e1.rnk in (cnt/2+0.5, cnt/2, cnt/2+1)

查询效率：

解法2

除了根据中位数的排序编号来定位其位置，实际上还可以换种思路但仍然是在其排序编号上做文章：如果一个数是中位数，那么就意味着正序和逆序时其位置是一致的：更严谨的说，奇数个数字是正逆序排序一致，偶数个数字时，两中位数顺序要互换一下，也就是相差为1。进而，我们发现无论数字总数是奇数还是偶数，中位数的正逆排序相差要么为0，要么为1。根据这一性质，我们分别实现正逆两遍排序，然后判断数字的排序编号即可。

查询SQL语句：

SELECT

    e1.Id, e1.Company, e1.Salary

FROM

    (SELECT Id, Company, Salary, @rnk:=if(@pre=Company, @rnk+1, 1) rnk, @pre:=Company

    FROM Employee, (SELECT @rnk:=0, @pre:=null)init

    ORDER by Company, Salary, Id)e1 

    JOIN 

    (SELECT Id, Company, Salary, @rnk:=if(@pre=Company, @rnk+1, 1) rnk, @pre:=Company

    FROM Employee, (SELECT @rnk:=0, @pre:=null)init

    ORDER by Company, Salary DESC, Id DESC)e2

    on e1.Id=e2.Id

WHERE abs(e1.rnk - e2.rnk)<=1

查询效率：

解法3

前2种解法都是根据中位数的定义在数字排序编号上作文章，下面是一个对中位数性质更深的理解（摘抄自官方题解）

根据定义，我们来找一下 [1, 3, 2] 的中位数。首先 1 不是中位数，因为这个数组有三个元素，却有两个元素 (3，2) 大于 1。3 也不是中位数，因为有两个元素小于 3。对于 2 来说，大于 2 和小于 2 的元素数量是相等的，因此 2 是当前数组的中位数。当数组长度为偶数，且元素唯一时，中位数等于排序后中间两个数的平均值。对这两个数来说，大于当前数的数值个数跟小于当前数的数值个数绝对值之差为 1，恰好等于这个数出现的频率。

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

好吧，力扣的官方题解读起来总是这么生涩。不过细品之下，我们还是可以发现这个结论是对的。【好像说了句废话】

根据中位数的这一性质，可以写出如下查询语句：

SELECT

    e1.Id, e1.Company, e1.Salary

FROM

    Employee e1,

    Employee e2

WHERE

    e1.Company = e2.Company

GROUP BY e1.Company , e1.Salary

HAVING SUM(e1.Salary = e2.Salary) >= ABS(SUM(SIGN(e1.Salary - e2.Salary)))

ORDER BY e1.Id

查询效率：

实际上，虽然3种解法均为两表关联，但由于解法3中涉及到相对更为复杂的计算，其效率竟然要比解法1和解法2中低太多。

所以，不妨想想奥卡姆剃刀原理，大道至简、大巧不工、简单之美！

巴特西

MySQL中如何查询中位数

最新文章

热门文章