邮件图图片实时数据

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 ;