题目描述
Numbers 表保存数字的值及其频率。
+----------+-------------+
| Number | Frequency |
+----------+-------------|
| 0 | 7 |
| 1 | 1 |
| 2 | 3 |
| 3 | 1 |
+----------+-------------+
在此表中,数字为 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3,所以中位数是 (0 + 0) / 2 = 0。
+--------+
| median |
+--------|
| 0.0000 |
+--------+
请编写一个查询来查找所有数字的中位数并将结果命名为 median 。
解题思路:
使用sum over(order by ) 对数字个数进行正序和逆序累计,
将从后往前和从前往后的频数相加,两个数都需要大于等于总数一半,再取平均。
select avg(number) median
from
(select number,
sum(frequency) over(order by number) asc_accumu,
sum(frequency) over(order by number desc) desc_accumu
from numbers) t1,
(select sum(frequency) total from numbers) t2
where asc_accumu >= total/2 and desc_accumu >=total/2
来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/find-median-given-frequency-of-numbers
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。