我要查询用户的1,2,3,4,5,6,7日留存。以前做出来了。但是需求改了,蛋有点疼,之前是查前几天,现在是要查今天登录的在以后的天数,所以涉及到修改。我这个循环为什么只是循环两遍?数据不对不对不对不对。问题出在哪啊??
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<=7 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>=current_date() and loginTime<date_add(curdate(),interval 1 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i day) and areaId=@AID))*100) where createTime>=date_sub(curdate(),interval i day) and createTime<current_date();
update statistics_player_l set twoDayPlayer= (select((select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i+1 day) and loginTime>=current_date() and loginTime<date_add(curdate(),interval 1 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i+1 day) and areaId=@AID))*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+2 day) and loginTime>=current_date() and loginTime<date_add(curdate(),interval 1 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i+2 day) and areaId=@AID))*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+3 day) and loginTime>=current_date() and loginTime<date_add(curdate(),interval 1 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i+3 day) and areaId=@AID))*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+4 day) and loginTime>=current_date() and loginTime<date_add(curdate(),interval 1 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i+4 day) and areaId=@AID))*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+5 day) and loginTime>=current_date() and loginTime<date_add(curdate(),interval 1 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i+5 day) and areaId=@AID))*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+6 day) and loginTime>=current_date() and loginTime<date_add(curdate(),interval 1 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i+6 day) and areaId=@AID))*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 MySQL存储select统计学
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<=7 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>=current_date() and loginTime<date_add(curdate(),interval 1 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i day) and areaId=@AID))*100) where createTime>=date_sub(curdate(),interval i day) and createTime<current_date();
update statistics_player_l set twoDayPlayer= (select((select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i+1 day) and loginTime>=current_date() and loginTime<date_add(curdate(),interval 1 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i+1 day) and areaId=@AID))*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+2 day) and loginTime>=current_date() and loginTime<date_add(curdate(),interval 1 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i+2 day) and areaId=@AID))*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+3 day) and loginTime>=current_date() and loginTime<date_add(curdate(),interval 1 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i+3 day) and areaId=@AID))*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+4 day) and loginTime>=current_date() and loginTime<date_add(curdate(),interval 1 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i+4 day) and areaId=@AID))*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+5 day) and loginTime>=current_date() and loginTime<date_add(curdate(),interval 1 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i+5 day) and areaId=@AID))*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+6 day) and loginTime>=current_date() and loginTime<date_add(curdate(),interval 1 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i+6 day) and areaId=@AID))*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 MySQL存储select统计学
select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i day) and loginTime>=current_date() and loginTime<date_add(curdate(),interval 1 day) and areaId=@AID)/(select count(distinct playerId) from log_login where registerTime=date_sub(curdate(),interval i day) and areaId=@AID))*100) where createTime>=date_sub(curdate(),interval i day) and createTime<current_date();