解析
中位数就是将所有数字按照升序或者降序排列,然后取最中间的数字
- 数字个数是奇数的话,那么中位数会在这个序列中
- 数字个数是偶数的话,那么中位数是最中间的两个数的平均值
步骤:
sum(frequency) over() total_frequency
计算出所有数字的个数,这里使用窗口函数over()
就不需要再后面使用group by
- 计算总数还可以用
select sum(frequency) as total_frequency from numbers
- 计算总数还可以用
sum(frequency) over(order by num desc) desc_frequency
使用窗口函数over(order by num desc)
按照num
降序计算出当前数字和之前数字出现的次数
select num, sum(frequency) over(order by num desc) desc_frequency
from numbers;
num | desc_frequency |
---|---|
3 | 1 |
2 | 4 |
1 | 5 |
0 | 12 |
sum(frequency) over(order by num asc) asc_frequency
使用窗口函数over(order by num asc)
按照num
升序计算出当前数字和之前数字出现的次数
select num, sum(frequency) over(order by num asc) asc_frequency
from numbers;
num | asc_frequency |
---|---|
0 | 7 |
1 | 8 |
2 | 11 |
3 | 12 |
- 将查询出来的
num
,desc_frequency
,asc_frequency
,total_frequency
作为临时表temp
- 查询临时表 t
emp
, 筛选条件是desc_frequency >= total_frequency / 2 and asc_frequency >= total_frequency / 2
,desc_frequency
的一半就是中位数
通过筛选条件查询出来的num
就是中位数,使用avg
对其求平均数,因为如果是偶数个的话,查出来的中位数是两个。
select round(avg(num),1) as median from (
select
num,
sum(frequency) over(order by num desc) desc_frequency,
sum(frequency) over(order by num asc) asc_frequency,
sum(frequency) over() total_frequency
from numbers
) as temp
where desc_frequency >= total_frequency / 2
and asc_frequency >= total_frequency / 2;