一、设备留存求解
代码:# 设备存留去重新
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
