欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

mysql统计留存率_MySQL

程序员文章站 2024-01-06 10:06:34
...
bitsCN.com

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 createTime

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_sub(curdate(),interval i day) and createTime

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_sub(curdate(),interval i day) and createTime

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_sub(curdate(),interval i day) and createTime

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_sub(curdate(),interval i day) and createTime

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_sub(curdate(),interval i day) and createTime

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_sub(curdate(),interval i day) and createTime

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_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

相关标签: mysql 统计