tkdm_output_payment_explore_day.sql 1.8 KB
Newer Older
mengdongxing committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
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
31
    where ds='2017-02-25'
mengdongxing committed
32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60
) 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'
;