- 第35天 - 数字频率的中位数
- 建表
- 表结构
- 测试数据
- 思考
- 查询结果
- SQL
- 结合昨日
- 解题一
- 求绝对值的方式
- 解题二
- 使用`floor()` 和 `ceil()`
- 总结
- 关于sql的规范问题
建表 表结构 测试数据我们昨天求的是薪水的中位数,今天再补充一种求中位数的方式,昨天可以通过三种方式,具体可以参考,昨天的链接: 挑战100天不停更hive sql第34天 - 薪水求中位数
先看看表字段:
number :数字
frequency :数字的频率(注意:这里是频率,并不是数字, 所以不能根据这个字段排序)
问题:
求得number的中位数(需要通过frequency 字段展开,
例如：
表的数据
(0, 7)
(1, 1)
(2, 3)
(3, 1)
展开:0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3 共12个数字,那么中位数就是0
我们刚看到这个问题,第一个想到的应该是先用炸裂函数列转行,但是有没有一步到位的办法呢? 今天的sql会有点绕,并不是简单的排序就可以解决问题, 我刚看到的时候也挺蒙的,但是这种思想其实还是很重要的,因为昨天是按照顺序去找,今天是根据sum求和的方式
当我们再扩展下思维时, 其实本道题涉及到的内容还是很多很多的, 比如: 行列转换/累计求值/累计登陆/访问统计/连续登陆等等
后面我也先用炸裂函数重新写一遍, 遇到了一些问题了,也贴出来 ,还有一些sql的复盘和总结,分享出来, 大家参考~
CREATE TABLE t36
(
number bigint,
frequency bigint
);
INSERT INTO t36 (number, frequency)
VALUES (0, 7),
(1, 1),
(2, 3),
(3, 1);
思考
查询结果 SQL
- 首先要拿到总数,这个思路没问题把? 后面可以拿总数除以二,然后对比, 求得中间的频率
- 接下来我们就要开窗了, 用哪个函数呢? 排序肯定不行,那就得用sum(),累加频率字段(按照number排序)这里要计算两个
- 第一个:用frequency 当前的累加减去当前的frequency ,得出的是之前的累加
- 第二个:当前的累加
- 将1中求得总数和2中的累加值join到一起,这里没有连接条件,就是将总数这一列拼接到每一行数据后面,那我们求得的字段是 number -> frequency->frequency去除上次的累加->frequency的累加->总数
- 这个时候我们应该写where了 . 看到这里, 你会有什么想法呢? 应该要怎么对比这个条件呢?
- 条件: 前一次的累加 <= 总数/2 并且 当前累加 >= 总数/2
- 结果可能是一个.也可能是两个,. 我们再使用avg()函数求平均值,这里的avg()用的非常巧
--解题一
SELECt avg(number) median
FROM (
SELECt number
, frequency
--求得前累计值
, sum(frequency) OVER (ORDER BY number) - frequency prev_frequency_sum
--求得当前累计值
, sum(frequency) OVER (ORDER BY number) current_frequency_sum
FROM t36
) t1,
(
--求总数
SELECt sum(frequency) total_frequency_sum
FROM t36
) t2
WHERe
-- 总数除以2求中间数,然后通过前一个累加和后一个累加进行对比
t1.prev_frequency_sum <= (cast(t2.total_frequency_sum AS float) / 2)
AND t1.current_frequency_sum >= (cast(t2.total_frequency_sum AS float) / 2);
--解题2
SELECt AVG(number) median
FROM (
SELECt n1.number
FROM t36 n1
JOIN t36 n2 ON n1.number >= n2.number
GROUP BY n1.number
HAVINg SUM(n2.frequency) >= (SELECt SUM(frequency) FROM t36) / 2
AND SUM(n2.frequency) - AVG(n1.frequency) <= (SELECt SUM(frequency) FROM t36) / 2
) s
结合昨日
解题一
求绝对值的方式
结果其实并不是我们想要的,我们并没有分区,正排序和倒着排序会将原有的顺序打乱
WITH tmp AS ( SELECt number , row_number() OVER ( ORDER BY number) rn , row_number() OVER ( ORDER BY number DESC ) rn_desc FROM t36 LATERAL VIEW explode(split(space(cast(frequency AS int)-1), '')) t AS numbers ) SELECt number FROM tmp WHERe rn = rn_desc OR abs(rn - rn_desc) = 1; ;
这里为什么会乱序呢?我们对比下昨天的sql,并没有出现??
对比昨天的sql
这里算是一个遗留问题吧,后面再好好看看! 也欢迎大家留言哈~
解题二 使用floor() 和 ceil()必须对结果去重
-- 解法二:向下取整的方式
WITH tmp AS
(
SELECt number
, row_number() OVER ( ORDER BY number) rn
, count(*) OVER () cnt
FROM t36 LATERAL VIEW explode(split(space(cast(frequency AS int)-1), '')) t AS numbers
)
SELECt number
FROM tmp
-- 下面两种方式都可以
-- WHERe rn IN (`floor`((cnt + 1) / 2) , `floor`((cnt + 2) / 2)) GROUP BY number;
WHERe rn IN (`floor`((cnt + 1) / 2) , `ceil`((cnt + 1) / 2)) GROUP BY number;
-- 结果我0,0 但是我们想要的结果只有一个? 再加上一个group by
总结
- 虽然使用炸裂函数也可以得出结果, 但是我们先 分 - > 再合 ->再分组, 这样会影响执行效率,所以大家遇到类似的求中位数的方法还是采用第一种方式比较好
- 这里关于求中位数的方式一共总结了四种,如果仔细去思考这个问题,涉及的面还是很广的, 那我们来举一反三一下
- 如果我们把mumber字段换成登陆时间, 那么就会变成求登陆次数,活跃度统计等等,只是本题是汇总拆分而已
- 挑战100天不停更hive sql第27天 -活跃用户统计
- 挑战100天不停更，刷爆 hive sql第26天 - 场景题-访问统计
- 使用炸裂函数, 涉及到行列转换,frequency字段就是累计求值, 连续出现的值,合并拆分等等
- 挑战100天不停更hive sql第30天 -求连续出现的值
- 挑战100天不停更之hive sql第12天-转多行
- 挑战100天不停更之hive sql第1天 - 行列转换
- 挑战100天不停更之hive sql第9天-合并和拆分
- 关于取值 , 取第一个,取最新的等等
- 挑战100天不停更hive sql第19天 -时间序列,求最新值
- 挑战100天不停更之hive sql第11天 - 成对提取数据
- 如果我们把mumber字段换成登陆时间, 那么就会变成求登陆次数,活跃度统计等等,只是本题是汇总拆分而已
- 上面我只贴出来部分的sql,具体大家可以去看专栏里面的详细内容
- 尽量把逗号放在前面,为什么?
- 方便排查,不会遗漏逗号
- 方便注释,可以单行直接注释,不用再改逗号
- 排版看起来更紧密,我用datagrip快捷键ctrl + alt+ L可以快速缩进
- 特别用datagrip进行快速复制一行的时候,只需要Ctrl + D不需要关注逗号，写起来很快
- 使用关键字大写,养成好习惯
- 参考 hive sql 官方conf地址:https://cwiki.apache.org/confluence/display/Hive/LanguageManual
- 使用公用表达式 with as,提高代码可读性和运行效率
- 复盘+思考 > 学习的新的知识点
