游戏玩家的留存率统计SQL实现

sea_bug

　　玩家在某段时间内注册开始游戏，经过一段时间后，仍然继续游戏的被认作是留存；这部分用户占当时新增用户的比例即是留存率，会按照每隔1单位时间（例日、周、月）来进行统计。顾名思义，留存指的就是“有多少玩家留下来了”。留存用户和留存率体现了应用的质量和保留用户的能力。

次日留存率	首次登陆后第二天登录游戏用户/统计日的注册用户数
三日留存率	首次登陆后第三天登陆过的用户/统计日的注册用户数
七日留存率	首次登陆后第七天登录过游戏的用户/统计日的注册用户数
三十日留存数	首次登陆后第三十天登录过游戏的用户/统计日的注册用户数

留存率 在不同的游戏中 算法不一样

留存率说明


　　某时间内的新增用户，经过一段时间后，仍继续登录游戏的被认作时留存用户；这部分用户占当时新增用户的比例即是留存率。


例如：
9月5日新增用户200，这200人在6日登录游戏的有100人,7日登录有80人，8日登录有50人；
则9月5日次日留存率是50%，3日留存率是40%，4日留存率是25%。


这是我们游戏里的计算方式

这样统计 有科学根据的
比如 哪天 你开广告了 就可以看 他带来的用户质量
还有 这样的留存 数据 也会好看的

1. -- 登录日志  
   
2. DROP TABLE IF EXISTS log_login;  
   
3. CREATE TABLE log_login(  
   
4.   id INT (11) UNSIGNED NOT NULL AUTO_INCREMENT,  
   
5.   player_id INT(11) UNSIGNED NOT NULL,  
   
6.   last_login_time timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',  
   
7.   register_time timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',   
   
8.   PRIMARY KEY (id)  
   
9. )ENGINE=MYISAM DEFAULT CHARSET=utf8;  

复制代码

log_login的数据在每个玩家登陆的时候产生一条，为了接下去的存储过程执行效率的考虑，冗余了每个玩家的注册时间。

1. -- 统计留存率  
   
2. DROP TABLE IF EXISTS stat_remain;  
   
3. CREATE TABLE stat_remain(  
   
4.   id INT (11) UNSIGNED NOT NULL AUTO_INCREMENT,  
   
5.   dru INT(11) NOT NULL, -- 每日新注册用户  
   
6.   second_day INT(11) DEFAULT NULL,  
   
7.   third_day INT(11) DEFAULT NULL,  
   
8.   seventh_day INT(11) DEFAULT NULL,  
   
9.   thirtieth_day INT(11) DEFAULT NULL,   
   
10.   stat_time timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',  
    
11.   add_time timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',   
    
12.   PRIMARY KEY (id)  
    
13. )ENGINE=MYISAM DEFAULT CHARSET=utf8;

复制代码

1. DELIMITER $  
   
2. -- 统计留存率  
   
3. DROP PROCEDURE IF EXISTS stat_remain_player$  
   
4. CREATE PROCEDURE stat_remain_player()  
   
5. BEGIN  
   
6. declare i int;  
   
7. SET i = 1;   
   
8.   
   
9. insert into stat_remain(dru,stat_time,add_time) select count(a_qid),date_sub(curdate(),interval 1 day),now() from tb_fish where datediff(now(),add_time)=1;  
   
10.   
    
11.   
    
12. while i<30 do   
    
13. if (i=1) or (i=2) or (i=6) or (i=29) then  
    
14.     if exists(select * from stat_remain where datediff(curdate(),stat_time) =i) then                                                                 
    
15.     update stat_remain set second_day=(select (select count(distinct player_id) from log_login where datediff(curdate(),register_time) =i and datediff(date_add(curdate(),interval 1-i day),last_login_time)=0)/(select count(distinct player_id) from log_login where datediff(curdate(),register_time) =i))*100 where datediff(curdate(),stat_time) =i;   
    
16.     update stat_remain set third_day=(select (select count(distinct player_id) from log_login where datediff(curdate(),register_time) =i and datediff(date_add(curdate(),interval 2-i day),last_login_time)=0)/(select count(distinct player_id) from log_login where datediff(curdate(),register_time) =i))*100 where datediff(curdate(),stat_time) =i;   
    
17.     update stat_remain set seventh_day=(select (select count(distinct player_id) from log_login where datediff(curdate(),register_time) =i and datediff(date_add(curdate(),interval 6-i day),last_login_time)=0)/(select count(distinct player_id) from log_login where datediff(curdate(),register_time) =i))*100 where datediff(curdate(),stat_time) =i;   
    
18.     update stat_remain set thirtieth_day=(select (select count(distinct player_id) from log_login where datediff(curdate(),register_time) =i and datediff(date_add(curdate(),interval 29-i day),last_login_time)=0)/(select count(distinct player_id) from log_login where datediff(curdate(),register_time) =i))*100 where datediff(curdate(),stat_time) =i;   
    
19.     end if;  
    
20. end if;  
    
21. SET i = i + 1;   
    
22. end while;   
    
23. END  
    
24. $  
    
25.   
    
26. DELIMITER ;  

复制代码

　　stat_remain_player 存储过程在每新的一天的0点0分1秒左右去执行，生成stat_remain数据，并对离当天stat_time差距为1天，2天，6天和29天的记录进行更新。

流子

QQ:41157121

h860254962

请问下面这条语句 的 tb_fish 怎么定义, 存放的是什么数据呢?
insert into stat_remain(dru,stat_time,add_time) select count(a_qid),date_sub(curdate(),interval 1 day),now() from tb_fish where datediff(now(),add_time)=1;

请楼主把完整的 sql 语句提供一下. 感谢!