用户留存率

出处:点击打开链接         

现在各种app盛行,与此同时,运营app也变得非常重要,关注app用户的留存情况是运营中的重要的一项,那么如何计算app的用户留存率呢?在该功能实现过程中,我使用了mysql的存储过程,每天定时任务执行调用存储过程,以计算每天用户的留存情况:

1、要计算用户的留存率,首先要准备好基础数据表

             基础数据表,就是记录每一天用户的使用app的情况,只要用户在某一天使用了app,那么该用户就是这一天的留存用户,eg:2016-02-13 用户A注册使用app,2016-02-14用户又登录使用了app,那么用户A就是2-14的留存用户,基础数据表就是记录用户A在2-14使用了app,就记录一条数据,这里需要注意的是,用户A在2-14多次登录使用app,在基础表中,我们只记录一次,便于计算和查询。

我设计的基础表结构

  1. CREATE TABLE `stat_remain_log_login` (  
  2.   `userId` varchar(32) DEFAULT NULL COMMENT '用户ID',  
  3.   `last_login_time` datetime DEFAULT NULL COMMENT '最后一次登录时间',  
  4.   `register_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户注册时间',  
  5.   `fromOsType` varchar(100) DEFAULT NULL COMMENT '手机操作系统'  
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
CREATE TABLE `stat_remain_log_login` (
  `userId` varchar(32) DEFAULT NULL COMMENT '用户ID',
  `last_login_time` datetime DEFAULT NULL COMMENT '最后一次登录时间',
  `register_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户注册时间',
  `fromOsType` varchar(100) DEFAULT NULL COMMENT '手机操作系统'
) ENGINE=InnoDB DEFAULT CHARSET=utf8


实际上这张基础表,是通过查询app的操作日志表所得,最后一次登录时间可以理解为用户在一天中最早一次登录的时间,这样便于计算,因为一个用户一天的登录数据我们只记录一次,这样我们得到了基础数据表;

2、设计记录留存率的数据库表

  1. CREATE TABLE `stat_remain` (  
  2.   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',  
  3.   `dru` int(11) NOT NULL COMMENT '每日新增注册用户',  
  4.   `fristDayRate` double(5,2) DEFAULT NULL COMMENT '1天后留存率',  
  5.   `secondDayRate` double(5,2) DEFAULT NULL COMMENT '2天后留存率',  
  6.   `thirdDayRate` double(5,2) DEFAULT NULL COMMENT '3天后留存率',  
  7.   `forthDayRate` double(5,2) DEFAULT NULL COMMENT '4天后留存率',  
  8.   `fifthDayRate` double(5,2) DEFAULT NULL COMMENT '5天后留存率',  
  9.   `sixthDayRate` double(5,2) DEFAULT NULL COMMENT '6天后留存率',  
  10.   `seventhDayRate` double(5,2) DEFAULT NULL COMMENT '7天后留存率',  
  11.   `fourteenthDayRate` double(5,2) DEFAULT NULL COMMENT '14天后留存率',  
  12.   `thirtiethDayRate` double(5,2) DEFAULT NULL COMMENT '30天后留存率',  
  13.   `stat_time` timestamp NOT NULL DEFAULT '2014-01-01 00:00:00',  
  14.   `add_time` timestamp NOT NULL DEFAULT '2014-01-01 00:00:00',  
  15.   `fromOsType` varchar(100) DEFAULT NULL COMMENT '手机操作系统',  
  16.   PRIMARY KEY (`id`)  
  17. ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
CREATE TABLE `stat_remain` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `dru` int(11) NOT NULL COMMENT '每日新增注册用户',
  `fristDayRate` double(5,2) DEFAULT NULL COMMENT '1天后留存率',
  `secondDayRate` double(5,2) DEFAULT NULL COMMENT '2天后留存率',
  `thirdDayRate` double(5,2) DEFAULT NULL COMMENT '3天后留存率',
  `forthDayRate` double(5,2) DEFAULT NULL COMMENT '4天后留存率',
  `fifthDayRate` double(5,2) DEFAULT NULL COMMENT '5天后留存率',
  `sixthDayRate` double(5,2) DEFAULT NULL COMMENT '6天后留存率',
  `seventhDayRate` double(5,2) DEFAULT NULL COMMENT '7天后留存率',
  `fourteenthDayRate` double(5,2) DEFAULT NULL COMMENT '14天后留存率',
  `thirtiethDayRate` double(5,2) DEFAULT NULL COMMENT '30天后留存率',
  `stat_time` timestamp NOT NULL DEFAULT '2014-01-01 00:00:00',
  `add_time` timestamp NOT NULL DEFAULT '2014-01-01 00:00:00',
  `fromOsType` varchar(100) DEFAULT NULL COMMENT '手机操作系统',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8



3、处理计算的留存率的存储过程

  1. DELIMITER $$  
  2.   
  3. USE `entplus`$$  
  4.   
  5. DROP PROCEDURE IF EXISTS `stat_remain_user`$$  
  6.   
  7. CREATE DEFINER=`root`@`%` PROCEDURE `stat_remain_user`(IN startDate DATE)  
  8. BEGIN  
  9. DECLARE i INT;    
  10. SET i = 1;      
  11.     
  12. INSERT INTO stat_remain(dru,stat_time,add_time,fromOsType) SELECT COUNT(DISTINCT userId),DATE_SUB(startDate,INTERVAL 1 DAY),NOW(),fromOsType FROM `entplus`.user WHERE DATEDIFF(startDate,create_time)=1 AND fromOsType = 'android' AND state != '3';   
  13. INSERT INTO stat_remain(dru,stat_time,add_time,fromOsType) SELECT COUNT(DISTINCT userId),DATE_SUB(startDate,INTERVAL 1 DAY),NOW(),fromOsType FROM `entplus`.user WHERE DATEDIFF(startDate,create_time)=1 AND fromOsType = 'iPhone OS' AND state != '3';     
  14.     
  15. WHILE i<30 DO     
  16. IF (TRUE)  THEN    
  17.     IF EXISTS(SELECT * FROM stat_remain WHERE DATEDIFF(startDate,stat_time) =i) THEN    
  18.     UPDATE stat_remain SET fristDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 1-i DAY),last_login_time)=0 AND fromOsType ='android' )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='android' ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='android';      
  19.     UPDATE stat_remain SET secondDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 2-i DAY),last_login_time)=0 AND fromOsType ='android' )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='android' ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='android';      
  20.     UPDATE stat_remain SET thirdDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 3-i DAY),last_login_time)=0 AND fromOsType ='android' )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='android'  ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='android';      
  21.     UPDATE stat_remain SET forthDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 4-i DAY),last_login_time)=0 AND fromOsType ='android' )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='android'  ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='android';      
  22.     UPDATE stat_remain SET fifthDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 5-i DAY),last_login_time)=0 AND fromOsType ='android' )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='android'  ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='android';                                                                     
  23.     UPDATE stat_remain SET sixthDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 6-i DAY),last_login_time)=0 AND fromOsType ='android' )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='android'  ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='android';      
  24.     UPDATE stat_remain SET seventhDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 7-i DAY),last_login_time)=0 AND fromOsType ='android' )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='android' ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='android';      
  25.     UPDATE stat_remain SET fourteenthDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 14-i DAY),last_login_time)=0 AND fromOsType ='android' )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='android'  ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='android';      
  26.     UPDATE stat_remain SET thirtiethDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 29-i DAY),last_login_time)=0 AND fromOsType ='android' )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='android'  ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='android';      
  27.       
  28.     UPDATE stat_remain SET fristDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 1-i DAY),last_login_time)=0 AND fromOsType ='iPhone OS'  )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='iPhone OS' ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='iPhone OS';      
  29.     UPDATE stat_remain SET secondDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 2-i DAY),last_login_time)=0 AND fromOsType ='iPhone OS' )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='iPhone OS' ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='iPhone OS';      
  30.     UPDATE stat_remain SET thirdDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 3-i DAY),last_login_time)=0 AND fromOsType ='iPhone OS'  )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='iPhone OS' ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='iPhone OS';      
  31.     UPDATE stat_remain SET forthDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 4-i DAY),last_login_time)=0 AND fromOsType ='iPhone OS'  )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='iPhone OS' ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='iPhone OS';      
  32.     UPDATE stat_remain SET fifthDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 5-i DAY),last_login_time)=0 AND fromOsType ='iPhone OS'  )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='iPhone OS' ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='iPhone OS';                                                                     
  33.     UPDATE stat_remain SET sixthDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 6-i DAY),last_login_time)=0 AND fromOsType ='iPhone OS'  )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='iPhone OS' ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='iPhone OS';      
  34.     UPDATE stat_remain SET seventhDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 7-i DAY),last_login_time)=0 AND fromOsType ='iPhone OS'  )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='iPhone OS'  ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='iPhone OS';      
  35.     UPDATE stat_remain SET fourteenthDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 14-i DAY),last_login_time)=0 AND fromOsType ='iPhone OS' )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='iPhone OS' ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='iPhone OS';      
  36.     UPDATE stat_remain SET thirtiethDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 29-i DAY),last_login_time)=0 AND fromOsType ='iPhone OS'  )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='iPhone OS' ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='iPhone OS';      
  37.    
  38.     END IF;    
  39. END IF;    
  40. SET i = i + 1;      
  41. END WHILE;   
  42.     END$$  
  43.   
  44. DELIMITER ;  
DELIMITER $$

USE `entplus`$$

DROP PROCEDURE IF EXISTS `stat_remain_user`$$

CREATE DEFINER=`root`@`%` PROCEDURE `stat_remain_user`(IN startDate DATE)
BEGIN
DECLARE i INT;  
SET i = 1;    
  
INSERT INTO stat_remain(dru,stat_time,add_time,fromOsType) SELECT COUNT(DISTINCT userId),DATE_SUB(startDate,INTERVAL 1 DAY),NOW(),fromOsType FROM `entplus`.user WHERE DATEDIFF(startDate,create_time)=1 AND fromOsType = 'android' AND state != '3'; 
INSERT INTO stat_remain(dru,stat_time,add_time,fromOsType) SELECT COUNT(DISTINCT userId),DATE_SUB(startDate,INTERVAL 1 DAY),NOW(),fromOsType FROM `entplus`.user WHERE DATEDIFF(startDate,create_time)=1 AND fromOsType = 'iPhone OS' AND state != '3';   
  
WHILE i<30 DO   
IF (TRUE)  THEN  
    IF EXISTS(SELECT * FROM stat_remain WHERE DATEDIFF(startDate,stat_time) =i) THEN  
    UPDATE stat_remain SET fristDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 1-i DAY),last_login_time)=0 AND fromOsType ='android' )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='android' ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='android';    
    UPDATE stat_remain SET secondDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 2-i DAY),last_login_time)=0 AND fromOsType ='android' )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='android' ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='android';    
    UPDATE stat_remain SET thirdDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 3-i DAY),last_login_time)=0 AND fromOsType ='android' )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='android'  ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='android';    
    UPDATE stat_remain SET forthDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 4-i DAY),last_login_time)=0 AND fromOsType ='android' )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='android'  ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='android';    
    UPDATE stat_remain SET fifthDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 5-i DAY),last_login_time)=0 AND fromOsType ='android' )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='android'  ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='android';                                                                   
    UPDATE stat_remain SET sixthDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 6-i DAY),last_login_time)=0 AND fromOsType ='android' )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='android'  ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='android';    
    UPDATE stat_remain SET seventhDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 7-i DAY),last_login_time)=0 AND fromOsType ='android' )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='android' ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='android';    
    UPDATE stat_remain SET fourteenthDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 14-i DAY),last_login_time)=0 AND fromOsType ='android' )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='android'  ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='android';    
    UPDATE stat_remain SET thirtiethDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 29-i DAY),last_login_time)=0 AND fromOsType ='android' )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='android'  ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='android';    
    
    UPDATE stat_remain SET fristDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 1-i DAY),last_login_time)=0 AND fromOsType ='iPhone OS'  )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='iPhone OS' ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='iPhone OS';    
    UPDATE stat_remain SET secondDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 2-i DAY),last_login_time)=0 AND fromOsType ='iPhone OS' )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='iPhone OS' ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='iPhone OS';    
    UPDATE stat_remain SET thirdDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 3-i DAY),last_login_time)=0 AND fromOsType ='iPhone OS'  )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='iPhone OS' ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='iPhone OS';    
    UPDATE stat_remain SET forthDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 4-i DAY),last_login_time)=0 AND fromOsType ='iPhone OS'  )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='iPhone OS' ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='iPhone OS';    
    UPDATE stat_remain SET fifthDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 5-i DAY),last_login_time)=0 AND fromOsType ='iPhone OS'  )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='iPhone OS' ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='iPhone OS';                                                                   
    UPDATE stat_remain SET sixthDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 6-i DAY),last_login_time)=0 AND fromOsType ='iPhone OS'  )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='iPhone OS' ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='iPhone OS';    
    UPDATE stat_remain SET seventhDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 7-i DAY),last_login_time)=0 AND fromOsType ='iPhone OS'  )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='iPhone OS'  ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='iPhone OS';    
    UPDATE stat_remain SET fourteenthDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 14-i DAY),last_login_time)=0 AND fromOsType ='iPhone OS' )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='iPhone OS' ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='iPhone OS';    
    UPDATE stat_remain SET thirtiethDayRate=(SELECT (SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND DATEDIFF(DATE_ADD(startDate,INTERVAL 29-i DAY),last_login_time)=0 AND fromOsType ='iPhone OS'  )/(SELECT COUNT(DISTINCT userId) FROM stat_remain_log_login WHERE DATEDIFF(startDate,register_time) =i AND fromOsType ='iPhone OS' ))*100 WHERE DATEDIFF(startDate,stat_time) =i AND fromOsType ='iPhone OS';    
 
    END IF;  
END IF;  
SET i = i + 1;    
END WHILE; 
    END$$

DELIMITER ;


第一部分,插入数据语句,从基础数据表中查询数据,计算每天新增用户数目,2016-2-13的时间为例,startDate应该传入2016-02-14,这一天是2-13注册用户留存一天后的时间,使用DATE_SUB(startDate,INTERVAL 1 DAY)方法,记录2-13用户注册时间,而addtime则是创建记录的时间,fromOsType是手机的操作系统,dru是2-13注册的用户数目,两个插入语句是为了区分android和ios;

第二部分则是整体计算的核心部分,我们通过while循环,循环传入i的值,当传入的时间和用户注册时间差为i时,则进入更新语句的执行,通过2-13的数据理解一下更新语句,startDate为2-14,stat_time为2-13,i = 1,那么DATEDIFF(DATE_ADD(startDate,INTERVAL 1-i DAY),last_login_time)=0的含义是2-14等于最后一次登录时间作为限定条件,第二条更新语句则是,DATEDIFF(DATE_ADD(startDate,INTERVAL 2-i DAY),last_login_time)=0,2-13与2-14的差值并不为0,限定条件无法满足,依次类推后续的更细语句都不满足,当startdate为2-15时,i = 2,1-i = -1,第一天更新语句限定条件不满足,第二条则可以满足,这样就能计算出2天后的留存率,依次计算,3、4、5、6、7、14、29等等的留存率情况,作为分母的是,当天的注册用户数,分子是某天注册的用户到某一天还登录的用户数,这样我们就能得到留存率了,在通过操作系统加以区分,得到了两种操作系统分别留存情况。


通过上述过程,我们得到了留存率,查询留存率计算结果表,eg:2-13注册ios用户为5,2-14登录人数为4,那么留存率为80%,2-15登录人数为3,留存率为60%,数据会以阶梯形式展现出来





http://blog.csdn.net/bjlf_1989/article/details/50673357
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页