SQL 计算中位数
笔者在 HackerRank 上的 SQL 编程挑战看到这题，这题有 96% 的提交成功率。实际上，使用 SQL 求中位数远远没那么简单。
问题描述
我们先来看关于“中位数”的解释：
❝中位数（Median）又称中值，统计学中的专有名词，是按顺序排列的一组数据中居于中间位置的数，代表一个样本、种群或概率分布中的一个数值，其可将数值集合划分为相等的上下两部分。对于有限的数集，可以通过把所有观察值高低排序后找出正中间的一个作为中位数。如果观察值有偶数个，通常取最中间的两个数值的平均数作为中位数。 ❞
再来看几个具体的例子：
- 对于 “1,2,3,4,5” ，总共有 5 个数，居中的数是 3 ，因此这组数据的中位数是 3 。
- 对于“1,2,3,4,5,6”，共有 6 个数，居中的是 3 和 4，因此这组数的中位数是 3 和 4 的平均数 3.5 。
- 对于“3,3,3,3,100,100,100”,总共有 7 个数，居中的是 3，因此 3 是这组数据的中位数。
解决方案
解决方案主要有两种，第一种方案是对数据按大小排序后找到居中的值，再求值的平均数；第二种解决方案计算出每个数与其它数的相对距离（两数相减，结果为正则作 1，结果为负作 0，相等是 0），再对位移的结果加和，通常得到最小的加和结果的数就在居中的位置。
先来看比较简单的解决方案，也就是第一种。具体的做法是：
- 对给定的一组数据按从小到大排序；
- 对排序后的数据编号，序号从 1 开始；
- 假设这组数据的总数是 N，若 N 是奇数，则居中的数的序号是 + 1；若 N 是偶数，则居中的数的序号是 N/2 和 + 1 。
注： 表示对 N/2 的结果向下取整。
对应的 SQL 实现：
# 准备数据
WITH t AS
(SELECT 3 AS num
UNION ALL
SELECT 6
UNION ALL
SELECT 3
UNION ALL
SELECT 2
UNION ALL
SELECT 5
UNION ALL
SELECT 7),
t1 AS
(SELECT
num,
row_number () over (
ORDER BY num) AS rn,
COUNT(*) over () AS total
FROM
t
ORDER BY num)
SELECT
AVG(num)
FROM
t1
WHERE rn IN (
FLOOR(total / 2) + 1,
IF(
total % 2 = 0,
FLOOR(total / 2),
FLOOR(total / 2) + 1
)
)只需要注意，在生成编号前先对原数据做排序，不然结果很可能就是错的。
我们再来看第二种解决方案。
如果事先没有对数据做排序处理，怎么知道一个数是否处于在一堆数的中间位置呢？
在数据没有出现重复的情况下，依次从这组数中取出一个数，和剩下的数做比较，如果该数大于要比较的数，则计为 1，反之为 0，再把比较的结果加和（把这个结果称作“margin”）。有多少个数，就有多少个加和的结果，加和的结果最小的数就是居中的数。
比如“1,2,3,5,6,7”这组数据，计算 margin，结果如下：
num margin
------ --------
1 5
2 3
3 1
5 1
6 3
7 5 3 和 5 对应的加和结果最小，因此它们是居中的数。
如果数据有重复，就不能只使用上面的方法处理，还得加一些限制条件。这个限制条件就是统计该数与原数据相等的数的个数（统计的结果称作“equal”），只选出相等的个数大于或等于加和结果的数。
比如“3,3,3,5,7,7”这组数据，结算 equal 和 margin 的值，得到：
num equal margin
------ ------ --------
3 3 3
5 1 1
7 2 4 最终实现的 SQL 如下：
WITH t AS
(SELECT 3 AS num
UNION ALL
SELECT 6
UNION ALL
SELECT 3
UNION ALL
SELECT 2
UNION ALL
SELECT 5
UNION ALL
SELECT 7),
t1 AS
(SELECT
a.num,
SUM(IF(a.num = b.num, 1, 0)) AS equal,
ABS(SUM(SIGN(a.num - b.num))) AS margin
FROM
t a
INNER JOIN t b
ON 1 = 1
GROUP BY a.num)
SELECT
AVG(num)
FROM
t1
WHERE equal >= margin由于我们对数据做了笛卡尔积的操作，因此实际上计算出来的 equal 和 margin 的值和演示时的值有差别。