给定数字的频率查询中位数
题目来源:Leetcode
方法一:
解题思路:中位数顺序 = (总个数+1)/2
解答:
SELECT AVG(number) median
FROM (SELECT a.Number, a.Frequency, SUM(b.frequency) cumttl
FROM Numbers a,Numbers b
WHERE a.Number >= b.Number
GROUP BY a.Number
ORDER BY a.Number
) c
WHERE cumttl = FLOOR((SELECT (SUM(frequency)+1)/2 num FROM numbers))
OR cumttl = CEILING((SELECT (SUM(frequency)+1)/2 num FROM numbers))
方法二:
解题思路:找出每个number开始和结束的位置,再确定中位数位于哪里 (思路来自leetcode评论区)
① 使用自定义变量,确定开始和结束位置
SELECT number, @a +1 as 'lower',
(@a:= @a+frequency) as 'upper',