hiveQL去重

去重：

以id进行分组，然后取出每组的第一个

select * from (select *,row_number() over (partition by id) num from t_link) t where t.num=1;

以id进行分组，按照create_time降序排序后，然后取出每组的第一个

select * from (select *,row_number() over (partition by id order by create_time desc) num from t_link) t where t.num=1;

将去重后的数据重新存储 

insert overwrite table t_link2 
　　select * from 
　　(
　　　　select *,row_number() over (partition by id order by crt_time desc) num from t_link
    ) t where t.num=1;

 去重之后与其他表join算匹配数

select count(*) as cnt from 
    (
        select * from table1 where pt='2017-06-01') t1 
    join 
    (
        select * from (select *,row_number() over(partition by id) num from table2 where pt='2017-06-01') t where t.num =1) t2 
    on t1.id = t2.id

 

转载于:https://www.cnblogs.com/kangoroo/p/7080180.html