紫金建设公司官网_成都疫情最严重的地区_电商入门基础知识_各类资源关键词

一、力扣链接

LeetCode_571

二、题目描述

Numbers 表：

+-------------+------+
| Column Name | Type |
+-------------+------+
| num         | int  |
| frequency   | int  |
+-------------+------+
num 是这张表的主键(具有唯一值的列)。
这张表的每一行表示某个数字在该数据库中的出现频率。

中位数 是将数据样本中半数较高值和半数较低值分隔开的值。

编写解决方案，解压 Numbers 表，报告数据库中所有数字的 中位数 。结果四舍五入至 一位小数 。

三、目标拆解

四、建表语句

Create table If Not Exists Numbers (num int, frequency int)
Truncate table Numbers
insert into Numbers (num, frequency) values ('0', '7')
insert into Numbers (num, frequency) values ('1', '1')
insert into Numbers (num, frequency) values ('2', '3')
insert into Numbers (num, frequency) values ('3', '1')

五、过程分析

1、为求中位数做准备，按num排序，找出中间的频率值对应的num

2、num个数为偶数或奇数情况下分析中位数的取值

六、代码实现

with t1 as(
select num, frequency,sum(frequency) over() total_cnt,sum(frequency) over(order by num) acc
from numbers
order by num
)
# select * from t1;
,t2 as(selectt1.*,total_cnt%2 _mod,total_cnt/2 even1,total_cnt/2 + 1 even2,(total_cnt + 1)/2 odd,lag(acc) over(order by acc) lag_acc,lead(acc) over(order by acc) lead_accfrom t1
)
-- 分析重点
select round(avg(casewhen _mod = 0 and lag_acc is null and even1 <= acc then num  -- 频率累计值前面的值为空when _mod = 0 and lag_acc is null and even2 <= acc then num  -- 需要<=当前频率累计值when _mod = 1 and lag_acc is null and odd <= acc then numwhen _mod = 0 and lead_acc is null and even1 > lag_acc then num  -- 频率累计值后面的值为空when _mod = 0 and lead_acc is null and even2 > lag_acc then numwhen _mod = 1 and lead_acc is null and odd > lag_acc then numwhen _mod = 0 and (even1 > lag_acc and even1 <= acc) then numwhen _mod = 0 and (even2 > lag_acc and even2 <= acc) then numwhen _mod = 1 and (odd > lag_acc and odd <= acc) then num end), 1)  median
from t2;

七、结果验证

八、小结

1、CTE表达式 + 移动窗口 + 场景分析 + 中位数算法

2、分析多种场景下的算法