mysql统计留存率 -

bqmcjl

浏览: 4908 次
性别:
来自: 北京

最近访客更多访客>>

huliang216

woweishenmebushishen

keyzhzh

lhutopia

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

2013-05 ( 3)
更多存档...

mysql统计留存率

博客分类：

个人

mysql sql SQL Server

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

分享到：