summary_keep_rate

BEGIN  
      /*DECLARE  minDate varchar(20) ;
      DECLARE  maxDate varchar(20) ;*/
      DECLARE  stkDate varchar(20) ;

      DECLARE done int default -1; 
			/* 游标中 注册服务器地址 */  
      DECLARE rgServer VARCHAR(24);  
      /* 声明游标 */  
			DECLARE mCursor CURSOR FOR (select DISTINCT server from yt_log_role_create where server IS  NOT  NULL ORDER BY server ASC);  

			/* 当游标到达尾部时，mysql自动设置done=1 */     
			DECLARE CONTINUE HANDLER FOR NOT found SET done=1;  

      #只有 第30日留存率 都统计过的才放弃重新统计
      SET  @FLAG = (SELECT date FROM yt_keep_rate  WHERE k=30 LIMIT 1,1 ) ;

      IF  minDate = '' OR minDate IS NULL  THEN
				IF maxDate = '' OR maxDate IS NULL THEN
					SELECT  MAX(time) INTO  maxDate FROM yt_log_role_create where server IS  NOT  NULL;
          SELECT  MIN(time) INTO  minDate FROM yt_log_role_create where server IS  NOT  NULL;
					TRUNCATE  TABLE   yt.yt_keep_rate;
        ELSE
          IF @FLAG IS NOT NULL OR @FLAG != '' THEN 
             SELECT   ADDDATE(MAX(date),1) INTO minDate FROM yt_keep_rate WHERE k=30;  
					ELSE
             SELECT  MIN(time) INTO  minDate FROM yt_log_role_create where server IS  NOT  NULL;
          END IF;
        END IF; 
			ELSE
				IF maxDate = '' OR maxDate IS NULL THEN
					SELECT  MAX(time) INTO  maxDate  FROM yt_log_role_create where server IS  NOT  NULL;
        END IF;
      END IF; 

      /*SET stkDate = (SELECT DATE_FORMAT(A,'%Y-%m-%d') FROM  (SELECT  MIN(time)  AS  A  from yt_log_role_login_in) AS T);*/
      SET stkDate = (SELECT DATE_FORMAT(minDate,'%Y-%m-%d'));
      SET @diff = DATEDIFF(stkDate,maxDate); 

      WHILE @diff<0 DO
            SET @likeTime = CONCAT(stkDate,'%');  
						/* 打开游标 */  
			      OPEN mCursor; 

						/* 游标服务器 循环开始 */  
						mLoop: LOOP 
							FETCH mCursor INTO rgServer;  

						IF done = 1 THEN  
                IF  @dayInterval=31 THEN
									SET done = 0;
									LEAVE mLoop;
								END IF; 
						  END IF; 

              /* 查询当天注册的人数 */ 
              SET @rgLikeServer = CONCAT(rgServer,'%');
							SET @registerSum =  (SELECT count(distinct role_id) FROM  yt_log_role_create WHERE time LIKE @likeTime AND server LIKE @rgLikeServer); 

              # dayInterval 表示第几天
							SET @dayInterval = 1;
							WHILE @dayInterval<31 DO 
								 SET  @IntervalFlag =  (select   @dayInterval   in (1,2,3,4,5,6,7,14,30)); 

									/* IntervalFlag 是 1  代表 dayInterval 是    a.time <= @adLikeTime 改 like*/ 
								 IF  @IntervalFlag>0 THEN 
									 SET @afterDate = (SELECT ADDDATE(stkDate,@dayInterval)); 
									 SET @adLikeTime = CONCAT(@afterDate,'%'); 

                   # 初始化为0   SELECT  stkDate,rgServer,@dayInterval,'',@registerSum,0 ;
                   INSERT INTO yt.yt_keep_rate values (stkDate,rgServer,@dayInterval,'',@registerSum,0); 
									 REPLACE INTO yt.yt_keep_rate 
															 (SELECT stkDate,rgServer,@dayInterval,a.server,@registerSum,COUNT(distinct a.role_id) FROM  yt_log_role_login_in as a INNER JOIN yt_log_role_create as r 
																ON  a.role_id=r.role_id
																WHERE  r.time like @likeTime  AND a.time like @adLikeTime AND r.server LIKE @rgLikeServer  GROUP BY a.server);

                   SELECT stkDate,rgServer,@dayInterval,a.server,@registerSum,COUNT(distinct a.role_id) FROM  yt_log_role_login_in as a INNER JOIN yt_log_role_create as r 
																ON  a.role_id=r.role_id
																WHERE  r.time like @likeTime  AND a.time like @adLikeTime AND r.server LIKE @rgLikeServer  GROUP BY a.server;
                   /*select stkDate,@dayInterval,@afterDate,@IntervalFlag,rgServer,@registerSum,@likeTime,@adLikeTime,@rgLikeServer; */
								 END IF; 

                 /*IF  @registerSum >0 THEN 
	                select stkDate,@dayInterval,@afterDate,@IntervalFlag,rgServer,@registerSum ; 
                 END IF;*/ 

								 SET  @dayInterval = @dayInterval+1;
							END WHILE;

            END LOOP mLoop;  

            SET @diff = DATEDIFF(stkDate,maxDate);
            SET stkDate = (SELECT ADDDATE(stkDate,1)); 

            /* 关闭游标 */  
            CLOSE mCursor; 
            /*SELECT stkDate, minDate,maxDate,@diff;*/
      END WHILE;
END