Mysql statistics retention rate _ MySQL-mysql tutorial

Mysql statistics retention rate bitsCN.com

Mysql statistics retention rate

SQL code

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 = @;

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

Update statistics_player_l set oneDayPlayer = (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 createTime

Update statistics_player_l set twoDayPlayer = (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 createTime

Update statistics_player_l set threeDayPlayer = (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 createTime

Update statistics_player_l set fourDayPlayer = (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 createTime

Update statistics_player_l set fiveDayPlayer = (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 createTime

Update statistics_player_l set sixDayPlayer = (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 createTime

Update statistics_player_l set sevenDayPlayer = (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