oracle如何判断奇数偶数_SQL复习笔记 —— 如何求中位数？

SQL数据库提供了多种聚合函数，譬如平均值、标准差等等，但是没有提供计算中位数的函数，因此需要自己编写查询语句取得中位数。SQL求中位数的逻辑并不简单，下面提供比较简单易记的两种思路：

一、窗口函数法 —— 简单却巧妙的办法

一般来说，如果序列的长度为奇数，中位数则为排序后最中间的一个数（如长度为5的序列，中位数是排序后第3个数）；如果序列的长度为偶数，我们这里规定中位数为最中间的两个数（如长度为6的序列，则第3个和第4个都是中位数，这里的规定与一般统计学中有差异，但只需略微改动便可实现一般意义下的中位数）。首先选取中位数的基本思路就是序列排好序，选取最中间的一个或者两个数。因此需要对数据进行编号，这里SQL中窗口函数row_number()便可以简单地实现这个功能。

我们考虑一种简洁的想法。先将一组数排好顺序，首先从小到大给序列标上号(1, 2, 3, ……, n)，然后再从大到小给序列编上号(n, ……, 3, 2, 1)。如何根据这两组编号确定中位数的位置呢？首先看序列长度为奇数的情况：

图1 长度为奇数的序列确定中位数

明显看到，当正向编号和反向编号相等时，该位置即是中位数所在的位置。下面再来看序列长度为偶数时的情况：

图2 长度为偶数的序列确定中位数

也很显然，这种情况下我们所规定的中位数处在正向编号和反向编号差

的两个位置（统计学上的中位数只需取一下平均即可）。

首先，如果序列长度为奇数，不会存在正反编号差1的情况；同样，序列长度为偶数也不会存在正反编号相等的情况，因此无须分类处理，将序号相等或者差1同时设为条件，即可对两种程度的序列统一处理，自动根据序列长度选取正确的处理方式。

还有个小细节就是在使用SQL窗口函数按照大小顺序编号的时候，相等的两个数会存在以升序序列来标注问题，如下图所示：

图3 反向编号失效情形

此时由于待求序列两个位置均是45，45，因此反向编号不会按照预想的那样编为5，4，3，2，1，这样的情况会导致上述的选取方法失效。此时可以利用SQL表的主键id，在待求序列相等时，正向编号按id升序排序编号，反向编号按id降序排序编号，这样就保证了两列编号的走向处处相反，从而使得判断条件生效。

Leetcode 569题便是一个典型的分组求中位数的题，在面试中频繁出现^[1]，利用上述的思路，可以给出以下一种可能的实现方式：

select
id,
company,
salary
from(select
id,
company,
salary,
cast(row_number() over(partition by company order by salary asc, id asc) as signed) as 'id1',
cast(row_number() over(partition by company order by salary desc, id desc) as signed) as 'id2'
from employee) as newtable
where abs(id1-id2)=1 or
id1=id2;

可以看出，这种思路比较巧妙，且使用SQL语句编写时逻辑十分简明，不易出错，因此比较推荐这种思路。

二、会话变量法 —— 想法自然，适用性强

尽管窗口函数在中位数问题上非常简洁高效，而且是解决许多问题的“大杀器”，但是其灵活性和泛用性并不高，而会话变量法则具备一定的“套路”，因此经常可以以不变应万变，同时效率一般来说相对较高。虽然在中位数选取问题上会话变量法有些繁杂，但是也希望通过这个例子，来复习如何设置变量来处理SQL逻辑相关问题。

这种方法的思路非常朴素，使用会话变量对每一组待求中位数的数据进行标号，同时使用COUNT()函数记录每一组数据的长度。从图2可以看出，对于奇数长度的序列，中位数的编号为COUNT()/2+0.5（譬如图2中中位数为5/2+0.5=3)；从图3可以看出，对于偶数长度的序列，中位数的编号为COUNT()/2和COUNT()/2+1两个位置（譬如图3中中位数为6/2=3和6/2+1=4）。因此筛选出这些位置上所有的数字即可得到中位数。

同样对于上题，一个MySQL实现如下所示：

select
d1.Id, d1.Company, d1.Salary
from(
    select
    e.id, e.company, e.salary,
    if(@prev=e.company, @rankid:=@rankid+1, @rankid:=1) as rankid,
    @prev := company as prev
    from employee as e, (select @prev:='', @rankid:=1) as init
    order by company, salary) as d1
    left join(
        select g.company, count(id)/2 as countnum
        from employee as g group by company
    ) as d2
    on d1.company=d2.company
    where rankid in (countnum, countnum+0.5, countnum+1);

该方法代码略显繁杂，细节略多，但是思路比较自然。

参考

1. ^Leetcode: 员工薪水中位数 https://leetcode-cn.com/problems/median-employee-salary/