insert overwrite table tkdm.tkdm_output_app_cumsum_all partition(ds='$ds') select '$ds' as dt, appid, sum(cum_num_click) as cum_num_click, sum(cum_dupnum_click_day) as cum_dupnum_click_day, sum(cum_dupnum_click_all) as cum_dupnum_click_all, sum(cum_num_install) as cum_num_install, sum(cum_dupnum_reged_day) as cum_dupnum_reged_day, sum(cum_dupnum_reged_mon) as cum_dupnum_reged_mon, round(sum(cum_amt_income_new_user),2) as cum_amt_income_new_user, sum(cum_num_payer_new_user) as cum_num_payer_new_user, round(sum(cum_amt_income_all_user),2) as cum_amt_income_all_user, sum(cum_num_payer_all_user) as cum_num_payer_all_user from ( select appid, cum_num_click, cum_dupnum_click_day, cum_dupnum_click_all, cum_num_install, cum_dupnum_reged_day, cum_dupnum_reged_mon, cum_amt_income_new_user, cum_num_payer_new_user, cum_amt_income_all_user, cum_num_payer_all_user from tkdm.tkdm_output_app_cumsum_all where ds=date_sub('$ds',1) union all select appid, sum(num_click) as cum_num_click, sum(dupnum_click_day) as cum_dupnum_click_day, sum(dupnum_click_all) as cum_dupnum_click_all, sum(num_install) as cum_num_install, sum(dupnum_reged_day) as cum_dupnum_reged_day, sum(dupnum_reged_mon) as cum_dupnum_reged_mon, sum(amt_income_new_user) as cum_amt_income_new_user, sum(num_payer_new_user) as cum_num_payer_new_user, sum(amt_income_all_user) as cum_amt_income_all_user, sum(num_payer_all_user) as cum_num_payer_all_user from tkdm.tkdm_data_app_summary_day where ds='$ds' group by appid ) as t group by appid create EXTERNAL table tkdm_output_app_cumsum_all( dt string, appid string, cum_num_click int, cum_dupnum_click_day int , cum_dupnum_click_all int, cum_num_install int, cum_dupnum_reged_day int, cum_dupnum_reged_mon int, cum_amt_income_new_user double, cum_num_payer_new_user int, cum_amt_income_all_user double, cum_num_payer_all_user int ) PARTITIONED BY (ds string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' location 's3://reyuntkio/warehouse/tkio/tkdm.db/tkdm_output_app_cumsum_all' ;