问题

Numbers 表保存数字的值及其频率。

+----------+-------------+
| Number | Frequency |
+----------+-------------|
| 0 | 7 |
| 1 | 1 |
| 2 | 3 |
| 3 | 1 |
+----------+-------------+
在此表中，数字为 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3，所以中位数是 (0 + 0) / 2 = 0。

+--------+
| median |
+--------|
| 0.0000 |
+--------+
请编写一个查询来查找所有数字的中位数并将结果命名为 median 。

解决办法

第一种解决办法 利用正序和倒叙

# Write your MySQL query statement below

select
    avg(Number) as median
from
    (select 
        Number,
        SUM(Frequency) OVER(ORDER BY Number) AS DN,
        SUM(Frequency) OVER(ORDER BY Number desc) AS UP,
        SUM(Frequency) OVER() AS TOTAL    
    from
        Numbers) A
where 
    DN >= TOTAL/2  AND  UP >= TOTAL/2  

image.png

第一个就是中位数的定义百度百科：一个数集中最多有一半的数值小于中位数，也最多有一半的数值大于中位数。

所以用倒叙和正序分别排序，当它们都大于等于 TOTAL/2 就可以了。

值得一提的是 select 5/2 得出的值是 2.50000

第二种方法 只使用一种顺序

# Write your MySQL query statement below

select
    avg(Number) as median
from
    (select 
        Number,
        SUM(Frequency) OVER(ORDER BY Number)-Frequency AS DN,
        SUM(Frequency) OVER(ORDER BY Number) AS UP,
        SUM(Frequency) OVER() AS TOTAL    
    from
        Numbers) A
where 
    DN <= TOTAL/2  AND  UP >= TOTAL/2

image.png

DN 先前的数字，UP是到现在的数字

第三种方法 利用变量

select
    avg(Number) median
from
    (select 
        Number,
        @num as bef,
        @num := @num+Frequency as aft
    from
        (select @num:=0 )B ,Numbers
    order by Number) A
 where
    bef <= @num/2 and aft >= @num/2

和前面那种方式差不多，但是是使用变量的。

image.png