挑战100天不停更hive sql第24天 -累计去重

🌿挑战100天不停更，刷爆 hive sql🧲

详情请点击🔗我的专栏🖲,共同学习,一起进步~

NUM: 第24天 -累计去重

去重? 我们一般会用到哪几个函数呢?
dinstinct ``group by ``row_number()等等…
那具体有什么区别呢?
在实际的场景中又怎么选择呢??
在数据量特别大的时候,又该如何调优呢 ?
先刷题 , sql后面会细细来总结~

🧨刷题~~🧨

🎈表结构

_1, __t24_为事件流水表，客户当天有一条记录则视为当天活跃

🎉建表

create table t24
(
    time_id string,
    user_id string
);

insert into t24 (time_id, user_id)
values ('2018-01-01 10:00:00', '001'),
       ('2018-01-01 11:03:00', '002'),
       ('2018-01-01 13:18:00', '001'),
       ('2018-01-02 08:34:00', '004'),
       ('2018-01-02 10:08:00', '002'),
       ('2018-01-02 10:40:00', '003'),
       ('2018-01-02 14:21:00', '002'),
       ('2018-01-02 15:39:00', '004'),
       ('2018-01-03 08:34:00', '005'),
       ('2018-01-03 10:08:00', '003'),
       ('2018-01-03 10:40:00', '001'),
       ('2018-01-03 14:21:00', '005');

👓问题:求当天活跃度,和当月的累计活跃度??

✨先看执行结果

解法:

🎨思考

当天的活跃度比较简单, 只需要group by, 就再对登陆的user_id去重即可,那么当月累计的呢?

1. 先根据时间去重,求出当月的每一天
2. 根据时间和用户分组,对重复登陆的进行去重
3. 通过left join做笛卡尔积,并根据t1.date_id >= t2.date_id求前几天的数据,再根据时间,用户分组求出累计活跃的用户
4. 分组去重,求当前活跃度
5. left join 根据时间id 求结果
   

🧨SQL

-- 5.left join 根据时间id 求结果
select t4.date_id
     , act_cnt_day
     , act_cnt_month
from (
         select date_id
              , count(user_id) as act_cnt_month
         from (
                  -- 3,通过left join做笛卡尔积,并根据t1.date_id >= t2.date_id求前几天的数据,再根据时间,用户分组求出累计活跃的用户
                  select t1.date_id
                       , t2.user_id
                  from ( -- 1,先根据时间去重,求出当月的每一天
                           select from_unixtime(unix_timestamp(time_id), 'yyyy-MM-dd') as date_id
                           from t24
                           group by from_unixtime(unix_timestamp(time_id), 'yyyy-MM-dd')
                       ) t1
                           left join
                       ( --2,根据时间和用户分组,对重复登陆的进行去重
                           select from_unixtime(unix_timestamp(time_id), 'yyyy-MM-dd') date_id
                                , user_id
                           from t24
                           group by from_unixtime(unix_timestamp(time_id), 'yyyy-MM-dd'), user_id
                       ) t2
                  where t1.date_id >= t2.date_id
                  group by t1.date_id, user_id
              ) t3
         group by date_id
     ) t4
         left join
        --4,分组去重,求当然活跃度,这里使用distinct进行去重,也可以用group by
     (
         select from_unixtime(unix_timestamp(time_id), 'yyyy-MM-dd') as date_id
              , count(distinct user_id)                              as act_cnt_day
         from t24
         group by from_unixtime(unix_timestamp(time_id), 'yyyy-MM-dd')
     ) t5
     on t4.date_id = t5.date_id
order by date_id;

总结下hive sql的去重和优化

distinct

• ditinct方法适合于单字段去重，但是单字段去重还要保留其他字段数据，就无法完成了；
• distinct对NULL是不进行过滤的，即返回的结果中是包含NULL值的；
• distinct应用到多个字段的时候，distinct必须放在开头，其应用的范围是其后面的所有字段
  

group by

• 对group by 后面所有字段去重，并不能只对一列去重；
• sql语句写成只对一列去重，保留其他字段，在hive上会报错
  

row_number() over()

• 给分组后的每个id加一列按某一字段倒叙排序的rank值，取rank=1

简单的去重推荐使用group by操作,distinct操作的所有数据都在reduce里面
推荐阅读: hive的五种去重方式

关于这个问题,我翻了csdn的好多文章,没找到深入讲解的内容,都比较浮于表面,姑且先这么记吧,后面有时间再来整理, 这里其实还有个问题, 在翻博客的时候发现很多的博客可能都是随手记的,当点进去发现并不是自己想要的答案的时候,感觉还是很苦恼的,又浪费时间,所以还是严格要求下自己,尽量把知识点都吃透,并整理出来,作为自己知识的沉淀吧~ 排版清晰,思路清晰, 也希望看到我博客人能最高效的理解~~

关于sql的规范问题

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