🌿挑战100天不停更，刷爆 hive sql🧲
详情请点击🔗我的专栏🖲,共同学习,一起进步~
文章目录
*
– 🌿挑战100天不停更，刷爆 hive sql🧲
– NUM: 第24天 -累计去重
–
+
* 🧨刷题~~🧨
– 🎈表结构
– 🎉建表
– 👓问题:求当天活跃度,和当月的累计活跃度??
–
+ ✨先看执行结果
+ 解法:
+
* 🎨思考
* 🧨SQL
+ 总结下hive sql的去重和优化
+
* distinct
* group by
* row_number() over()
+ 关于sql的规范问题
NUM: 第24天 -累计去重
去重? 我们一般会用到哪几个函数呢?
dinstinctgroup byrow_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去重即可,那么当月累计的呢?
- 先根据时间去重,求出当月的每一天
- 根据时间和用户分组,对重复登陆的进行去重
- 通过
left join做笛卡尔积,并根据t1.date_id >= t2.date_id求前几天的数据,再根据时间,用户分组求出累计活跃的用户 - 分组去重,求当前活跃度
left join根据时间id求结果
🧨SQL
select t4.date_id
, act_cnt_day
, act_cnt_month
from (
select date_id
, count(user_id) as act_cnt_month
from (
select t1.date_id
, t2.user_id
from (
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
(
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
(
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不需要关注逗号，写起来很快
Original: https://blog.csdn.net/weixin_38221481/article/details/124350459
Author: 程序员的三板斧
Title: 挑战100天不停更hive sql第24天 -累计去重
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/817877/
转载文章受原作者版权保护。转载请注明原作者出处！