Create table If Not Exists Numbers (num int, frequency int)tablespace etc; Truncate table Numbers; insert into Numbers (num, frequency) values ('0', '7'); insert into Numbers (num, frequency) values ('1', '1'); insert into Numbers (num, frequency) values ('2', '3'); insert into Numbers (num, frequency) values ('3', '1');
.
中位数 是将数据样本中半数较高值和半数较低值分隔开的值。
编写一个 SQL 查询,解压 Numbers 表,报告数据库中所有数字的 中位数 。结果四舍五入至 一位小数 。
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