insert overwrite table tkdm.tkdm_output_payment_explore_day partition(ds='2017-02-25') select '2017-02-25' as dt, x.appid, count(1) as num_ins, sum(x.is_new_ins_pay) as num_pay, sum(if(x.amt_ins_pay_level=0 and is_potential>1,1,0)) as num_potential, sum(if(x.rmb_level=1,1,0)) as num_low_rmb, sum(if(x.rmb_level=2,1,0)) as num_medium_rmb, sum(if(x.rmb_level=3,1,0)) as num_high_rmb, sum(if(x.amt_ins_pay_level=1,1,0)) as num_level_1, sum(if(x.amt_ins_pay_level=2,1,0)) as num_level_2, sum(if(x.amt_ins_pay_level=3,1,0)) as num_level_3 from (select appid, deviceid, if(last_ins_date='2017-02-25' ,1,0) as is_new_ins, if(last_ins_date='2017-02-25' and last_payment_ds='2017-02-25' ,1,0) as is_new_ins_pay, if( last_ins_date='2017-02-25' and last_payment_ds='2017-02-25' , case when last_amt_payment < 6 then 1 when last_amt_payment < 198 then 2 else 3 end, 0 ) as amt_ins_pay_level, case when amt_payment <=0 or amt_payment is null then 0 when amt_payment <500 then 1 when amt_payment <5000 then 2 else 3 end as rmb_level, sum(if(amt_payment>0,1,0)) over(partition by deviceid) as is_potential from tkdm.tkdm_base_active_payment_info where ds='2017-02-25' ) x where x.is_new_ins=1 group by x.appid cluster by appid create EXTERNAL table tkdm_output_payment_explore_day( dt string, appid string, num_ins int, num_pay int, num_potential int, num_low_rmb int, num_medium_rmb int, num_high_rmb int, num_level_1 int, num_level_2 int, num_level_3 int ) PARTITIONED BY (ds string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS ORC location 's3://reyuntkio/warehouse/tkio/tkdm.db/tkdm_output_payment_explore_day' ;