欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-05-11 14:23:28
一、distinct,group by与ROW_Number()窗口函数使用方法 1. Distinct用法:对select 后面所有字段去重,并不能只对一列去重。 (1)当distinct应用到多个字段的时候,distinct必须放在开头,其应用的范围是其后面的所有字段,而不只是紧挨着它的一个字段 ......

一、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,如下:

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

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是一样的,只是排序不同罢了。
 Hive中的去重 :distinct,group by与ROW_Number()窗口函数

 

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

 

 方案二:因为要求按照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
Hive中的去重 :distinct,group by与ROW_Number()窗口函数

 

 

方案三:首先利用窗口函数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
 Hive中的去重 :distinct,group by与ROW_Number()窗口函数