sql 技巧(-)留存设备计算

一、设备留存求解

过程分解

  代码:# 设备存留去重新

SELECT  g.time  ,

-- 行列转置

sum(if(days = 1,rc,0)) as '1d',sum(if(days = 2,rc,0)) as '2d',sum(if(days = 3,rc,0)) as'3d',sum(if(days=4,rc,0)) as '4d',sum(if(days = 5,rc,0)) as '5d'FROM

-- 按照注册日期和留存日期汇总

(SELECT  G.TIME,G.DAYS ,COUNT(TIME) RC FROM

--计算留存日期

(SELECT a.time ,a.deviceid,b.* ,DATEDIFF(b.logintime,TIME) AS DAYS FROM

--去除创角表中的重复值

(SELECT  b.accid,b.deviceid,DATE_FORMAT(b.time,'%Y-%m-%d')  time from

t_hero_createrole    as  b

INNER JOIN

(SELECT  a.accid  , min(a.time)  as  t  FROM  t_hero_createrole  as  a

GROUP BY  a.accid)a

on  a.accid =b.accid and  a.t =b.time) as a

RIGHT JOIN

--去除登录表的重复值

(select  c.accid  ,date_format(c.logintime,'%Y-%m-%d' ) as logintime from t_hero_logout as c

GROUP BY  c.accid  ,date_format(c.logintime,'%Y-%m-%d' ) ) as b

on a.accid =b.accid) AS  G

WHERE  G.TIME IS NOT NULL AND  DAYS > 0

GROUP BY  G.TIME,G.DAYS)  G

GROUP BY  G.TIME