571. 给定数字的频率查询中位数

SQL架构

Numbers 表：

+-------------+------+
| Column Name | Type |
+-------------+------+
| num         | int  |
| frequency   | int  |
+-------------+------+
num 是这张表的主键。这张表的每一行表示某个数字在该数据库中的出现频率。

中位数 是将数据样本中半数较高值和半数较低值分隔开的值。

编写一个 SQL 查询，解压 Numbers 表，报告数据库中所有数字的 中位数 。结果四舍五入至 一位小数 。

查询结果如下例所示。

示例：

输入： 
Numbers 表：
+-----+-----------+
| num | frequency |
+-----+-----------+
| 0   | 7         |
| 1   | 1         |
| 2   | 3         |
| 3   | 1         |
+-----+-----------+
输出：
+--------+
| median |
+--------+
| 0.0    |
+--------+
解释：
如果解压这个 Numbers 表，可以得到 [0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3] ，所以中位数是 (0 + 0) / 2 = 0 。

select
round((b1.b1n+ifnull(b2.b2n,0))/2,1) median
from
(select
s2.n1n b1n
from
(select
-- sum(frequency)
if(mod(sum(frequency),2)=0,round(sum(frequency)/2,0),round((sum(frequency)+1)/2)) as s11,if(mod(sum(frequency),2)=0,round(sum(frequency)/2,0)+1,null) as s12
 from
Numbers) as s1

,
(select
n1.num n1n,sum(n2.frequency)-(n1.frequency-1) n1s,sum(n2.frequency) n1ss
from
Numbers as n1,Numbers as n2
where
n1.num>=n2.num
group by
n1.num) as s2
where
 s1.s11>=s2.n1s and s1.s11<=s2.n1ss ) as b1
,
(
select
s2.n1n b2n
from
(select
-- sum(frequency)
if(mod(sum(frequency),2)=0,round(sum(frequency)/2,0),round((sum(frequency)+1)/2)) as s11,if(mod(sum(frequency),2)=0,round(sum(frequency)/2,0)+1,round((sum(frequency)+1)/2)) as s12
 from
Numbers) as s1

,
(select
n1.num n1n,sum(n2.frequency)-(n1.frequency-1) n1s,sum(n2.frequency) n1ss
from
Numbers as n1,Numbers as n2
where
n1.num>=n2.num
group by
n1.num) as s2
where
 s1.s12>=s2.n1s and s1.s12<=s2.n1ss ) as b2

法二:

select 
    round(avg(num),1) as median 
from
(select
    a.*,
    sum(frequency) over(order by num) as rnk1,
    sum(frequency) over(order by num desc) as rnk2,
    sum(frequency) over() as s
from Numbers a) tmp
where rnk1>=s/2 
and rnk2>=s/2

法三:

select round(avg(num),1) as median
from (
	select  n1.num 
    from numbers n1 join numbers n2 
    group by n1.num
	 having sum(if(n1.num >= n2.num, n2.frequency, 0)) >= sum(n2.frequency) / 2
	 and sum(if(n1.num <= n2.num, n2.frequency, 0)) >= sum(n2.frequency) / 2
 ) as temp;