1.計(jì)算分日期分渠道的LTV
SELECT b.regdate,b.gamechannel ,c.channelname,b.users
,if(DATEDIFF(curdate(),b.regdate)>1,sum(IF(a.days<=1,a.pay,0))/b.users,"-") as ltv1
,if(DATEDIFF(curdate(),b.regdate)>2,sum(IF(a.days<=2,a.pay,0))/b.users,"-") as ltv2
,if(DATEDIFF(curdate(),b.regdate)>3,sum(IF(a.days<=3,a.pay,0))/b.users,"-") as ltv3
,if(DATEDIFF(curdate(),b.regdate)>4,sum(IF(a.days<=4,a.pay,0))/b.users,"-") as ltv4
,if(DATEDIFF(curdate(),b.regdate)>5,sum(IF(a.days<=5,a.pay,0))/b.users,"-") as ltv5
,if(DATEDIFF(curdate(),b.regdate)>6,sum(IF(a.days<=6,a.pay,0))/b.users,"-") as ltv6
,if(DATEDIFF(curdate(),b.regdate)>7,sum(IF(a.days<=7,a.pay,0))/b.users,"-") as ltv7
,if(DATEDIFF(curdate(),b.regdate)>8,sum(IF(a.days<=8,a.pay,0))/b.users,"-") as ltv8
,if(DATEDIFF(curdate(),b.regdate)>9,sum(IF(a.days<=9,a.pay,0))/b.users,"-") as ltv9
,if(DATEDIFF(curdate(),b.regdate)>10,sum(IF(a.days<=10,a.pay,0))/b.users,"-") as ltv10
,if(DATEDIFF(curdate(),b.regdate)>11,sum(IF(a.days<=11,a.pay,0))/b.users,"-") as ltv11
,if(DATEDIFF(curdate(),b.regdate)>12,sum(IF(a.days<=12,a.pay,0))/b.users,"-") as ltv12
,if(DATEDIFF(curdate(),b.regdate)>13,sum(IF(a.days<=13,a.pay,0))/b.users,"-") as ltv13
,if(DATEDIFF(curdate(),b.regdate)>14,sum(IF(a.days<=14,a.pay,0))/b.users,"-") as ltv14
,if(DATEDIFF(curdate(),b.regdate)>15,sum(IF(a.days<=15,a.pay,0))/b.users,"-") as ltv15
,if(DATEDIFF(curdate(),b.regdate)>16,sum(IF(a.days<=16,a.pay,0))/b.users,"-") as ltv16
,if(DATEDIFF(curdate(),b.regdate)>17,sum(IF(a.days<=17,a.pay,0))/b.users,"-") as ltv17
,if(DATEDIFF(curdate(),b.regdate)>18,sum(IF(a.days<=18,a.pay,0))/b.users,"-") as ltv18
,if(DATEDIFF(curdate(),b.regdate)>19,sum(IF(a.days<=19,a.pay,0))/b.users,"-") as ltv19
,if(DATEDIFF(curdate(),b.regdate)>20,sum(IF(a.days<=20,a.pay,0))/b.users,"-") as ltv20
,if(DATEDIFF(curdate(),b.regdate)>21,sum(IF(a.days<=21,a.pay,0))/b.users,"-") as ltv21
,if(DATEDIFF(curdate(),b.regdate)>22,sum(IF(a.days<=22,a.pay,0))/b.users,"-") as ltv22
,if(DATEDIFF(curdate(),b.regdate)>23,sum(IF(a.days<=23,a.pay,0))/b.users,"-") as ltv23
,if(DATEDIFF(curdate(),b.regdate)>24,sum(IF(a.days<=24,a.pay,0))/b.users,"-") as ltv24
,if(DATEDIFF(curdate(),b.regdate)>25,sum(IF(a.days<=25,a.pay,0))/b.users,"-") as ltv25
,if(DATEDIFF(curdate(),b.regdate)>26,sum(IF(a.days<=26,a.pay,0))/b.users,"-") as ltv26
,if(DATEDIFF(curdate(),b.regdate)>27,sum(IF(a.days<=27,a.pay,0))/b.users,"-") as ltv27
,SUM(IF(a.days<=1,a.pay,0)) as ltvpay1
,SUM(IF(a.days<=2,a.pay,0)) as ltvpay2
,SUM(IF(a.days<=3,a.pay,0)) as ltvpay3
,SUM(IF(a.days<=4,a.pay,0)) as ltvpay4
,SUM(IF(a.days<=5,a.pay,0)) as ltvpay5
,SUM(IF(a.days<=6,a.pay,0)) as ltvpay6
,SUM(IF(a.days<=7,a.pay,0)) as ltvpay7
,SUM(IF(a.days<=30,a.pay,0)) as ltvpay30
FROM
(-- 計(jì)算分渠道分日期的新增用戶
SELECT DATE(registertime) as regdate,gamechannel ,COUNT(DISTINCT roleid) as users
FROM createrole
WHERE registertime >='2018-09-14'
AND roleid NOT IN (SELECT roleid FROM createrole_optimize)
AND registertime <CURDATE()
GROUP BY DATE(registertime),gamechannel) as b ,
(
-- 對(duì)分渠道分日期的LTV的數(shù)據(jù)進(jìn)行匯總
SELECT a.gamechannel, a.date as regdate ,a.paydays as days ,SUM(pay) as pay
FROM
(-- 計(jì)算付費(fèi)天數(shù)距離注冊(cè)天數(shù)時(shí)間
SELECT date(a.registertime) as date ,a.gamechannel,a.roleid,IFNULL(DATEDIFF(b.paytime,a.registertime)+1,0) as paydays , IFNULL(b.money,0) as pay
FROM createrole as a LEFT JOIN recharge as b
on a.roleid=b.roleid
AND a.roleid NOT IN (SELECT roleid FROM createrole_optimize)
WHERE b.paytime >='2018-09-14'
AND b.paytime <CURDATE() ) as a
GROUP BY a.date , a.gamechannel,a.paydays
)as a ,allchannel as c
WHERE a.regdate=b.regdate AND a.gamechannel=b.gamechannel AND c.channelid=a.gamechannel
GROUP BY b.regdate,b.gamechannel ,b.users,c.channelname
2.計(jì)算分渠道的LTV
計(jì)算分渠道的LTV涉及一個(gè)問題，日期的選擇,9.14開服，現(xiàn)在9.29計(jì)算LTV14的時(shí)候只能，用9.14的新增玩家和9.14的新增玩家在9.19-9.29期間的付費(fèi)來計(jì)算，其他的
日期和付費(fèi)都不符合條件
SELECT b.gamechannel ,c.channelname
,IFNULL(SUM(IF(a.days<=1 AND (DATEDIFF(curdate(),b.regdate)>=1),a.pay,0))/SUM(IF(a.days=1 AND (DATEDIFF(curdate(),b.regdate)>=1),b.users,0)),"-") as LTV1
,IFNULL(SUM(IF(a.days<=2 AND (DATEDIFF(curdate(),b.regdate)>=2),a.pay,0))/SUM(IF(a.days=1 AND (DATEDIFF(curdate(),b.regdate)>=2),b.users,0)),"-") as LTV2
,IFNULL(SUM(IF(a.days<=3 AND (DATEDIFF(curdate(),b.regdate)>=3),a.pay,0))/SUM(IF(a.days=1 AND (DATEDIFF(curdate(),b.regdate)>=3),b.users,0)),"-") as LTV3
,IFNULL(SUM(IF(a.days<=4 AND (DATEDIFF(curdate(),b.regdate)>=4),a.pay,0))/SUM(IF(a.days=1 AND (DATEDIFF(curdate(),b.regdate)>=4),b.users,0)),"-") as LTV4
,IFNULL(SUM(IF(a.days<=5 AND (DATEDIFF(curdate(),b.regdate)>=5),a.pay,0))/SUM(IF(a.days=1 AND (DATEDIFF(curdate(),b.regdate)>=5),b.users,0)),"-") as LTV5
,IFNULL(SUM(IF(a.days<=6 AND (DATEDIFF(curdate(),b.regdate)>=6),a.pay,0))/SUM(IF(a.days=1 AND (DATEDIFF(curdate(),b.regdate)>=6),b.users,0)),"-") as LTV6
,IFNULL(SUM(IF(a.days<=7 AND (DATEDIFF(curdate(),b.regdate)>=7),a.pay,0))/SUM(IF(a.days=1 AND (DATEDIFF(curdate(),b.regdate)>=7),b.users,0)),"-") as LTV7
,IFNULL(SUM(IF(a.days<=8 AND (DATEDIFF(curdate(),b.regdate)>=8),a.pay,0))/SUM(IF(a.days=1 AND (DATEDIFF(curdate(),b.regdate)>=8),b.users,0)),"-") as LTV8
,IFNULL(SUM(IF(a.days<=9 AND (DATEDIFF(curdate(),b.regdate)>=9),a.pay,0))/SUM(IF(a.days=1 AND (DATEDIFF(curdate(),b.regdate)>=9),b.users,0)),"-") as LTV9
,IFNULL(SUM(IF(a.days<=10 AND (DATEDIFF(curdate(),b.regdate)>=10),a.pay,0))/SUM(IF(a.days=1 AND (DATEDIFF(curdate(),b.regdate)>=10),b.users,0)),"-") as LTV10
,IFNULL(SUM(IF(a.days<=11 AND (DATEDIFF(curdate(),b.regdate)>=11),a.pay,0))/SUM(IF(a.days=1 AND (DATEDIFF(curdate(),b.regdate)>=11),b.users,0)),"-") as LTV11
,IFNULL(SUM(IF(a.days<=12 AND (DATEDIFF(curdate(),b.regdate)>=12),a.pay,0))/SUM(IF(a.days=1 AND (DATEDIFF(curdate(),b.regdate)>=12),b.users,0)),"-") as LTV12
,IFNULL(SUM(IF(a.days<=13 AND (DATEDIFF(curdate(),b.regdate)>=13),a.pay,0))/SUM(IF(a.days=1 AND (DATEDIFF(curdate(),b.regdate)>=13),b.users,0)),"-") as LTV13
,IFNULL(SUM(IF(a.days<=14 AND (DATEDIFF(curdate(),b.regdate)>=14),a.pay,0))/SUM(IF(a.days=1 AND (DATEDIFF(curdate(),b.regdate)>=14),b.users,0)),"-") as LTV14
,IFNULL(SUM(IF(a.days<=30 AND (DATEDIFF(curdate(),b.regdate)>=30),a.pay,0))/SUM(IF(a.days=1 AND (DATEDIFF(curdate(),b.regdate)>=30),b.users,0)),"-") as LTV30
,IFNULL(SUM(IF(a.days<=60 AND (DATEDIFF(curdate(),b.regdate)>=60),a.pay,0))/SUM(IF(a.days=1 AND (DATEDIFF(curdate(),b.regdate)>=60),b.users,0)),"-") as LTV60
,IFNULL(SUM(IF(a.days<=90 AND (DATEDIFF(curdate(),b.regdate)>=90),a.pay,0))/SUM(IF(a.days=1 AND (DATEDIFF(curdate(),b.regdate)>=90),b.users,0)),"-") as LTV90
,SUM(IF(a.days=1 AND (DATEDIFF(curdate(),b.regdate)>=3),b.users,0)) as ltv3users
,SUM(IF(a.days=1 AND (DATEDIFF(curdate(),b.regdate)>=14),b.users,0)) as ltv14users
,SUM(IF(a.days<=3 AND (DATEDIFF(curdate(),b.regdate)>=3),a.pay,0)) as ltvpay3
,SUM(IF(a.days<=14 AND (DATEDIFF(curdate(),b.regdate)>=14),a.pay,0)) as ltvpay14
FROM
(-- 計(jì)算分渠道分日期的新增用戶
SELECT DATE(registertime) as regdate,gamechannel ,COUNT(DISTINCT roleid) as users
FROM createrole
WHERE registertime >='2018-09-14'
AND registertime <CURDATE()
-- 剔除優(yōu)化用戶
AND roleid not in (SELECT roleid FROM createrole_optimize)
GROUP BY DATE(registertime),gamechannel
) as b ,
(
-- 對(duì)分渠道分日期的LTV的數(shù)據(jù)進(jìn)行匯總
SELECT a.gamechannel, a.date as regdate ,a.paydays as days ,SUM(pay) as pay
FROM
(-- 計(jì)算付費(fèi)天數(shù)距離注冊(cè)天數(shù)時(shí)間
SELECT date(a.registertime) as date ,a.gamechannel,a.roleid,IFNULL(DATEDIFF(b.paytime,a.registertime)+1,0) as paydays , IFNULL(b.money,0) as pay
FROM createrole as a LEFT JOIN recharge as b
on a.roleid=b.roleid
WHERE b.paytime >='2018-09-14'
AND b.paytime <CURDATE()
-- 剔除優(yōu)化用戶
AND a.roleid not in (SELECT roleid FROM createrole_optimize) ) as a
GROUP BY a.date , a.gamechannel,a.paydays
)as a ,allchannel as c
WHERE a.regdate=b.regdate AND a.gamechannel=b.gamechannel AND c.channelid=a.gamechannel
GROUP BY b.gamechannel, c.channelname
