SQL笔面试题：如何求取中位数？

大家好，我是宝器。

前面写了篇：最近的面试，里面提到了我问了中位数的求取。正好看到胖里做了汇总，分享下给大家

中位数（Median）又称中值，统计学中的专有名词，是按顺序排列的一组数据中居于中间位置的数，代表一个样本、种群或概率分布中的一个数值，其可将数值集合划分为相等的上下两部分。对于有限的数集，可以通过把所有观察值高低排序后找出正中间的一个作为中位数。如果观察值有偶数个，通常取最中间的两个数值的平均数作为中位数。

百度百科

说到中位数，大家应该都不陌生。中位数是九年义务教育中数学里的一个重要概念，想当初刚学习的时候会经常对平均数、中位数、众数进行比较，比较它们的优缺点是什么，什么情况下应该用哪一种数。

不知道大家日常数据分析工作中是否会用到中位数的求取，如果是通过Excel、Python等进行数据分析、可视化展示，那这两款软件都有其对应的求中位数的函数，分别是median()和numpy库中的median()。如果写SQL的时候用到，可能就没有那么现成的函数了。（oracle中有求取中位数的函数）

除了日常工作，数据分析的笔面试中也经常会出现中位数求取的考察。今天就总结三种用SQL求取中位数的方法。

方法1：充分利用窗口函数

思路介绍：

根据中位数的概念，想要求取中位数，需要对一组数据进行排序，找出居于中间位置的数，如果有奇数个数，那最中间的一个为中位数，如果有偶数个数，那中间两个数的平均数为中位数。因此我们需要实现的是排序，取中间值或中间两值的平均值，既然涉及到中间这种位置定位，那自然少不了编号与总体个数的比较。

因此可以概括为排序、编号、找位置、取值。那就需要考虑到排序函数row_number()、计数函数cout()、求均值函数avg()。

示例：

有一组数：1，3，55，8，34，66，42，88，SQL找出中位数。

按照先前的思路，使用相关函数实现：

select avg(num)from (    select num         ,row_number() over(order by num) as rn         ,count(*) over() as n    from tmp)as t where rn in (floor(n/2)+1,if(mod(n,2) = 0,floor(n/2),floor(n/2)+1))

先使用row_number()函数对数据从小到大进行排序标号，用count()顺便实现数据总数的记录，假设为n个。如果n为奇数，则取最中间一个值作为中位数，也就是编号为floor(n/2)+1的数，如果n为偶数，需要取中间位置的两个数，也就是floor(n/2)和floor(n/2)+1的两个数。因此可以将floor(n/2)+1作为一个rn的取值，另一个通过判断奇偶性来选择。

当然思路一致，你选择不同的函数实现也是可以的，比如不用if用case when来判断。

方法2：正排倒排来一遍法

思路介绍：

不妨这样想一想，还是根据中位数的概念，一组数据想求中位数，那么这个数或这两个数肯定在一组排序好的数据的中间位置，那是不是正排和倒排的编号会存在某种规律？

假设有一组数：33，25，4，63，18（奇数个），正排和倒排编号之后如下。

假设还有一组数：33，25，4，63，18，22（偶数个），正排和倒排编号之后如下。

观察上述两个示例，会发现由于中位数的独特魅力所在，无论正排还是倒排，对于奇数个数来说，其编号始终不变，而对于偶数个数来说，两个中位数（取均值）的编号相差±1。

按照上述观察结果，就可以得到另一种求中位数的思路，也就是对数据进行正排和倒排，编号，按照奇偶两种条件进行限制，求得编号是这两种条件的一个值或两个值的平均数作为中位数。

示例：

有一组数：1，3，55，8，34，66，42，88，SQL找出中位数。

按照先前的思路，使用相关函数实现：

select avg(num)from (    select num         ,row_number() over(order by num) as rn1        ,row_number() over(order by num desc) as rn2    from tmp)as t where rn1 = rn2 or abs(rn1-rn2) = 1

此处需要注意一个问题，上述SQL代码用MySQL跑时，会报错，需要设置下参数，SET sql_mode='NO_UNSIGNED_SUBTRACTION'。

还有，我们不得不考虑这样一种情况，如果待求中位数的数据中存在相等的数怎么办？比如下图的示例，出现了多个重复数据，对于相同的值使用row_number()函数可能不能实现像我们预期那样的正排倒排，此时若按照rn1 = rn2或abs(rn1 - rn2)相差1这两个条件进行限制只能得到6，但实际上中位数为2和6的平均数。

因此为了达到预期想要的正排倒排的效果，可以使用主键id跟着要排序的数据进行正排倒排，保证正排和倒排数据的编号走向处处相反。

select avg(num)from (    select id        ,num         ,row_number() over(order by num, id) as rn1        ,row_number() over(order by num desc, id desc) as rn2    from tmp)as t where rn1 = rn2 or abs(rn1-rn2) = 1

难道求中位数只能通过排序？不排序可以找到中位数吗？我们来看看方法三。

方法三：自连接比较法

思路介绍：

我们可以想一想，除了被动排序编号，这些数据是不是可以主动一把？一个数A可以主动去跟别的数作比较，如果比别的数小则+1，比别的数大则-1，这+1，-1加和是不是能表示这个数的“地位”，也就是变相的表征如果按大小排序，是排在什么样的位置上。是不是也就意味着+1，-1加和得到的结果（绝对值）越小，这个数越处于居中位置？

举个例子。

有这么一组数据：1，2，3，4，5，6。按照刚刚描述，对它们分别求取一个加和结果margin和margin的绝对值。

从图上可以看到，3和4对应的margin的绝对值最小，因此它们两个就是居中的数。通过这个例子是否能get此方法？是否能得到某个处于中间位置的值，或某两个处于中间位置的值？

当然上述示例比较简单，再多考虑一下，这种操作对于有重复值的适用吗？

我们看图中这个示例，数据中存在多个重复值，按刚刚的思路，margin绝对值最小的num即为中位数，但此示例中显然不是，2和6的均值才是中位数。

此时要想沿用之前的思路，就得加限制条件，也就是统计下与该数相等的数的个数，记为equal，毕竟个数会影响到num的位置。选出equal大于或等于margin绝对值的num，也就是2和6。

按照目前的思路，使用相关语句实现：

select avg(num)from (    select t1.num         ,abs(sum(sign(t2.num-t1.num))) as margin        ,sum(if(t1.num = t2.num,1,0)) as equal    from     (        select num         from tmp     )as t1     inner join     (        select num         from tmp    )as t2     group by t1.num )as t where equal >= margin

以上便是我今天分享的三种使用SQL进行中位数求取的方法。当然大家也可以考虑下是否存在某些现成的计算分位数的函数，毕竟中位数就是二分位数，直接使用函数可比写SQL来的容易。如此看来，此考题的目的就是考你对于中位数的理解，考你的思路和方法了。

至于与中位数相关的笔试题目我这边就不做赘述了，大家可以去网站上搜搜看，有类似的题目，现实中也有相关应用。比如求各科成绩的中位数、各部门员工薪资的中位数等等。实例有很多，可以自己结合现成函数或上述方法操作看看。