NSERT OVERWRITE table dw_elbs.f_sid_app_filter_agg_in_one_day PARTITION (bizdate=$startdate)
select
sid
,cast(oid_type as int)
,cast(client_id as int)
,app_json_list
,record_count
,first_get_time
,latest_get_time
,first_time
,latest_time
from
(
select
tb_all.oid as sid
,oid_type
,client_id
,app_json_list
,record_count
,first_get_time
,latest_get_time
,first_time
,latest_time
,case when (tb_ex_oid.oid is null) then 0
when (tb_ex_oid.oid is not null) then 1
end as exception
from
(
select
*
from device_base.m_oid_app_filter_agg_in_one_day
where bizdate=$startdate
) tb_all
left join
(
select
oid
from
(
select
oid
,count(1) as cnt
from
(
select
*
from device_base.m_oid_app_filter_agg_in_one_day
where bizdate=$startdate
) a
group by oid
) b
where b.cnt>=3
) tb_ex_oid
on tb_all.oid=tb_ex_oid.oid
) c
where c.exception=0;