当前位置： 移动技术网 > IT编程>数据库>其他数据库 > Hive中的去重 ：distinct,group by与ROW_Number()窗口函数

Hive中的去重 ：distinct,group by与ROW_Number()窗口函数

2018年11月21日  | 移动技术网IT编程  | 我要评论

北京世纪同力家具,利齿青鱼群,皇室小萌狐

一、distinct,group by与row_number()窗口函数使用方法

1. distinct用法：对select 后面所有字段去重，并不能只对一列去重。

（1）当distinct应用到多个字段的时候，distinct必须放在开头，其应用的范围是其后面的所有字段，而不只是紧挨着它的一个字段，而且distinct只能放到所有字段的   前面

（2）distinct对null是不进行过滤的，即返回的结果中是包含null值的

（3）聚合函数中的distinct,如 count( ) 会过滤掉为null 的项

2.group by用法：对group by 后面所有字段去重，并不能只对一列去重。

3. row_number() over()窗口函数

注意：row_number() over (partition by id order by time desc) 给每个id加一列按时间倒叙的rank值，取rank=1

select m.id,m.gender,m.age,m.rank

from (select id,gender,age,row_number() over(partition by id order by id) rank

      from temp.control_201804to201806_unimp_demo_sk_ii_0803

      where id!='na' and gender!='' or age!=''

           ) m

where m.rank=1

二、案例：

1.表中有两列：id ，superid，按照superid倒序排序选出前100条不同的id，如下：

 

1.方案一：子查询中对id,superid同时去重，可能存在一个id对应的superid不同，id这一列有重复的id，但                  是结果只需要一列不同的id， 如果时不限制数量，则可以选择这种方法

%jdbc(hive)
create table temp.match_relation_3m_active_pampers_bcdcity_2million_180928_v5 as
select a.id
from (select distinct id,superid
      from temp.match_relation_3m_active_pampers_bcdcity_180928_v2
      order by superid desc
      limit 100
     ) a
group by a.id
注意，对id去重时可以用gruop by 或者distinct id，两者去重后的id排序时一致的，但是加了distinct(group by)后，distinct字段自带排序功能，会先按照distinct后面的字段进行排序,即已经改变了子查询的中order by的排序，但是结果应该有的id是一样的，只是排序不同罢了。
 

 

 

 方案二：因为要求按照superid倒序排序选出，而一个id对应的superid不同，必有大有小，选出最大的那                   一个，即可。 同理若是按照superid正序排列，可以选出最小的一列

        %jdbc(hive)
create table temp.match_relation_3m_active_pampers_bcdcity_2million_180928_v7 as
select a.id
from (select id,max(superid) as superid
         from temp.match_relation_3m_active_pampers_bcdcity_180928_v2
         group by id
         order by superid desc
         limit 100
      ) a

 

 

方案三：首先利用窗口函数row_number() over()窗口函数对id这一列去重，不能用distinct

                或者group by对id,superid同时去重

%jdbc(hive)
create table temp.match_relation_3m_active_pampers_bcdcity_2million_180928_v11 as
select n.id
from (select m.id,superid
          from (select id,superid,row_number() over(partition by id order by id) rank
                    from temp.match_relation_3m_active_pampers_bcdcity_180928_v2
                   ) m  
          where m.rank=1
          order by  superid desc
          limit 100
        )n
注意，以下代码中，窗口函数row_number() over（）的执行顺序晚于 order by  superid desc，最终的结果并非 superid的倒叙排列的结果
%jdbc(hive)
create table temp.match_relation_3m_active_pampers_bcdcity_2million_180928_v9 as
select m.id
from (select id, superid,row_number() over(partition by id order by id) rank
         from temp.match_relation_3m_active_pampers_bcdcity_180928_v2
         order by  superid desc
        ) m
where m.rank=1
group by m.id
limit 100
 

 

 
 

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网