测试期间实时数据.png
1.第一个图片的SQL
SELECT a.channelid,sum(a.devs) as "新增设备"
,b.dnewdevsTB as "新增设备环比"
,sum(a.users) as "新增用户",sum(a.logdevs) as"登录设备",(sum(a.logdevs) -sum(a.devs)) as "活跃设备",(sum(a.logusers) -sum(a.users) ) as "活跃用户"
,b.dlogindevsTb as "登录设备环比" --
,sum(a.logusers) as "登录用户",sum(a.payusers) as "付费人数",sum(a.pay) as "付费金额"
,b.payTb as "付费金额环比"
,concat(round(sum(a.payusers)/sum(a.logusers)*100,0),"%") as "付费率",round(sum(a.pay)/sum(a.payusers),2) as "ARPPU" ,round(sum(a.pay)/sum(a.logusers),2) as "ARPU"
FROM
-- 计算基本数据
(
SELECT channelid,SUM(dnewdevs) as devs ,SUM(dnewusers) as users ,SUM(dlogindevs) as logdevs, SUM(dloginusers) as logusers,SUM(payusers) as payusers,SUM(pay) as pay
FROM realinfo
WHERE channelid in(select channelid from allchannel_bak )
AND basicdate = DATE_ADD(CURDATE(),INTERVAL HOUR(NOW()) HOUR)
GROUP BY channelid
) as a
left JOIN
-- 计算环比数据
(SELECT a.channelid
,ifnull(concat(round(( (sum(a.devs)-sum(b.devs))/(sum(b.devs)-sum(c.devs))-1 )*100,0),"%"),"-") as dnewdevsTB
,ifnull(concat(round(((sum(a.logdevs)-sum(b.logdevs))/(sum(b.logdevs)-sum(c.logdevs))-1)*100,0),"%"),"-") AS dlogindevsTb
,ifnull(concat(round(((sum(a.pay)-sum(b.pay))/(sum(b.pay)-sum(c.pay))-1)*100,0),"%"),"-") as payTb
from
(SELECT channelid,SUM(dnewdevs) as devs ,SUM(dlogindevs) as logdevs,SUM(pay) as pay
FROM realinfo
WHERE basicdate =DATE_ADD(CURDATE(),INTERVAL HOUR(NOW()) HOUR)
and channelid in(select channelid from allchannel_bak )
GROUP BY channelid having sum(dlogindevs)>0
) as a left join
(SELECT channelid,SUM(dnewdevs) as devs ,SUM(dlogindevs) as logdevs,SUM(pay) as pay
FROM realinfo
WHERE basicdate =DATE_ADD(CURDATE(),INTERVAL HOUR(NOW())-1 HOUR)
and channelid in(select channelid from allchannel_bak )
GROUP BY channelid
) as b on a.channelid=b.channelid left join
(SELECT channelid,SUM(dnewdevs) as devs ,SUM(dlogindevs) as logdevs,SUM(pay) as pay
FROM realinfo
WHERE basicdate =DATE_ADD(CURDATE(),INTERVAL HOUR(NOW())-2 HOUR)
and channelid in(select channelid from allchannel_bak )
GROUP BY channelid
) as c on a.channelid=c.channelid
group by a.channelid) b
on a.channelid=b.channelid
GROUP BY a.channelid
2.计算分渠道实时留存的数据
SELECT a.channelid,sum(a.devs) as "新增设备"
,b.dnewdevsTB as "新增设备环比"
,sum(a.users) as "新增用户",sum(a.logdevs) as"登录设备",(sum(a.logdevs) -sum(a.devs)) as "活跃设备",(sum(a.logusers) -sum(a.users) ) as "活跃用户"
,b.dlogindevsTb as "登录设备环比" --
,sum(a.logusers) as "登录用户",sum(a.payusers) as "付费人数",sum(a.pay) as "付费金额"
,b.payTb as "付费金额环比"
,concat(round(sum(a.payusers)/sum(a.logusers)*100,0),"%") as "付费率",round(sum(a.pay)/sum(a.payusers),2) as "ARPPU" ,round(sum(a.pay)/sum(a.logusers),2) as "ARPU"
,c.u1remainratio,c.u2remainratio,c.u3remainratio,c.u4remainratio,c.u5remainratio,c.u6remainratio,c.u7remainratio
FROM
-- 计算基本数据
(
SELECT channelid,SUM(dnewdevs) as devs ,SUM(dnewusers) as users ,SUM(dlogindevs) as logdevs, SUM(dloginusers) as logusers,SUM(payusers) as payusers,SUM(pay) as pay
FROM realinfo
WHERE channelid in(select channelid from allchannel_bak )
AND basicdate = DATE_ADD(CURDATE(),INTERVAL HOUR(NOW()) HOUR)
GROUP BY channelid
) as a
left JOIN
-- 计算环比数据
(SELECT a.channelid
,ifnull(concat(round(( (sum(a.devs)-sum(b.devs))/(sum(b.devs)-sum(c.devs))-1 )*100,0),"%"),"-") as dnewdevsTB
,ifnull(concat(round(((sum(a.logdevs)-sum(b.logdevs))/(sum(b.logdevs)-sum(c.logdevs))-1)*100,0),"%"),"-") AS dlogindevsTb
,ifnull(concat(round(((sum(a.pay)-sum(b.pay))/(sum(b.pay)-sum(c.pay))-1)*100,0),"%"),"-") as payTb
from
(SELECT channelid,SUM(dnewdevs) as devs ,SUM(dlogindevs) as logdevs,SUM(pay) as pay
FROM realinfo
WHERE basicdate =DATE_ADD(CURDATE(),INTERVAL HOUR(NOW()) HOUR)
and channelid in(select channelid from allchannel_bak )
GROUP BY channelid having sum(dlogindevs)>0
) as a left join
(SELECT channelid,SUM(dnewdevs) as devs ,SUM(dlogindevs) as logdevs,SUM(pay) as pay
FROM realinfo
WHERE basicdate =DATE_ADD(CURDATE(),INTERVAL HOUR(NOW())-1 HOUR)
and channelid in(select channelid from allchannel_bak )
GROUP BY channelid
) as b on a.channelid=b.channelid left join
(SELECT channelid,SUM(dnewdevs) as devs ,SUM(dlogindevs) as logdevs,SUM(pay) as pay
FROM realinfo
WHERE basicdate =DATE_ADD(CURDATE(),INTERVAL HOUR(NOW())-2 HOUR)
and channelid in(select channelid from allchannel_bak )
GROUP BY channelid
) as c on a.channelid=c.channelid
group by a.channelid) b
on a.channelid=b.channelid
-- 计算留存的数据
LEFT JOIN
(SELECT b.channelid ,u1remainratio -- ,u1remain,u1dnewdevs
,u2remainratio-- ,u2remain,u2dnewdevs
,u3remainratio-- , u3remain,u3dnewdevs
,u4remainratio-- , u4remain,u4dnewdevs
,u5remainratio-- , u5remain,u5dnewdevs
,u6remainratio-- ,u6remain,u6dnewdevs
,u7remainratio-- ,u7remain,u7dnewdevs
FROM
(SELECT channelid,ROUND(IFNULL(SUM(usr1remain)/SUM(dnewdevs),0)*100,2) u1remainratio,IFNULL(SUM(usr1remain),0) u1remain,IFNULL(SUM(dnewdevs),0) u1dnewdevs
FROM serverinfo
WHERE basicdate >= '2018-09-14' AND basicdate < DATE_SUB(CURDATE(),INTERVAL IF(HOUR(NOW())=0,2,1) DAY) and channelid in(select channelid from allchannel_bak )
GROUP BY channelid) b
LEFT JOIN
(SELECT channelid,ROUND(IFNULL(SUM(usr3remain)/SUM(dnewdevs),0)*100,2) u3remainratio,IFNULL(SUM(usr3remain),0) u3remain,IFNULL(SUM(dnewdevs),0) u3dnewdevs
FROM serverinfo
WHERE basicdate >= '2018-09-14' AND basicdate < DATE_SUB(CURDATE(),INTERVAL IF(HOUR(NOW())=0,4,3) DAY) and channelid in(select channelid from allchannel_bak )
GROUP BY channelid) c ON b.channelid = c.channelid
LEFT JOIN
(SELECT channelid,ROUND(IFNULL(SUM(usr7remain)/SUM(dnewdevs),0)*100,2) u7remainratio,IFNULL(SUM(usr7remain),0) u7remain,IFNULL(SUM(dnewdevs),0) u7dnewdevs
FROM serverinfo
WHERE basicdate >= '2018-09-14' AND basicdate < DATE_SUB(CURDATE(),INTERVAL IF(HOUR(NOW())=0,8,7) DAY) and channelid in(select channelid from allchannel_bak )
GROUP BY channelid) d ON c.channelid =d.channelid
LEFT JOIN
(SELECT channelid,ROUND(IFNULL(SUM(usr2remain)/SUM(dnewdevs),0)*100,2) u2remainratio,IFNULL(SUM(usr2remain),0) u2remain,IFNULL(SUM(dnewdevs),0) u2dnewdevs
FROM serverinfo
WHERE basicdate >= '2018-09-14' AND basicdate < DATE_SUB(CURDATE(),INTERVAL IF(HOUR(NOW())=0,3,2) DAY) and channelid in(select channelid from allchannel_bak )
GROUP BY channelid) e ON b.channelid = e.channelid
LEFT JOIN
(SELECT channelid,ROUND(IFNULL(SUM(usr4remain)/SUM(dnewdevs),0)*100,2) u4remainratio,IFNULL(SUM(usr4remain),0) u4remain,IFNULL(SUM(dnewdevs),0) u4dnewdevs
FROM serverinfo
WHERE basicdate >= '2018-09-14' AND basicdate < DATE_SUB(CURDATE(),INTERVAL IF(HOUR(NOW())=0,5,4) DAY) and channelid in(select channelid from allchannel_bak )
GROUP BY channelid) f ON b.channelid = f.channelid
LEFT JOIN
(SELECT channelid,ROUND(IFNULL(SUM(usr5remain)/SUM(dnewdevs),0)*100,2) u5remainratio,IFNULL(SUM(usr5remain),0) u5remain,IFNULL(SUM(dnewdevs),0) u5dnewdevs
FROM serverinfo
WHERE basicdate >= '2018-09-14' AND basicdate < DATE_SUB(CURDATE(),INTERVAL IF(HOUR(NOW())=0,6,5) DAY) and channelid in(select channelid from allchannel_bak )
GROUP BY channelid) g ON b.channelid = g.channelid
LEFT JOIN
(SELECT channelid,ROUND(IFNULL(SUM(usr6remain)/SUM(dnewdevs),0)*100,2) u6remainratio,IFNULL(SUM(usr6remain),0) u6remain,IFNULL(SUM(dnewdevs),0) u6dnewdevs
FROM serverinfo
WHERE basicdate >= '2018-09-14' AND basicdate < DATE_SUB(CURDATE(),INTERVAL IF(HOUR(NOW())=0,7,6) DAY) and channelid in(select channelid from allchannel_bak )
GROUP BY channelid) h ON b.channelid = h.channelid
GROUP BY b.channelid) as c on a.channelid=c.channelid
GROUP BY a.channelid ;
