挑战100天不停更第35天 - 数字频率的中位数(+复盘)

程序员的三板斧

已于 2022-05-16 13:09:24 修改

阅读量734

点赞数 1

分类专栏： 🌿挑战100天不停更，刷爆 hive sql🧲 文章标签： hive big data hadoop

于 2022-05-16 10:42:51 首次发布

本文链接：https://blog.csdn.net/weixin_38221481/article/details/124794879

版权

🌿挑战100天不停更，刷爆 hive sql🧲 专栏收录该内容

35 篇文章 70 订阅

订阅专栏

文章目录

第35天 - 数字频率的中位数

我们昨天求的是薪水的中位数,今天再补充一种求中位数的方式,昨天可以通过三种方式,具体可以参考,昨天的链接: 挑战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);

思考

首先要拿到总数,这个思路没问题把? 后面可以拿总数除以二,然后对比, 求得中间的频率
接下来我们就要开窗了, 用哪个函数呢? 排序肯定不行,那就得用sum(),累加频率字段(按照number排序)这里要计算两个
第一个:用frequency 当前的累加减去当前的frequency ,得出的是之前的累加
第二个:当前的累加

将1中求得总数和2中的累加值join到一起,这里没有连接条件,就是将总数这一列拼接到每一行数据后面,那我们求得的字段是 number -> frequency->frequency去除上次的累加->frequency的累加->总数
这个时候我们应该写where了 . 看到这里, 你会有什么想法呢? 应该要怎么对比这个条件呢?
条件: 前一次的累加 <= 总数/2 并且 当前累加 >= 总数/2

结果可能是一个.也可能是两个,. 我们再使用avg()函数求平均值,这里的avg()用的非常巧

查询结果

SQL

--解题一
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

总结

虽然使用炸裂函数也可以得出结果, 但是我们先分 - > 再合 ->再分组, 这样会影响执行效率,所以大家遇到类似的求中位数的方法还是采用第一种方式比较好
这里关于求中位数的方式一共总结了四种,如果仔细去思考这个问题,涉及的面还是很广的, 那我们来举一反三一下
1. 如果我们把mumber字段换成登陆时间, 那么就会变成求登陆次数,活跃度统计等等,只是本题是汇总拆分而已
  1. 挑战100天不停更hive sql第27天 -活跃用户统计
  2. 挑战100天不停更，刷爆 hive sql第26天 - 场景题-访问统计
2. 使用炸裂函数, 涉及到行列转换,frequency字段就是累计求值, 连续出现的值,合并拆分等等
3. 关于取值 , 取第一个,取最新的等等
  1. 挑战100天不停更hive sql第19天 -时间序列,求最新值
  2. 挑战100天不停更之hive sql第11天 - 成对提取数据
上面我只贴出来部分的sql,具体大家可以去看专栏里面的详细内容

关于sql的规范问题

尽量把逗号放在前面,为什么?
方便排查,不会遗漏逗号
方便注释,可以单行直接注释,不用再改逗号
排版看起来更紧密,我用datagrip快捷键ctrl + alt+ L可以快速缩进
特别用datagrip进行快速复制一行的时候,只需要Ctrl + D不需要关注逗号，写起来很快

使用关键字大写,养成好习惯
参考 hive sql 官方conf地址:https://cwiki.apache.org/confluence/display/Hive/LanguageManual
使用公用表达式 with as,提高代码可读性和运行效率
复盘+思考 > 学习的新的知识点