Hive的数据去重Group by Having count()等方法

大数据 专栏收录该内容
28 篇文章 0 订阅

查询出当前表中,电话号码出现数次数为两次的数据!并且删除掉重复多余的数据,只留下一条数据!having count and group by

select mobile,count(1)cnt from rbc_audience_users group by mobile having count(mobile) >1

查询出rbc_audience_users表中 mobile大于一条的,并且查询出最小的uid 嵌套一个子查询,将查询的结果作为一个子表,用嵌套查询uid 查询出uid ,然后用当前表去 uid IN (),即可查询出来电话号码重的其中一个!

可以查询出当前需要去重的UID 和电话号码! 仅有一份

SELECT uid, mobile
FROM rbc_audience_users
WHERE mobile IN (
    SELECT mobile
    FROM (
        SELECT mobile, COUNT(1) AS cnt
        FROM rbc_audience_users
        WHERE mobile != ''
        GROUP BY mobile
        HAVING COUNT(mobile) > 1
    ) a
);

查询出重复的所有的电话号码的用户ID,和Mobile

SELECT uid,mobile
FROM rbc_audience_users
WHERE mobile IN (
    SELECT mobile
    FROM (
        SELECT mobile, COUNT(1) AS cnt
        FROM rbc_audience_users
        WHERE mobile != ''
            AND mobile != 'null'
        GROUP BY mobile
        HAVING COUNT(mobile) > 1
    ) a
);

查询出所有的电话号码出现次数为两次数据,并且查询mobile 不为null  和空串的电话号码!

将上述两个表的结果数据数据插入到分别的两张表,然后使用 left join where is null(取差集的方式) 的方式查询出来重复数据

将ID全部查询出来,然后传入Es对当前用户ID数据进行删除! 

即可达到数据删除冗余数据的目的! 

全部重复数据,

微信
insert into rbc_audience_user_not_distinct_mobiledata select uid,mobile,'10300010001' from rbc_audience_user_v1 where mobile in (select mobile from (select mobile,count(1)cnt from rbc_audience_user_v1 where mobile !='null' and mobile !='' and data_code='10300010001'  group by mobile having count(mobile) >1) a) and data_code='10300010001';
1947

听听
insert into rbc_audience_user_not_distinct_mobiledata  select uid,mobile,'10300010002' from rbc_audience_user_v1 where mobile in (select mobile from (select mobile,count(1)cnt from rbc_audience_user_v1 where mobile !='null' and mobile !='' and data_code='10300010002'  group by mobile having count(mobile) >1) a) and data_code='10300010002';
0

交通台数据
insert into rbc_audience_user_not_distinct_mobiledata  select uid,mobile,'10300010003' from rbc_audience_user_v1 where mobile in (select mobile from (select mobile,count(1)cnt from rbc_audience_user_v1 where mobile !='null' and mobile !='' and data_code='10300010003'  group by mobile having count(mobile) >1) a) and data_code='10300010003';
35743
========================================================================================
仅有一条数据

微信
insert into rbc_audience_user_distinct_mobiledata SELECT uid, mobile,'10300010001' FROM rbc_audience_user_v1 WHERE uid IN (SELECT uid FROM (SELECT count(1) cnt,min(uid) uid, mobile FROM rbc_audience_user_v1 WHERE mobile != '' and  mobile !='null' and data_code='10300010001' GROUP BY mobile HAVING count(1) > 1) tb) and  data_code='10300010001';
860

听听
insert into rbc_audience_user_distinct_mobiledata SELECT uid, mobile,'10300010002' FROM rbc_audience_user_v1 WHERE uid IN (SELECT uid FROM (SELECT count(1) cnt,min(uid) uid, mobile FROM rbc_audience_user_v1 WHERE mobile != '' and  mobile !='null'  and  data_code='10300010002' GROUP BY mobile HAVING count(1) > 1) tb) and  data_code='10300010002';
0

交通台数据
insert into rbc_audience_user_distinct_mobiledata SELECT uid, mobile,'10300010003' FROM rbc_audience_user_v1 WHERE uid IN (SELECT uid FROM (SELECT count(1) cnt,min(uid) uid, mobile FROM rbc_audience_user_v1 WHERE mobile != '' and  mobile !='null' and  data_code='10300010003' GROUP BY mobile HAVING count(1) > 1) tb) and  data_code='10300010003';


数据取差集
select a.id from rbc_audience_user_not_distinct_mobiledata where id not in (select id from rbc_audience_user_distinct_mobiledata) limit 10;

 

  • 0
    点赞
  • 0
    评论
  • 2
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

本程序是在python中完成,基于sklearn.cluster中的k-means聚类包来实现数据的聚类,对于里面使用的数据格式如下:(注意更改程序中的相关参数) 138 0 124 1 127 2 129 3 119 4 127 5 124 6 120 7 123 8 147 9 188 10 212 11 229 12 240 13 240 14 241 15 240 16 242 17 174 18 130 19 132 20 119 21 48 22 37 23 49 0 42 1 34 2 26 3 20 4 21 5 23 6 13 7 19 8 18 9 36 10 25 11 20 12 19 13 19 14 5 15 29 16 22 17 13 18 46 19 15 20 8 21 33 22 41 23 69 0 56 1 49 2 40 3 52 4 62 5 54 6 32 7 38 8 44 9 55 10 70 11 74 12 105 13 107 14 56 15 55 16 65 17 100 18 195 19 136 20 87 21 64 22 77 23 61 0 53 1 47 2 33 3 34 4 28 5 41 6 40 7 38 8 33 9 26 10 31 11 31 12 13 13 17 14 17 15 25 16 17 17 17 18 14 19 16 20 17 21 29 22 44 23 37 0 32 1 34 2 26 3 23 4 25 5 25 6 27 7 30 8 25 9 17 10 12 11 12 12 12 13 7 14 6 15 6 16 12 17 12 18 39 19 34 20 32 21 34 22 35 23 33 0 57 1 81 2 77 3 68 4 61 5 60 6 56 7 67 8 102 9 89 10 62 11 57 12 57 13 64 14 62 15 69 16 81 17 77 18 64 19 62 20 79 21 75 22 57 23 73 0 88 1 75 2 70 3 77 4 73 5 72 6 76 7 76 8 74 9 98 10 90 11 90 12 85 13 79 14 79 15 88 16 88 17 81 18 84 19 89 20 79 21 68 22 55 23 63 0 62 1 58 2 58 3 56 4 60 5 56 6 56 7 58 8 56 9 65 10 61 11 60 12 60 13 61 14 65 15 55 16 56 17 61 18 64 19 69 20 83 21 87 22 84 23 41 0 35 1 38 2 45 3 44 4 49 5 55 6 47 7 47 8 29 9 14 10 12 11 4 12 10 13 9 14 7 15 7 16 11 17 12 18 14 19 22 20 29 21 23 22 33 23 34 0 38 1 38 2 37 3 37 4 34 5 24 6 47 7 70 8 41 9 6 10 23 11 4 12 15 13 3 14 28 15 17 16 31 17 39 18 42 19 54 20 47 21 68 22
©️2021 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值