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'
;