impala去重的两种方案

方案一

聚合函数,GROUP BY可以跟多个字段

SELECT
	min( o.userid ),min( o._id ),min( o.action ),min( o.event_value ),
	min( o.deviceid ),min( o.platform ),min( o.mac ),min( o.androidid ),
	min( o.ip ),min( o.channel ),min( o.brand ),min( o.oaid ),min( o.time ),
	min( o.imei ),min( o.idfa ),min( o.idfv ),min( o.user_agent ),min( o.guid ),
	min( o.page ),min( o.duration ),min( o.city ),min( o.country ),min( o.region ) 
FROM
	origin_log o 
WHERE
	action IN ( 'app_090103', 'web_090103' ) 
	AND time > '2020-05-01 00:00:00' 
	AND NOT EXISTS ( SELECT userid FROM new_user_days n WHERE o.userid = n.userid ) 
GROUP BY
	o.userid 

方案二

窗口函数 row_number() over

select 
    a.userid, a._id,a.action,a.event_value,a.deviceid,a.platform,a.mac,a.androidid,a.ip,
    a.channel,a.brand,a.oaid,a.time,a.imei,a.idfa,a.idfv,a.user_agent,a.guid,a.page,
    a.duration,a.city,a.country,a.region 
from 
    (select o.*,row_number() over(partition by userid order by time desc)  as row_id 
    from origin_log o  where  
    action in ('app_090103','web_090103') and time > '2020-05-01 00:00:00'
    and  NOT exists(select userid from new_user_days n where o.userid = n.userid))a
where a.row_id=1