SQL脚本-广告业务

最新推荐文章于 2025-01-27 16:48:01 发布

桃桃酱儿

最新推荐文章于 2025-01-27 16:48:01 发布

阅读量1.8k

点赞数

文章标签： sql android 数据库

本文链接：https://blog.csdn.net/weixin_37364055/article/details/121930972

版权

该SQL查询用于合并iOS和Android平台的广告展示（adv_show）和点击（adv_click）数据，按日期（ds）、平台、广告位（space_id）和广告ID（adv_id）进行统计。对于没有点击数据的情况，使用0填充。同时，包含了针对特定对象ID（obj_id）在全平台的展示和点击情况。

摘要由CSDN通过智能技术生成

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;