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

留存数是一款游戏是否成功的重要指标之一。

玩家在某个时间点注册并开始游戏，经过一段时间后，继续游戏的玩家成为是留存。

这部分用户占当时新增用户的比例即是留存率，会按照每隔1单位时间（例日、周、月）来进行统计。

留存用户和留存率体现了游戏的品质。较高的留存率对于游戏开发者来说是成功的标志之一。

例如：

9月5日新增用户3000人，

在这3000人在9月6日登录游戏的人数有1500人,

9月7日登录游戏的人数是1200人，

9月8日登录游戏的人数是300人；

那么9月5日的次日留存率是50%，3日留存率是40%，4日留存率是10%.

玩家留存数统计

留存数的统计方式：

首先需要表log_login来记录每日登陆的用户，考虑到存储过程执行的效率，在log_login表中增加一条字段说明每个玩家的注册时间：

CREATE TABLE IF NOT EXISTS `log_login` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `playerId` int(11) unsigned NOT NULL,
  `lastLoginTime` timestamp NOT NULL DEFAULT '2016-05-31 16:00:00',
  `createTime` timestamp NOT NULL DEFAULT '2016-05-31 16:00:00',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=29 ;

然后需要表stat_remain记录每日的新增用户，次日留存，三日留存和七日留存等。

CREATE TABLE `stat_remain` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `dru` int(11) NOT NULL,
  `second_day` decimal(11,3) DEFAULT NULL,
  `third_day` decimal(11,3) DEFAULT NULL,
  `seventh_day` decimal(11,3) DEFAULT NULL,
  `fourteen_day` decimal(11,3) DEFAULT NULL,
  `thirtieth_day` decimal(11,3) DEFAULT NULL,
  `stat_time` timestamp NOT NULL DEFAULT '2017-06-01 00:00:00',
  `add_time` timestamp NOT NULL DEFAULT '2017-06-01 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

创建一个存储过程stat_remain_player， 存储过程在每天的0点0分1秒左右去执行。该存储过程新增当天的注册用户并更新之前的留存数据。

BEGIN
declare today date default CURDATE();
declare yesterday date default date_sub(today, interval 1 day);
declare days_ago_2 date default date_sub(today, interval 2 day);
declare days_ago_3 date default date_sub(today, interval 3 day);

insert into stat_remain(dru, stat_time, add_time)
select count(id) , yesterday, now()
from account
where createTime between UNIX_TIMESTAMP(yesterday) and UNIX_TIMESTAMP(today);

-- 修改前天的2日留存
update stat_remain set second_day = (
select(
(select count(distinct id) from log_login where (createTime between days_ago_2 and yesterday) and (lastLoginTime between yesterday and today))
/
(select count(distinct id) from log_login where (createTime between days_ago_2 and yesterday))
)
) where stat_time = days_ago_2;

-- 修改大前天的3日留存
update stat_remain set third_day = (
select(
(select count(distinct id) from log_login where (createTime between days_ago_3 and days_ago_2) and (lastLoginTime between yesterday and today))
/
(select count(distinct id) from log_login where (createTime between days_ago_3 and days_ago_2))
)
) where stat_time = days_ago_3;


-- 7日留存
update stat_remain set seventh_day = (
 select(
(select count(distinct id) from log_login where (createTime between days_ago_7 and days_ago_6) and (lastLoginTime between yesterday and today))
/
(select count(distinct id) from log_login where (createTime between days_ago_7 and days_ago_6))--
)
) where stat_time = days_ago_7;
END