tkdm_output_payment_explore_day.sql 1.8 KB
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'
;