问题描述
出来了一个,但总是感觉不对。贴上来看看,如果不对应该怎么写?我要查询本周登录下周不再登录的用户数量。select count(distinct playerId) from log_login l1 where week(now())-1=week(loginTime) and exists(select 1 from log_login l2 where l1.playerId = l2.playerId and week(NOW())=week(loginTime)); 问题补充:大哥们~~~我提问错了。是周流失数,上周登录了,本周没登录
解决方案
select count(distinct playerId) from log_login l1 where week(now())=week(loginTime) and exists(select 1 from log_login l2 where l1.playerId = l2.playerId and week(NOW()) - 1=week(loginTime));留存率是:上周登录了 这周也登录了 你写的按理说没错我要查询本周登录下周不再登录的用户数量。 不是下周 而是上周select count(distinct playerId) from log_login l1 where week(now())-1=week(loginTime) and exists(select 1 from log_login l2 where l1.playerId = l2.playerId and week(NOW())=week(loginTime));
解决方案二:
本周登录,下周不在登录的数据,这个问题感觉怪怪的。看你的SQL,子查询中是查询本周的数据,然后外部的查询是上周的数据。最后上周的id 存在于本周的idselect distinct playerId from log_login l1 where week(now())-1=week(loginTime) and exists(select 1 from log_login l2 where l1.playerId = l2.playerId and week(NOW())=week(loginTime));这里不要count吧,不然你上周的结果就是聚合后的数字,跟本周的比不了吧。要获取数量,在外层在包一个 select count(1) from (select distinct playerId from log_login l1 where week(now())-1=week(loginTime) and exists(select 1 from log_login l2 where l1.playerId = l2.playerId and week(NOW())=week(loginTime));) t1是这个意思不?