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<8 do

    if exists(select * from statistics_player_l where createTime>=date_sub(curdate(),interval i day) and createTime

    update statistics_player_l set neDayPlayer=(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