571.(Hard)给定数字的频率查询中位数

571.(Hard)给定数字的频率查询中位数

题目描述

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 。

题目分析

选出正中间的数，通过这种判别方式：
正序asc和倒序desc都大于等于总数的一半： asc >= total/2 and desc >= total/2

代码实现

select
    avg(number) median
from
    (
    select
        numbers
        ,sum(frequency) over(order by number desc) desc_amount
        ,sum(frequency) over(order by number asc) asc_amount
        ,sum(frequency) over(order by 1) total_num
    from
        numbers
    )aa
where
    asc_amount >= total_num/2 
    and desc_amount >= total_num/2