活跃留存存储过程

BEGIN
DECLARE i INT;
DECLARE startCurrDate DATETIME;
DECLARE endCurrDate DATETIME;
DECLARE nextStarCurrDate DATETIME;
  DECLARE nextEndCurrDate DATETIME;


  DECLARE tnextStarCurrDate DATETIME;
  DECLARE tnextEndCurrDate DATETIME;


  DECLARE myStarCurrDate DATETIME;


  
DECLARE currDay VARCHAR(10);


DROP TABLE IF EXISTS `tmp_keep_analys`;
CREATE TEMPORARY  TABLE `tmp_keep_analys` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`day`int(11) NOT NULL,
`gameOperator` varchar(200) DEFAULT NULL,
`count`int(11) NOT NULL,
`type`int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


SET i = 37;
  SET myStarCurrDate = CURDATE() - INTERVAL i DAY;
WHILE i > 0 DO
SET startCurrDate = CURDATE() - INTERVAL i DAY;
SET endCurrDate = INTERVAL 1 DAY + startCurrDate;
SET currDay = FROM_UNIXTIME(UNIX_TIMESTAMP(startCurrDate), '%Y%m%d');


TRUNCATE TABLE `tmp_keep_analys`;


/*活跃人数*/
INSERT INTO `tmp_keep_analys` (`type`, `day`,`gameOperator`,`count`)
SELECT 
1,currDay,10000,COUNT(DISTINCT a.player)
FROM 
dbtest2 a
WHERE 
a.tm >= startCurrDate
AND 
a.tm < endCurrDate
    AND
     (a.room = 4085 OR a.room = 4086 )
;


    /*新增人数*/
    SET nextStarCurrDate = myStarCurrDate;
    SET nextEndCurrDate = startCurrDate ;
INSERT INTO `tmp_keep_analys` (`type`, `day`,`gameOperator`,`count`)
    SELECT 
 2,currDay,10000,COUNT(DISTINCT a.player)
FROM 
dbtest2 a
WHERE 
      a.tm >= startCurrDate
    AND
      a.tm < endCurrDate 
    AND
      (a.room = 4085 OR a.room = 4086 )
AND 
a.player NOT IN (
SELECT 
DISTINCT b.player
FROM 
dbtest2 b
WHERE  
  b.tm >= nextStarCurrDate 
        AND
          b.tm < nextEndCurrDate 
        AND
         (b.room = 4085 OR b.room = 4086 )
)
;

    /*次日留存*/
    SET nextStarCurrDate = myStarCurrDate;
    SET nextEndCurrDate = startCurrDate ;


    SET tnextStarCurrDate = endCurrDate;
    SET tnextEndCurrDate = INTERVAL 1 DAY + tnextStarCurrDate;
INSERT INTO `tmp_keep_analys` (`type`, `day`,`gameOperator`,`count`)
    SELECT 
 3,currDay,10000,COUNT(DISTINCT tt.player)
    FROM 
       (SELECT 
     DISTINCT a.player
   FROM 
     dbtest2 a
   WHERE 
            a.tm >= startCurrDate 
        AND
            a.tm < endCurrDate 
        AND
           (a.room = 4085 OR a.room = 4086 )
   AND 
    a.player NOT IN 
            (SELECT 
      DISTINCT b.player
    FROM 
      dbtest2 b
    WHERE  
        b.tm >= nextStarCurrDate 
             AND
                 b.tm < nextEndCurrDate 
             AND
                (b.room = 4085 OR b.room = 4086 ))


       ) tt
    WHERE
       tt.player IN (
        SELECT 
DISTINCT bb.player
FROM 
dbtest2 bb
WHERE  
  bb.tm >=  tnextStarCurrDate
        AND
          bb.tm < tnextEndCurrDate 
        AND
         (bb.room = 4085 OR bb.room = 4086 )
       )
    ;




    /*3日留存*/
    SET nextStarCurrDate = myStarCurrDate;
    SET nextEndCurrDate = startCurrDate ;


    SET tnextStarCurrDate = INTERVAL 3 DAY + startCurrDate;
    SET tnextEndCurrDate = INTERVAL 1 DAY + tnextStarCurrDate;
INSERT INTO `tmp_keep_analys` (`type`, `day`,`gameOperator`,`count`)
    SELECT 
 4,currDay,10000,COUNT(DISTINCT tt.player)
    FROM 
       (SELECT 
     DISTINCT a.player
   FROM 
     dbtest2 a
   WHERE 
            a.tm >= startCurrDate 
        AND
            a.tm < endCurrDate 
        AND
           (a.room = 4085 OR a.room = 4086 )
   AND 
    a.player NOT IN 
            (SELECT 
      DISTINCT b.player
    FROM 
      dbtest2 b
    WHERE  
        b.tm >= nextStarCurrDate 
             AND
                 b.tm < nextEndCurrDate 
             AND
                (b.room = 4085 OR b.room = 4086 ))


       ) tt
    WHERE
       tt.player IN (
        SELECT 
DISTINCT bb.player
FROM 
dbtest2 bb
WHERE  
  bb.tm >=  tnextStarCurrDate
        AND
          bb.tm < tnextEndCurrDate 
        AND
         (bb.room = 4085 OR bb.room = 4086 )
       )
    ;


    /*7日留存*/
    SET nextStarCurrDate = myStarCurrDate;
    SET nextEndCurrDate = startCurrDate ;


    SET tnextStarCurrDate = INTERVAL 7 DAY + startCurrDate;
    SET tnextEndCurrDate = INTERVAL 1 DAY + tnextStarCurrDate;
INSERT INTO `tmp_keep_analys` (`type`, `day`,`gameOperator`,`count`)
    SELECT 
 5,currDay,10000,COUNT(DISTINCT tt.player)
    FROM 
       (SELECT 
     DISTINCT a.player
   FROM 
     dbtest2 a
   WHERE 
            a.tm >= startCurrDate 
        AND
            a.tm < endCurrDate 
        AND
           (a.room = 4085 OR a.room = 4086 )
   AND 
    a.player NOT IN 
            (SELECT 
      DISTINCT b.player
    FROM 
      dbtest2 b
    WHERE  
        b.tm >= nextStarCurrDate 
             AND
                 b.tm < nextEndCurrDate 
             AND
                (b.room = 4085 OR b.room = 4086 ))


       ) tt
    WHERE
       tt.player IN (
        SELECT 
DISTINCT bb.player
FROM 
dbtest2 bb
WHERE  
  bb.tm >=  tnextStarCurrDate
        AND
          bb.tm < tnextEndCurrDate 
        AND
         (bb.room = 4085 OR bb.room = 4086 )
       )
    ;


    /*14日留存*/
    SET nextStarCurrDate = myStarCurrDate;
    SET nextEndCurrDate = startCurrDate ;


    SET tnextStarCurrDate = INTERVAL 14 DAY + startCurrDate;
    SET tnextEndCurrDate = INTERVAL 1 DAY + tnextStarCurrDate;
INSERT INTO `tmp_keep_analys` (`type`, `day`,`gameOperator`,`count`)
    SELECT 
 6,currDay,10000,COUNT(DISTINCT tt.player)
    FROM 
       (SELECT 
     DISTINCT a.player
   FROM 
     dbtest2 a
   WHERE 
            a.tm >= startCurrDate 
        AND
            a.tm < endCurrDate 
        AND
           (a.room = 4085 OR a.room = 4086 )
   AND 
    a.player NOT IN 
            (SELECT 
      DISTINCT b.player
    FROM 
      dbtest2 b
    WHERE  
        b.tm >= nextStarCurrDate 
             AND
                 b.tm < nextEndCurrDate 
             AND
                (b.room = 4085 OR b.room = 4086 ))


       ) tt
    WHERE
       tt.player IN (
        SELECT 
DISTINCT bb.player
FROM 
dbtest2 bb
WHERE  
  bb.tm >=  tnextStarCurrDate
        AND
          bb.tm < tnextEndCurrDate 
        AND
         (bb.room = 4085 OR bb.room = 4086 )
       )
    ;


DELETE FROM `role_keep_analys` WHERE `day`= currDay;
INSERT INTO `role_keep_analys` (`day`,`gameOperator`) SELECT `day`,`gameOperator` FROM `tmp_keep_analys` GROUP BY `day`,`gameOperator`;

/*活跃人数*/
UPDATE `role_keep_analys` a,`tmp_keep_analys` b SET a.`loginCount` = b.`count` WHERE a.`day` = b.`day` AND a.`gameOperator` =  b.`gameOperator` AND b.`type` = 1;

    /*新增人数*/
UPDATE `role_keep_analys` a,`tmp_keep_analys` b SET a.`addCount` = b.`count` WHERE a.`day` = b.`day` AND a.`gameOperator` =  b.`gameOperator` AND b.`type` = 2;
    
    /*次日留存*/
UPDATE `role_keep_analys` a,`tmp_keep_analys` b SET a.`keepRate1` = b.`count` WHERE a.`day` = b.`day` AND a.`gameOperator` =  b.`gameOperator` AND b.`type` = 3;
    UPDATE `role_keep_analys` a,`tmp_keep_analys` b SET a.`keepRate3` = b.`count` WHERE a.`day` = b.`day` AND a.`gameOperator` =  b.`gameOperator` AND b.`type` = 4;
UPDATE `role_keep_analys` a,`tmp_keep_analys` b SET a.`keepRate7` = b.`count` WHERE a.`day` = b.`day` AND a.`gameOperator` =  b.`gameOperator` AND b.`type` = 5;
    UPDATE `role_keep_analys` a,`tmp_keep_analys` b SET a.`keepRate` = b.`count` WHERE a.`day` = b.`day` AND a.`gameOperator` =  b.`gameOperator` AND b.`type` = 6;






    SET i = i -1;
END WHILE;

DROP TABLE IF EXISTS `tmp_keep_analys`;
SELECT rka.`day` as 日期, rka.loginCount as 活跃, rka.addCount as 新曾, rka.keepRate1 as 次留存,  (rka.keepRate1 * 100 / rka.addCount) as 次留率
         , rka.keepRate3 as 3留存,  (rka.keepRate3 * 100 / rka.addCount) as 3留率 , rka.keepRate7 as 7留存,  (rka.keepRate7 * 100 / rka.addCount) as 7留率
          , rka.keepRate as 14留存,  (rka.keepRate * 100 / rka.addCount) as 14留率
  FROM `role_keep_analys` rka;
END