這個(gè)確實(shí)不太好做 之后多看看

題目
The Numbers table keeps the value of number and its frequency.

+----------+-------------+
|  Number  |  Frequency  |
+----------+-------------|
|  0       |  7          |
|  1       |  1          |
|  2       |  3          |
|  3       |  1          |
+----------+-------------+

In this table, the numbers are 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3, so the median is (0 + 0) / 2 = 0.
Write a query to find the median of all numbers and name the result as median.

審題
之前做過一個(gè)分組中位數(shù)的題
這個(gè)是給定一個(gè)序列各元素的頻率再找出中位數(shù)

生成數(shù)據(jù)

DROP  TABLE Numbers;

CREATE TABLE Numbers(
Number INT,
Frequency INT);

INSERT INTO Numbers VALUE(0,7),(1,1),(2,3),(3,1);

解答
給出累計(jì)頻率

SELECT N.`Number`, N.Frequency ,@Interval:=@Interval + N.Frequency AS Inte
FROM Numbers AS N, (SELECT @Interval:=0) AS init
ORDER BY N.Number;

給出每個(gè)數(shù)的排名的區(qū)間 太復(fù)雜了 不考慮這個(gè)了

SELECT tmp1.Number, tmp1.Linte, tmp2.RInte
FROM (SELECT N.`Number`, @Interval:=@Interval + N.Frequency AS LInte, @num:=@num+1 AS num
FROM Numbers AS N, (SELECT @Interval:=0, @num:=0) AS init
ORDER BY N.Number) tmp1
LEFT JOIN (SELECT N.`Number`, @Interval1:=@Interval1 + N.Frequency AS RInte, @num1:=@num1+1 AS num1
FROM Numbers AS N, (SELECT @Interval1:=0, @num1:=0) AS init
ORDER BY N.Number) tmp2
ON tmp1.num +1 = tmp2.num1;

又涉及到奇偶性的問題 統(tǒng)一成一個(gè)問題選取 如果累計(jì)頻率在[總頻率數(shù)/2, 總頻率數(shù)/2 + 當(dāng)前頻率數(shù)]即可

AccFreq范圍在[SumFreq / 2, SumFreq / 2 + Frequency]的Number均值即為答案。

先把總頻率加入表中

SELECT * 
FROM (SELECT N.`Number`, N.Frequency ,@Interval:=@Interval + N.Frequency AS Inte
FROM Numbers AS N, (SELECT @Interval:=0) AS init
ORDER BY N.Number) AS t1, (SELECT SUM(N.`Frequency`) AS all_cnt
FROM Numbers AS N) AS t2

按如上方法選擇

SELECT *, t2.all_cnt/2 AS Left_int,  t2.all_cnt/2 + t1.Frequency AS Right_int
FROM (SELECT N.`Number`, N.Frequency ,@Interval:=@Interval + N.Frequency AS AccFreq
FROM Numbers AS N, (SELECT @Interval:=0) AS init
ORDER BY N.Number) AS t1, (SELECT SUM(N.`Frequency`) AS all_cnt
FROM Numbers AS N) AS t2
WHERE t1.AccFreq BETWEEN t2.all_cnt/2 AND t2.all_cnt/2 + t1.Frequency;

偶數(shù)情況可能出現(xiàn)兩個(gè)不同的數(shù)在區(qū)間邊界 所以需要取均值

SELECT AVG(t1.Number) AS 'median'
FROM (SELECT N.`Number`, N.Frequency ,@Interval:=@Interval + N.Frequency AS AccFreq
FROM Numbers AS N, (SELECT @Interval:=0) AS init
ORDER BY N.Number) AS t1, (SELECT SUM(N.`Frequency`) AS all_cnt
FROM Numbers AS N) AS t2
WHERE t1.AccFreq BETWEEN t2.all_cnt/2 AND t2.all_cnt/2 + t1.Frequency;