tkdm_data_active_detial_day.sql 1.14 KB
Newer Older
mengdongxing committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
insert overwrite table tkdm.tkdm_data_active_detial_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
17
     where ds='2017-02-26' and dt='2017-02-26' and 
mengdongxing committed
18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
           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_detial_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_detial_day'
;