%presto cube select a.ds as “日期”, a.dau as “登录人数”, a.nau as “新用户数”, a.charge as “充值金额”, a.newcharge as “新用户充值”, a.payrole as “充值用户数”, a.paytimes as “充值次数”, a.newpayrole as “新用户付费人数”,
1.0*a.payrole/a.dau as "付费率(percent)",
1.0*a.newpayrole/a.nau as "新用户付费率(percent)",
round(1.0*a.charge/a.payrole,2) as "arppu",
round(1.0*a.charge/a.dau,2) as "arpu",
coalesce(cast(round(1.0*b.dau02/a.nau,4) as varchar),'') as "次留(percent)",
coalesce(cast(round(1.0*c.dau03/a.nau,4) as varchar),'') as "三留(percent)",
coalesce(cast(round(1.0*d.dau07/a.nau,4) as varchar),'') as "七留(percent)",
round(1.0*a.newcharge/a.nau,2) as "首日ltv",
coalesce(cast(round(1.0*e.ltv03/a.nau,2) as varchar),'') as "3日ltv",
coalesce(cast(round(1.0*f.ltv07/a.nau,2) as varchar),'') as "7日ltv" from
(select ds,
count(distinct concat(server_id,'-',role_id)) as dau,
count(case when regexp_replace(role_register_date,'-','')=ds then concat(server_id,'-',role_id) end) as nau,
sum(paymoney) as charge,
sum(case when regexp_replace(role_register_date,'-','')=ds then paymoney end) as newcharge,
count(case when paymoney>0 then concat(server_id,'-',role_id) end) as payrole,
sum(paytimes) as paytimes,
count(case when paymoney>0 and regexp_replace(role_register_date,'-','')=ds then concat(server_id,'-',role_id) end) as newpayrole
from data_analyze.dm_gamelog_roleinfo_ds_new
where ds>='20181016' and ds<='20181115'
and game_id=94
and if('-1'='-1',true,cast(op_id as integer) in (-1))
and account not in (select distinct account from Data_hub.dw_blacklabel_ds where label='shaosan_blacklabel' and label_value='2')
group by ds) a left join
(select date_format(date_parse(ds,'%Y%m%d') - interval '1' day,'%Y%m%d') as reviseds,
count(distinct concat(server_id,'-',role_id)) as dau02
from data_analyze.dm_gamelog_roleinfo_ds_new
where ds>='20181016' and ds<='20181115'
and game_id=94
and if('-1'='-1',true,cast(op_id as integer) in (-1))
and account not in (select distinct account from Data_hub.dw_blacklabel_ds where label='shaosan_blacklabel' and label_value='2')
and date_diff('day',date_parse(regexp_replace(role_register_date,'-',''),'%Y%m%d'),date_parse(ds,'%Y%m%d'))=1
group by date_format(date_parse(ds,'%Y%m%d') - interval '1' day,'%Y%m%d')) b on a.ds=b.reviseds left join
(select date_format(date_parse(ds,'%Y%m%d') - interval '2' day,'%Y%m%d') as reviseds,
count(distinct concat(server_id,'-',role_id)) as dau03
from data_analyze.dm_gamelog_roleinfo_ds_new
where ds>='20181016' and ds<='20181115'
and game_id=94
and if('-1'='-1',true,cast(op_id as integer) in (-1))
and account not in (select distinct account from Data_hub.dw_blacklabel_ds where label='shaosan_blacklabel' and label_value='2')
and date_diff('day',date_parse(regexp_replace(role_register_date,'-',''),'%Y%m%d'),date_parse(ds,'%Y%m%d'))=2
group by date_format(date_parse(ds,'%Y%m%d') - interval '2' day,'%Y%m%d')) c on a.ds=c.reviseds left join
(select date_format(date_parse(ds,'%Y%m%d') - interval '6' day,'%Y%m%d') as reviseds,
count(distinct concat(server_id,'-',role_id)) as dau07
from data_analyze.dm_gamelog_roleinfo_ds_new
where ds>='20181016' and ds<='20181115'
and game_id=94
and if('-1'='-1',true,cast(op_id as integer) in (-1))
and account not in (select distinct account from Data_hub.dw_blacklabel_ds where label='shaosan_blacklabel' and label_value='2')
and date_diff('day',date_parse(regexp_replace(role_register_date,'-',''),'%Y%m%d'),date_parse(ds,'%Y%m%d'))=6
group by date_format(date_parse(ds,'%Y%m%d') - interval '6' day,'%Y%m%d')) d on a.ds=d.reviseds left join
(select x.regtime,
sum(case when x.days<=2 then x.paymoney end) as ltv03
from
(select ds,
regexp_replace(role_register_date,'-','') as regtime,
date_diff('day',date_parse(regexp_replace(role_register_date,'-',''),'%Y%m%d'),date_parse(ds,'%Y%m%d')) as days,
concat(server_id,'-',role_id),paymoney
from data_analyze.dm_gamelog_roleinfo_ds_new
where ds>='20181016' and ds<='20181115'
and game_id=94
and paymoney>0
and if('-1'='-1',true,cast(op_id as integer) in (-1))
and account not in (select distinct account from Data_hub.dw_blacklabel_ds where label='shaosan_blacklabel' and label_value='2')) x
where date_diff('day',date_parse(regexp_replace(x.regtime,'-',''),'%Y%m%d'),date_parse('20181115','%Y%m%d'))>=2
group by x.regtime) e on a.ds=e.regtime left join
(select y.regtime,
sum(case when y.days<=6 then y.paymoney end) as ltv07
from
(select ds,
regexp_replace(role_register_date,'-','') as regtime,
date_diff('day',date_parse(regexp_replace(role_register_date,'-',''),'%Y%m%d'),date_parse(ds,'%Y%m%d')) as days,
concat(server_id,'-',role_id),paymoney
from data_analyze.dm_gamelog_roleinfo_ds_new
where ds>='20181016' and ds<='20181115'
and game_id=94
and paymoney>0
and if('-1'='-1',true,cast(op_id as integer) in (-1))
and account not in (select distinct account from Data_hub.dw_blacklabel_ds where label='shaosan_blacklabel' and label_value='2')) y
where date_diff('day',date_parse(regexp_replace(y.regtime,'-',''),'%Y%m%d'),date_parse('20181115','%Y%m%d'))>=6
group by y.regtime) f on a.ds=f.regtime order by a.ds desc