tkdm_data_active_summary_day.sql 1.14 KB
insert overwrite table tkdm.tkdm_data_active_summary_day partition(ds='2017-02-26') 

select /*+ mapjoin(b) */ a.appid,
       a.cid,
       a.dupnum_click_all,
       a.num_install,
       round(a.num_install / a.dupnum_click_all,4) as ins_rate,
       b.category_id,
       b.category_name,
       b.isgame
from 
(select appid,
       cid,
       sum(dupnum_click_all) as dupnum_click_all,
       sum(num_install) as num_install
     from tkio.tkio_rpt_channel_compare_day
     where ds='2017-02-26' and dt='2017-02-26' and 
           campaignid !='_default_' and 
           cid!=-2
     group by appid,cid
     having sum(dupnum_click_all) > 10
) as a 

join 

(select appid,
        category_id,
        category_name,
        isgame
     from tkio_v2.tkio_dp_app_info
     where ds ='2017-02-26'
) as b on a.appid=b.appid



create EXTERNAL table tkdm_data_active_summary_day(
appid string,
cid int,
dupnum_click_all int,
num_install int,
ins_rate double,
category_id int,
category_name string,
isgame int
)
PARTITIONED BY (ds string)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
location 's3://reyuntkio/warehouse/tkio/tkdm.db/tkdm_data_active_summary_day'
;