add jar /mnt1/testdata/udf-0.0.1.jar;
CREATE TEMPORARY FUNCTION bootstrapmedian AS 'com.reyun.hive.udaf.BootStrap';

insert overwrite table tkdm.tkdm_output_active_app_std_week partition(ds='2017-02-22') 

select /*+ mapjoin(x)*/ x.appid,
       '2017-02-22' as week_dt,
       x.category_id,
       x.isgame,
       nvl(y.avg_ins_rate,-1)  as avg_ins_rate,
       nvl(y.approx_med_ins_rate,-1) as approx_med_ins_rate,
       nvl(y.bootstarp_med_ins_rate,-1) as bootstarp_med_ins_rate
from 

(select appid,category_id,isgame
    from tkio_v2.tkio_dp_app_info
    where ds='2017-02-28' 
) as x 

left join 

(select category_id, 
       isgame,
       round(avg(ins_rate), 4) avg_ins_rate,
       round(percentile_approx(ins_rate,0.5),4) approx_med_ins_rate,
       round(bootstrapmedian(ins_rate),4) bootstarp_med_ins_rate
    from (
       select  appid,
               category_id,
               isgame ,
               ds,
               round(sum(num_install) / sum(dupnum_click_all),4) as ins_rate
           from tkdm.tkdm_data_app_summary_day
           where ds between '2017-02-22' and '2017-02-28'
           group by appid,category_id,isgame,ds
       ) as tt 
    group by category_id, isgame
) y on x.category_id=y.category_id and x.isgame=y.isgame




create EXTERNAL table tkdm_output_active_app_std_week(
appid string,
week_dt string,
category_id int,
isgame int,
avg_ins_rate double,
approx_med_ins_rate double,
bootstarp_med_ins_rate double
)
PARTITIONED BY (ds string)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
location 's3://reyuntkio/warehouse/tkio/tkdm.db/tkdm_output_active_app_std_week'
;