571. 给定数字的频率查询中位数
思路
数字个数可由n = SUM(frequency)求得，
先创建两列。asc_frequency:记录小于等于当前number的数字个数；desc_frequency:记录大于等于当前number的数字个数
这么创建的原因是，当我们找到asc_frequency中对应>=n/2，且在desc_frequency中>=n/2的对应的number。
如果有两个，则取平均AVG(number)为median
步骤
- 建立临时表t
2.选取asc_frequency中对应>=n/2，且在desc_frequency中>=n/2的对应的number
WHERE t.asc_frequency >= (SELECT SUM(Frequency) FROM Numbers)/2
AND t.desc_frequency >= (SELECT SUM(Frequency) FROM Numbers)/2
- 如果有多个数，返回Number的平均
SELECT AVG(Number) AS median
FROM () t
WHERE ...
代码
SELECT AVG(Number) AS median
FROM (SELECT n1.Number, n1.Frequency,
(SELECT SUM(Frequency) FROM numbers n2 WHERE n2.Number<=n1.Number) AS asc_frequency,
(SELECT SUM(Frequency) FROM numbers n3 WHERE n3.Number>=n1.Number) AS desc_frequency
FROM numbers n1) t
WHERE t.asc_frequency >= (SELECT SUM(Frequency) FROM Numbers)/2
AND t.desc_frequency >= (SELECT SUM(Frequency) FROM Numbers)/2 ;