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
