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

建表

create table numbers ( Number int, Frequency int ); insert into numbers values(0, 7); insert into numbers values(1, 1); insert into numbers values(2, 3); insert into numbers values(3, 1);

开窗函数思路：

从后往前和从前往后的频数相加，两个数都需要大于等于总数一半，再取平均

select avg(number) median from( select number, sum(Frequency) over (order by number) a, -- 从前往后 频数相加 sum(Frequency) over (order by number desc) b, -- 从后往前 频数相加 sum(Frequency) over () c -- 总数，开窗了就不用group by了 from Numbers ) n1 where a >= c/2 and b >= c/2

变量做法

select avg(Number) as median from ( select Number, Frequency, @sum as sum1, @sum:=Frequency+@sum as sum2 from Numbers,(select @sum:=0) t order by Number ) t where if ( @sum&1, sum1<=floor(@sum/2) and sum2>floor(@sum/2), sum1<=(@sum/2) and sum2>=(@sum/2) )

此题我觉得逻辑能解决的非要写到sql里就是很麻烦，我估计出题人的本意是可以用存储过程来处理