LeetCode MySQL 571. 给定数字的频率查询中位数

1. 题目

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 。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/find-median-given-frequency-of-numbers
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

2. 解题

• 求出正序、逆序的前缀频数和

select Number, sum(Frequency) over(order by Number asc) presum
from Numbers

{"headers": ["Number", "presum"], 
"values": [[0, 7], [1, 8], [2, 11], [3, 12]]}

select Number, sum(Frequency) over(order by Number desc) presum
from Numbers

{"headers": ["Number", "presum"], 
"values": [[3, 1], [2, 4], [1, 5], [0, 12]]}

• 选出正反序中间位置的数（前缀频数大于等于一半的第一个满足的数），最后求平均

# Write your MySQL query statement below
select avg(Number) median
from
(
    (
        select Number
        from
        (
            select Number, sum(Frequency) over(order by Number asc) presum
            from Numbers
        ) t1
        where presum >= ceil((select sum(Frequency) from Numbers)/2)
        order by Number
        limit 1
    )
    union all
    (
        select Number
        from
        (
            select Number, sum(Frequency) over(order by Number desc) presum
            from Numbers
        ) t2
        where presum >= ceil((select sum(Frequency) from Numbers)/2)
        order by Number desc
        limit 1
    )
) tmp

评论区简洁解法

select avg(Number) median
from
(
    select Number,
           sum(Frequency) over (order by Number asc) c1,
           sum(Frequency) over (order by Number desc) c2,
           sum(Frequency) over () cnt
    from Numbers
) t
where c1 >= cnt/2 and c2 >= cnt/2

---

我的CSDN博客地址 https://michael.blog.csdn.net/

长按或扫码关注我的公众号（Michael阿明），一起加油、一起学习进步！