mysql中怎么统计留存率

61次阅读

共计 4070 个字符，预计需要花费 11 分钟才能阅读完成。

这期内容当中丸趣 TV 小编将会给大家带来有关 mysql 中怎么统计留存率，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

Sql 代码
begin
declare i int;
declare numareaId int(10);
declare currentareaId int(10);
SELECT COUNT(areaId),MIN(areaId) INTO @a,@b FROM option_area_info;
SET numareaId=@a;
SET currentareaId=@b;
loop1:WHILE numareaId 0 DO
SET @AID = currentareaId;
SET i=1;
while i 8 do
if exists(select * from statistics_player_l where createTime =date_sub(curdate(),interval i day) and createTime date_sub(curdate(),interval i-1 day) and areaId=@AID) then
update statistics_player_l set oneDayPlayer=(select ((select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i day) and loginTime =date_add(date_sub(curdate(),interval i day),interval 1 day) and loginTime date_add(date_sub(curdate(),interval i day),interval 2 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i day)))*100) where createTime =date_sub(curdate(),interval i day) and createTime date_sub(curdate(),interval i-1 day);
update statistics_player_l set twoDayPlayer=(select ((select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i day) and loginTime =date_add(date_sub(curdate(),interval i day),interval 2 day) and loginTime date_add(date_sub(curdate(),interval i day),interval 3 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i day)))*100) where createTime =date_sub(curdate(),interval i day) and createTime date_sub(curdate(),interval i-1 day);
update statistics_player_l set threeDayPlayer=(select ((select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i day) and loginTime =date_add(date_sub(curdate(),interval i day),interval 3 day) and loginTime date_add(date_sub(curdate(),interval i day),interval 4 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i day)))*100) where createTime =date_sub(curdate(),interval i day) and createTime date_sub(curdate(),interval i-1 day);
update statistics_player_l set fourDayPlayer=(select ((select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i day) and loginTime =date_add(date_sub(curdate(),interval i day),interval 4 day) and loginTime date_add(date_sub(curdate(),interval i day),interval 5 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i day)))*100) where createTime =date_sub(curdate(),interval i day) and createTime date_sub(curdate(),interval i-1 day);
update statistics_player_l set fiveDayPlayer=(select ((select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i day) and loginTime =date_add(date_sub(curdate(),interval i day),interval 5 day) and loginTime date_add(date_sub(curdate(),interval i day),interval 6 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i day)))*100) where createTime =date_sub(curdate(),interval i day) and createTime date_sub(curdate(),interval i-1 day);
update statistics_player_l set sixDayPlayer=(select ((select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i day) and loginTime =date_add(date_sub(curdate(),interval i day),interval 6 day) and loginTime date_add(date_sub(curdate(),interval i day),interval 7 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i day)))*100) where createTime =date_sub(curdate(),interval i day) and createTime date_sub(curdate(),interval i-1 day);
update statistics_player_l set sevenDayPlayer=(select ((select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i day) and loginTime =date_add(date_sub(curdate(),interval i day),interval 7 day) and loginTime date_add(date_sub(curdate(),interval i day),interval 8 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i day)))*100) where createTime =date_sub(curdate(),interval i day) and createTime date_sub(curdate(),interval i-1 day);
end if;
SET i=i+1;
end while;
SET numareaId=numareaId-1;
SET currentareaId=currentareaId+1;
END WHILE loop1;
end