SQL脚本-广告业务

INSERT OVERWRITE TABLE stat_bf_spaceid_adv PARTITION (ds='@@{yyyy-MM-dd}')
select case when a.platform='2' then 'iOS' 
            when a.platform='3' then 'android' else '其他' end as platform
    ,a.space_id as space_id
    ,a.ad_id as adv_id
    ,case when a.adv_show  is not null then a.adv_show else 0 end  as adv_show
    ,case when b.adv_click  is not null then b.adv_click else 0 end as adv_click
from (
    select platform
      ,space_id
      ,ad_id
      ,count(*) as adv_show
    from sdo_bf_adv_new_exposure
    where event_date='@@{yyyy-MM-dd}'
        and event_id='100202'
    group by platform
      ,space_id
      ,ad_id
      ) a
left outer join (
    select platform
      ,space_id
      ,ad_id
      ,count(*) as adv_click
    from sdo_bf_adv_new_click
    where event_date='@@{yyyy-MM-dd}'
        and event_id='100203'
    group by platform
      ,space_id
      ,ad_id
      ) b on a.platform=b.platform 
          and a.space_id=b.space_id 
        and a.ad_id=b.ad_id
union all
select case when a.platform='2' then 'iOS' 
            when a.platform='3' then 'android' else '其他' end as platform
    ,'1001' as space_id
    ,a.obj_id as adv_id
    ,case when a.adv_show  is not null then a.adv_show else 0 end  as adv_show
    ,case when b.adv_click  is not null then b.adv_click else 0 end as adv_click
from (
    select platform
      ,obj_id
      ,count(*) as adv_show
    from sdo_bf_log_ads_start_show
    where ds='@@{yyyy-MM-dd}'
        and event_id in ('2710002','100110')
    group by platform
      ,obj_id
      ) a
left outer join (
    select platform
      ,obj_id
      ,count(*) as adv_click
    from sdo_bf_log_ads_start_click
    where ds='@@{yyyy-MM-dd}'
        and event_id in ('2710003','100111')
    group by platform
      ,obj_id
      ) b on a.platform=b.platform
        and a.obj_id=b.obj_id;