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
if exists(select * from statistics_player_l where createtime>=date_sub(curdate(),interval i day) and createtimeupdate 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_sub(curdate(),interval i day) and createtimeupdate 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_sub(curdate(),interval i day) and createtimeupdate 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_sub(curdate(),interval i day) and createtimeupdate 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_sub(curdate(),interval i day) and createtimeupdate 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_sub(curdate(),interval i day) and createtimeupdate 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_sub(curdate(),interval i day) and createtimeupdate 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_sub(curdate(),interval i day) and createtime
end if;
set i=i+1;
end while;
set numareaid=numareaid-1;
set currentareaid=currentareaid+1;
end while loop1;
end
bitscn.com