tkdm_output_active_cid_std_week.sql 1.37 KB
Newer Older
mengdongxing committed
1 2 3 4 5 6 7
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_cid_std_week partition(ds='2017-02-22') 


select /*+ mapjoin(x)*/ x.appid,
8
       '2017-02-22' as week_dt,
mengdongxing committed
9 10 11 12 13 14 15 16
       y.cid,
       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 
17 18 19 20 21 22 23 24

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

left join 

mengdongxing committed
25 26 27 28 29 30
(select category_id, 
        isgame,
        cid,
        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
31
    from tkdm.tkdm_data_app_summary_day
mengdongxing committed
32 33
    where ds between '2017-02-22' and '2017-02-28'
    group by category_id, isgame,cid
34
) y on x.category_id=y.category_id and x.isgame=y.isgame
mengdongxing committed
35 36 37 38 39 40




create EXTERNAL table tkdm_output_active_cid_std_week(
appid string,
41
week_dt string,
mengdongxing committed
42 43 44 45 46 47 48 49 50 51 52 53
cid int,
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_cid_std_week'
;
54