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