1 题目描述
Numbers 表:
+-------------+------+
| Column Name | Type |
+-------------+------+
| num | int |
| frequency | int |
+-------------+------+
num 是这张表的主键 (具有唯一值的列)
这张表的每一行表示某个数字在该数据库中的出现频率
中位数是将数据样本中半数较高值和半数较低值分隔开的值
编写解决方案, 解压 Numbers 表, 报告数据库中所有数字的中位数. 结果四舍五入至一位小数
2 测试用例
输入:
Numbers 表:
+-----+-----------+
| num | frequency |
+-----+-----------+
| 0 | 7 |
| 1 | 1 |
| 2 | 3 |
| 3 | 1 |
+-----+-----------+
输出:
+--------+
| median |
+--------+
| 0.0 |
+--------+
解释:
如果解压这个 Numbers 表, 可以得到 [0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3], 所以中位数是 (0 + 0) / 2 = 0
3 解题思路
| 位置 1 | 位置 2 | 位置 3 | 位置 4 | 位置 5 | 位置 6 | 位置 7 | 位置 8 | 位置 9 | 位置 10 | 位置 11 | 位置 12 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 2 | 2 | 2 | 3 |
总数为偶数场景: 中位数的位置有两个, 假设总数为 n, 计算中位数位置的公式为 n/2 和 n/2 + 1, 上面的数据总数为 12, 此测试用例的中位数计算出来是 6, 7, 使用升序和降序分别统计各个数字最后出现的位置, 交叉锁定中位数的位置区间
| 位置 1 | 位置 2 | 位置 3 | 位置 4 | 位置 5 | 位置 6 | 位置 7 | 位置 8 | 位置 9 | 位置 10 | 位置 11 |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 0 | 0 | 0 | 0 | 0 | 1 | 2 | 2 | 2 | 3 |
总数为奇数场景: 中位数的位置是一个, 假设总数为 n, 计算中位数位置的公式为 (n+1)/2, 上面的数据总数为 11, 此测试用例的中位数计算出来是 6, 使用升序和降序分别统计各个数字最后出现的位置, 交叉锁定中位数的位置区间
- 按照升序排序依次累积统计数字的总数, 按照倒序排序依次累计统计数字的总数, 所有数字的总数据量
select num,
sum(frequency) over (order by num desc) desc_frequency,
sum(frequency) over (order by num asc) asc_frequency,
sum(frequency) over () total_frequency
from Numbers
查询结果
+---+--------------+-------------+---------------+
|num|desc_frequency|asc_frequency|total_frequency|
+---+--------------+-------------+---------------+
|0 |12 |7 |12 |
|1 |5 |8 |12 |
|2 |4 |11 |12 |
|3 |1 |12 |12 |
+---+--------------+-------------+---------------+
- 通过
desc_frequency >= total_frequency / 2 and asc_frequency >= total_frequency / 2, 通过升序统计和降序统计的交集来锁定中位数的位置
select *
from (select num,
sum(frequency) over (order by num desc) desc_frequency,
sum(frequency) over (order by num asc) asc_frequency,
sum(frequency) over () total_frequency
from Numbers) as temp
where desc_frequency >= total_frequency / 2
and asc_frequency >= total_frequency / 2;
查询结果
+---+--------------+-------------+---------------+
|num|desc_frequency|asc_frequency|total_frequency|
+---+--------------+-------------+---------------+
|0 |12 |7 |12 |
+---+--------------+-------------+---------------+
测试用例的数据, 总共有 12 个数字, 中位数的位置是 6, 7, 而这两个位置的数字是 0
如果测试数据换成下面
+---+---------+
|num|frequency|
+---+---------+
|0 |6 |
|1 |2 |
|2 |3 |
|3 |1 |
+---+---------+
查询结果
+---+--------------+-------------+---------------+
|num|desc_frequency|asc_frequency|total_frequency|
+---+--------------+-------------+---------------+
|0 |12 |6 |12 |
|1 |6 |8 |12 |
+---+--------------+-------------+---------------+
总数据的量为 12, 中位数的位置为 6,7 这两个位置的数字分别是 0 和 1
3. 计算中位数位置的数字的平均值, 结果四舍五入保留一位小数
select round(avg(num), 1) as median
from (select num,
sum(frequency) over (order by num desc) desc_frequency,
sum(frequency) over (order by num asc) asc_frequency,
sum(frequency) over () total_frequency
from Numbers) as temp
where desc_frequency >= total_frequency / 2
and asc_frequency >= total_frequency / 2;
两组测试数据的结果分别是
+------+
|median|
+------+
|0.0 |
+------+
+------+
|median|
+------+
|0.5 |
+------+
