建表
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里就是很麻煩，我估計出題人的本意是可以用存盤程序來處理
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/162625.html
標籤：其他
上一篇：資料庫-刷題
下一篇：Mybatis-Plus學習筆記