发表于 分类于

游戏上线后，需要统计留存，付费等信息。留存一般关心次日留存，三日留存，周留存和月留存。付费包括平均用户付费等，平均付费用户付费等。

假设我们有以下两个表，分别是用户表（tb_player）和支付订单表（tb_billing_order）:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE `tb_player` (
  `player_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '角色ID',
    ...
  `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
  `last_login_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '上次登录时间',
    ...
  PRIMARY KEY (`player_id`),
  KEY `account` (`account`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `tb_billing_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `player_id` int(11) NOT NULL DEFAULT '0' COMMENT '玩家ID',
    ...
  `income` varchar(32) NOT NULL DEFAULT '' COMMENT '收入',
    ...
  `complete_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '完成时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

所有统计数据都从其中产生。

首先创建一个记录统计数据的表（tb_stat_remain）:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE `tb_stat_remain` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `dnu` int(11) NOT NULL COMMENT '每日新增用户',
  `dau` int(11) DEFAULT NULL COMMENT '每日活跃用户',
  `dpr` int(11) DEFAULT NULL COMMENT '每日充值收入',
  `dpu` int(11) DEFAULT NULL COMMENT '每日充值用户',
  `r2` int(11) DEFAULT NULL COMMENT '次日留存',
  `r3` int(11) DEFAULT NULL COMMENT '三日留存',
  `r7` int(11) DEFAULT NULL COMMENT '周留存',
  `r30` int(11) DEFAULT NULL COMMENT '月留存',
  `stat_time` bigint(20) DEFAULT '0' COMMENT '统计时间',
  `op_time` bigint(20) DEFAULT '0' COMMENT '操作时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

然后创建一个存储过程，用来实际统计数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE PROCEDURE `proc_stat_remain`()
BEGIN
    DECLARE tuts BIGINT DEFAULT UNIX_TIMESTAMP() * 1000;
    DECLARE today DATE DEFAULT func_ctz(tuts);
    DECLARE yuts BIGINT DEFAULT tuts - 86400000 * 1;
    DECLARE yesterday DATE DEFAULT func_ctz(yuts);

    DECLARE d2 INT DEFAULT 1;
    DECLARE d3 INT DEFAULT 2;
    DECLARE d7 INT DEFAULT 6;
    DECLARE d30 INT DEFAULT 29;

    SET @dnu = (SELECT COUNT(*) FROM tb_player WHERE DATEDIFF(yesterday, func_ctz(create_time)) = 0);
    SET @dau = (SELECT COUNT(*) FROM tb_player WHERE DATEDIFF(yesterday, func_ctz(last_login_time)) = 0);
    SET @dpr = (SELECT SUM(income) FROM tb_billing_order WHERE STATUS = 3 AND DATEDIFF(yesterday, func_ctz(complete_time)) = 0);
    SET @dpu = (SELECT COUNT(DISTINCT player_id) FROM tb_billing_order WHERE STATUS = 3 AND DATEDIFF(yesterday, func_ctz(complete_time)) = 0);
    SET @r2 = func_stat_remain(yesterday, d2);
    SET @r3 = func_stat_remain(yesterday, d3);
    SET @r7 = func_stat_remain(yesterday, d7);
    SET @r30 = func_stat_remain(yesterday, d30);

    #SELECT @dnu, @dau, @dpr, @dpu, @r2, @r3, @r7, @r30;

    INSERT INTO tb_stat_remain(dnu, dau, dpr, dpu, stat_time, op_time) VALUES(@dnu, @dau, @dpr, @dpu, yuts, tuts);

    UPDATE tb_stat_remain SET r2 = @r2 WHERE DATEDIFF(yesterday, func_ctz(stat_time)) = d2;
    UPDATE tb_stat_remain SET r3 = @r3 WHERE DATEDIFF(yesterday, func_ctz(stat_time)) = d3;
    UPDATE tb_stat_remain SET r7 = @r7 WHERE DATEDIFF(yesterday, func_ctz(stat_time)) = d7;
    UPDATE tb_stat_remain SET r30 = @r30 WHERE DATEDIFF(yesterday, func_ctz(stat_time)) = d30;
END

计算并插入昨天的付费相关的数据，同时计算并更新与昨天相关的存留相关的数据。其中调用了两个函数：

1
2
3
4
5
6
7
8
CREATE FUNCTION `func_stat_remain`(`yesterday` date, `days`  int) RETURNS double
BEGIN
    SET @number = (SELECT COUNT(*) FROM tb_player WHERE DATEDIFF(yesterday, func_ctz(create_time)) = days AND DATEDIFF(yesterday, func_ctz(last_login_time)) = 0);
    SET @total = (SELECT COUNT(*) FROM tb_player WHERE DATEDIFF(yesterday, func_ctz(create_time)) = days);
    SET @percent = @number / @total * 100;

    RETURN @percent;
END

func_stat_remain 计算与指定日期相隔 days 天的留存率。

1
2
3
4
5
6
7
8
9
10
11
-- ----------------------------
-- Function structure for func_ctz
-- ----------------------------
DROP FUNCTION IF EXISTS `func_ctz`;
DELIMITER ;;
CREATE DEFINER=`catstudiolc2`@`%` FUNCTION `func_ctz`(`milliseconds` bigint) RETURNS datetime
BEGIN
    SET @dt = CONVERT_TZ(FROM_UNIXTIME(milliseconds /1000), "+0:00", "+8:00");

    RETURN @dt;
END

func_ctz 转换毫秒时间为当前时区日期，时区问题在上篇文章阐述过。

最后创建一个事件：

1
CREATE EVENT `event_stat_remain` ON SCHEDULE EVERY 1 DAY STARTS '2015-10-01 16:00:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL proc_stat_remain()

每天零点执行。因为数据库所在时区是UTC（零时区），所以使用 16:00:00，正好是GMT+8（东八区）的零点。也可以在服务器程序里在每天零点调用上面的主存储过程，就不需要这个事件了。

每天产生存留和付费的基础数据，保存在 tb_stat_remain 表中，具体关心的每项数据从中获取计算即可。